[gdal-dev] [EXTERNAL] Re: ogr2ogr does not convert OSM polygons

Mon Nov 8 06:18:46 PST 2021

Jukka:
  I apologize for the delay getting back to you.  Thanks you for your help. I am posting this as the solution that worked for me in case it will help anyone else.

I created a batch file (CreateandLoadOsmSchema.bat) with the following calls:

"C:\Program Files\PostgreSQL\12\bin\psql" -h data-trust -d osm_%1  -p 5432 -U postgres -c "CREATE SCHEMA %2";

Z:\OpenStreetMap\release-1916-x64-gdal-mapserver\bin\ogr2ogr -f PostgreSQL PG:"dbname='osm_%1' host='<hostname>port='5432' user='postgres' password='<pwd>'" -lco schema=%2 %3  --config OSM_MAX_TMPFILE_SIZE 1024

then I created a separate batch file (CreateAndLoadAfricaOsm.bat) that contained:

set GDAL_DRIVER_PATH=Z:\OpenStreetMap\release-1916-x64-gdal-mapserver\bin
set PGPASSWORD=<pwd>

cd ..\africa

CALL ..\scripts\CreateAndLoadOsmSchema algeria algeria-latest.osm.pbf
CALL ..\scripts\CreateandLoadOsmSchema angola angola-latest.osm.pbf

added a line for each PBF that I downloaded

This created a postgresql /postgis table for each dataset.

Bruce

________________________________
From: Rahkonen Jukka (MML) <jukka.rahkonen at maanmittauslaitos.fi>
Sent: Tuesday, November 2, 2021 3:35 PM
To: Clay, Bruce <bclay at infoscitex.com>
Cc: 'gdal-dev at lists.osgeo.org' <gdal-dev at lists.osgeo.org>
Subject: Re: [EXTERNAL] Re: ogr2ogr does not convert OSM polygons

Hi,

Please take care to send mail also to the list.

The error that you get means probably that the Proj coordinate transformation library finds an older version of the proj.db file. With the gisinternals installation that file is in [gdal_installation_directory]\bin\proj7\share. Environmental variable PROJ_LIB should point into there. How did you launch ogr2ogr after installation? If you installed from the zip file you must run first the batch file “sdkshell.bat” for setting the paths and environment variables. If you used installer then you should launch GDAL command window from the shortcuts. In both cases when settings are OK you can run ogr2ogr simply as “ogr2ogr”.

Other_relations contain geometries which are of geometry type “GeometryCollection”. Unfortunately QGIS has very little support for GeometryCollections. You can see the attributes but not the geometries. If you are interested in the relations you must use some workarounds with QGIS or use other programs. For example OpenJUMP can do pretty well with relations but it is not so well documented.

-Jukka Rahkonen-

Lähettäjä: Clay, Bruce <bclay at infoscitex.com>
Lähetetty: tiistai 2. marraskuuta 2021 20.35
Vastaanottaja: Rahkonen Jukka (MML) <jukka.rahkonen at maanmittauslaitos.fi>
Aihe: Re: [EXTERNAL] Re: ogr2ogr does not convert OSM polygons

Jukka:

  I upgraded to 3.4.0 from sysinternals.  All tables appear to be going in ok but I do get an error message for each table (shown below).  QGIS shows the SRID is 4326.  "other_relations" has a geometry column and it loads in QGIS but the geometry does not show.  It appears I can get what I need for my task, just annoying messages.

Should "other_relations" show in QGIS?

Thanks for you help.

Bruce

Z:\OpenStreetMap\release-1916-x64-gdal-mapserver\bin\ogr2ogr -f PostgreSQL PG:"dbname='open_street_map' host='hostname' port='5432' user='postgres' password='pwd'" -lco schema=algeria algeria-latest.osm.pbf  --config OSM_MAX_TMPFILE_SIZE 1024

0...10...20...30...40.ERROR 1: PROJ: proj_create_from_database: cannot build geodeticCRS 4326: SQLite error on SELECT extent.description, extent.south_lat, extent.north_lat, extent.west_lon, extent.east_lon, scope.scope, (CASE WHEN scope.scope LIKE '%large scale%' THEN 0 ELSE 1 END) AS score FROM usage JOIN extent ON usage.extent_auth_name = extent.auth_name AND usage.extent_code = extent.code JOIN scope ON usage.scope_auth_name = scope.auth_name AND usage.scope_code = scope.code WHERE object_table_name = ? AND object_auth_name = ? AND object_code = ? ORDER BY score, usage.auth_name, usage.code: no such table: usage

..50...60...70...80...90...100 - done.

