311 requests in the city database
Every 15 minutes, new and changed Sentra 311 requests are upserted into a PostGIS table with a map point.
DatabasesConnector
PostgreSQL and PostGIS integration
for city data
PostgreSQL, often with the PostGIS extension, is a common city database for GIS data, reporting copies and in-house applications. Many GIS teams keep their authoritative layers in PostGIS.
Pre-built connector
PostgreSQL / PostGIS
- Tier
- Pre-built connector
- Category
- Databases
- Sign-in
- Database login over TLS
- Paging
- single response
- Sandbox
- Yes: practice with made-up data
- Default port
- 5432
§ Overview
What connecting PostgreSQL does.
SentraBridge reads tables and views, writes with insert, update or upsert, and builds PostGIS points from latitude and longitude. Flows can start when rows change, with a changed-time column or LISTEN/NOTIFY. Records pass through SentraBridge; only counts and IDs are logged.
- Tables and views: Read a table or a read-only SELECT; insert, update or upsert rows.
- Changed rows: Read rows changed since the last run, or start flows on LISTEN/NOTIFY.
- PostGIS geometry: Build points from latitude and longitude in the spatial reference you choose.
What SentraBridge can do with PostgreSQL / PostGIS
| resource | read | write | changes only | fields |
|---|---|---|---|---|
| Tables | read a table or a read-only query | insert, update, upsert | yes (changed-time column) | Any table you grant |
§ Use cases
What cities connect PostgreSQL for.
Bank payment file to a table
Payment CSVs from the bank's SFTP folder are cleaned, checked and loaded each morning, and the files archived.
Work orders back to 311
Work orders created or changed in the database create matching 311 requests, so phone and inspection work shows up.
Permits to ArcGIS
Permits issued since the last run are read from the database and added as points on an ArcGIS layer.
Nightly CSV backup
A permits table is exported nightly to a dated CSV in S3.
§ Examples
Flows that use PostgreSQL / PostGIS.
Sentra 311 → Sentra Scheduler → PostgreSQL / PostGIS → Esri ArcGIS feature layer → SFTP server
311 triage: route every request to the right system
Cleans and checks changed 311 requests, sends bad ones to a data-quality sub-flow, checks the city database to see if each is new, then routes new…
PostgreSQL / PostGIS → Amazon S3 / S3-compatibleNightly backup: a database table to CSV in S3
Every night, exports a city database table (here: permits) to a dated CSV file in S3, so there's always a plain-text copy outside the database.
SFTP server → PostgreSQL / PostGISLoad the bank's daily payment file into the city database
Every morning, reads new payment CSV files from the bank's SFTP folder, cleans and checks them, loads them into a database table, and archives the…
§ How it works
How to connect PostgreSQL
- 01Get accessCreate a PostgreSQL role for SentraBridge.
- 02Add the connectionGrant SELECT on read tables and INSERT/UPDATE only on write tables.
- 03Preview real recordsEnter host, port (5432 by default), database and password.
- 04Turn it onUse the relay if the database is on the internal network.
§ Often connected with
PostgreSQL and the rest of city hall.
Systems cities commonly connect with PostgreSQL, each with its own connector page.
Sentra 311
SentraQueue
Sentra Scheduler§ FAQ
PostgreSQL integration questions
Can SentraBridge write PostGIS geometry?
Yes. The Write to database step builds a point from latitude and longitude fields in the spatial reference you choose.
Can a flow start when a PostgreSQL row changes?
Yes. Use a changed-time column for polling, or PostgreSQL LISTEN/NOTIFY to start the flow when a row changes.
Is it safe to point at a production database?
Read queries run in a read-only transaction, table and column names are checked first, and the SentraBridge user only has the grants you give it.
§ Next
Related connectors
Connect PostgreSQL / PostGIS to the rest.
We'll set up a workspace with your systems in a sandbox and build your first flow with you.