ogr2ogr error when loading table simplified_water_polygons

[MBabakov]

Ubuntu 18.04 PostgreSQL 12

Execute: osm2pgsql -d gis --create --slim -G --hstore --tag-transform-script /home/osm/src/openstreetmap-carto/openstreetmap-carto.lua -C 2500 --number-processes 5 -S /home/osm/src/openstreetmap-carto/openstreetmap-carto.style /home/osm/data/kaliningrad-latest.osm.pbf It's normal

gis=# \dt List of relations Schema | Name | Type | Owner --------+--------------------+-------+---------- public | external_data | table | postgres public | planet_osm_line | table | postgres public | planet_osm_nodes | table | postgres public | planet_osm_point | table | postgres public | planet_osm_polygon | table | postgres public | planet_osm_rels | table | postgres public | planet_osm_roads | table | postgres public | planet_osm_ways | table | postgres public | spatial_ref_sys | table | osm (9 rows)

Actual behavior

postgres@ub1:/home/osm/src/openstreetmap-carto$ scripts/get-external-data.py INFO:root:Checking table simplified_water_polygons CRITICAL:root:ogr2ogr returned 1 with layer simplified_water_polygons CRITICAL:root:Command line was ogr2ogr -f PostgreSQL -lco GEOMETRY_NAME=way -lco SPATIAL_INDEX=FALSE -lco EXTRACT_SCHEMA_FROM_LAYER_NAME=YES -nln loading.simplified_water_polygons PG:dbname=gis data/simplified_water_polygons/simplified-water-polygons-split-3857/simplified_water_polygons.shp CRITICAL:root:Output was

Traceback (most recent call last): File "scripts/get-external-data.py", line 243, in main subprocess.check_output(ogrcommand, stderr=subprocess.PIPE, universal_newlines=True) File "/usr/lib/python3.6/subprocess.py", line 356, in check_output **kwargs).stdout File "/usr/lib/python3.6/subprocess.py", line 438, in run output=stdout, stderr=stderr) subprocess.CalledProcessError: Command '['ogr2ogr', '-f', 'PostgreSQL', '-lco', 'GEOMETRY_NAME=way', '-lco', 'SPATIAL_INDEX=FALSE', '-lco', 'EXTRACT_SCHEMA_FROM_LAYER_NAME=YES', '-nln', 'loading.simplified_water_polygons', 'PG:dbname=gis', 'data/simplified_water_polygons/simplified-water-polygons-split-3857/simplified_water_polygons.shp']' returned non-zero exit status 1.

During handling of the above exception, another exception occurred:

Traceback (most recent call last): File "scripts/get-external-data.py", line 258, in main() File "scripts/get-external-data.py", line 249, in main raise RuntimeError("ogr2ogr error when loading table {}".format(name)) RuntimeError: ogr2ogr error when loading table simplified_water_polygons

What have I not done?

[pnorman]

What happens if you run ogr2ogr -f PostgreSQL -lco GEOMETRY_NAME=way -lco SPATIAL_INDEX=FALSE -lco EXTRACT_SCHEMA_FROM_LAYER_NAME=YES -nln loading.simplified_water_polygons PG:dbname=gis data/simplified_water_polygons/simplified-water-polygons-split-3857/simplified_water_polygons.shp by hand?

[MBabakov]

