AddGeometryColumn

[A Ask]

Why use AddGeometryColumn to store spatial objects into tables?

I know that it allows for Z (elevation) and M (measure) into the definition of points whereas the OGC methods methods do not.

If I do not need Z & M, I can store spatial object as follows:

spatialite> create table xx as select geomfromtext('POINT (28.58000199 -20.16999754)',4326) as position;

This persists the SRID and all related functionality is available; see my session below. And I can store different types into the same column.

Why/When should I use AddGeometryColumn?

Session:

/* SRID */

spatialite> select srid(buffer(position,1000)) from xx;

4326

/* Geometry Type */

spatialite> select geometrytype(position) from xx;

POINT

spatialite> select geometrytype(buffer(position,1000)) from xx;

POLYGON

/*  Conversion to WKT */

spatialite> select astext(buffer(position,1000)) from xx;

POLYGON((1028.580002 -20.169998, 1027.209537 -72.505954, 1023.101897 -124.698461, 1016.268343 -176.604463, 1006.727603 -228.081688, 994.505828 -278.989043, 979.636518 -329.186992, 962.160428 -378.537947, 942.12546 -426.906641, 919.586526 -474.160497, 894.605406 -520.169998, 867.25057 -564.809033, 837.596996 -607.95525, 805.725963 -649.490389, 771.724827 -689.300604, 735.686783 -727.276779, 697.710608 -763.314823, 657.900393 -797.315959, 616.365254 -829.186992, 573.219037 -858.840565, 528.580002 -886.195401, 482.570502 -911.176522, 435.316645 -933.715455, 386.947952 -953.750424, 337.596996 -971.226514, 287.399047 -986.095824, 236.491693 -998.317598, 185.014467 -1007.858338, 133.108465 -1014.691893, 80.915958 -1018.799532, 28.580002 -1020.169998, -23.755954 -1018.799532, -75.948461 -1014.691893, -127.854463 -1007.858338, -179.331689 -998.317598, -230.239043 -986.095824, -280.436992 -971.226514, -329.787948 -953.750424, -378.156641 -933.715455, -425.410498 -911.176522, -471.419998 -886.195401, -516.059033 -858.840565, -559.20525 -829.186992, -600.740389 -797.315959, -640.550604 -763.314823, -678.526779 -727.276779, -714.564823 -689.300604, -748.565959 -649.490389, -780.436992 -607.95525, -810.090566 -564.809033, -837.445402 -520.169998, -862.426522 -474.160497, -884.965456 -426.906641, -905.000425 -378.537947, -922.476514 -329.186992, -937.345824 -278.989043, -949.567599 -228.081688, -959.108339 -176.604463, -965.941893 -124.698461, -970.049533 -72.505954, -971.419998 -20.169998, -970.049533 32.165959, -965.941893 84.358466, -959.108339 136.264468, -949.567599 187.741693, -937.345824 238.649048, -922.476514 288.846997, -905.000425 338.197952, -884.965456 386.566646, -862.426522 433.820502, -837.445402 479.830002, -810.090566 524.469037, -780.436992 567.615255, -748.565959 609.150394, -714.564823 648.960609, -678.526779 686.936784, -640.550604 722.974828, -600.740389 756.975964, -559.20525 788.846997, -516.059033 818.50057, -471.419998 845.855406, -425.410498 870.836527, -378.156641 893.37546, -329.787948 913.410429, -280.436992 930.886519, -230.239043 945.755829, -179.331689 957.977603, -127.854463 967.518343, -75.948461 974.351898, -23.755954 978.459537, 28.580002 979.830002, 80.915958 978.459537, 133.108465 974.351898, 185.014467 967.518343, 236.491693 957.977603, 287.399047 945.755829, 337.596996 930.886519, 386.947952 913.410429, 435.316645 893.37546, 482.570502 870.836527, 528.580002 845.855406, 573.219037 818.50057, 616.365254 788.846997, 657.900393 756.975964, 697.710608 722.974828, 735.686783 686.936784, 771.724827 648.960609, 805.725963 609.150394, 837.596996 567.615255, 867.25057 524.469037, 894.605406 479.830002, 919.586526 433.820502, 942.12546 386.566646, 962.160428 338.197952, 979.636518 288.846997, 994.505828 238.649048, 1006.727603 187.741693, 1016.268343 136.264468, 1023.101897 84.358466, 1027.209537 32.165959, 1028.580002 -20.169998))

spatialite>

[mj10777]

On Saturday, 29 August 2020 at 22:48:42 UTC+2 AA2...@lycos.co.uk wrote:

Why use AddGeometryColumn to store spatial objects into tables?

