3

I Have a modified version of the function from How to ST_Split features in one table by features in another?

CREATE OR REPLACE FUNCTION ST_Multi Split(geom Geometry, blades Geometry) RETURNS Geometry AS $$
DECLARE
  
  geom_collect varchar;
  blades_collect varchar;
  blade_count int;
  
BEGIN
 
 SELECT ST_AsText (ST_Collect (geom)) into geom_collect where ST_Intersects (geom, blades);
 SELECT ST_AsText (ST_Collect (blades)) into blades_collect where ST_Intersects (geom, blades);
 SELECT st_numgeometries(ST_GeomFromText(blades_collect)) into blade_count;
 IF blade_count is null or blade_count = 0 then
  RETURN null;
 ELSE
  FOR i IN 1..blade_count
  LOOP
  geom = ST_Split(ST_SetSRID(ST_GeomFromText(geom_collect), 4326), ST_GeometryN(ST_SetSRID(ST_GeomFromText(blades_collect), 4326), i));
  END LOOP;
  RETURN geom;
 END IF;
END;
$$ LANGUAGE plpgsql;

The original funcion doe the same. It returns "double" geometries i.e. when one line is split with two points it returns 4 lines each split at a particular point:

What happens

I need it to do the following for an entire network:

WHat I need is to do

I am trying so split an road network at the intersections. I have already created the point "blade" layer where I want it split, and have a OSM extract.

How should I modify the function?

2
  • Inside the loop, you are always using the source geom_collect instead of using the previously split geom.
    – JGH
    Commented Jan 30, 2023 at 14:30
  • While there, you should remove the conversion to and from text. Work with geometries directly.
    – JGH
    Commented Jan 30, 2023 at 14:32

1 Answer 1

1

The obvious approach - but I dare say you'd be surprised how badly it is going to perform, and fail.

The inherent floating point imprecision will make intersections between Point coordinates and a LineString (mathematically defined as a vector between two vertices) close to impossible for anything but a regularly gridded linework.

Besides, you likely get much faster results by splitting with a MultiPoint directly:

SELECT
  nw.<id>,
  dmp.path[1],
  dmp.geom
FROM
  <network> AS nw,
  LATERAL (
    SELECT
      ST_Split(nw.geom, ST_Collect(pt.geom)) AS geom
    FROM
      <point> AS pt
    WHERE
      ST_Intersects(nw.geom, pt.geom)
  ) AS bd,
  LATERAL ST_Dump(bd.geom) AS dmp

Still mostly failing.

If you want to keep going with a pure PostGIS approach to simply node a network, better use ST_Node instead:

SELECT
  dmp.geom
FROM (
  SELECT
    ST_Node(
      ST_Collect(nw.geom)
    ) AS geom
  FROM
    <network> AS nw
) AS nd,
  LATERAL ST_Dump(nd.geom) AS dmp
;

However, there's no way to keep any of your network attributes with this approach!


I strongly suggest to consider moving to pgRouting, and specifically using the pgr_nodeNetwork function to not only node the network efficiently, but also create a true network topology with intact node/edge references.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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