30

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?

Improve this question
1
  • 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, 2012 at 14:53

3 Answers 3

Reset to default
32

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

Improve this answer
3
  • 4
    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, 2019 at 16:23
  • 1
    omg. Justa came across this, to_jsonb(row) - 'gid' - 'geom' is gold
    – arredond
    Oct 22, 2021 at 11:41
  • I'm having some problems with the - operator ("Delete key/value pair or string element from left operand"). But it's working well with the related #- operator. That is, I'm using: to_jsonb(row) #- '{gid}' #- '{geom}'
    – Paulo Vieira
    Jan 26, 2022 at 18:40
30

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;
Improve this answer
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, 2017 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, 2017 at 4:50
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.

Improve this answer

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