PostGIS Preview

A lightweight node api and frontend for quickly previewing PostGIS queries. Pull Requests Welcomed! Take a look at the open issues

Why

Our team at the NYC Department of City Planning needed to be able to test out PostGIS queries in a local environment and iterate quickly. CartoDB provides this functionality, giving users a SQL pane and a map view to quickly see the geometries returned from the database (This UI and SQL preview workflow are inspired by the CartoDB editor)

When asking on Twitter if anyone had solutions to this problem, responses included:

• Run queries in pgadmin and use ST_asGeoJson(), copy and paste the geojson into geojson.io
• Use QGIS dbmanager. This works, but requires a few clicks once the data are returned to add them to the map.
• Use various command line tools that show previews in the terminal or send the results to geojson.io programmatically.

How it works

The express.js app has a single endpoint: /sql that is passed a SQL query q as a url parameter. That query is passed to PostGIS using the pg-promise module. The resulting data are transformed into topojson using a modified dbgeo module (modified to include parsing WKB using the WKX module), and the response is sent to the frontend.

The frontend is a simple Bootstrap layout with a Leaflet map, CartoDB basemaps, a table, and a SQL pane. The TopoJSON from the API is parsed using omnivore by Mapbox, and added to the map as an L.geoJson layer with generic styling.

How to Use

• Clone this repo
• Have a PostGIS instance running somewhere that the node app can talk to
• Edit .env.sample to include your DATABASE_URL, rename it .env
• Install dependencies npm install
• Run the express app node server.js
• Load the frontend http://localhost:4000
• Query like a boss

Notes

• PostGIS preview expects your geometry column to be called geom, and that it contains WGS84 geometries. See #17 for some discussion on how to allow for other geom column names and SRIDs.