AddGeometryColumn will insure that rhe geometry column is properly registered and the contrainst enforced (srid, geomery types etc).  

I know that it allows for Z (elevation) and M (measure) into the definition of points whereas the OGC methods methods do not.

If I do not need Z & M, I can store spatial object as follows:

spatialite> create table xx as select geomfromtext('POINT (28.58000199 -20.16999754)',4326) as position;

This would not be a valid geometry column, only a table  that contains a blob that is also a geometry.  

This persists the SRID and all related functionality is available; see my session below. And I can store different types into the same column.

You could, through an INSERT, add a geometry that contains a different srid or geometry type - which is not  allowed. Reading software assums that all inserted geometries are  of the same srid and type. 

Why/When should I use AddGeometryColumn?

TRIGGERS will be created that prevents,  through INSERT or UPDATE, that geometries that have a different srid or geometry type will be added or changed. 

With AddGeometryColumn, a new table column will be added to the table, needed triggers created and the permitted srid and geometry type added to the admin tables which other software will read. 

[A Ask]

This would not be a valid geometry column, only a table  that contains a blob that is also a geometry. 

SQLite columns are essentially typeless, you can insert a literal in a column (without an optional  trigger preventing it) that contains integers etc; therefore, there is no such thing as  a 'valid geometry column'  

- SQLite does not have a Geometry type AND SpatiaLite is recognising the value as a geometry type since other methods such as srid,  buffer, AsText etc. process correctly.

- a 'valid' geometry   is not dependent  on how/where it is stored; a geometry is valid when IsValid is true.

Using AddGeometryColumn, I would need as many columns as there are types i.e. Point, MultiPoint, LineString, MultiLineString, Polygon, MuliPolygon , GeometryCollection  & the counterparts of these with Z & M. 

Is that practical?  For instance, 

- if stored the administrative boundaries of countries, I would need two columns one for those that are polygons and another for those that are multipolygons. 

- any SQL that establishes whether a point is in which country will need to be executes twice - once on the polygon column, and again on the multipolygon column.

Storing both in 1 column, I can use GeometryType to determine what type and  the SQL statement can cater for the different types in one sweep.

Reading software assums that all inserted geometries are  of the same srid and type. 

What reading software? That is, what reading software other than Spatialite CLI or SQLite with mod_spatialite loaded?

Both srid and type are queryable read-only properties and the 'reading software' can establish both of these properties for every value in a geometry column.

Unlike the WKT format, the WKB format of a geometry persists its srid within and it can be saved in the databases.

I do not see any reason for using AddGeometryColumn unless I am using the Z & M attributes; your response does not entice me to think otherwise.

PS: I have not tested this with SpatiaLite but I know it is true with SQL Server: any computation with objects with different SRID raises an error.

[mj10777]

On Sunday, 30 August 2020 at 00:20:52 UTC+2 AA2...@lycos.co.uk wrote:

This would not be a valid geometry column, only a table  that contains a blob that is also a geometry. 

SQLite columns are essentially typeless, you can insert a literal in a column (without an optional  trigger preventing it) that contains integers etc; therefore, there is no such thing as  a 'valid geometry column'  

- SQLite does not have a Geometry type AND SpatiaLite is recognising the value as a geometry type since other methods such as srid,  buffer, AsText etc. process correctly.

- a 'valid' geometry   is not dependent  on how/where it is stored; a geometry is valid when IsValid is true.

It will do this because it is reading a single blob. 

Using AddGeometryColumn, I would need as many columns as there are types i.e. Point, MultiPoint, LineString, MultiLineString, Polygon, MuliPolygon , GeometryCollection  & the counterparts of these with Z & M. 

Yes, according to the  

OpenGIS Simple Features Specification

2.2.2 Geometry Columns Metadata Views

where each geometry column must be registered with 

• the identity of the feature table of which it is a member,

• the spatial reference system ID,

• the type of geometry for the column,

• the coordinate dimension for the column,

• the identity of the geometry table that stores its instances, and

• the information necessary to navigate the geometry tables in the case of normalized geometry storage.

This is what AddGeometryColumn does plus adding the needed triggers to enforce it. 

Is that practical?  For instance, 

- if stored the administrative boundaries of countries, I would need two columns one for those that are polygons and another for those that are multipolygons. 

- any SQL that establishes whether a point is in which country will need to be executes twice - once on the polygon column, and again on the multipolygon column.

Everything has its drawbacks. With your method a query reading 100000 geometries, would have to check each one first which type, dimension and srid it has. SpatialIndex could not be used since it contains no srid. By mixed srids, transformations must be done when searching if a point is within abother geometry etc. 

