One Metadata to Rule Them All
NEARC - Groton, CT
October 7, 2014
Sean Sweeney, GIS Programmer Analyst
Rachel Sheketoff, Web Developer
City of Cambridge, MA
When we recently built our new GIS website, we were searching for a way to better manage our
metadata. At the time we had a data maintenance book in Word documents, plus a separate HTML data
dictionary for our old website in addition to the standard SDE metadata. All of these were carrying
similar information but needed to be edited individually. With the prospect of posting some of our
data on GitHub, ArcGIS Online, and our Socrata data portal, we were looking at even more places to edit.
In this presentation, we will demo our new data dictionary, which is generated directly from the XML
metadata in our ArcSDE database, and we’ll show the process we used to build it. We’ll also discuss
our other ongoing efforts to consolidate our metadata into one single source.
Rachel
Metadata XML
...
This data set derives from several sources. The...
Updated parcel layer with the 2014 land use data.
...
ML
Map-Lot: This a unique parcel identifier found in...
...
Metadata is stored in the SDE database in XML format. Our thought was that we could write code to
extract and parse this XML and use the contents to populate the metadata in other locations.
GitHub Export
installDir = arcpy.GetInstallInfo("desktop")["InstallDir"]
translator = installDir + "Metadata/Translator/ARCGIS2FGDC.xml"
source = database + schema + featureClass
if featureDataset != '':
source = database + schema + featureDataset + "/" + source
dest = dest_root + "\\" + dest_path + "\\" + dest_name
if not arcpy.Exists(source):
print "ERROR: " + source + " Not Found"
else:
arcpy.gp.esri2open_esri2open(source, dest + ".geojson", "Default")
arcpy.ExportMetadata_conversion(source, translator, dest + ".xml")
GitHub was the low hanging fruit, so we tackled that first. We are providing the metadata on GitHub
in XML format, so we simply export the XML using a GP tool when we are exporting the data.
On GitHub, the XML metadata file is just stored alongside the data.
The City of Philadelphia has taken this one step further and created ta tool they call
Arc-Open , which
creates a GitHub standard Markdown file to add rich text formatting to their GitHub data. This
is an upgrade we may consider in the future.
Data Dictionary
The next output destination for our metadata is our GIS Data Dictionary on our website. The
process we are using for this one is more complex and is tied tightly to our content management
system (CMS), but the concepts should work for any HTML output target.
We have a configuration table that specifies some source and destination information for each
data set. We loop through each data set and use eXtensible Stylesheet Language Transformation (XSLT)
to translate the sections of the XML metadata that we need to the proper HTML tags for our data
dictionary pages.
Configuration Table
Source Dataset
Source Name
Destination Name
Destination Path
On GitHub
In Data Dictionary
Updated
CDD_Zoning
ZoningDistricts
CDD_ZoningDistricts
CDD\Zoning_Districts
1
1
1
LANDMARK_ElderlyFacilities
LANDMARK_ElderlyFacilities
Landmark\Elderly_Facilities
1
1
1
RECREATION_CommunityGardens
RECREATION_CommunityGardens
Recreation\Community_Gardens
1
1
1
UrbanForestry_Trees
DPW_StreetTrees
DPW\Trees
1
1
1
We have a configuration table which is also stored in our SDE database and is used to set the
source dataset and feature class and the destination name and path for output. This is the same
configuration table we use for our GitHub data and metadata export, so there are columns to allow
each layer to be in one or the other or both. The last column lets us know if the data and/or
metadata has been updated and needs to be re-exported (we have another script that helps us populate
that field based on the SDE MVTABLES_MODIFIED table).
SQL Access to SDE Metadata
SELECT Documentation
FROM sde.GDB_ITEMS
WHERE PhysicalName LIKE '%.RECREATION_OpenSpace'
The SQL query to get the metadata out of the SDE database is quite simple. The metadata is stored as
a field in the GDB_ITEMS table, and the layer can be specified by its PhysicalName (the one that shows
up in SQL Server Management Studio).
Sample of eXtensible Stylesheet Language Transformation (XSLT) File
data:image/jpeg;base64,
350px
...
...
The XSLT file contains the bulk of the information required to translate the metadata from XML into
HTML for our site.
Transformation in C#
//do the transformation
XslCompiledTransform xslt = new XslCompiledTransform();
xslt.Load(Settings.GetSetting("GisDataDictionary.XslFileLoc"));
StringWriter sW = new StringWriter();
using (XmlWriter xmlW = XmlWriter.Create(sW, xslt.OutputSettings))
{
xslt.Transform(xmlR, null, xmlW);
returnValue = sW.ToString();
}
With the XSLT file in place, the actual C# code for the translation becomes very short, using
the .NET XslCompiledTransform() object.
This is an example of the final result - our GIS Data Dictionary pages with
the content automatically generated from the SDE metadata.
ArcGIS Online
The ArcGIS Online process is a little different. Here we are using the ArcGIS Online REST API to
update the item's information page. Since we are not really going from one XML-based format to
another in this case, we do not use XSLT. Instead we are parsing the XML in Python to pull out the
parts we need and using that information to generate the AGOL REST calls. We use the same
configuration table that is used for the other outputs.
SQL Access to SDE Metadata and Config in Python
sde_conn = arcpy.ArcSDESQLExecute(server, instance, database, user, password)
sde_return = sde_conn.execute(sql)
Since we were already using ArcPy we took a shortcut and used the ArcSDESQLExecute class to
connect to the SDE database and query the metadata and the configuration file, as opposed to
using a standard Python SQL library.
ArcGIS REST API Calls
/sharing/generateToken
/sharing/rest/search
/sharing/rest/content/users/{username}/{folderId}/items/{itemId}/update
Updating the item
def updateItemDescription(userID, folderID, itemID, portalURL, token=None, snippet, tags):
'''Update the item descriptive information.'''
params = {
'snippet': snippet, # Summary
'description': description, # Includes HTML attrib table
'tags' : tags,
'token' : token,
'f': 'json'
}
requestURL = portalURL + '/sharing/rest/content/users/' + userID + '/' + \
folderID + '/items/' + itemID + '/update'
print requestURL
f = urllib.urlopen(requestURL, urllib.urlencode(params));
results = f.read();
print results
return results
This function is used to update the descriptive information (metadata) for an AGOL item. There is some
prep work that needs to go into setting up the inputs that is not shown here. In particular, the three
ID values must be acquired for the given data set. These can all be obtained from an AGOL REST query
that returns item information given the data set name.
One other thing to note here is that the description field can take certain HTML tags for formatting
the output. We are using the various table tags to make the attribute table.
This is what the resulting Item Detail looks like on AGOL. The same information is passed through
to ArcGIS for Open Data.
At the moment, Socrata is the only platform that we don't have a solution for at all. There is
talk of a major update coming in the spring of 2015 that may have some promise, but until then the
metadata on Socrata needs to be updated by hand.