Importing geometries from other system with sql-scripts

[mj10777]

A problem has turned up for a user, who would like to import a geometry-table from another system (MS Sql Server 2008 R2)

- attempts to do so using spatialite_gui's CSV import function have failed

-- since the maximum field-length supported seems to be 65536

--> this seems to be also true for the export function

So the intention is to develop a general method to achieve this goal

- without the use of external tools

Since at the moment, I have no 'PostGis' or 'MS Sql Server 2008 R2' available

- the developed sql-scripts will 

-- export an existing spatialite table 

and

-- import it back

For this sample I have used the Topology-Demo Database that can be downloaded here:

https://www.gaia-gis.it/gaia-sins/toposamples/tuscany-admin.7z

---

Stage 01:

the main geometry table 'comuni' has the following structure:

CREATE TABLE comuni 

(

 com_id INTEGER NOT NULL PRIMARY KEY,

 codcom TEXT NOT NULL,

 codpro TEXT NOT NULL,

 codcatasto TEXT NOT NULL,

 nome TEXT NOT NULL

);

---

SELECT AddGeometryColumn('comuni_imported','geom',3003,'MULTIPOLYGON','XY');

SELECT CreateSpatialIndex('comuni_imported','geom');

The first step will be to:

- create a TABLE that will contain a

- 'Open Geospatial Consortium (OGC)'  geometry

-- with extra fields containing the Geometry-Type and SRID

- and the other 5 attribute fields

The second step will be to:

- create a TABLE with the 

-- sqlite3 specific INSERT statements from the first TABLE

--- from which a proper spatialite table can then be created

This second TABLE should then be exported to a text file

- which will be used as a sql-script

The attached file ''export_ogc_comuni.sql"

- is a spatialite version of these first steps

-- notes have been included in the scripts

--- for the needed Syntax-changed for 

---> 'PostGis' and 'MS Sql Server 2008 R2'

(which may or may not work correctly, since I cannot test this)

---

Assuming that the scripts and 'tuscany-admin.sqlite' are in the same directory

- the script can be called with the following command in a terminal

rm export_ogc_comuni.db ; spatialite export_ogc_comuni.db < export_ogc_comuni.sql

when completed, a export_ogc_comuni.db file should exist

- with 2 tables

-- export_ogc_comuni

--> created as:

CREATE TABLE export_ogc_comuni

(

 com_id INTEGER,

 codcom TEXT,

 codpro TEXT,

 codcatasto TEXT,

 nome TEXT,

 geom_type TEXT,

 geom_srid  INTEGER,

 ogc_geom  BLOB

);

from the source Database, which was ATTACHed with

ATTACH DATABASE 'tuscany-admin.sqlite' AS db_import;

---> and filled with (For PostGis: possibly no changes needed for this command)

INSERT INTO export_ogc_comuni 

 SELECT

  com_id,

  codcom,

  codpro,

  codcatasto,

  nome, 

  -- MS Sql-Server R2: 'geom.STGeometryType()'

  ST_GeometryType(geom) AS geom_type,

  -- MS Sql-Server R2: 'geom.STSrid()'

  ST_SRID(geom) AS geom_srid,

  -- MS Sql-Server R2: 'geom.STAsBinary()'

  ST_AsBinary(geom) AS ogc_geom

 FROM

  db_import.comuni;

-- sql_insert_ogc_comuni

--> created with:

CREATE TABLE sql_insert_ogc_comuni AS

 SELECT

 'INSERT INTO import_ogc_comuni VALUES('

  ||com_id||',"'

  ||codcom||'","'

  ||codpro||'","'

  ||codcatasto||'","'

  ||nome||'","'

  ||geom_type||'",'

  ||geom_srid||',"'

  ||hex(ogc_geom)||'");' AS insert_sql

 FROM

  export_ogc_comuni;

For the 'concat' command ('||')

- changes for both systems will be needed

-> something in the form of:

- for Postgres: use "||com_id:text||" instead of "||com_id||"

- for MS Sql-Server R2: use "+com_id+" instead of "||com_id||" 

- for Postgres: use "encode(ogc_geom,''hex'')" instead of "hex(ogc_geom)"

- for MS Sql-Server R2: use "convert(varchar(max), @ogc_geom, 2)" instead of "hex(ogc_geom)"

---

At this point, the results (one IǸSERT stateme per record) that are in 

- sql_insert_ogc_comuni

-- must be exported to a text-file:

-- sqlite3 export_ogc_comuni.db < select.comuni_imported.sql > insert.comuni_imported.sql

