8

How can I move a Spatialite table from a Spatialite db to a PostgreSQL db using ogr2ogr?

2
  • 1
    I know you wanted to use ogr2ogr, and have posted a solution below, but you can also use the VirtualPG support (gaia-gis.it/fossil/virtualpg/wiki?name=tutorial) if you have a fairly recent SpatiaLite. That can give you a live connection.
    – BradHards
    Aug 18, 2014 at 7:48
  • A very cool feature. Works well. Aug 18, 2014 at 8:09

2 Answers 2

14

And, after a bit more reading the ogr2ogr help I found the answer more easily than I expected.

ogr2ogr -f "PostgreSQL" PG:"dbname=db" spatialitedb -sql "SELECT * FROM table" -dialect spatialite -nln new_table

EDIT: As suggested by user30184 in the comments a cleaner, simpler method is:

ogr2ogr -f "PostgreSQL" PG:"dbname=db" spatialitedb spatialite_table_name
3
  • 1
    No need to delete it. This kind of thing is fine on GIS.SE.
    – Nathan W
    Aug 18, 2014 at 4:26
  • 3
    However, if the whole table with all the attributes should be copied it is more simple with ogr2ogr -f "PostgreSQL" PG:"dbname=db" spatialitedb spatialite_table_name. Select * probably also copies the "rowid" column which you do not need.
    – user30184
    Aug 18, 2014 at 4:44
  • :-o I have to wait 2 days to mark it as the answer. Aug 18, 2014 at 7:35
2

A more user friendly option, for those not familiar with command-line tools such as ogr2ogr, is to use Export to PostgreSQL available in the Processing Toolbox in QGIS 3.12.

This processing tool exposes ogr2ogr via GDAL into the QGIS user interface.

I'm unsure if this function exists in earlier versions.

You'll need to add the layers to a project first, though the process is pretty straightforward from there as it uses the existing PostGIS connections set up in QGIS.

You can also Export to PostgreSQL without needing to create a connection in QGIS by using Export to PostgreSQL (new connection).

Screenshot of QGIS' Export to PostgreSQL function

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.