Storing both in 1 column, I can use GeometryType to determine what type and  the SQL statement can cater for the different types in one sweep.

Reading software assums that all inserted geometries are  of the same srid and type. 

What reading software? That is, what reading software other than Spatialite CLI or SQLite with mod_spatialite loaded?

All  that use spatialite such as QGis, gdal as source. 

In your samle, qgis would not find the geometry column, since it is not registered in the geometry_column table. 

Both srid and type are queryable read-only properties and the 'reading software' can establish both of these properties for every value in a geometry column.

No, they read the geometry_colums table first and then assums that all the blobs of that column will have the same srid, geometry type and dimension as required by the OpenGIS Simple Features Specification

Unlike the WKT format, the WKB format of a geometry persists its srid within and it can be saved in the databases.

The WKB, as definded by the  Open Geospatial Consortium does bot store the srid. Most database system have there own binary format which does. 

I do not see any reason for using AddGeometryColumn unless I am using the Z & M attributes; your response does not entice me to think otherwise.

PS: I have not tested this with SpatiaLite but I know it is true with SQL Server: any computation with objects with different SRID raises an error.

 Since mssql seever with spatial support is also also is based on the OpenGis Specfication and has checked the geometry column bevorhand. It should alo bring errirs by different geometry types and dimensions. 

[A Ask]

I am still open to persuasion but I am not convinced , yet.

Take the country boundaries subject; some are polygons and others are mulripolygons. According to you, I should have a table with 3 columns, e.g.

country polygon multipolygon

-----------  ------------ ---------------------

Therefore, a country for which a POLYGON represents its boundaries will have MULTIPOLYGON as NULLl and vice-versa.

It follows that the POLYGON column has TWO types of geometry - POLYGON & NULL and therefore  TWO srid (whatever I used, say, 4326) and NULL and TWO geometry type POLYGON & NULL. Likewise for the MULTIPOLYGON column.

Thus, given that a NULL value is equal to no t=other value not even another NULL value,  your statement that each geometry type should be in a separate column does not hold UNLESS you are proposing that there should be  two tables, one for polygons and another for multipolygons. To me that spells chaos not least because it requires cross-table integrity validation and application code becomes unnecessarily complex.

The OGC WKT & WKB specification do not allow for Z or M and, so far, my view remains that AddGeometryColumn is useful only when Z and/or M properties are required and it does not follow that each geometry type be in a separate column.

[a.fu...@lqt.it]

On Sat, 29 Aug 2020 23:06:50 -0700 (PDT), A Ask wrote:
> I am still open to persuasion but I am not convinced , yet.
>
> Take the country boundaries subject; some are polygons and others are
> mulripolygons. According to you, I should have a table with 3
> columns,
> e.g.
>
> country polygon multipolygon
> ----------- ------------ ---------------------
> Therefore, a country for which a POLYGON represents its boundaries
> will have MULTIPOLYGON as NULLl and vice-versa.
>
> It follows that the POLYGON column has TWO types of geometry -
> POLYGON
> & NULL and therefore TWO srid (whatever I used, say, 4326) and NULL
> and TWO geometry type POLYGON & NULL. Likewise for the MULTIPOLYGON
> column.
>

Hi,

sorry, but you are grossly misunderstanding the OGC Simple Features
conceptual model.

- a POLYGON is a simple geometry, and can be either NULL or contain
a single POLYGON elementary geometry.

- a MULTIPOLYGON is a collection of geometries, and can be either
NULL or conaint _ANY_ number of elementary POLYGONs.
At least 1 POLYGON must be present in the collection.

So, if your administrative boundaries are a mix of both POLYGONs
and MULTIPOLYGONs (as it usually happens) you simply have to craate
a Geometry Column of the MULTIPOLYGON type, then casting all simple
POLYGONs to the MULTIPOLYGON type.

> I know that it allows for Z (elevation) and M (measure) into the
> definition of points whereas the OGC methods methods do not.
>

sorry again, but this is not correct.
earlier versions of OGC SFS specifications where limited to a
pure X,Y data model, but thigs changed in following years and
a complete X,Y,Z,M model was introduced.

please note: starting since 1999 OGC SFS was superseded by the
most complete ISO/IEC 13249-3 SQL/MM standard specification
(Information technology — Database languages — SQL Multimedia
and Application Packages — Part 3: Spatial)



A final rationale
=================
SQLite is intrinsically typeless, but the above mentioned
international standard specifications strictly dictate for
strongly typed and fully constrained Geometries.
using AddGeometryColumn() ensures the fulfillment of such
requirements.

