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

[Martin Davis]

Could you use a KNN query for this, choosing the 6 nearest neighbours?
Might still need to filter on distance to handle edge conditions, though.

On Thu, Jul 29, 2021 at 3:26 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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20210729/35195ad0/attachment.html>