We want to ensure that the zoning OpenCounter presents to your residents and business community matches the most updated zoning on record with the City.

To meet this goal, OpenCounter uses an in-house Extraction-Transformation-Load (ETL) solution to consume spatial data from cities, transform it into the structure required by OpenCounter, and validate data quality before loading it into OpenCounter.

OpenCounter ETL (OETL) enables us to maintain parity between the zoning data in your city's spatial database and the zoning data in OpenCounter with minimal effort. Updates are scheduled on a recurring basis, and validations ensure that new updates won't cause issues in OpenCounter.

This article is an overview of how OpenCounter ETL works, and how we use it in our workflow.

If you are a new client city, please read our recommendations on preparing your city's zoning data to ensure the smoothest transition between your systems and OpenCounter.

Extraction



OpenCounter ETL consumes JSON data served by a Map Service or Feature Service. Most of our client cities are Esri customers using ArcServer to manage spatial data, and a few use Accela to manage other workflows. ArcServer and Accela provide Map/Feature Services, which they call REST services or endpoints.

For example, the service endpoint for zoning in Las Cruces, NM is located at: https://maps.las-cruces.org/arcgis/rest/services/LandUsePlanning/Zoning_Service/MapServer/1/

During the extraction process, OETL launches a flood of parallel requests—up to 20 at a time—to the service endpoint provided by the city. OETL requests each feature in JSON format, and requests that the map projection is 4326, also known as WGS84.

Transformation



Once we extract the layer, we immediately transform the data. Each transformation is a set of SQL queries that together prepare the spatial data for a smooth transition into OpenCounter's databases.

The first time we extract and transform a new client city's spatial data, we upload the data into a spatial database and determine what SQL queries need to be executed in order to best prepare the data for OpenCounter. We then save those queries as a transformation script which runs every time we re-extract.

Usually, these scripts are idempotent, meaning they can be run an infinite number of times and result in the same output. However, when we need to alter spatial data (which can be avoided by following the guide here), the script becomes non-idempotent.

Validate

After we transform the data, we want to ensure that the data meets certain quality standards. We write a set of data validations into our scripts that run after the transformation, that tell us whether the data is ready to be loaded into OpenCounter.

For example, we'll usually check that there are no missing geometries, that the number of features is within a reasonable range, and that certain city-specific data quality issues (like misspellings, etc.) are all resolved.

We're also able to see how the latest update to the city's zoning data compares to the zoning data already loaded in OpenCounter. If we notice that the latest update creates or deletes zones, we may reach out to the city to make sure this is supposed to be the case.

Load



The last step in this process is exporting the zoning data to be loaded into the OpenCounter zoning database. At present, we simply generate a GeoJSON file from the spatial database, then upload it to OpenCounter.

Loading in this fashion can be done any number of times, so long as the zone codes and names line up. Incoming geospatial data will replace the existing geospatial data, but will preserve clearances on that zone if the code and name match.

Workflow

To facilitate spatial data updates, OETL allows us to extract data layers on a recurring basis. Using OETL, we're able to pull in updates to your spatial zoning data regularly and ensure the data meets standards.

Many cities provide zoning layers with several thousand multipolygon features. We're able to extract this data in less than two minutes. The transformation and validation script takes another second or two, and immediately gives us feedback on whether the data validations are passing.

By using OETL, we maintain parity between your most up-to-date zoning data and the zoning we present to OpenCounter users.

Did this answer your question?