[postgis-users] Split(line, point), a temporary version until ST_Split gets fixed

[Rémi Cura]

Hey,
overall seems like you should use postgis topology
(or GRASS GIS).

Secondly,
I'm afraid you don't use the function correctly.

You should
- for each line
  - find points that may cut the line
  - group those points into a blade
  - cut the line with the blade

Which is not at all what you do .

Your querry should look like this (provided you don't use postgis topology,
which has been designed exactly for your use case)

WITH points_intersecting_lines AS( --for each line, which point shall
intersect it
   SELECT lines.id AS lines_id, lines.geom AS line_geom,
ST_Collect(points.geom) AS blade
   FROM lines, points
   WHERE st_dwithin(lines.geom, points.geom, your_tolerance) = true
   GROUP BY lines.id, lines.geom
)
SELECT lines_id, rc_split(line_geom, blade, your_tolerancy)
FROM points_intersecting_lines
--note : lines.geom and points.geom should have GIST index

it should take less than a minute (obviously not tested here! )

If perf is a big issue, I'd recommand using st_snap to grid, and st_node.

Cheers,
Rémi-C

2015-07-16 20:06 GMT+02:00 Vladimir Ezequiel Bellini <vlasvlasvlas at gmail.com
>:

> nicely done..
>
> the thing is:
>
> st_union table with lines (Crossing lines, like street lines) : 1200 rows
> (lines)
> st_union table with points (intersection points of this lines): 1700 rows
> (points)
>
> the test with rc_split_line_by_points (replace lines & points with my
> tables and kept tolerance 4) is really slow.. like 2 hours ago and still
> going......... is this normal?
>
> i already did the st_dump(st_split way and this also is a huge delay.. and
> the result is bringing me lots of garbage that i must replace after.
>
>
> ------------------------------
> ## 1 First attempt (really long, without discint / clean table is a whole
> mess like millions of rows). And this takes really forever..really
> slow..like 6hrs : 1200lines, 1700 points
>
> --first
> CREATE TABLE lines_with_mess AS (
> SELECT
> ((ST_DUMP(ST_SPLIT(a.geom,b.ix))).geom) as geom
> FROM st_union_lines a
> INNER JOIN lots_of_points b
> ON ST_INTERSECTS(a.geom, b.ix)
> );
> --then
> create table lines_clean_segments as (
> SELECT DISTINCT ON (ST_AsBinary(geom))
> geom
> FROM
> lines_with_mess
> );
>
> source of this way/approach :
> http://stackoverflow.com/questions/25753348/how-do-i-divide-city-streets-by-intersection-using-postgis
>
> --------------
>
> ---------------------------------
> ## 2nd attempt: cleaner but still takes really a lot of time......
>
> --first, really gets also messy, lots LOTS of unwanted rows in result
> Create table lines_segments as
>   Select
>     row_number() over() as id,
>     (st_dump(st_split(input.geom, blade.ix))).geom as geom
>   from st_union_lines input
>   inner join lots_of_points blade on st_intersects(input.geom, blade.ix);
>
> --then cleaning the table
> delete from lines_segments a
> where exists
>   (
>   select 1 from lines_segments b where a.id != b.id and
> st_coveredby(b.geom,a.geom)
>   );
>
> source of this way / approach :
> http://gis.stackexchange.com/questions/115973/split-lines-at-intersection-points/115982#115982
>
> ---------------------
>
>
> Now i'm testing your function but i still got really delayed for result
> and dunno if i got messy rows..
>
>
> ...
>
> :\
>
> Vladimir.
>
>
> El martes, 4 de marzo de 2014, 12:54:33 (UTC-3), Rémi Cura escribió:
>>
>> Hey List,
>> here is a link a link
>> <https://github.com/Remi-C/PPPP_utilities/blob/master/postgis/rc_Split_Line_By_Points.sql>
>> to a working implementation of ST_Split((multi)line,(multi)point).
>> The current ST_Split is not working correctly with points due to
>> precision issues (and it is not working with multi ofc).
>>
>> The proposed function hacks the Curvilinear api (
>> http://postgis.refractions.net/docs/ST_Line_Locate_Point.html, etc), in
>> order to escape the precision issue that plagues all point related stuff in
>> postgis.
>>
>> Tolerance is fully supported (no segment too small can be generated with
>> a filtering on split point, only split if close enough).
>>
>> I didn't benchmarked it, but I would expect it to be way slower than the
>> C (non-working) function. This is totally non-optimal but is  temporary
>> until the necessary changes are made on ST_Split.
>>
>> Function is tested for 2.0
>>
>> Cheers,
>> Rémi-C
>>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150717/ae1d9362/attachment.html>