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?
- Adding -a_srs epsg:3857 to ogr2ogr as suggested by @user30184 did not help.
- 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.
-a_srs epsg:4326into you ogr2ogr command.