3

I am downloading and importing all countries geometries from OpenStreetMap, processing the files with osmtogeojson and importing into PostgreSQL with ogr2ogr. However, Taiwan is retrieved with a very long row, and I cannot import it into the database.

What can I do?

These are the steps to reproduce the issue.

cat << EOF > query
ut:json];
rel(449220);
(._;>;);
out; 
EOF

wget -O 449220.json --post-file=query "https://overpass-api.de/api/interpreter"

osmtogeojson 449220.json > 449220.geojson

ogr2ogr -f "PostgreSQL" PG:"dbname=postgres user=myuser" "449220.geojson" -nln import -overwrite

And I got this:

Resolving overpass-api.de (overpass-api.de)... 178.63.48.217, 178.63.11.215, 2a01:4f8:120:6464::2, ...
Connecting to overpass-api.de (overpass-api.de)|178.63.48.217|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [application/json]
Saving to: ‘449220.json’

449220.json                                     [   <=>                                                                                      ] 111.00K   187KB/s    in 0.6s    

2022-11-05 00:19:49 (187 KB/s) - ‘449220.json’ saved [113669]

ERROR 1: COPY statement failed.
ERROR:  row is too big: size 8616, maximum size 8160
CONTEXT:  COPY import, line 16

What can I do? Is there a way to modify the row size limit in PostgreSQL? Create another tablespace? Or can I do something at the OSM side to split the row? Is there any way to simplify the geometry?

1
  • 1
    Instead of using ogr2ogr you could use osm2pgsql to transfer yout OSM extract directly to PostgreSQL Commented Nov 5, 2022 at 15:26

1 Answer 1

1

By the answer in https://stackoverflow.com/questions/61137111/postgres-copy-error-row-is-too-big-size-8216-maximum-size-8160#comment108159091_61137656 it seems that you must either compile your PostgeSQL by yourself to allow very long rows of attribute data or to remove some of the OSM attributes.

Ogr2ogr supports only selecting certain attributes with SQL, not discarding some attributes. Therefore the SQL would be tedious to write -SQL "select geometry, attr_1, attr_2, attr_3 .... from ....

I suggest to try if ogr2ogr can convert the data into GeoPackage. Then you could use standard SQLite tools for dropping some of the columns ALTER TABLE ... DROP COLUMN .... As a final step convert GeoPackage into PostGIS.

1
  • In addition to this ^ answer, ogr2ogr supports a -simplify option that supposedly preserves topology, which will be especially important for a polygon dataset. Discussed in a different GIS.SE answer here. I imagine the -simplify option takes units relative to the dataset's coordinate system. Be aware of this because if your data is in a GCS (i.e. WGS84) it might be awkward to set the simply tolerance.
    – elrobis
    Commented Nov 13, 2022 at 3:48

Your Answer

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

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