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

[Paul Ramsey]

Why are you materializing the tables in a CTE? Do a direct self-join

SELECT 
p1.id as src,
p2.id as dest,
st_makeline(p1.geom,p2.geom) as geom,
FROM points p1, points 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

And have an index on points, of course. 

CREATE INDEX points_geom_x ON points USING GIST (geom);



> On Jul 29, 2021, at 3:25 PM, Bruce Rindahl <bruce.rindahl at gmail.com> wrote:
> 
> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users