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.
    – user52245
    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

4

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.
    – user52245
    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.