5

I need to get the intersections of streets and create a graph, so to get the intersections I use this select:

SELECT ST_geometrytype(geom), geom from (SELECT
a.gid, st_intersection(a.geom, b.geom) as geom
    FROM
        streets a
    JOIN
        streets b ON st_intersects(a.geom, b.geom)
    and
        a.gid!=b.gid
    and
        a.gid>b.gid) as foo

enter image description here

But this query returns different types of geometries, and some rows returns ST_GeometryColletion.

enter image description here

I tried to use this select:

SELECT ST_geometrytype(geom), geom from (SELECT
    (ST_Dump(st_intersection(a.geom, b.geom))).geom as geom
        FROM
            streets a
        JOIN
            streets b ON st_intersects(a.geom, b.geom)
        and
            a.gid!=b.gid
        and
            a.gid>b.gid) as foo

enter image description here When I used this query many linestrings were "broken" in short linestrings.

enter image description here

enter image description here

You can see that linestrings have some common subset, but I think that all subset would be only one linestring, do I wrong?

If the subsets is right, I need to know how to join the subsets into only linestring (the same intersection). I need this to then create a graph, the linestrings will be my vertex.

enter image description here

How can I do this?

  • 1
    Are you asking how to merge multiple linestrings or create a graph. ST_LineMerge might be what you are looking for, for the first part. A minor point, but if you have a.gid > b.gid then you don't need a.gid != b.gid (though the query planner will remove this, of course). – John Powell Apr 17 at 7:46
  • 1
    you are trying to node a network, that is, you want vertices at intersections, as the core topology for a routing graph (?). I suggest to have a look at pgRouting as a routing extension to PostgreSQL/PostGIS, and the function pgr_nodeNetwork in particular. – ThingumaBob Apr 17 at 7:54
  • 1
    ST_Intersection works differently than you seem to expect; it doesn't return points of intersection per se, but any geometric shape that two geometries have in common. thus your results. – ThingumaBob Apr 17 at 7:59
  • @JohnPowell, first I need to merge multiple linestrings. I tried to use ST_LineMerge, but it did not work. I want to merge just the linestrings that has a common path. How can I do this ? Thanks – Jessicadomingues Apr 17 at 17:49
  • @ThingumaBob I don't know about pgRouting, I'll look for it, thanks. – Jessicadomingues Apr 17 at 17:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.