I'd like to write the contents of a PostGIS table (or query) to GeoJSON using ogr2ogr.

How can I select an attribute and have it be used as a top-level id in the output GeoJSON, without losing it as a property?

Given some PostGIS data:

    city_id int,
    entity_id int,
    geom geometry(Point, 4326)

INSERT INTO things VALUES (3, 19, 'SRID=4326;POINT (4 8)');

And an ogr2ogr command:

ogr2ogr -f "GeoJSON" things.geojson PG:"dbname=postgres" ***magic*** things

I'd like to see:

    "type": "FeatureCollection",
    "crs": { 
        "type": "name", 
        "properties": { 
            "name": "urn:ogc:def:crs:OGC:1.3:CRS84" 
    "features": [
        "type": "Feature",
        "id" : 19,
        "properties": { 
            "city_id": 3,  
            "entity_id": 19 
        "geometry": { 
             "type": "Point", 
              "coordinates": [ 4.0, 8.0 ] 

Note that the value of entity_id was copied to the top level, but the attribute was not removed from the properties section.

I've tried the following:

  1. Set primary key to entity_id then run ogr2ogr with -preserve_fid. This gives me a top-level "id" but removes entity_id from the properties.

  2. Use SQL to copy the entity_id to another column, and then instruct ogr2ogr to use this as the id.

    ogr2ogr -f "GeoJSON" things.geojson PG:"dbname=postgres" --config PGSQL_OGR_FID=fizz -preserve_fid -sql "SELECT *, entity_id AS fizz FROM things"

In this case, I get no top-level id.

Tried this on GDAL 1.11.2 and current master.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.