self intersecting polygon SQl

Question

I am trying to write a sql to query one land parcel for intersections between itself. I can do this fairly easy in QGIS using topology checker but i don’t know why this does not produce the correct results.

    select * from land_parcel as a     
join  land_parcel as b where     
ST_Intersects (a.geom,b.geom) and a.gid <>b.gid

It is written in your question, actually but are we speaking only about polygons? Are there multipolygons as well? Which DBMS are you using? — Stefan, Mar 04 '14 at 09:30

score 1 · Answer 1 · answered Mar 04 '14 at 08:09

1

Two commands that should help you test the geometry are ST_IsSimple and ST_IsValid. Once you have identified the bad geometry you can use the plpgsql cleangeometry function which is available from the link on the website (which also gives suggestions on how to roll-your-own proceedure).

answered Mar 04 '14 at 08:09

MappaGnosis

33,857
2
66
129

ST_valid shows that all the geometries are correct and ST_IsSimple shows false indicating that they are indeed intersections. So how do I write a sql to show these intersection. I need to create a view to show where they are self intersecting polygons – kartoza-geek Mar 04 '14 at 08:16
Have a look at the page linked to the cleangeometry function. – MappaGnosis Mar 04 '14 at 09:24

score 0 · Answer 2 · answered Mar 04 '14 at 10:08

You can try these two queries with PostgreSQL:

SELECT
    a.gid,
    a.geom
FROM land_parcel AS a
    LEFT JOIN land_parcel b ON a.gid <> b.gid
        WHERE ST_Intersects (a.geom,b.geom)

...

SELECT
    a.gid,
    a.geom
FROM land_parcel AS a, (SELECT gid, geom FROM land_parcel) b
    WHERE ST_Intersects (a.geom,b.geom) 
        AND a.gid <> b.gid

self intersecting polygon SQl

2 Answers2