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 AS SELECT a.*,gid,nextval('seq') AS edge_gid, ST_SetSRID(ST_MakeLine(a.geom_c, b.geom_c),4326) AS geom_line FROM centroids a, centroids b WHERE ST_Distance(a.geom_c, b.geom_c) < 0.26;
Remember that the edges table has the centroid points as well.The above method will create 4 edge lines for each centroid point. Hence, to delete the duplicate edge lines, 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 edge lines 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?
The edge lines in edges table will be same as 2 visually