2

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) Centroids (All the points are there) Edges table (Edges table has centroids and lines, and this plot shows the lines) Edges table After the deleting query, centroid points in edges table Deleted points are highlighted

The edge lines in edges table will be same as 2 visually

1
  • I have changed the condition from ST_Distance(a.geom_c, b.geom_c) = 0.25 to <0.26 as well
    – kaja
    Sep 10, 2015 at 19:22

1 Answer 1

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".

8
  • I edited my question with two more images. Since each centroid have four edges from it (before deletion), it centroid id 1 will have edge id 1,2,3,4. Also I am sure the problem with the second step. When you delete with e1.edge_gid < e2.edge_gid logic, it is possible that certain centroids are totally removed though the edges are kept. That causes the pattern based deletion.
    – kaja
    Sep 10, 2015 at 13:08
  • I don't understand. Your points are in table centroid. The second query doesn't use table centroid, so how could it delete rows from it? Do you work with foreign keys or triggers? @kaja
    – Redoute
    Sep 10, 2015 at 13:24
  • I can see the confusion here. Edge table has all the centroid points as well as I am selecting "SELECT *,gid,nextval('seq') AS edge_gid...." in my first query to create edge table. So when I run the query to delete the duplicate edges, it will delete the whole row and hence some of the centroid points are deleted.
    – kaja
    Sep 10, 2015 at 14:34
  • Sorry for misunderstanding this. I rewrote my answer. @kaja
    – Redoute
    Sep 10, 2015 at 18:31
  • it did not solve the problem. I have updated my question bit more clearly. Edges table has edge lines as well as centroids and the final plot is from edges table not centroids table. The requirement is finally get a edges table with corresponding centroid points without any duplicate edge lines.
    – kaja
    Sep 11, 2015 at 15:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.