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?
Centroids (All the points are there)
Edges table (Edges table has centroids and lines, and this plot shows the lines)
After the deleting query, centroid points in edges table
The edge lines in edges table will be same as 2 visually