My problem is that I want to get the shortest route between multiple point pairs which are stored in a table (PostGIS).

In PostGIS I also have a network created using pgr_createTopology on a table containing about a 1000 MultiLineString rows. It's my understanding that to calculate routes between points with pg_routing, you need to either accept that the route goes to and from the nearest network node or you need to work around that limitation by adding new nodes to the network which corresponds to the stops you want to calculate on.

To add new nodes to my network I've tried tried to follow the answer from @alpha-beta-soup in this tread
- Split lines into non-overlapping subsets based on points
Before following the answer I used ArcMap to snap the points to the lines. I then imported the two shapefiles (network and radars both with geometry columns "geom") into PostGIS using the PostGIS Shapefile loader (2.1).

When using alpha_beta_soup's code I get a table with all the right columns, but there are zero rows in it, I've tried running only the first part of the code, but I still only get a table with no content. This is the first part of the code:

CREATE TABLE tmp_lineswithstops AS (
WITH subq AS (
        ST_ClosestPoint(roads.geom, radars.geom)
    ) AS LR,
    rank() OVER (
        PARTITION BY roads.gid
        ORDER BY ST_Line_Locate_Point(
            ST_ClosestPoint(roads.geom, radars.geom)
    ) AS LRRank,
    ST_ClosestPoint(roads.geom, radars.geom),
    FROM network AS roads
    LEFT OUTER JOIN radars
    ON ST_Distance(roads.geom, radars.geom) < 0.0001
    WHERE ST_Equals(ST_StartPoint(roads.geom), radars.geom) IS false
    AND ST_Equals(ST_EndPoint(roads.geom), radars.geom) IS false
    ORDER BY gid, LRRank

The two tables look like this: radars table

network table

Does anyone have a clue as to what i'm doing wrong?

  • Try breaking down that command into its components. For instance, just try SELECT ST_Line_Locate_Point(roads.geom, ST_ClosestPoint(roads.geom, radars.geom)) AS LR FROM... It's hard to tell what's wrong otherwise. It could be topology, it could be something in the code that I've overlooked. I just copied that code from an old file and changed it slightly so it had more general names. Which parts of the statement work, and which don't? Try it without each of the WHERE clauses, for example. Keep making it smaller. – alphabetasoup May 17 '15 at 9:58
  • When running that small statement (SELECT ST_Line_Locate_Point(network.geom, ST_ClosestPoint(network.geom, radars.geom)) AS LR FROM network, radars), I get the error - ERROR: line_locate_point: 1st arg isnt a line CONTEXT: SQL function "st_line_locate_point" statement 2 ********** Error ********** ERROR: line_locate_point: 1st arg isnt a line SQL state: XX000. So could the error be that it's a MultiLineString and not a LineString? – JonasPedersen May 17 '15 at 10:12
  • Yes, I think that is quite likely. You probably need singlepart lines for your network to make any sense. There's a tool to convert your lines that you can run first. – alphabetasoup May 17 '15 at 18:50

In case anyone lands here searching how to split lines with points, here is a current (2019) solution.

  • union point geometry
  • snap lines to points with tolerance of 1m - for projected coordinate system (if using geographic you'll need to adjust this value to degrees (0.00001 very roughly equals 1m)
  • split lines at points
  • insert source lines that weren't processed because they didn't intersect any points

id column must be unique or this won't work correctly:

create table edges_split as
  select a.id, (st_dump(st_split(st_snap(a.geom,b.geom,1),b.geom))).geom as geom from
    edges a, 
    (select id, st_union(geom) as geom from points) b;
insert into edges_split (id, geom) 
  select id, geom from edges 
  where id not in (select id from edges_split);

Your Answer

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

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