How can I move a Spatialite table from a Spatialite db to a PostgreSQL db using ogr2ogr?
-
1I 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.– BradHardsAug 18, 2014 at 7:48
-
A very cool feature. Works well.– Phil DonovanAug 18, 2014 at 8:09
Add a comment
|
2 Answers
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
-
1
-
3However, 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. Aug 18, 2014 at 4:44 -
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).
