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

[Andreas Forø Tollefsen]

I have attached an image showing the issue with building neighbor lags
using ST_DWithin.

Test query:
SELECT p.gid, p.cell FROM priogrid p, priogrid b WHERE b.gid = 111996 AND
ST_DWithin(p.cell, b.cell, 3.25)


Andreas


2012/2/17 Andreas Forø Tollefsen <andreasft at gmail.com>

> 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/6afdeaa3/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: st_dwithin_neighbor.jpg
Type: image/jpeg
Size: 65280 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120217/6afdeaa3/attachment.jpg>