Geometry constraint [geom-type or SRID not allowed]

[Elham Peiravian]

Hi Everyone,

I have a table called 'roads' containing LINESTRING road features. I am using the following query to create offsets of the roads and store them in a separate table:

insert into road_offset(id, segmentA, segmentB) select id, st_transform(st_offsetCurve(st_transform(segment, 3347), -5), 4326), st_transform(st_offsetCurve(st_transform(segment, 3347), 5), 4326) from roads;

I initially tested this method with a small dataset, (~3000) rows and everything worked perfectly. Now that I want to run the query on a larger dataset, I am getting a geometry constraint error. I've used the ST_IsValid() function and the output is 1. I know which features are causing this error and when I both check their spatial metadata in spatialite and visualizing them in QGIS, everything seems ok. But I still get the Geometry constraint [geom-type or SRID not allowed] error and the query won't run. The SRID of both my 'roads' and 'road_offset' table is 4326.

I thought maybe there is an extra step I can take to check the geometry of the features, or if the issue lies some where completely different?

[Elham Peiravian]

Forgot to mention that this is how  I create my geometry columns in the road_offset tabe:

select addgeometrycolumn('road_offset', 'segmentA', 4326, 'LINESTRING', 'XY');
select addgeometrycolumn('road_offset', 'segmentB', 4326, 'LINESTRING', 'XY');

select createspatialindex('road_offset', 'segmentA');
select createspatialindex('road_offset', 'segmentB');

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

On Tue, 18 Jul 2023 05:12:31 -0700 (PDT), Elham Peiravian wrote:
> I initially tested this method with a small dataset, (~3000) rows and
> everything worked perfectly. Now that I want to run the query on a
> larger dataset, I am getting a geometry constraint error.
>

Hi Elham,

evidently all this means that in a few rare cases your approach fails,
so you simply have ot discover where, when and why this happans.

luckily there is a very quick and easy way to find out; let SQL
do the work for you.


CREATE TABLE test_table AS
SELECT id,
ST_Transform(ST_OffsetCurve(
ST_Transform(segment, 3347), -5), 4326) AS segmentA,
ST_Transform(ST_OffsetCurve(
ST_Transform(segment, 3347), 5), 4326) AS segmentB
FROM roads;

this query is exactly equivalent to yours, except for one detail.
now no constraints will be applied to output geometries, and
any possible result will be inserted into the target table.


SELECT * FROM test_table
WHERE GeometryType(segmentA) <> 'LINESTRING'
OR Srid(segmentA) <> 4326
OR GeometryType(segmentB) <> 'LINESTRING'
OR Srid(segmentB) <> 4326;

at this point a trivial SQL query will allow you to identify
any issue causing a violation the constraints imposed
on the geometries.

you'll see that at this point you'll be able to understand with
very little effort where you need to correct your initial query.

conclusion: nothing is better than a bit of healthy debugging :-D

bye Sandro

[Elham Peiravian]

Hi Sando,

I did what you explained and I get an empty result set which if I am not wrong means there were no issues found. Could there be anything else causing this error?

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

On Tue, 18 Jul 2023 07:29:34 -0700 (PDT), Elham Peiravian wrote:
> Hi Sando,
>
> I did what you explained and I get an empty result set which if I am
> not wrong means there were no issues found. Could there be anything
> else causing this error?
>

Hi Elham,

this query allows you to check all the values ​​that are evaluated
by the geometry constraints

SELECT GeometryType(geom), Srid(geom), CoordDimension(geom)
FROM some_table;

carefully check once again: if you can't detect any constraint
violations in theory everything should be fine.
having said this, however, there can be an almost infinite
number of other disturbing factors, as e.g.:

a) insufficient memory
b) not enough disk space
c) some nasty mmory corruption
(may be due to buffer overflows)
making SQLite to become crazy
d) ... and many, many others

as you can see, it's a bit like looking
for a needle in the middle of a haystack.

just a suggestion: try to insert your geometries
not all together in a single pass but working for
small consecutive blocks.

with a few tries you should be able to isolate
where the critical points are.

bye Sandro

[Elham Peiravian]

Hi Sandro,

Thank you. By using  GeometryType(geom) I was able to find that some of the line features created by the ST_OffsetCurve function are actually MultiLinestring instead of Linestrings. I tried adding the following functions to my query with hopes of the final outputs being Linestrings:
DissolveSegments, UnaryUnion,  LineMerge. 

All these still produced MultiLinestrings.  What else do you suggest I can try?

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

On Thu, 20 Jul 2023 06:36:37 -0700 (PDT), Elham Peiravian wrote:
> What else do you suggest I can try?
>

1) create your Geometry Columns of the MULTILINESTRIG type
recall: a simple geometry can alwayes be representeed as
a corresponding MultiXXX containing just 1 single element.

2) then in the INSERT INTO query just call the cast function
CastToMultilinestring()
so to promote all simple linestrings to multilinestrings

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

select addgeometrycolumn('road_offset', 'segmentA', 4326,

'MULTILINESTRING', 'XY');

select addgeometrycolumn('road_offset', 'segmentB', 4326,

'MULTILINESTRING', 'XY');

SELECT id,
CastToMultiLinestring(ST_Transform(ST_OffsetCurve(.....))),
CastToMultiLinestring(ST_Transform(ST_OffsetCurve(.....)))
FROM roads;

bye Sandro