A lightweight node api and frontend for quickly previewing PostGIS queries. Pull Requests Welcomed! Take a look at the open issues
Take a look a the open issues. Come chat on Gitter if you have questions. Create a Pull Request on the
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
- Create a
.envfile in the root of the repo based on
DATABASE_URLwith a connection string for your database (
- Install dependencies
- Run the express app
yarn start, or
yarn devstartif you are developing and want the server to restart on file changes.
- Load the frontend
- Query like a boss, see your geoms on the map!
- PostGIS preview expects your geometry column to be called
geom, and that it contains WGS84 geometries.
Optional Feature Requirements
At some time in the near future, this app will allow the option to serve mvt locally, instead of geojson. However, for that to work, you will need the following:
- PostGIS 2.4 (and PostGres 10); and,