4

I have a neighborhoods layer and a parcels layer. I want to find how many parcels are in each layer.

Initially, I used this query:

SELECT neighborhoods.name, COUNT(parcels_kcgis.geom) AS total
FROM neighborhoods LEFT JOIN parcels_kcgis
ON ST_Contains(neighborhoods.geom, parcels_kcgis.geom)
GROUP BY neighborhoods.name

This query returns counts from all 200 neighborhoods in less than a second.

However, I noticed that it misses some parcels on the edges of the borders. I thought a good way to do this would be to buffer the neighborhood geometry slightly:

SELECT neighborhoods.name, COUNT(parcels_kcgis.geom) AS total
FROM neighborhoods LEFT JOIN parcels_kcgis
ON ST_Contains(ST_Buffer(neighborhoods.geom, 10), parcels_kcgis.geom)
GROUP BY neighborhoods.name

Unfortunately this takes approximately 30 minutes to run. Using a where clause to only get one neighborhood takes 4-5 seconds.

I am trying to use this information in a web application and need a much quicker response time. Is there a better way to do this?

3
  • 1
    try st_dwithin() postgis.net/docs/ST_DWithin.html it will operate faster than using st_buffers within st_contains because you will not be creating an actual buffer, it will just be a boolean T/F to see how many parcels fall within each neighborhood
    – ziggy
    Jan 19, 2017 at 18:05
  • great suggestion, but with ST_Dwithin(neighborhoods.geom, parcels_kcgis.geom, 1) I get the opposite problem. Now the numbers are too high because it's including parcels in neighboring neighborhoods.
    – J M
    Jan 19, 2017 at 18:08
  • hmmm..maybe (ive never tried this) use the st_dwithin inside the st_contains? not sure if that will even work but give it a try
    – ziggy
    Jan 19, 2017 at 18:12

1 Answer 1

5

Loosey goosey overlap calculation, using centroid containment to pick up loose edge cases while avoiding double counting:

SELECT neighborhoods.name, COUNT(parcels_kcgis.geom) AS total
  FROM neighborhoods LEFT JOIN parcels_kcgis
    ON neighborhoods.geom && parcels_kcgis.geom 
   AND ST_Contains(neighborhoods.geom, ST_Centroid(parcels_kcgis.geom))
GROUP BY neighborhoods.name
1
  • 2
    Works perfectly! Makes sense that comparing the centroid would be a much better way to do it.
    – J M
    Jan 19, 2017 at 18:43

Your Answer

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

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