postgres@ub1:~$ ogr2ogr -f PostgreSQL -lco GEOMETRY_NAME=way -lco SPATIAL_INDEX=FALSE -lco EXTRACT_SCHEMA_FROM_LAYER_NAME=YES -nln loading.simplified_water_polygons PG:dbname=gis data/simplified_water_polygons/simplified-water-polygons-split-3857/simplified_water_polygons.shp
FAILURE:
Unable to open datasource `data/simplified_water_polygons/simplified-water-polygons-split-3857/simplified_water_polygons.shp' with the following drivers.
  -> `PCIDSK'
  -> `netCDF'
  -> `JP2OpenJPEG'
  -> `PDF'
  -> `ESRI Shapefile'
  -> `MapInfo File'
  -> `UK .NTF'
  -> `OGR_SDTS'
  -> `S57'
  -> `DGN'
  -> `OGR_VRT'
  -> `REC'
  -> `Memory'
  -> `BNA'
  -> `CSV'
  -> `NAS'
  -> `GML'
  -> `GPX'
  -> `LIBKML'
  -> `KML'
  -> `GeoJSON'
  -> `Interlis 1'
  -> `Interlis 2'
  -> `OGR_GMT'
  -> `GPKG'
  -> `SQLite'
  -> `OGR_DODS'
  -> `ODBC'
  -> `WAsP'
  -> `PGeo'
  -> `MSSQLSpatial'
  -> `OGR_OGDI'
  -> `PostgreSQL'
  -> `MySQL'
  -> `OpenFileGDB'
  -> `XPlane'
  -> `DXF'
  -> `CAD'
  -> `Geoconcept'
  -> `GeoRSS'
  -> `GPSTrackMaker'
  -> `VFK'
  -> `PGDUMP'
  -> `OSM'
  -> `GPSBabel'
  -> `SUA'
  -> `OpenAir'
  -> `OGR_PDS'
  -> `WFS'
  -> `SOSI'
  -> `HTF'
  -> `AeronavFAA'
  -> `Geomedia'
  -> `EDIGEO'
  -> `GFT'
  -> `SVG'
  -> `CouchDB'
  -> `Cloudant'
  -> `Idrisi'
  -> `ARCGEN'
  -> `SEGUKOOA'
  -> `SEGY'
  -> `XLS'
  -> `ODS'
  -> `XLSX'
  -> `ElasticSearch'
  -> `Walk'
  -> `Carto'
  -> `AmigoCloud'
  -> `SXF'
  -> `Selafin'
  -> `JML'
  -> `PLSCENES'
  -> `CSW'
  -> `VDV'
  -> `GMLAS'
  -> `TIGER'
  -> `AVCBin'
  -> `AVCE00'
  -> `HTTP'

[MBabakov]

And:

root@ub1:/home/osm/src/openstreetmap-carto# ll data/simplified_water_polygons/simplified-water-polygons-split-3857/
total 30088
drwxr-xr-x 2 postgres postgres     4096 Nov 10 21:36 ./
drwxr-xr-x 3 postgres postgres     4096 Nov 10 21:36 ../
-rw-r--r-- 1 postgres postgres        6 Nov 10 21:36 simplified_water_polygons.cpg
-rw-r--r-- 1 postgres postgres   172974 Nov 10 21:36 simplified_water_polygons.dbf
-rw-r--r-- 1 postgres postgres      425 Nov 10 21:36 simplified_water_polygons.prj
-rw-r--r-- 1 postgres postgres 30495216 Nov 10 21:36 simplified_water_polygons.shp
-rw-r--r-- 1 postgres postgres   115372 Nov 10 21:36 simplified_water_polygons.shx

[SomeoneElseOSM]

I wonder if what's happening here is the same as https://github.com/gravitystorm/openstreetmap-carto/issues/4249 ? ne_110m_admin_0_boundary_lines_land.zip has moved and is now at https://naciscdn.org/naturalearth/110m/cultural/ne_110m_admin_0_boundary_lines_land.zip ; as a temporary workaround you can try changing the URL in get_external_data.yml , rerun "scripts/get_external_data.py" and see if everything then works?

[MBabakov]

Maybe. Thanks, but I was very limited in time, so I put Ubuntu on 20.04 and everything worked on it normally. If this problem occurs again, I will check your suggestion. Thanks again for your time!

[audour]

Hi, First thanks for maintaining openstreetmap-carto which is essential.

I'm currently working on upgrading https://github.com/Magellium/osmtilemaker to use the last current version of openstreetmap-carto (5.3.1) and I face a similar issue running get-external-data.py, which was initially documented here : https://github.com/Magellium/osmtilemaker/issues/4

When I run get-external-data.py -d $DB_NAME -U $DB_USER, I got the following error:

INFO:root:Checking table simplified_water_polygons
CRITICAL:root:ogr2ogr returned 1 with layer simplified_water_polygons
CRITICAL:root:Command line was ogr2ogr -f PostgreSQL -lco GEOMETRY_NAME=way -lco SPATIAL_INDEX=FALSE -lco EXTRACT_SCHEMA_FROM_LAYER_NAME=YES -nln loading.simplified_water_polygons "PG:dbname=osmtilemaker user=osmtilemaker" data/simplified_water_polygons/simplified-water-polygons-split-3857/simplified_water_polygons.shp
CRITICAL:root:Output was

Traceback (most recent call last):
  File "/docker_mounted_volumes/working_dir/openstreetmap-carto/scripts/get-external-data.py", line 275, in main
    ogrcommand, stderr=subprocess.PIPE, universal_newlines=True)
  File "/usr/lib/python3.7/subprocess.py", line 395, in check_output
    **kwargs).stdout
  File "/usr/lib/python3.7/subprocess.py", line 487, in run
    output=stdout, stderr=stderr)
subprocess.CalledProcessError: Command '['ogr2ogr', '-f', 'PostgreSQL', '-lco', 'GEOMETRY_NAME=way', '-lco', 'SPATIAL_INDEX=FALSE', '-lco', 'EXTRACT_SCHEMA_FROM_LAYER_NAME=YES', '-nln', 'loading.simplified_water_polygons', 'PG:dbname=osmtilemaker user=osmtilemaker', 'data/simplified_water_polygons/simplified-water-polygons-split-3857/simplified_water_polygons.shp']' returned non-zero exit status 1.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/docker_mounted_volumes/working_dir/openstreetmap-carto/scripts/get-external-data.py", line 298, in <module>
    main()
  File "/docker_mounted_volumes/working_dir/openstreetmap-carto/scripts/get-external-data.py", line 284, in main
    "ogr2ogr error when loading table {}".format(name))
RuntimeError: ogr2ogr error when loading table simplified_water_polygons

As @pnorman suggested, I've tried to run ogr2ogr command manually, without success:

# ls -lrh data/simplified_water_polygons/simplified-water-polygons-split-3857/
total 30M
-rw-r--r-- 1 root root 113K Sep  7 14:46 simplified_water_polygons.shx
-rw-r--r-- 1 root root  29M Sep  7 14:46 simplified_water_polygons.shp
-rw-r--r-- 1 root root  425 Sep  7 14:46 simplified_water_polygons.prj
-rw-r--r-- 1 root root 267K Sep  7 14:46 simplified_water_polygons.dbf
-rw-r--r-- 1 root root    6 Sep  7 14:46 simplified_water_polygons.cpg

# ogr2ogr -f PostgreSQL -lco GEOMETRY_NAME=way -lco SPATIAL_INDEX=FALSE -lco EXTRACT_SCHEMA_FROM_LAYER_NAME=YES -nln loading.simplified_water_polygons "PG:dbname=osmtilemaker user=osmtilemaker" data/simplified_water_polygons/simplified-water-polygons-split-3857/simplified_water_polygons.shp
Warning 6: 'FALSE' is an unexpected value for SPATIAL_INDEX layer creation option of type string-select.
ERROR 1: ERROR:  column s.consrc does not exist
LINE 1: ...nrelid = c.oid AND a.attnum = ANY (s.conkey) AND (s.consrc L...
                                                             ^
HINT:  Perhaps you meant to reference the column "s.conkey" or the column "s.conbin".

ERROR 1: ERROR:  column s.consrc does not exist
LINE 1: ...nrelid = c.oid AND a.attnum = ANY (s.conkey) AND (s.consrc L...
                                                             ^
HINT:  Perhaps you meant to reference the column "s.conkey" or the column "s.conbin".

ERROR 1: ERROR:  current transaction is aborted, commands ignored until end of transaction block

ERROR 1: ERROR:  current transaction is aborted, commands ignored until end of transaction block

ERROR 1: ERROR:  current transaction is aborted, commands ignored until end of transaction block

ERROR 1: ERROR:  current transaction is aborted, commands ignored until end of transaction block

ERROR 1: ERROR:  current transaction is aborted, commands ignored until end of transaction block

ERROR 1: CREATE TABLE "loading"."simplified_water_polygons" ( "ogc_fid" SERIAL, PRIMARY KEY ("ogc_fid"), "x" NUMERIC(9,0), "y" NUMERIC(9,0), "way" geometry(POLYGON,1) )
ERROR:  current transaction is aborted, commands ignored until end of transaction block

ERROR 1: Unable to write feature 0 from layer simplified_water_polygons.
ERROR 1: Terminating translation prematurely after failed
translation of layer simplified_water_polygons (use -skipfailures to skip errors)

As @SomeoneElseOSM suggested, I've checked for a potential URL issue within external-data.yml, without success too:

• I've checked all the URL within external-data.yml and the corresponding one (https://naciscdn.org/naturalearth/110m/cultural/ne_110m_admin_0_boundary_lines_land.zip) was not working yesterday (even the domain name seemed to be not reachable). Today it seems to work when using http instead of https.
• However, I tried to replace the corresponding URL by another one which is working and provided within this issue https://github.com/nvkelso/natural-earth-vector/issues/528 : https://naturalearth.s3.amazonaws.com/110m_cultural/ne_110m_admin_0_boundary_lines_land.zip but the issue remains ...

Any help is welcome ! I can provide more information if needed.

[pnorman]

You've installed conflicting versions of ogr2ogr and postgresql. It is unrelated to this topic.

[audour]

Thanks for your quick answer. You're right:

• I used postgres-13 docker image which is based on Debian Buster where gdal-bin packages GDAL 2.4.0
• according to GDAL 2.4 releases notes, support for Postgres 12 was added through GDAL 2.4.3

So I switched to postgres-11 docker image and the issue is closed.

However there was also a dead url issue in external-data.yml but it's now discussed here : https://github.com/gravitystorm/openstreetmap-carto/issues/4461#issuecomment-915216055

[kowo1001]

@MBabakov Did you solve the problem ??

[MBabakov]

@MBabakov Did you solve the problem ??

Yes thank you! The problem was resolved a year ago by installing Ubuntu 20.04.