This question already has an answer here:

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?

marked as duplicate by Midavalo May 24 '17 at 17:37

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 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

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;
  • 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
  • 1
    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

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.

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

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