0

So I have a table with approximately 10k rows, with each row having its corresponding geometry entry. What I am attempting to do is count how many row geometries are contained/within the geometry of a single row as follows:

SELECT id, COUNT(*) FROM poly_table
WHERE WITHIN(poly_table.geom, poly_table.geom)
GROUP BY id ORDER BY COUNT(*) DESC;

However, all I get in return is a table with all rows having a COUNT(*) value of 1. So in understand they will certainly contain themselves but there are definitely polygons within polygons that are not accounted for.

1

You are comparing each geometry to itself, and for [ST_]Within/[ST_]Contains, a geometry is a positive match for itself!

You need to self-join the table to allow for comparisons between different rows of it:

SELECT a.id,
       COUNT(b.id)
FROM   <table> AS a
JOIN   <table> AS b
  ON   ST_Within(b.geom, a.geom)
 AND   a.id <> b.id
GROUP BY
       a.id
;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.