Import/Export geodata between Spatialite (SQLite), PostGIS, Shapefile,…

With this blog-entry I try to give some overview how to import/export geodata between different file-formats/databases. This article is will be updated regulary (hopefully) 😉1) Let’s start with some good old command-line tools

Example 1: From Spatialite to PostGIS
To import Spatialite-data into a PostGIS database ogr2ogr is a nice command-line tool:
[USER@localhost TestPG_Sptialite]$ ogr2ogr -append -lco GEOMETRY_NAME=the_geom -lco SCHEMA=public -f „PostgreSQL“ PG:“host=IP-Adress port=5432 user=databaseuser dbname=geodatabase password=yourpassword“ -a_srs „EPSG:4326“ Spatialitefile.sqlite

The syntax above imports all tables from the Spatialite-database into an existing PostGIS-database.

The option „-append“ appends data to existing tables
-lco GEOMETRY_NAME=the_geom: geometry-column should be „the_geom“
-lco SCHEMA=public: Postgis schema name is „public“
-f „PostgreSQL“: YES, we want to import it into PostgreSQL/PostGIS 🙂 (-f = Format)
PG:“….“: connection information. Be aware that the database-user has the correct settings in pg_hba.conf
-a_srs „EPSG:4326“: define SRS as EPSG-Code
Spatialitefile.sqlite: the Spatialite-database we want to import

A short and quick syntax:
ogr2ogr -f PostgreSQL PG:“host=hostname port=5432 user=dbusername password=youpassword dbname=geodatabase“ -lco LAUNDER=“YES“ SQLitefile.sqlite -skipfailures

The option „-append“ is sometimes also a good choice in this case.

Example 2: From Spatialite to Shapefile
In this case we also use the swiss-knife „ogr2ogr“ 🙂 to export all tables of the Spatialite-database into Shapefiles.

[USER@localhost shp]$ ogr2ogr -f „ESRI Shapefile“ shp SQLitedatabase.sqlite -dsco SPATIALITE=yes

-f: Defines target format and the Option „shp“ defines the target-directory of the Shapefiles

Example 3: From Shapefile to PostGIS

ogr2ogr would also be a choise, but I prefer shp2pgsql

2 steps have to be done: 1) Create SQL-File from the Shapefile with shp2pgsql and 2) Import SQL-file to PostGIS

1) Create the SQL-File from the Shapefile
shp2pgsql Shapefile.shp TablenamePostGIS Database > Filename.sql

2) Import the data into PostGIS
psql -U DatabaseUser -d database -f Filename.sql

Defining the EPSG-Code of the SRS while converting to the SQL-File is very useful: The Option „-s EPSG-Code“ does the job.

User@localhost:~/Geodata$ shp2pgsql -s 4326 paddeltracks.shp kajaktracks geodatabase > kajaktracks.sql

2) The colorful way: Using GUI-Tools…

coming soon