1

I used ogr2ogr to export osm layers from PostGIS to SpatiaLite using this command:

ogr2ogr -f SQLite -dsco SPATIALITE=yes ./mapfile/psql_polygon.sqlite PG:"host=localhost port=5432 dbname=gis user=postgres password=123" "planet_osm_xxx"

(xxx here is different for different table)

It goes well for all layers "planet_osm_point, planet_osm_line, planet_osm_polygon and planet_osm_roads". i.e. no errors.

However, with QGIS, all layers can be viewed except planet_osm_polygon where it says "This is not a spatial table" (missing CRS). Someone suggested to use below (in PostGIS) then export.

ALTER TABLE planet_osm_polygon ALTER COLUMN way TYPE geometry(Geometry, 3857) USING ST_SetSRID(way,3857);

But did not work either.

Does planet_osm_polygon require special treatment during export or need further processing?

  1. Adding -a_srs epsg:3857 to ogr2ogr as suggested by @user30184 did not help.
  2. QGIS DB Manager reported "There is no entry in geometry_columns" for this layer on PostGIS side, other layers don't. So, I did "SELECT Populate_Geometry_Columns('planet_osm_polygon'::regclass);" in PostGIS from this blog. QGIS DOES show the layer BUT there is alot of missing polygons (comapred PostGIS to sptialite) although row count is the same.
8
  • If planet_osm_polygon is actually in EPSG:4326 in PostGIS but for some reason it is not registered so into the metadata, you can just add -a_srs epsg:4326 into you ogr2ogr command.
    – user30184
    Commented Apr 10, 2022 at 11:07
  • I did this (added -a_srs epsg:3857 ... on command line) but did not work. Still QGIS shows no CRS.
    – alsaleem
    Commented Apr 10, 2022 at 16:03
  • Have you checked the data on PostGIS side? Can you add the table into QGIS directly from PostGIS?
    – user30184
    Commented Apr 10, 2022 at 16:14
  • I checked the planet_osm_polygon table in PostGIS using QGIS DB Manager, DB manager reported "There is no entry in geometry_columns". Other tables do not have this note.
    – alsaleem
    Commented Apr 10, 2022 at 16:19
  • 1
    Open both the PostGIS table and SpatiaLite table into QGIS and identify a few features that are present in PostGIS but not in SpatiaLite. Try to find what is special for the features which were not copied into SpatiaLite.
    – user30184
    Commented Apr 10, 2022 at 16:52

1 Answer 1

1

For some reason osm2pgsql has created the planet_osm_polygon table without making the corresponding entry into the "geometry_columns" metadata table right. By the osm2pgsql manual https://osm2pgsql.org/doc/manual.html#geometry-processing that should not happen. However, if all the other tables has been created as EPSG:3857 then "planet_osm_polygon" is using the same because the -projection parameter in osm2pgsql affects all tables and it can be overridden for a single table only by using special flex output configuration.

The metadata in the geometry_columns table can be fixed with the PostGIS function Populate_Geometry_Columns https://postgis.net/docs/Populate_Geometry_Columns.html and then also the exported SpatiaLite table should be created with the right CRS.

If you are using the most recent version of osm2pgsql and you can repeat the issue about incorrect CRS metadata of the polygon table I suggest to report it to the osm2pgsql project https://osm2pgsql.org/support/.

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.