Deleting duplicating edges while creating grids from centroid points

Question

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

Answer 1

The pattern you observed is related to the order the edge table rows are generated, which is not defined by your query. I don't see how you could repair your queries to meet your expectations.

My advice is to keep edges and nodes in distinct tables and join them by id. This is a basic principle with relational databases.

If the "centroids" input table is given I would create the edges table like this:

CREATE TABLE edges (
    gid serial PRIMARY KEY,
    gid_c1 integer,
    gid_c2 integer,
    geom geometry(linestring, 4326);
INSERT INTO edges (gid_c1, gid_c2, geom)
    SELECT
        a.gid,
        b.gid,
        ST_MakeLine(a.geom_c, b.geom_c)
        FROM centroids a, centroids b
        WHERE a.gid < b.gid AND
              ST_Distance(a.geom_c, b.geom_c) < 0.26;

Then you may delete isolated points from centroid:

DELETE
    FROM centroid
    WHERE NOT EXISTS (
        SELECT
            1
            FROM edges
            WHERE edges.gid_c1 = centroid.gid OR
                  edges.gid_c2 = centroid.gid);

or create a third table "nodes" if you don't want to touch table "centroid".