Exporting to PostgreSQL PostGIS

Transferring Location Information into PostgreSQL/PostGIS

While different spatial analytics can be achieved with QGIS, there might be instances when using the full functionalities of PostGIS in a PostgreSQL database will be beneficial. This might also be useful when there is a need to link other tables, such as credit or pre-paid card information, into the Location History and again do spatial analytics.

Below are the steps done to transfer the Excavator created tables in SQLite into a PostgreSQL database. This assumes that both PostgreSQL and PostGIS have already been installed in the host machine.

Exporting the SQLite Database

• Creating a dump file of the Excavator created SQLite database

sqlite3 ichnion.db .dump > ichnion.sql

Importing SQLite data into PostgreSQL

• Create a PostgreSQL database

createdb ichnion

• Import the tables from the SQLite dump file into the PostgreSQL database

psql -f ichnion.sql ichnion

• Add the PostGIS extension into the created database

ichnion=# create extension postgis;

Transferring Point data into Geometry Columns

• Create a Geometry Column to the imported google_location_history table

alter table google_location_history add column geom geometry('POINT',4326);

• Add the Points data contained in the lat,lng columns into the created Geometry column

update google_location_history set geom = st_makepoint(lng,lat);

• Create a Geometry Column to the imported google_location_history_updated table

alter table google_location_history_updated add column geom geometry('POINT',4326);

• Add the Points data contained in the lat,lng columns into the created Geometry column

update google_location_history_updated set geom = st_makepoint(lng,lat);

• Create a Geometry Column to the imported google_location_activity table

alter table google_location_activity add column geom geometry('POINT',4326);

• Add the Points data contained in the lat,lng columns into the created Geometry column

update google_location_activity set geom = st_makepoint(lng,lat);

• Create a Geometry Column to the imported google_location_placevisit table

alter table google_location_placevisit add column geom geometry('POINT',4326);

• Add the Points data contained in the lat,lng columns into the created Geometry column

update google_location_placevisit set geom = st_makepoint(lng,lat);

Transferring Linestring data into a Geometry Column

• Create a Geometry Column to the imported google_location_activitysegment table

alter table google_location_activitysegment add column geom geometry('Linestring',4326);

• Add the Linestring data contained in the wkt column into the created Geometry column

update google_location_activitysegment set geom = st_geomfromtext(wkt);