0

I have a PostGIS database with a layer of 60k polygons, some of them erroneous. These erroneous polygons are extremely narrow, long shaped. Maybe I can best describe them as 'Needle' shaped polygons. I need to detect these 'needle' polygons with an SQL query.

Can I do this with a ratio calculation on the edges or is there a more efficient and robust SQL function to do this?

At present I used the LongestLine function with a default setting for the length, which does not seem to be 100% robust:

SELECT t.identifier, t.country, t.id_planting_site, ST_AsText(ST_LongestLine(pol, pol)) AS llinewkt,
                  --ST_MaxDistance(pol, pol) AS max_dist,
                  ST_Length(ST_LongestLine(pol, pol), true) AS lenll_m,

ST_AsText(ST_ShortestLine(pol, pol)) AS slinewkt,
                  --ST_MaxDistance(pol, pol) AS min_dist,
                  ST_Length(ST_ShortestLine(pol, pol), true) AS lenls_m
FROM registration_areas AS t
where 
ST_Length(ST_LongestLine(pol, pol), true) > 10000
order by t.country

I also tried the MinimumBoundingCircle:

ST_Area(geom)/(ST_Area(ST_MinimumBoundingCircle(geom))

However, as many of these 'needle' polygons also have self-intersections, this solution does not seem to be robust neither.

6
  • 2
    I would try finding them by their compactness
    – BERA
    Nov 25, 2023 at 12:28
  • Thanks @Bera for pointing in a direction. Its seems that you understand my question while the moderator does not (question is closed): Procedures for posting seems very subjective on this forum. What is so unclear about my question for closing it: no idea. I've seen poorer postings that remained open.
    – GIStrees
    Nov 25, 2023 at 15:50
  • 1
    Depending on your exact needs, other (or extra) checks you can consider are ST_ReducePrecision or ST_Buffer with a negative distance. Using those function you can find polygons that are narrower than a certain thresshold.
    – Pieter
    Nov 25, 2023 at 20:11
  • You can find the self intersecting polygons by st_isvalid. And/or fix them with st_makevalid
    – BERA
    Nov 30, 2023 at 12:24
  • There's several approaches discussed in gis.stackexchange.com/questions/316128/…. In newer PostGIS versions you can use ST_MaximumInscribedCircle to find polygons with a small "width".
    – dr_jts
    Dec 1, 2023 at 19:29

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.