ogr2ogr error when loading table simplified_water_polygons #4248

MBabakov · 2020-11-10T21:45:49Z

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

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 · 2020-11-10T23:25:02Z

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 · 2020-11-11T06:34:22Z

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 · 2020-11-11T07:15:00Z

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 · 2020-11-14T12:45:53Z

I wonder if what's happening here is the same as #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 · 2020-11-14T13:27:38Z

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 · 2021-09-07T15:14:00Z

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 : Magellium/osmtilemaker#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 Dead URL nvkelso/natural-earth-vector#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 · 2021-09-07T18:35:30Z

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

audour · 2021-09-08T13:01:10Z

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 : #4461 (comment)

kowo1001 · 2021-11-13T10:44:39Z

@MBabakov Did you solve the problem ??

MBabakov · 2021-11-13T12:23:04Z

@MBabakov Did you solve the problem ??

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

MBabakov closed this as completed Nov 14, 2020

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

ogr2ogr error when loading table simplified_water_polygons #4248

ogr2ogr error when loading table simplified_water_polygons #4248

MBabakov commented Nov 10, 2020

pnorman commented Nov 10, 2020

MBabakov commented Nov 11, 2020 •

edited by pnorman

Loading

MBabakov commented Nov 11, 2020 •

edited by pnorman

Loading

SomeoneElseOSM commented Nov 14, 2020

MBabakov commented Nov 14, 2020

audour commented Sep 7, 2021

pnorman commented Sep 7, 2021

audour commented Sep 8, 2021

kowo1001 commented Nov 13, 2021

MBabakov commented Nov 13, 2021

ogr2ogr error when loading table simplified_water_polygons #4248

ogr2ogr error when loading table simplified_water_polygons #4248

Comments

MBabakov commented Nov 10, 2020

Actual behavior

pnorman commented Nov 10, 2020

MBabakov commented Nov 11, 2020 • edited by pnorman Loading

MBabakov commented Nov 11, 2020 • edited by pnorman Loading

SomeoneElseOSM commented Nov 14, 2020

MBabakov commented Nov 14, 2020

audour commented Sep 7, 2021

pnorman commented Sep 7, 2021

audour commented Sep 8, 2021

kowo1001 commented Nov 13, 2021

MBabakov commented Nov 13, 2021

MBabakov commented Nov 11, 2020 •

edited by pnorman

Loading

MBabakov commented Nov 11, 2020 •

edited by pnorman

Loading