I am creating a edge table (edges) from some centroid points table(centroids). The distances between the centroids are 0.25 horizontally and vertically. The query I used to create the edge table as follows:
CREATE TABLE edges ASSELECT *,gid,nextval('seq') AS edge_gid, ST_SetSRID(ST_MakeLine(a.geom_c, b.geom_c),4326) AS geom_line FROM centroids AS a,centroids AS b) AS tmp WHERE ST_Length(ST_Transform(ST_SetSRID(tmp.geom_line,4326),4326)) = 0.25;The above method will create 4 edges for each centroid point. Hence, to delete the duplicate edges, I have used the following query:
DELETE FROM edges WHERE edge_gid IN ( SELECT e1.edge_gid FROM edges e1, edges e2 WHERE st_equals(e1.geom_line, e2.geom_line) AND e1.edge_gid < e2.edge_gid);But this one resulted in certain centroids are deleted in a pattern. Any help on how to delete the duplicate edges without the above issue? I understand that deleting based on the gid causes this one.
The techniques I have used are from the approached discussed here:Is it possible to make a routable graph from Polygons?
أكثر...
CREATE TABLE edges ASSELECT *,gid,nextval('seq') AS edge_gid, ST_SetSRID(ST_MakeLine(a.geom_c, b.geom_c),4326) AS geom_line FROM centroids AS a,centroids AS b) AS tmp WHERE ST_Length(ST_Transform(ST_SetSRID(tmp.geom_line,4326),4326)) = 0.25;The above method will create 4 edges for each centroid point. Hence, to delete the duplicate edges, I have used the following query:
DELETE FROM edges WHERE edge_gid IN ( SELECT e1.edge_gid FROM edges e1, edges e2 WHERE st_equals(e1.geom_line, e2.geom_line) AND e1.edge_gid < e2.edge_gid);But this one resulted in certain centroids are deleted in a pattern. Any help on how to delete the duplicate edges without the above issue? I understand that deleting based on the gid causes this one.
The techniques I have used are from the approached discussed here:Is it possible to make a routable graph from Polygons?
أكثر...