Connecting points (bus stops), which don't lie on lines (LINESTRING), to network?

Question

I need to connect bus stops (points) to a network layer (OSM data). These bus stops do not lie directly on the lines (see screenshot) nor should their location be moved. I use PostGIS, pgrouting and QGIS and the network is already routable with source and target columns etc.

Mainly I want to do two things afterwards:

1. Getting the distances between the bus stops using shortest path analysis.
2. Creating isochrones with walking distances from the bus stop using the OSM network.

To get exact values it is necessary, that the routing 'starts' and 'stops' most closely to the bus stops. In many cases the closest existing node will be too far away to get exact values. But there should not be a routing to the actual point location of the bus stop. In my example on the picture you can see how the routing between stops should look like.

Is there a possibility to insert new nodes automatically into the network (LINESTRING) which are closest to the bus stops or is it possible to start routing on a kind of 'dummy point' which is set just for the query (similar to what the road graph plugin in QGIS does)?

Answer 1

The first part of the solution is this:

SELECT a.id, ST_Closestpoint(ST_Collect(b.geom_way), a.geom) AS geom 
FROM point_table a, line_table b
GROUP BY a.id, a.geom;

This snaps the busstops to the lines of the road network as you can see in the picture and works quite easy.

Next I will try to split the lines at the locations of the points. After splitting the lines I want to use pgr_createTopology again. After that it should be possible to create a query to find out the nearest nodes to the busstops, which will than be my newly generated nodes at the 'splitpoints'.

I do not know how to split linestring with point features in PostGIS, since after I looked at similar questions there does not seem to be an easy solution for that at the moment.

Answer 2

This is my full solution. It involves a sort of a hack to get the splitting done: I get the points on the lines (ways, to use OSM terminology) using ST_ClosestPoint, and then buffer them by a very small distance to get the splitting to actually work. Otherwise imprecision / rounding errors were preventing the splitting.

This has the problem that it generates two splits on each line per point (because of the buffering). For my use this was fine, as later I routed between the splitting points nearest to the original points, which were outside the line, and it could be either of the two splitting points of the line-buffer intersection.

I started by downloading OSM data and impoting it into Postgres:

CITY="MY_CITY"
BBOX="-46.6003,-23.7362,-46.4806,-23.5965"
wget --progress=dot:mega -O "$CITY.osm" "http://www.overpass-api.de/api/xapi?*[bbox=${BBOX}][@meta]"

# create database
createdb my_database
# add extensions
psql -d my_database -c "CREATE EXTENSION postgis;"
psql -d my_database -c "CREATE EXTENSION pgrouting;"

# import osm data to postgres database
osm2pgrouting \
    -f MY_CITY.osm \
    -d my_database \
    -U user

# load points into db
shp2pgsql -I -s 4326 points_to_split_ways.shp public.points_to_split_ways | psql -d my_database

Splitting the ways using a buffer:

WITH pts_ways AS (
  -- get nearest way for each point we want to split the ways by
  SELECT s.gid AS pt_id, ws.gid AS way_gid, s.geom AS pt_geom, ws.the_geom AS way_geom FROM points_to_split_ways s
  CROSS JOIN LATERAL
  (
    SELECT w.gid, w.the_geom
    FROM ways w
    ORDER BY s.geom <-> w.the_geom LIMIT 1
  ) AS ws
), pts_on_ways AS (
  -- "move" these points to be on top of the ways
  SELECT pt_id, way_gid, ST_ClosestPoint(way_geom, pt_geom) as geom
  FROM pts_ways
), ways_without_pts AS (
  -- get the ways that don't have any points on them
  SELECT the_geom as the_geom, gid as way_gid FROM ways
  WHERE gid NOT IN (SELECT way_gid FROM pts_ways)
)
SELECT
  way_gid as old_id,
  -- we need to build a new unique ID, because split ways will share the old ID
  row_number() over(order by way_gid) as gid,
  -- this is the split way geometry
  the_geom
FROM (
  SELECT 
    way_gid,
    -- split the ways and dump into indiviudal segments
    (ST_Dump(ST_Split(line_geom, pt_geom))).geom AS the_geom
  FROM (
    (SELECT the_geom as line_geom, gid FROM ways) AS lines
    LEFT JOIN
    -- HACK: use a buffer to fix imprecisions / rounding errors
    -- this will generate one extra splitting per point (each buffer will intersect each way twice)
    -- but it's ok for our purposes
    -- also, collect them grouped by the way to handle cases where there are multiple points on the same way
    (SELECT ST_Collect(ST_Buffer(geom, 0.000001)) as pt_geom, way_gid FROM pts_on_ways GROUP BY way_gid) AS pts
    ON lines.gid = pts.way_gid
  ) AS tmp1
  -- union the ways without points, otherwise you'd get only the ones that were split
  UNION ALL
  SELECT way_gid, the_geom FROM ways_without_pts
) AS tmp2;

Create the topology needed for routing with pgrouting:

SELECT UpdateGeometrySRID('ways_split','the_geom', 4326);
SELECT find_srid('public','ways_split','the_geom');
ALTER TABLE ways_split ADD COLUMN "source" integer;
ALTER TABLE ways_split ADD COLUMN "target" integer;
ALTER TABLE ways_split ADD PRIMARY KEY (gid);
ALTER TABLE ways_split ADD CONSTRAINT ways_source_fkey FOREIGN KEY (source) REFERENCES ways_split_vertices_pgr (id) MATCH FULL;
ALTER TABLE ways_split ADD CONSTRAINT ways_target_fkey FOREIGN KEY (target) REFERENCES ways_split_vertices_pgr (id) MATCH FULL;
SELECT pgr_createTopology('ways_split', 0.00001, 'the_geom', 'gid', clean := TRUE);
SELECT pgr_analyzeGraph('ways_split', 0.000001, the_geom := 'the_geom', id := 'gid');

Answer 3

Since I am working on a similar task I just wanted to tell about the approach that I am using currently. This makes use of GRASS GIS, but as far as my experiments with PostGIS went, it is quite complicated to add multiple new points to existing LineStrings by splitting those LineStrings at the respective locations - although I am sure there is a convenient solution.

I now made use of the GRASS GIS v.net function using the option connect. Just choose input vector line layer and points layer. There's the option to either snap the points to the closest point on the lines, or to create new connections between the closest point on the lines and the new point.

Here is an before and after image. On the right hand side, for each point of the points layer a node on the roadnetwork was added:

Afterwards in PostGIS, after creating your ..._vertices_pgr table out of the roadnetwork just assign your points to the closest vertice so you can use them in your routing requests. For this task you can make use of the ST_ClosestPoint function as done by @Setraworks in his answer.

Disadvantages of this approach are:

• connecting of points with lines has to be done in GRASS GIS
• routes that are calculated might consist of many components (depending on quantity of newly added points)
• dynamic addition of new points not possible

This approach works fine if you have a defined number of points to be added to the roadnetwork (as in the example of the question with bus stops).

If anybody can provide a working example using PostGIS I would love to read about that!