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!
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);