Geometry tables

[Bulent Arikan]

Dear List,

Since I am new to SpatiaLite, I am trying to understand how one constructs spatial dbase here. I understand that coordinate data (and associated details such as datum and projection info) are stored in a separate table. In my project, I am dealing with mainly two types of geometry: points (i.e., archaeological sites) and polygons (i.e., watersheds). I already have a geometry table for points (WGS84, UTM 36N).

My first question is about creating geometry tables in SpatiaLite. I used the commands below to create the table "coordinates" for my point data:

CREATE TABLE coordinates (    

  id INTEGER NOT NULL      

    PRIMARY KEY AUTOINCREMENT,   

  site_name TEXT NOT NULL, "the_geom" POINT)

 

Select AddGeometryColumn ('the_geom', 'gtry', 32636, 'POINT', 2);

I have seen a lot of discussions on the internet about triggers and BLOB statements so, I am not sure if these two are all the necessary steps for me to have an accurately functioning spatial data table.

My second question is: should I have a separate geometry table for polygons? The reason for having polygon data is to determine the location of a specific point in relation to many polygons (to figure out in which watershed the archaeological site is located at).

Finally, I am assuming that I need to connect geometry tables with the main table (containing the names and other details of archaeological sites), right?

Thank you!

Bulent

[mj10777]

On Saturday, 1 April 2017 17:25:03 UTC+2, Bulent Arikan wrote:

Dear List,

Since I am new to SpatiaLite, I am trying to understand how one constructs spatial dbase here. I understand that coordinate data (and associated details such as datum and projection info) are stored in a separate table. In my project, I am dealing with mainly two types of geometry: points (i.e., archaeological sites) and polygons (i.e., watersheds). I already have a geometry table for points (WGS84, UTM 36N).

My first question is about creating geometry tables in SpatiaLite. I used the commands below to create the table "coordinates" for my point data:

CREATE TABLE coordinates (    

  id INTEGER NOT NULL      

    PRIMARY KEY AUTOINCREMENT,   

  site_name TEXT NOT NULL, "the_geom" POINT)

 

Select AddGeometryColumn ('the_geom', 'gtry', 32636, 'POINT', 2);

The correct syntax is:

CREATE TABLE coordinates

(-- do not include the geometry field in the SQL CREATE Staatement   

 id INTEGER NOT NULL  PRIMARY KEY AUTOINCREMENT,   

 site_name TEXT NOT NULL

); 
-- Syntax: table_name, geometry_name, srid, geometry_type, dimension

SELECT AddGeometryColumn ('coordinates', 'point_utm', 32636, 'POINT', 'XY');

 

I have seen a lot of discussions on the internet about triggers and BLOB statements so, I am not sure if these two are all the necessary steps for me to have an accurately functioning spatial data table.

Spatialite has been designed to be used primarily with SQL-Statements

- so normally you never need to bother on how it is internally stored

Internally Spatialite will store the geometry's in BLOBs, with TRIGGERs used to administer everything it needs to react to SQL-commands correctly

- that is why one should never 'play' around with the internal TABLEs and TRIGGERs

My second question is: should I have a separate geometry table for polygons? The reason for having polygon data is to determine the location of a specific point in relation to many polygons (to figure out in which watershed the archaeological site is located at).

This is a Database designing issue.

If 90% of your queries are intended for  'names and other details' and only rarely (10%) the geometries

- then use 2 TABLEs that reverenced with a foreign key

-- each query must load the fields in the TABLE, so big geometries could slow down the query

If the ratio is 50/50, then everything can be contained in 1 TABLE

- for queries on geometries, create SpatialIndexes and use the special syntax to use these queries

-- Add a WSG84 POINT to the TABLE
SELECT AddGeometryColumn ('coordinates', 'point_wsg84', 4326, 'POINT', 'XY');
-- Add a WSG84 POLYGON to the TABLE
SELECT AddGeometryColumn ('coordinates', 'polygon_wsg84', 4326, 'POLYGON', 'XY');
-- create SpatialIndex for the 3 geometries
SELECT CreateSpatialIndex('coordinates','point_utm');
SELECT CreateSpatialIndex('coordinates','point_wsg84');
SELECT CreateSpatialIndex('coordinates','polygon_wsg84');

List of Spatialite specfic commands:

http://www.gaia-gis.it/gaia-sins/spatialite-sql-latest.html

Hope this helps

Mark

[Bulent Arikan]

Hi Mark,

Thank you so much for such an informative response. I will take these suggestions.

Best,

Bulent