Search code examples
nodesgeospatialpostgisspatialedges

Avoid edge duplicates in PostGIS

I would love to create edges between nodes between which distance smaller than 5m. The following PostGIS code works.

create table edges as
  select a.id from_gid, 
         b.id to_gid, 
         st_shortestline(a.geom_26916, b.geom_26916), 
         st_distance(a.geom_26916, b.geom_26916) as distance
  from flowers as a,
      (select * from flowers) as b
  where st_dwithin(a.geom_26916, b.geom_26916, 5);

Nevertheless, it gives me duplicates of edges. How can I modify the code to get unique edges? Thanks!

Solution

  • The same table is queried twice (as a and b), so it will return an edge between records 1 and 2, and a second line between records 2 and 1. It will also return strange result for the same record (a point).

    You would need to add a filtering clause on id:

    ...
WHERE a.id < b.id AND st_dwithin(a.geom_26916, b.geom_26916, 5);