DE<>GIS Automations - Phase 1

[damonmcc]

This phase covers scoping, exploration, and trying discussed approaches on a couple of datasets (likely to be MIH and COLP).

Glossary

packaging - data and metadata transformations that happen prior to push to other data stores (e.g. Socrata) flow - a movement of data. It could be a push or a pull. metadata -

• attachments (pdfs, etc.),
• file-level (ie baked into shapefiles),
• external system data (ie Socrata Legislative Compliance section, or contact info)

Tasks

• [x] #353
• [x] #393
• [x] #427
• [x] #426
• [x] #268

[damonmcc]

OTI PDF which details Open Data self-publishing option: link

[alexrichey]

Also shared, DCAT standards

[alexrichey]

Takeways from meeting with GIS on Oct 13.

• Open Data Guidelines lists several ways to upload data to OpenData/Socrata. There are two or three options (sdks/publishing api/rest api) that we're going to POC.
• We'll focus first on pushing data from AGOL (ArcGIS Online) -> OpenData/Socrata because this could be the most standardized and repeatable data flow, due to AGOL's enforced data standards.
• Then we'll try going from Digital Ocean to OpenData/Socrata. This may be more difficult because data on Digital Ocean are largely shapefiles.
• We have a test dataset for MIH (Mandatory Inclusionary Housing) here. We'll push to this for testing.
• This sprint will be largely exploratory work.

[alexrichey]

Notes:

• SDE is an ESRI Spatial Database Engine, which is effectively git for geographers.
• ~80% of data at GDE is accounted for by this path.

The scope for this initial phase is to draw a line straight from AGOL to OpenData. Currently OTI receives OpenData updates via email, and OTI would like very much to have this process automated.

[alexrichey]

Options for pushing to OpenData

• Socrata Gateway Agent (a running process on our servers) that will configure itself by phoning home (to Socrata) and push data on a schedule. It exposes data syncing functionality, but not metadata. Conceivably you could run this on a machine behind our firewall to sync data to Socrata. Probably not worth due to the limitations of syncing just data.

• Socrata-py Fully featured python client. Docs say it hits the Socrata Data Management API but it hits the publishing REST API as well. Our best option. I've tried pushing datasets, metadata, etc. and it's straightforward.

• Publishing API Umbrella for a few different APIs (SODA Producer API, Dataset Management API, Import API)

• Metadata API

Misc

• sodapy - Dead Python Client
• Socrata DataSync - Java jar you can download and run. Basically a GUI layer over the data API. Not worth it, due to limitations.
• Connectors Proprietary connectors for Socrata and other sources. Plugins are largely declarative, and one exists for ESRI. Built by a partner company, and you need an account with them, so it's probably not worth the time.

[alexrichey]

Agenda:

Progress So Far (Ie, last sprint's work)

• Worked with GIS + Amanda to understand the current state of things.
• Assessed OpenData options (see above)
• POC'd pulling from ESRI feature server and pushing to Socrata. (Well, almost. Having some weirdness pulling from ESRI for the MIH dataset. Can export features for individual layers, but the zips I'm exporting are empty. This needs a little more poking around to figure out, but it doesn't worry me)

Next Steps

• Now that we've reasonably proofed things out, and I understand the current state at DCP, I'd like to revisit the original proposal (daisy chaining ESRI -> OpenData). If we still think that's the best option, then let's go ahead and implement a Github Action to sync data to OpenData.

• However, I wonder about a different option to simplify the flow of data, namely pushing from SDE back to Digital Ocean, then fanning out to OpenData and ESRI at the same time. From the perspective of Data, Metadata, and versioning, this seems ideal. But as I understand it, interaction with SDE/arcpy is somewhat auto-magical. (To Simplify: Should we Fan-Out, or Daisy-Chain?) Also to consider: There's some time pressure from OTI to automate things. So that might inform our decision about what's a first good step.

What I mean by "Fan Out"

[alexrichey]

Notes from DE <> GIS meeting (10/23/2023)

Question: Can we implement the fanout diagrammed above?

Next Steps:

• Prove out pushing to AGOL
• Exercise: take a handful of Data Engineering's products, and map formats.
  • start with: housing, cpdb, pluto, facdb
  • what metadata goes with diff products.

Notes / Questions:

• Filenames on Bytes may change imminently (ie in the next month+)
• DE needs scoped AGOL creds
• Alex Finckel would be able to help pulling from AGOL (OTI)
• Data dictionaries don't have spatial details, e.g. projections. ESRI metadata already has it.
• • Design should think about metadata on shapefiles
• Wrinkle: have to name differently for DO and destinations
• Tommy (former intern) wrote PHP app to package for ESRI. Investigate *

[croswell81]

Adding two diagrams of GIS open data process: Original:

More recent: when @jackrosacker returns he can add the other diagram.

[croswell81]

@alexrichey FYI - Alex from OTI open data team shared the following resources:

• Tyler Metadata API information: https://socratametadataapi.docs.apiary.io/#
• Socrata Support page: https://support.socrata.com/hc/en-us

[caseysmithpgh]

@alexrichey Here's the updated diagram. I did my best to include bits from the whiteboard yesterday, and synthesize some of our thoughts on "phases". Looking forward to hearing your thoughts once we're both back in the office.

[alexrichey]

Next Steps [As of Nov 20]:

OpenData

• replace metadata on push (Attachments)
• figure out versions/revisions weirdness
• QA what happens when data / column names / etc are changed.
• can we check/in and then push the column metadata (e.g. field descriptions)

[alexrichey]

Next Steps:

• Finish Code
• First iteration of storing metadata. -- extracting existing metadata from socrata, then converting into DCP representation.
• Figure out where attachments are stored, so they can be picked up by the socrata push.
• Casey show off DO csv filtering
• Matt: follow up with ITD about DE ownership/acces to datasets.
• For datasets that are just links e.g MapPluto, check metadata. Make sure we aren't pushing data.

Questions:

• Is there a comprehensive list of DCP datasets that existing on Socrata?

[croswell81]

@alexrichey to answer the question, Is there a comprehensive list of DCP datasets on Socrata? - unfortunately its not that straightforward since there is not a one-to-one match of all data on BYTES of the BIG APPLE (BOBA) and NYC Open Data portal.

1. This is OTI's list they generate as all datasets on the NYC Open Data portal, which can be filtered by agency

• I feel like this is not comprehensive
• Some data is not uploaded to portal, and only a link back to BOBA

2. Recommend you use this list, which is our list we use to track open data updates and send to OTI. We just added open data UID and URL as fields in this table.

[alexrichey]

Next Steps

Checklist for datasets

• [ ] Is it in DO? If so where
• [ ] Is pushing to DO?

Process for GIS to onboard datasets to push to DO

• [ ] Adding Product metadata
• [ ] Adding product do edm-distributions

Get Other DE Team members involved

Planning Metadata for Products

• With Amanda, GIS, and DE
• Figure out data formats, location