[postgis-users] Finding all 'adjacent' points in a point set

[Bruce Rindahl]

I have a table of points on a regular hexagon grid spacing.  I need to
create an edge table between every point and the points in its adjacent
hexagon.
Say the table is points with an id field and point geom.  I am trying :

WITH
p1 as (select * from points),
p2 as (select * from points)
SELECT
p1.id as src,
p2.id as dest,
st_makeline(p1.geom,p2.geom) as geom,
FROM p1, p2
WHERE p1.id != p2.id  -- don't link a point to itself
and  ST_dwithin (p1.geom,p2.geom, 16 * 1.74 )  -- only link to nearest 6
points (spacing 16)
and p1.geom > p2.geom  -- only link in one direction

This works fine but gets really slow with lots of points.  Is there a
better way of attacking this problem?

Thanks
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20210729/8b383112/attachment.html>