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?

Vince
  • 20,017
  • 15
  • 45
  • 64
AngocA
  • 131
  • 3

1 Answers1

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.

user30184
  • 65,331
  • 4
  • 65
  • 118
  • 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 Nov 13 '22 at 03:48