ogr2ogr OSM polygon layer export from PostGIS to SpatiaLite has no CRS

Question

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.

Answer 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/.