Certainly, you can safely store your geometries just as
simple unqualified BLOBs, but then only you (or your own
software) can be able to read such a database.

if you are anyway interested in preserving a robust
conformance to international standards so to get
spatial databases than can easily interoperate
with third parties software (such as GDAL, QGIS,
ESRI, FMF and many others) you necessarily have
to properly register your Geometry Columns by
calling AddGeometryColumn()

bye Sandro

[mj10777]

On Sunday, 30 August 2020 at 08:06:50 UTC+2 AA2...@lycos.co.uk wrote:

I am still open to persuasion but I am not convinced , yet.

Take the country boundaries subject; some are polygons and others are mulripolygons. According to you, I should have a table with 3 columns, e.g.

country polygon multipolygon

-----------  ------------ ---------------------

Therefore, a country for which a POLYGON represents its boundaries will have MULTIPOLYGON as NULLl and vice-versa.

It follows that the POLYGON column has TWO types of geometry - POLYGON & NULL and therefore  TWO srid (whatever I used, say, 4326) and NULL and TWO geometry type POLYGON & NULL. Likewise for the MULTIPOLYGON column.

A MULTIPOLYGON can contain 1 POLYGON. 

For my historical database of Berlin with administration areas and streets from 1700 to today, MULTI LINESTRING/POLOYGON is used.  

See pratical samples of importing admin geometries using CastToMulti:

https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.04.html

Thus, given that a NULL value is equal to no t=other value not even another NULL value,  your statement that each geometry type should be in a separate column does not hold UNLESS you are proposing that there should be  two tables, one for polygons and another for multipolygons. To me that spells chaos not least because it requires cross-table integrity validation and application code becomes unnecessarily complex.

Thus problem resolves itself by using a MULTI when the chance exists that 1 can be a multi geometry type.  

[A Ask]

" Certainly, you can safely store your geometries just as simple unqualified BLOBs, but then only you (or your own software) can be able to read such a database."

Thanks for confirming.

"if you are anyway interested in preserving a robust conformance to ..."

Right now, I am sticking to the CLI and I use Google Earth for visualisation i.e. I produce the KML myself. In due course, I would need a visualisation tool  that integrates more seamlessly.

I looked at GUIs for SQLite - on a 4K display, they are not usable because the toolbar icons are so small and I am not at all keen on being coerced into building SQL statements using a GUI when I can simply write them.

[mj10777]

On Sunday, 30 August 2020 at 09:04:28 UTC+2 AA2...@lycos.co.uk wrote:

" Certainly, you can safely store your geometries just as simple unqualified BLOBs, but then only you (or your own software) can be able to read such a database."

Thanks for confirming.

"if you are anyway interested in preserving a robust conformance to ..."

Right now, I am sticking to the CLI and I use Google Earth for visualisation i.e. I produce the KML myself. In due course, I would need a visualisation tool  that integrates more seamlessly.

I looked at GUIs for SQLite - on a 4K display, they are not usable because the toolbar icons are so small and I am not at all keen on being coerced into building SQL statements using a GUI when I can simply write them.

Try qgis, where you can read and write a spatialite database directly, without the need if kml. 

[A Ask]

" Try qgis, where you can read and write a spatialite database directly, without the need if kml.  "

I am aware of QGIS. Unlike SQLite/SpatiaLite, QGIS requires installation on Windows and, as yet, I do not understand its footprint or how well it will uninstall if that becomes necessary. I am investigating and based on Google Search, it looks promising.

I am using the 32-bit versions of SQLite/SpatiaLite with ADODB and I can load the mod_spatialite extension.

I tried to set up SQLite as a linked server in SSMS but it fails with just the 32-bit ODBC drivers installed. With the 64-bit ODBC drivers ALSO installed, I cannot see the data. The objective in setting up the linked server was to establish whether the visualisation in SSMS  will work with SpatiaLite geometries.

My forray into SQLite/SpatiaLite is barely 2 weeks old.  In due course, I intend to move to C# with System.Data.SQLite.

[mj10777]

On Sunday, 30 August 2020 at 09:29:57 UTC+2 AA2...@lycos.co.uk wrote:

" Try qgis, where you can read and write a spatialite database directly, without the need if kml.  "

I am aware of QGIS. Unlike SQLite/SpatiaLite, QGIS requires installation on Windows and, as yet, I do not understand its footprint or how well it will uninstall if that becomes necessary. I am investigating and based on Google Search, it looks promising.

QGIS should install and uninstall well on Windows, Linux and Mac. 

The map/geometry images shown in the cookbook pages are from QGIS. 

With properly georeferenced maps, the visualization, creation and maintenance of geometries is very simple.