GIS publishing from your permit database

Most permit systems already know where each permit is. The address was geocoded at intake, or the parcel carries a point. Yet the public permit map is often weeks out of date, because getting the data onto it is a manual export.

The usual routine: run a report, save it as a spreadsheet, fix the columns, import it into a GIS project, publish. It takes an hour when it goes well, so it happens monthly.

What "publishing" really needs

Strip it down and a permit map needs four things, every night:

• The permits that changed since last time, and only those.
• A latitude and longitude for each.
• A point in the shape the map layer expects.
• An add or update on the feature layer.

That's a short, repeatable flow. Nothing about it needs a person.

Step 1: read only what changed

Use a read-only query against the permit database, filtered on the last change time. In SentraBridge, a SQL query can refer to :last_success_at, the time of the last good run, so each night picks up exactly where the last one ended:

SELECT permit_number AS ref, permit_type, status, address,
       ST_Y(geom) AS lat, ST_X(geom) AS lng, updated_at
FROM permits
WHERE status = 'issued'
  AND updated_at > COALESCE(CAST(:last_success_at AS timestamp), now() - interval '1 day')

If your permits are in PostGIS, ST_Y and ST_X pull latitude and longitude out of the stored point. Custom queries must be a single SELECT and run read-only, so a typo can't change the permit table.

Step 2: make the point

Map layers don't want two loose numbers. They want a geometry. The Make map point step builds one from the latitude and longitude fields, in one of three shapes:

• An ArcGIS feature, with geometry and attributes, ready for a feature layer.
• A GeoJSON point field, for web maps and open-data portals.
• WKT text, such as POINT(-119.78 36.74), for databases that take it.

Points use WGS 84 (SRID 4326), plain latitude and longitude, which is what most web maps expect. Records without coordinates can be dropped here, so a permit with no location doesn't fail the whole run.

Step 3: add or update features

The ArcGIS feature layer connector can add features and update existing ones. For a permit layer, adding issued permits is often enough. If permits change after issue, update by the permit number instead, so the map shows one point per permit rather than a trail of duplicates.

Here is the ready-made version: nightly at 2 am, read issued permits, make the point, add to the layer.

Going further: one read, three outputs

Once permits are flowing, the same read can feed more than the map. The permit pipeline example reads changed permits once, cleans and checks them, then branches:

• Issued permits go onto the ArcGIS map.
• Finaled permits are totaled by type, with valuation, and emailed to the building department.
• Expired permits are written to a dated CSV on SFTP for another system.

Writing back into PostGIS

Publishing goes both ways. If your city keeps its own spatial database, a flow can upsert records into a PostGIS table and build the geometry column from latitude and longitude as it writes. The 311-to-database example does exactly that for service requests, so the GIS team can join them to council districts or work zones in their own tools.

Why this matters beyond GIS

A current map answers questions before they become phone calls. Residents can see what's been permitted on their street. Council offices can see activity by district. Inspectors can plan routes from real data.

How SentraBridge does it

In SentraBridge, this is a few steps on a drag-and-drop canvas: read from PostgreSQL/PostGIS, MySQL or SQL Server; make the point; send to an ArcGIS feature layer. Permit records pass through in memory and aren't stored by SentraBridge. Preview shows what each step would send to the layer, and writes nothing. See GIS for the details.