Hunting down source of geometry violation

[ckgoo...@gmail.com]

Hi, I am getting a violation of geometry constraint error when running a query which is inserting polygons into a table on joining the intersections from two other tables.

I have checked the two source polygons are polygons.

They are all SRID 4326.

I am able to calculate an area for each polygon so they seem good polygons.

There are no null entries in my source tables.

What other kinds of checks can I do to try and work out what might be causing the problem?

Best, Chris

[Jukka Rahkonen]

Hi,

Create a new target table to use generic "geometry" type, run your query and check what other geometries than polygons get inserted. Then it is easy to locate the problematic place, find the source polygons and study them more closely.

-Jukka Rahkonen-

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

On Wed, 23 Oct 2024 21:01:21 +0100, ckgoo...@gmail.com wrote:
> Hi, I am getting a violation of geometry constraint error when
> running
> a query which is inserting polygons into a table on joining the
> intersections from two other tables.
>

Hi Chris,

you can never assume that the outcome of an intersection
between two POLYGONs will always be another POLYGON, because
in reality the result can be a geometry of any possible class.

just few practical examples to understand better:

1. the attached figure shows the case of an intersection
between two polygons resulting in a MULTIPOLYGON

2. SELECT ST_Intersection(
GeomFromText('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
GeomFromTexT('POLYGON((1 0, 2 0, 2 1, 1 1, 1 0))')) AS geom;
in this special case the resulting intersection
actually is a LINESTRING because the two polygons
have touching borders.

3. SELECT ST_Intersection(
GeomFromText('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
GeomFromTexT('POLYGON((1 1, 2 1, 2 2, 1 2, 1 1))')) AS geom;
in this other special case the resulting intersection
is a POINT because the two polygons have a touching
vertex

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

in all these cases the right method to understand where
the problem are is the one reported by Jukka.

even simpler; you can create a temporary table for storing
the intersection results as generic unqualified blobs, e.g.

CREATE TEMPORARY TABLE tmp_table AS
SELECT <your join and intersect query>;

SELECT ST_SRID(geom), ST_GeometryType(geom), ST_NDims(geom)
FROM tmp_table;

bye Sandro

[ckgoo...@gmail.com]

|Hi Jukka, Sandro,
Wonderful. Thanks for the explanations and the solution. All obvious when you know.
Looks like 1% of my results were MULTILINESTRING type.
Will also try putting the filter
WHERE ST_GeometryType( polygon ) = 'POLYGON'
Into my join query.
Best, Chris

--
You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to spatialite-use...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/spatialite-users/beb926ea2a61b9c0862888b18b47cad1%40lqt.it.

[Pieter Roggemans]

Hey Chris,

Also check out ST_CollectionExtract. The intersection can also contain a combination of point, lines and polygons, and if you filter on only polygons you'll lose those...

Regards,
Pieter

Op do 24 okt 2024 19:07 schreef <ckgoo...@gmail.com>:

To view this discussion visit https://groups.google.com/d/msgid/spatialite-users/000901db2636%249c309280%24d491b780%24%40gmail.com.

[ckgoo...@gmail.com]

Thanks.  That’s also a section of the functions table I haven’t explored  before.  Lots of new functions there.

Best, Chris

To view this discussion visit https://groups.google.com/d/msgid/spatialite-users/CALDLyw81rrSYz2Y%2BgA%3D%3Dv1MV8UpdbEsntJn5CRFTQ3emp%3DSeGw%40mail.gmail.com.