Linear referencing¶

Creating linear references¶

To reference an existing point table to a linear network, use the ST_Line_Locate_Point function. This function accepts a line and point, and returns a value representing the proportion along the line the point is located. In the following example, a point is located half-way along a line.

SELECT ST_Line_Locate_Point('LINESTRING(0 0, 2 2)', 'POINT(1 1)');

Locating a point along a line feature

The answer returned is 0.5—the distance along the line feature the point was located. If the point is not on the line, the result is determined by projecting to the closest location on the line.

SELECT ST_Line_Locate_Point('LINESTRING(0 0, 2 2)', 'POINT(0 2)');

Again, the answer returned is 0.5.

Locating a point near a line feature

To convert a nyc_subway_stations table into an event table relative to the streets, use the function ST_Line_Locate_Point. Create a new event table and identify a candidate set of streets that could be the closest to the subway stations. Order the streets by the id and distance attributes.

CREATE TABLE nyc_subway_station_events AS
WITH ordered_nearest AS (
SELECT
  ST_GeometryN(streets.the_geom,1) AS streets_geom,
  streets.gid AS streets_gid,
  subways.the_geom AS subways_geom,
  subways.gid AS subways_gid,
  ST_Distance(streets.the_geom, subways.the_geom) AS distance
FROM nyc_streets streets
  JOIN nyc_subway_stations subways
  ON ST_DWithin(streets.the_geom, subways.the_geom, 200)
ORDER BY subways_gid, distance ASC
);

Use the PostgreSQL DISTINCT ON feature to identify the nearest street for each unique street id. Pass the nearest street to ST_Line_Locate_Point along with its candidate subway station to calculate the measure.

SELECT
  DISTINCT ON (subways_gid)
  subways_gid,
  streets_gid,
  ST_Line_Locate_Point(streets_geom, subways_geom) AS measure,
  distance
FROM ordered_nearest;

To support visualization application software that require access to the results, add a primary key.

ALTER TABLE nyc_subway_station_events ADD PRIMARY KEY (subways_gid);

To reverse the process and go from a measurement to a point, use the ST_Line_Interpolate_Point function. For example, to locate a point along a line, execute the following:

SELECT ST_AsText(ST_Line_Interpolate_Point('LINESTRING(0 0, 2 2)', 0.5));

The answer returned this time is the location of the point.

POINT(1 1)

You can also join the nyc_subway_station_events table back to the nyc_streets table and use the measure attribute to generate the spatial event points, without referencing the original nyc_subway_stations table. The following example illustrates how to create a view that turns events back into spatial objects.

CREATE OR REPLACE VIEW nyc_subway_stations_lrs AS
SELECT
  events.subways_gid,
  ST_Line_Interpolate_Point(ST_GeometryN(streets.the_geom, 1), events.measure)AS geom,
  events.streets_gid
FROM nyc_subway_station_events events
JOIN nyc_streets streets
ON (streets.gid = events.streets_gid);

Viewing the original (red star) and event (blue circle) points with the streets, you can see how the events are snapped directly to the closest street lines.

Spatial objects and events