inventory your geodata and map services - esri · inventory your geodata and map services ... •...

Inventory Your GeoData and Map ServicesUSING SQL SERVER AND PYTHON

Metropolitan Transportation Commission

• MPO for 9-County San Francisco Bay Area

• Regional Transportation Plan • A blueprint for the development of mass transit, highway, freight, bicycle and

pedestrian facilities

• GIS Team of 4 supports Planning, Finance and Operations• Cartography• Geographic analysis• Web development• Data collection• DB Replication/Management

• Types of data:• Census• Tom Tom MultiNet• Transit Route and Stop• Bike routes• Parking• Managed Lanes• Electric Vehicle Fueling• Freeway Service Patrol• Vehicle Miles Traveled• Clipper Card• Inrix TMC

GIS at MTC

• System Architecture(All AWS Cloud Servers):

GIS at MTC

G-DriveLayer Files

Copies of GISDB1 data for Web Services

Main data repository Production Maps Server

DevelopmentMaps Server

Open Data (official data)

Custom Applications

AGOL (unofficial temp maps)

GISDB1 DB Server RDS

Problems

• Open Data bulk uploads

• Integrating Metadata Updates into a fast-paced environment

• Open up Metadata to employees outside GIS to access/search/contribute

• Track feature class updates through workflow

• Identify feature classes tied to Map Services

• Search for data on all servers (Search metadata)

Integrate Metadata Updates into WorkflowUSING PYTHON AND ARCPY

Integrate Metadata Updates into Workflow

PART 1

• Gatekeeper for adding data to main repository

• Narrow metadata fields to our minimum standards• Title• Tags• Purpose• Abstract• Data Credits• Use Limitations• Contact Info

Integrate Metadata Updates into Workflow

PART 2

• Metadata Toolkit Python/ArcPy

• Create tool to import feature data that enforces metadata updates

• Create Python Script tool to update metadata outside of Catalog’s editing interface

• Uses Validation

• Uses XSLT: arcpy.XSLTransform_conversion

Searching for Data and Expanding Access to MetadataUSING SQL SERVER AND PYTHON

Searching and Metadata

PART 1: SQL/SDE Solutions

• Search for data on all servers

• Determine which servers a dataset is on

• Compare row counts of the same dataset on different servers/dbs

• Search metadata

• SQL Server and XPath

gdb.Documentation.value('(*/dataIdInfo/idCitation/resTitle)[1]', 'nvarchar(max)') AS Title

Metadata View

Dataset Locator View

Searching and Metadata

PART 2: Python Solutions

• Map Service Inventory

• Layer Files Inventory

• Python:• os module:

• walk

• path

• arcpy package:• InsertCursor

• Mapping

• Layer

• dataSource/datasetName

• MapDocument

• ListDataFrames

• ListLayers

• dataSource/datasetName

Map Services in SQL

Layer Library Table in SQL

Searching and Metadata

PART 3: Inventory

• Search Data, Layer files, and Map Services

• View Metadata

• Edit/Update Metadata

• Add data to ArcMap or AGOL

DEMO

Inventory Application

Inventory Application

Inventory Application

Inventory Application

Inventory Application

Inventory Application

View Map Service in AGOL

Inventory Application

• Data and Services• SQL Server• ESRI

• ArcGIS Server, ArcSDE, ArcMap, AGOL, ArcPy/Python

• Backend Code• Node.js

• Frontend Code• Bootstrap, HTML5, CSS, jQuery, Kendo UI

Technology:

Open Data Bulk Upload

• ESRI Github AGOL Admin Tools

• Bulk load map services to AGOL• Python

• auto-calculate values for CSV file

Solutions

• Open Data bulk uploads

• Integrating Metadata Updates into a fast-paced environment

• Open up Metadata to employees outside GIS to access/search/contribute

• Track feature class updates through workflow

• Identify feature classes tied to Map Services

• Search for data on all servers (Search metadata)

Thank you!Metropolitan Transportation Commission

GIS AnalystSolana Foosfoo@mtc.ca.gov