6

I am using ogr2ogr to import geojson files into a PostGIS table. The table has two geography columns ("footprint" and "centroid"). The geojson files contain polygon features, and I wish to import these into the footprint column. All other columns have useful defaults or are nullable.

Unfortunately when I do this, the footprint geography is always imported as NULL.

If I remove the centroid column (so I only have one geography column) it works perfectly with something like:

ogr2ogr -nln spatial.buildings -update -append -gt 65536  -f "PostgreSQL" PG:"<my connection string>"   "RhodeIsland.geojson"

Using ogrinfo I was able to find that the geography was being imported as ogr_geometry. So I tried renaming it using the SQL parameter:

ogr2ogr -nln spatial.buildings -update -append -gt 65536 -sql "SELECT ogr_geometry AS footprint FROM RhodeIsland" -f "PostgreSQL" PG:"<my connection info"   "RhodeIsland.geojson"

But this doesn't work either. (as an aside, the sql parameter could prove useful to do a better job with the other fields)

Does ogr2ogr Postgres import support tables with multiple geography columns and, if so, how do I specify them?

Note: I'm appending data to the table. This is NOT a new table.

Improve this question
2
  • 1
    For geometry column name, can be ogr_geometry or geometry. See answer gis.stackexchange.com/a/296749/638 to understand the difference
    – ThomasG77
    Jun 15, 2021 at 0:38
  • Yes I found "ogr_geometry" from ogrinfo which was reporting ogr_geometry for the name. @ThomasG77's answer appears to be working. Compared to my code this overrides the dialect to SQLite (my understanding is that my use of Postgres results in ogr2ogr defaulting to postgres dialect) and uses "geometry".
    – winwaed
    Jun 15, 2021 at 13:08

1 Answer 1

Reset to default
5

You may try the following (tested)

ogr2ogr -nln spatial.buildings -dialect SQLite -sql "SELECT geometry AS footprint FROM RhodeIsland" -update -append -gt 65536  -f "PostgreSQL" PG:"<my connection string>" "RhodeIsland.geojson"

Using -sql and -dialect SQLite, you are able to remap geometry column correctly

You can in insert polygon and centroid in one go by changing the SQL query from above to the following

ogr2ogr -nln spatial.buildings -dialect SQLite -sql "SELECT geometry AS footprint, centroid(geometry) AS centroid FROM RhodeIsland" -update -append -gt 65536  -f "PostgreSQL" PG:"<my connection string>" "RhodeIsland.geojson"

Edit: For history, as it's possible to fill both polygon geometry and it centroid

I suppose you are already aware that you can execute the update to the centroid after loading in PostGIS with ogrinfo using something like

ogrinfo -sql "your update query"  PG:"<my connection string>"
Improve this answer
7
  • 1
    My reading of the question is that OP is aware that there's only one geometry in the GeoJSON, but the target table has two (geography columns); OP still wants to import the GeoJSON and only populate the "footprint" geometry/geography, perhaps to later update the centroid geometry/geography exactly as you suggest.
    – alphabetasoup
    Jun 14, 2021 at 23:53
  • Thanks. Updated my answer
    – ThomasG77
    Jun 15, 2021 at 0:34
  • Thanks - it looks like it was the SQLite dialect option that I was missing. Although I was also using ogr_geometry (divined from ogrinfo, and ogr2ogr didn't complain...).
    – winwaed
    Jun 15, 2021 at 18:10
  • Yes the centroid needs updating. Whilst trying to find the fix, I did see ogrinfo being used with SQL which is useful to know for the future; but at the moment I'm actually using psycopg2.
    – winwaed
    Jun 15, 2021 at 18:12
  • and it looks like it might be possible to add the centroid update in my initial ogr2ogr call which definitely saves steps...
    – winwaed
    Jun 15, 2021 at 18:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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