-- SELECT insert_sql FROM sql_insert_ogc_comuni LIMIT 2;

SELECT insert_sql FROM sql_insert_ogc_comuni;

The created 'insert.comuni_imported.sql'

- will be used to import the data to a sqlite3 Database.

At this point, the 2 above tables can be DROPed, since they are no longer needed.

---

Stage 2

Here a third script (create.comuni_imported.sql) will be used to

- to import the data created from 'insert.comuni_imported.sql'

and is called with:

rm import_ogc_comuni.db ; spatialite import_ogc_comuni.db < create.comuni_imported.sql

-- filling the TABLE that is created with the following command:

CREATE TABLE import_ogc_comuni

(

 com_id INTEGER,

 codcom TEXT,

 codpro TEXT,

 codcatasto TEXT,

 nome TEXT,

 geom_type TEXT,

 geom_srid INTEGER,

 hex_ogc_geom TEXT

);

Here I believe is is wiser to create the TABLE with proper INTEGER (and possibly DOUBLE) definitions

The Data will be read in with:

.read insert.comuni_imported.sql UTF-8

Note:

- when calling this script

-- spatialite should always be used (not sqlite3)

--> for unix/mac sources, sqlite3 could (possibly) cause no problems

---> but for Window sources, the Characters must be properly converted to UTF-8

Since the script also creates a Spatialite TABLE

- spatialite is in any case needed

---

Here an extra Casting is done on the INTEGER (and possibly DOUBLE) values

- to insure that the field 'geom_srid' is a proper INTEGER, since it will be needed later

UPDATE import_ogc_comuni SET

 com_id=CAST(com_id AS INTEGER),

 geom_srid=CAST(geom_srid AS INTEGER);

---

Now two BLOB fields will be added to the 'import_ogc_comuni' TABLE

- which will be used to create

-- a ogc_geom from hex_ogc_geom

-- a spatialite_geom from ogc_geom

ALTER TABLE import_ogc_comuni ADD COLUMN ogc_geom BLOB;

ALTER TABLE import_ogc_comuni ADD COLUMN spatialite_geom BLOB;
--
UPDATE import_ogc_comuni SET ogc_geom=CastToBlob(hex_ogc_geom,1);
UPDATE import_ogc_comuni SET spatialite_geom=ST_GeomFromWKB(ogc_geom,geom_srid);

---

At this point, spatialite_geom

- should now contain valid spatialite geometries

-- for which we will do a small sanity check with:

SELECT

 "Sanity-check: all values MUST be the same:" AS text,

 count(geom_srid) AS count_records,

 (SELECT count(spatialite_geom) FROM import_ogc_comuni AS test_type WHERE ST_GeometryType(test_type.spatialite_geom)=import_ogc_comuni.geom_type) AS count_type,

 (SELECT count(spatialite_geom) FROM import_ogc_comuni AS test_srid WHERE ST_SRID(test_srid.spatialite_geom)=import_ogc_comuni.geom_srid) AS count_srid

FROM 

 import_ogc_comuni; 
---

Sanity-check: all values MUST be the same:|279|279|279

---

The last step is to then create the final spatialite TABLE

- comuni_imported 

CREATE TABLE comuni_imported 

(

 com_id INTEGER NOT NULL PRIMARY KEY,

 codcom TEXT NOT NULL,

 codpro TEXT NOT NULL,

 codcatasto TEXT NOT NULL,

 nome TEXT NOT NULL

);

---

SELECT AddGeometryColumn('comuni_imported','geom',3003,'MULTIPOLYGON','XY');

SELECT CreateSpatialIndex('comuni_imported','geom');

and filled with:

INSERT INTO comuni_imported 

 SELECT

  com_id,

  codcom,

  codpro,

  codcatasto,

  nome,

  spatialite_geom AS geom

 FROM

  import_ogc_comuni;

---

SELECT DateTime('now'),'UpdateLayerStatistics: comuni_imported.geom';

SELECT UpdateLayerStatistics('omuni_imported','geom');

---

After a bit of cleaning up ...

---

SELECT DateTime('now'),'DROPing TABLE import_ogc_comuni (comment out if there are errors)';

-- DROP TABLE IF EXISTS sql_insert_ogc_comuni;

---

COMMIT;

---

SELECT DateTime('now'),'VACUUM';

VACUUM;

SELECT DateTime('now'),'import_ogc_comuni.sql [finished] [Habe fertig!] ';

---

... and assuming that everything ran correctly

- we are finished.

---

After adapting the different column names to your needs

- and after testing that the system specific commands are correct

-- not really that difficult

Mark