Splitting Lines With Intersecting Points Using PostGIS

Article Number : 11

In this article, I will teach you how to split LineString geometries using PostGIS, with your LineString Spatial table and another Point spatial table.

While doing this process, we first consider the points as a single whole and bring them together using the multipoint and ST_Collect function. This will be our table of objects that will cut the lines. That’s why I named it “splits” in the line of code.

With ST_Split, I defined the geometries to be cut to the first available parameter, and the geometry column of the cut objects to the second parameter. Since the geometries will be more than one after cutting, we need to separate them with ST_Dump.

Apart from all these, only intersecting geometries are combined with the ST_Intersects function to extract points and lines that do not have a spatial relationship with each other. After this information, it is now to run SQL code on the output screen with PostgreSQL’s capabilities. The code I’ve shared with you below does exactly that.

WITH
splits AS (
  SELECT ST_Collect(geom) AS geom
  FROM   point_table
)
  
SELECT a.id as line_id, dmp.geom
FROM   line_table AS a, splits AS b,
LATERAL ST_Dump(ST_Split(a.geom, b.geom)) AS dmp
   
UNION ALL

SELECT a.id as line_id,geom
FROM   line_table AS a
WHERE NOT EXISTS (
  SELECT 1
  FROM   point_table AS b
  WHERE  ST_Intersects(a.geom, b.geom)
);

You can use the output from here by creating a different table with “CREATE TABLE TABLE_NAME AS“.

Best of luck, hope it’s useful.