Re: [SpatiaLite-Users] geom violates Geometry constraint [geom-type or SRID not allowed]

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

Hardik,

from your report I'm absolutely unable to see any possible failure
even vaguely related to AddGeometryColumn()

as your figure clearly shows, the Geometry Column was effectively
created and the Triggers too were created; so AddGeometryColumn()
succesfully completed for sure.
within the "rail" table 100,000 rows have been inserted (I ignore
if it happened before or after creating the geometry column);
what's absolutely sure is that all Geometries are NULL.

so I strongly suspect that during some INSERT (or may be UPDATE)
steps you've probably received some message "geom violates Geometry
constraint [geom-type or SRID not allowed]"; this seems reasonably
consistent whith the subject, but doesn't match at all what you
are stating in your post.

if this is what really happened, this clearly means that for
some reason to be discovered the Geometries you attempted to
INSERT or UPDATE were surecly malformed, and consequenlty
were rejected by the safeguard Triggers protecting the table.

bye Sandro

[Hardik Pandya]

Dear Sandro,

100,000 rows already exists before AddGeometryColumn().

After I run AddGeometrycolumn()  (it's adding 'geom' column) but when I check it through 'Check Geometries' it's not showing (as you can see in image)

What I am doing is ::

ExecuteStatement("select load_extension('spatialite.dll')", con);
ExecuteStatement("SELECT InitSpatialMetaData()", con);

1) Creating table (rail)

create table rail AS Select LINK_ID from RDF_LINK where LINK_ID IN (Select LINK_ID from RDF_ROAD_LINK) limit 100000

(this would create table 'rail' with 100000 records)

2)  Creating Index on table (rail)

CREATE UNIQUE INDEX railtemp ON rail(LINK_ID)

3) Discarding Geometry column (after reading cookbook (http://www.gaia-gis.it/gaia-sins/spatialite-cookbook/html/new-geom.html))

SELECT DiscardGeometryColumn('rail','geom')

4) Adding Geometry Column

SELECT AddGeometryColumn('rail','geom',32632,'LINESTRING','XY')

5) Creating Spatial Index

SELECT CreateSpatialIndex('rail','geom')

(After then checked in database with 'Check geometries' which showing geometry null .. (see in image)
and it's also showing triggers (in most cases I observe that If it Add geometry column correctly it's not showing this trigger list)

Trigger ggi_rail_geom

CREATE TRIGGER "ggi_rail_geom" BEFORE INSERT ON "rail"
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'rail.geom violates Geometry constraint [geom-type or SRID not allowed]')
WHERE (SELECT geometry_type FROM geometry_columns
WHERE Lower(f_table_name) = Lower('rail') AND Lower(f_geometry_column) = Lower('geom')
AND GeometryConstraints(NEW."geom", geometry_type, srid) = 1) IS NULL;
END

Trigger ggu_rail_geom

CREATE TRIGGER "ggu_rail_geom" BEFORE UPDATE ON "rail"
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'rail.geom violates Geometry constraint [geom-type or SRID not allowed]')
WHERE (SELECT geometry_type FROM geometry_columns
WHERE Lower(f_table_name) = Lower('rail') AND Lower(f_geometry_column) = Lower('geom')
AND GeometryConstraints(NEW."geom", geometry_type, srid) = 1) IS NULL;
END

If I make query to geometry_columns

(please see in image)

(output makes me feel Geometrycolumn added successfully , but check geometry not saying same :(


6) Executing UPDATE statement

UPDATE rail SET geom = LineStringFromText('LINESTRING(6.71502 50.7393,6.72127 50.74388)',4326)WHERE LINK_ID= 50863366

it's giving me error

constraint failed
rail.geom violates Geometry constraint [geom-type or SRID not allowed]

Please guide me.

[Hardik Pandya]

correction::

6) Executing UPDATE statement

UPDATE rail SET geom = Transform(LineStringFromText('LINESTRING(6.71502 50.7393,6.72127 50.74388)',4326),32632)WHERE LINK_ID= 50863366

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

Hi Hardik,

> 4) ADDING GEOMETRY COLUMN

>
> SELECT AddGeometryColumn('rail','geom',32632,'LINESTRING','XY')
>

in this step you are creating a Geometry Column imposing to
always enforce the following contraints for each INSERT or
UPDATE (via safeguard Triggers):

a) GeometryType must by LINESTRING
b) Dimensions must by XY
c) and the SRID value must by 32632

> UPDATE rail SET geom = LineStringFromText('LINESTRING(6.71502
> 50.7393,6.72127 50.74388)',4326)WHERE LINK_ID= 50863366
>
> it's giving me error
>
> constraint failed
> rail.geom violates Geometry constraint [geom-type or SRID not
> allowed]
>

it's rather obvious that in this case the safeguard Trigger
will reject any attempt to INSERT/UPDATE such Geometry.
and the reason is exactly the one clearly reported by
the returned error message.

LINESTRING(6.71502 50.7393,6.72127 50.74388)',4326)

this WKT expression will effectively create a Linestring XY;
anyway the SRID is explicitly set as 4326; but this clearly
is a blatant violation of the SRID contraint :-D

the correct way to perform such an operation should be instead:

UPDATE rail SET geom = ST_Transform(LineStringFromText(
'LINESTRING(6.71502 50.7393,6.72127 50.74388)',4326), 32632)
WHERE LINK_ID= 50863366;

bye Sandro

[Hardik Pandya]

Dear Sandro,

Thank you very much. I completely understand flow now.

It looks me some problem in UPDATE statements (still not sure but maybe)

I will check one more time and will back to you.

[Hardik Pandya]

Hello Sandro,

I have found the problem.

There are few UPDATE statements which have another SRID.

UPDATE rail SET geom = Transform(LineStringFromText('LINESTRING(2.95224 51.15385,2.95148 51.15467)',4326),32631) WHERE LINK_ID= 53688623

how can I resolve this issue ?

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

On Tue, 8 Jul 2014 08:11:10 -0700 (PDT), Hardik Pandya wrote:
> Hello Sandro,
>
> I have found the problem.
>
> There are few UPDATE statements which have another SRID.
>
> UPDATE rail SET geom =
> Transform(LineStringFromText('LINESTRING(2.95224 51.15385,2.95148
> 51.15467)',4326),32631) WHERE LINK_ID= 53688623
>
> how can I resolve this issue ?
>

this statement is clearly invalid, and you have to rewrite
it in a more convenient form.
the srid *must* absolutely be 32632, because this is the
value declared by AddGeometryColumn.

Just for my personal curiosity: from where comes out this 32631 ??

bye Sandro

[Hardik Pandya]

Hello Sandro,

Currently for time being I have assigned 32632 for all the Geometry which going to build. (or I can say SRID will be built as per the first latitude and longitude)

SRID i am building with below logic. You only helped me out for same.

======================================
Let's suppose I have value POINT(23.21532 69.9537)
    longitude:: 23.21532
    latitude :: 69.9537

For, Zone      = ((longitude+180)/6) + 1
                   = 34 (for mine)

For, SRID     = if(latitude > 0)
                             SRID = 32600 + zone
                     else
                             SRID = 32700 + zone
 
        = 32634  (in this case)
============================================

You also said me :: each UTM zone works at its best in a 6
longitude degrees fuse: but you can rather safely extend
this fuse to 12 degrees (no more tha this !!!), and this
will introduce only very limited errors, practically
neglectable for any practical scope.

So I think making SRID 32631 to 32632 will not impact much on my result.. can it ?