26

I'd like to do a GeoJSON dump of a PostGIS table, but I want to export features (the geometry and the properties) not just the geometry. I've been digging into the ST_AsGeoJSON function but it looks like I can only get the coordinate information (which makes sense since it's a geometry function).

For example:

select st_asgeojson(the_geom) from street_centerline limit 1;

Returns:

{"type":"MultiLineString","coordinates":[[[-65.591776562805038,41.682190576167052],[-65.591998971284028,41.682082119060382],[-65.592001213509064,41.682081025737766],[-65.593689871787177,41.681257533373952],[-65.595415661879244,41.680415888937219],[-65.595440519465640,41.680403765889309],[-65.595603134242481,41.680324459445771]]]}

I'm curious if anyone knows of a simple way to get the property information as well. Has anyone written a pgsql2geojson script yet?

1
  • Is PostGIS able to leverage the improvements in PostgreSQL 9.2? As postgres now natively supports 9.2, is anyone aware of a similar development in PostGIS? Would be nice to do without the ogr2ogr segway. – user12212 Oct 24 '12 at 14:53
29

For completeness here's an actual example with ogr2ogr:

ogr2ogr -f GeoJSON out.json \
  "PG:host=localhost dbname=gis user=ubuntu password=toomanysecrets" \ 
  -sql "select way,name,amenity from planet_osm_point a where a.amenity is not null"

Make sure your user has read access to the geometry_columns table.

Specifically:

GRANT SELECT ON geometry_columns to ubuntu;
GRANT SELECT ON geography_columns to ubuntu;
GRANT SELECT ON spatial_ref_sys to ubuntu;
2
  • I'm curious about this output. I mean, since this is required to output geojson files, isn't the output have to be out.geojson and not out.json ??? I'm new to this. Thank you – Stefanos Feb 1 '17 at 18:55
  • 2
    Well, that's just the filename. There isn't really a standard on that - some systems expect .json (because, well, they are JSON files), other systems expect .geojson, others don't care. – Steve Bennett Feb 2 '17 at 4:50
23

Just to provide info on an old question if someone encounters this:

as of PostgreSQL 9.5+

SELECT jsonb_build_object(
    'type',     'FeatureCollection',
    'features', jsonb_agg(feature)
)
FROM (
  SELECT jsonb_build_object(
    'type',       'Feature',
    'id',         gid,
    'geometry',   ST_AsGeoJSON(geom)::jsonb,
    'properties', to_jsonb(row) - 'gid' - 'geom'
  ) AS feature
  FROM (SELECT * FROM input_table) row) features;

Taken from full original answer here

1
  • 1
    WHAT THE ACTUAL F... I've looked for years for a way to perform a SELECT * EXCEPT COLUMNS (a,b) and you had the answer all along... – ffflabs Feb 8 '19 at 16:23
19

Here is an obvious solution to my own question - ogr2ogr! I'm not sure why I didn't think of it sooner. It has both PostGIS and GeoJSON drivers so this should work great.

Not the answer you're looking for? Browse other questions tagged or ask your own question.