Export from PostGIS to SpatiaLite?

Question

My PostGIS database contains OSM data from my country. For transportation reasons I want to export a spatial view I created from that data to a SpatiaLite database.

Is there any way to directly extract my PostGIS view to a SpatiaLite db without resorting to a man-in-the-middle (shapefile then SQLite) solution?

Im using Ubuntu Linux, with PostGIS 2.1 GDAL 191.

EDIT:

Here’s a single cmd to export to shapefile:

ogr2ogr -f "ESRI Shapefile" <name-of-folder-to-be-created> 
PG:"dbname=dbname user=username password=pass" 
-sql "<sql query eg> select * from database where <whatever>"

If you get a getsrid(geom) error, load in your database the "legacy.sql"

psql -f /path/to/legacy.sql

ogr postgis sqlite

Stéphane Henriod · Accepted Answer

If you want to select from a postgis table, you can use this statement:
ogr2ogr -f SQLite -dsco SPATIALITE=yes C:/temp/myfile2.sqlite PG:"host=HOSTNAMEORIP port=XXXX dbname=YOURDB user=YOURUSER password=YOURPASSWORD" -sql "select * from mypostgislayer"

If you want to export the whole postgis table, you can use this statement:
ogr2ogr -f SQLite -dsco SPATIALITE=yes C:/temp/myfile.sqlite PG:"host=HOSTNAMEORIP port=XXXX dbname=YOURDB user=YOURUSER password=YOURPASSWORD" "mypostgislayer"

For further information see the following links:
ogr2ogr and
spatialite.

BradHards · Answer

If you haven't done a lot of work in postgis, another alternative might be to import it directly from OSM into spatialite.

Export from PostGIS to SpatiaLite?

2 Answers

Add your own answers!

Ask a Question