0

I want to centralise several small sqlite databases into a single, big, postgresql database. I am using ogr2ogr to convert my sqlite / spatialite (v3.7.17 / v4.1.1) databases, each one consisting of one geometry table and several attribute tables (linked to the geometry table via foreign keys), to the postgresql / postgis database ( v9.3 / v2.1.3). Here is the code:

ogr2ogr -append -lco LAUNDER=no -lco SCHEMA=public -lco GEOMETRY_NAME=geom -f PostgreSQL PG:"..." -a_srs EPSG:4269 c:\Mydata1.sqlite line attrib1 attrib2

All my int4 primary keys (ex: line_pk, attrib1_pk, attrib2_pk) are converted to serial type and their names are all changed to the same name: ogc_fid (eg I have ogc_fid as PK in my line, attrib1 and attrib2 tables). I can live with this, but I find that all my foreign key constraints (from the sqlite database) are eliminated during the conversion process! Any idea why this is so and how to correct this? I can always re-code the constraints manually, but then I will have to somehow manage what value is put into my foreign keys each time I append a new set of data...

1

I do not believe that GDAL cares anything about the foreign key constraints and relational model. It has just made to handle one layer at a time. I would consider to copy your int4 primary keys into normal attributes and take care on the Spatialite side that they are unique over all your distinct databases. Once source data is in order you can append all data into PostGIS tables and create unique indexes and foreign key constraints. When appending even more data you must check the max ID value from PostGIS and update new IDs to start from (max+1).

You can define the name of the primary key that GDAL creates with layer creation option -lco FID=xxx as documented in http://www.gdal.org/drv_pg.html but that does not solve your real problem.

  • Thanks! So basically, to paraphrase, I have to: 1) create a unique identifier that applies to my several SQLite databases; 2) update/change the foreign keys of my attribute tables with this unique identifier; 3) Only then are my SQLite databases ready for conversion into PostgreSQL; 4) Once everything is imported in PostgreSQL, rebuild the foreign key constraints using the unique identifier created above. (I could always try and get the primary key generated by PostgreSQL, but it could get messy. This way looks easier.) – lavaman Oct 1 '14 at 14:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.