________________________________

From: Rahkonen Jukka (MML) <jukka.rahkonen at maanmittauslaitos.fi<mailto:jukka.rahkonen at maanmittauslaitos.fi>>
Sent: Tuesday, November 2, 2021 12:32 PM
To: Clay, Bruce <bclay at infoscitex.com<mailto:bclay at infoscitex.com>>
Subject: VS: [EXTERNAL] Re: ogr2ogr does not convert OSM polygons

Actually, it was GDAL 3.3.1 from OSGeo4W installer.

-Jukka-

Lähettäjä: Clay, Bruce <bclay at infoscitex.com<mailto:bclay at infoscitex.com>>
Lähetetty: tiistai 2. marraskuuta 2021 18.19
Vastaanottaja: Rahkonen Jukka (MML) <jukka.rahkonen at maanmittauslaitos.fi<mailto:jukka.rahkonen at maanmittauslaitos.fi>>
Aihe: Re: [EXTERNAL] Re: ogr2ogr does not convert OSM polygons

Jukka:

  Thanks for your reply.

I was in the progress of loading all OSM so I started with Africa and saw the same thing for all pbf files.  You can pick a small one such as algeria-latest.osm.pbf.  In my post I  just replaced the country name with the word "country" to be generic.  All of the files came from the website you mentioned.

I am using the OSGeo shell on Windows 10.  gdalinfo show version is 3.0.4

I did not change the osmconf.ini file.

After looking deeper into the table osm2pgsql created I noticed roads are not actually put in the osm roads layer.  administrative boundaries or other lines were in there. More lines are in the table created by osm2pgsql than the table created by ogr2ogr.

it looks like ogr2ogr would produce a similar approach if it would generate the polygon table.

Bruce

________________________________

From: Rahkonen Jukka (MML) <jukka.rahkonen at maanmittauslaitos.fi<mailto:jukka.rahkonen at maanmittauslaitos.fi>>
Sent: Tuesday, November 2, 2021 11:11 AM
To: Clay, Bruce <bclay at infoscitex.com<mailto:bclay at infoscitex.com>>; gdal-dev at lists.osgeo.org<mailto:gdal-dev at lists.osgeo.org> <gdal-dev at lists.osgeo.org<mailto:gdal-dev at lists.osgeo.org>>
Subject: [EXTERNAL] Re: ogr2ogr does not convert OSM polygons

Hi,

What layers does ”ogrinfo country_pbf” list? Can you reproduce the error with some small OSM country file from http://download.geofabrik.de/<https://usg02.safelinks.protection.office365.us/?url=http%3A%2F%2Fdownload.geofabrik.de%2F&data=04%7C01%7Cbclay%40infoscitex.com%7C693c41133a944e0a44dd08d99e37df46%7C3430c5de5e6f4f9d9d9690e985038b58%7C0%7C0%7C637714785093150348%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=CNmLeTGNprUF77hujrYlaWuG4vvrKGMC%2B8718zzXhY8%3D&reserved=0>?

What is your operating system and GDAL version? Do you use the default osmconf.ini file? GDAL OSM driver and osm2pgsql are creating quite different database schemas so they are not very comparable.

-Jukka Rahkonen-

Lähettäjä: gdal-dev <gdal-dev-bounces at lists.osgeo.org<mailto:gdal-dev-bounces at lists.osgeo.org>> Puolesta Clay, Bruce
Lähetetty: tiistai 2. marraskuuta 2021 16.22
Vastaanottaja: gdal-dev at lists.osgeo.org<mailto:gdal-dev at lists.osgeo.org>
Aihe: [gdal-dev] ogr2ogr does not convert OSM polygons

When I convert a OSM pbf file to postgres using the following script it does not create a multipolygon table

ogr2ogr -f PostgreSQL PG:"dbname='open_street_map' host='db-host' port='5432' user='postgres' password='pwd'" -lco schema=country country_pbf  --config OSM_MAX_TMPFILE_SIZE 1024

When I do the same thing using osm2pgsql (shown below) it creates the polygon table but not the other_relations table

osm2pgsql -H hostname -U postgres -d open_street_map --output-pgsql-schema=country --create --latlong -G --hstore --tag-transform-script /OpenStreetMap/openstreetmap-carto-master/openstreetmap-carto.lua -C 2500 --number-processes 5 -S /OpenStreetMap/openstreetmap-carto-master/openstreetmap-carto.style country-latest.osm.pbf.

I did not see this problem when I Googled.

Is this a known issue and is there a work around?

Bruce
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20211108/accc85d4/attachment-0001.html>