[postgis-users] Spatial lag (quadrat grid cells)

[Andreas Forø Tollefsen]

Hi all,

I am trying to create a fast and efficient way of calculating the number of
different order of neighbors for a quadrat vector cell polygon in a grid
having a specific value.

I tried to calculate this using ST_DWithin() but since my cells are
quadrat, ST_DWithin does not capture cells in the outer corner when trying
to count a high order number of neighbors.

For instance using 3rd order neighbors ST_DWithin only find 44 neighbors
while it should have found 48 neighbors. Hence, it misses the 4 corner
cells in the 7x7 grid representing the 3rd order neighbors.

So I was thinking about making quadrat buffers around the cell to do my
count. However, this takes a lot of time since it have to do the buffering
for each cell polygon in the grid. (some 259,200 of them, 720x360).

This was my query:

DROP TABLE IF EXISTS cf_spatiallag_n;

SELECT DISTINCT a.gid, c.gridyear, d.gwcode, count(DISTINCT b.gid)
INTO cf_spatiallag_n
FROM priogrid a, priogrid b, confsitegrid c, priogridall d
WHERE a.gid != b.gid AND b.gid = c.gid AND a.gid = d.gid AND c.gwcode =
d.gwcode AND c.cf = 1
AND ST_Intersects(ST_Buffer(a.cell, 0.25, 'join=mitre'), b.cell)
GROUP BY a.gid, c.gridyear, d.gwcode
ORDER BY c.gridyear, a.gid;

Any ideas on how i can optimize this query?

Thanks in advance.

Andreas
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120217/194efd31/attachment.html>