A lightweight node api and frontend for quickly previewing PostGIS queries. Pull Requests Welcomed! Take a look at the open issues
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:
ST_asGeoJson(), copy and paste the geojson into geojson.io
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.
.env.sampleto include your
DATABASE_URL, rename it
geom, and that it contains WGS84 geometries. See #17 for some discussion on how to allow for other geom column names and SRIDs.