CODEWITHSUNDEEP
sqlpostgis
Ron
Ron

Reputation: 97

Need polygon intersect counts with buffered polygon neighbors FOR EACH polygon

I'm trying to find whether its possible in purely SQL to generate a table with the number of intersects each polygon in a layer has with its corresponding neighboring polygons(buffered) in a buffered version of the layer.

A rough and flawed version is the following:

For each value in list:

SELECT 
    Count(*) 
INTO 
    intersectcounts
FROM
    parcels,parcelsbuffered
WHERE
    parcels.apn = value AND ST_INTERSECT(parcels.geom,parcelsbuffered.geom)

Here the geom is the polygon I need as result like

intersectscount table

APN COUNT
100   3
101  87
...
...

I could use python loop and modify the query string with a different value in the WHERE clause but I dont think this will have good performance - there are thousands of parcels(polygons)

Upvotes: 1

Views: 89

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

 SELECT parcels.apn, count(*) as intersectcounts
 FROM parcels 
 JOIN parcelsbuffered
   ON ST_INTERSECT(parcels.geom, parcelsbuffered.geom)
 GROUP BY parcels.apn

You probably want include some validation to remove the parcel intersect with his own buffered version like

(count(*) - 1) as intersectcounts

or 

WHERE parcerls.apn <> parcelsbuffered.apn

Upvotes: 2

Related Questions