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.rind...@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@lists.osgeo.org > https://lists.osgeo.org/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users