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.

GIStrees
  • 119
  • 1
  • 5
  • 2
    I would try finding them by their compactness – BERA Nov 25 '23 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 '23 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 '23 at 20:11
  • You can find the self intersecting polygons by st_isvalid. And/or fix them with st_makevalid – BERA Nov 30 '23 at 12:24
  • There's several approaches discussed in https://gis.stackexchange.com/questions/316128/identifying-long-and-narrow-polygons-in-with-postgis. In newer PostGIS versions you can use ST_MaximumInscribedCircle to find polygons with a small "width". – dr_jts Dec 01 '23 at 19:29
  • Thanks all. The problem is that I also have many very small polygons which will then also be selected with the ST_MaximumInscribedCircle and ST_ReducePrecision. But I might be using a combination of methods using threshold values. I might be trying a method that checks the ratio between the length of the longest boundaries against the area size as I think that best represents needle polygons. – GIStrees Dec 13 '23 at 06:31

0 Answers0