i was checking the best way to cut linestrings by points.
The scenario is: lots of streets, need the segments cut by intersect points, kind of this:
I got - linestrings (full uncuted by points) table - st_intersection points table
I need to get the independent linestring segments cutted by the intersection points table.
I'm using postgis functions, and found several approachs, but every one of them has giving me some kind of issue.
Here's what i already tested:
1
Line table: 1 row , st_memunion of 1200 lines Point table: 1700 rows (points)Whats bad: really takes really lot of time and memory flush. Cant create multiple tables at same time cause memory just cant handle it.And the result is dirty and messy. Instead of giving me the correct row number, and i need to clean it up later (well explained here Split lines at intersection points )
CREATE TABLE lines_with_messy_result AS (SELECT((ST_DUMP(ST_SPLIT(a.geom,b.ix))).geom) as geomFROM st_union_lines aINNER JOIN lots_of_points bON ST_INTERSECTS(a.geom, b.ix));--then need to clean this upcreate table lines_segments_cleaned as (SELECT DISTINCT ON (ST_AsBinary(geom)) geom FROM lines_with_messy_result);source of this way/approach : http://stackoverflow.com/questions/...de-city-streets-by-intersection-using-postgis
2
Same lines/points table.Still messy results and need to clean this up. Still lots of time to finish query.
--messy table Create table messy_lines_segments as Select row_number() over() as id,(st_dump(st_split(input.geom, blade.ix))).geom as geomfrom st_union_lines inputinner join lots_of_points blade on st_intersects(input.geom, blade.ix);--then cleaning the messy tabledelete from messy_lines_segments awhere exists (select 1 from messy_lines_segments b where a.id != b.id and st_coveredby(b.geom,a.geom));source of this way / approach : Split lines at intersection points
3
I also found this function: https://github.com/Remi-C/PPPP_utilities/blob/master/postgis/rc_Split_Line_By_Points.sql
wich has the good thing that it does not leave a messy_result that then i needs to clean it up. But you do need st_memunion from both sides (lines table and points table)
Its kind of:
create table osm.calles_cortadas_03segmentos_sanluis as (SELECT result.geomFROM osm.calles_cortadas_01uniones_sanluis AS line, osm.calles_cortadas_00intersecciones_sanluis AS point,rc_split_line_by_points(input_line:=line.geom,input_points:=point.ix,tolerance:=4) AS result);But its also superlong hrs for get the results. And i also tried with longer tables (10k lines, 14k points) and i just get memory out issues.
I also tried esri's Arcgis with also bad results..
So, whats the best way of getting this done with postgis geom functions?
I mean, without stepping into topology.
Or whats your best recomendation?
أكثر...
The scenario is: lots of streets, need the segments cut by intersect points, kind of this:
I got - linestrings (full uncuted by points) table - st_intersection points table
I need to get the independent linestring segments cutted by the intersection points table.
I'm using postgis functions, and found several approachs, but every one of them has giving me some kind of issue.
Here's what i already tested:
1
Line table: 1 row , st_memunion of 1200 lines Point table: 1700 rows (points)Whats bad: really takes really lot of time and memory flush. Cant create multiple tables at same time cause memory just cant handle it.And the result is dirty and messy. Instead of giving me the correct row number, and i need to clean it up later (well explained here Split lines at intersection points )
CREATE TABLE lines_with_messy_result AS (SELECT((ST_DUMP(ST_SPLIT(a.geom,b.ix))).geom) as geomFROM st_union_lines aINNER JOIN lots_of_points bON ST_INTERSECTS(a.geom, b.ix));--then need to clean this upcreate table lines_segments_cleaned as (SELECT DISTINCT ON (ST_AsBinary(geom)) geom FROM lines_with_messy_result);source of this way/approach : http://stackoverflow.com/questions/...de-city-streets-by-intersection-using-postgis
2
Same lines/points table.Still messy results and need to clean this up. Still lots of time to finish query.
--messy table Create table messy_lines_segments as Select row_number() over() as id,(st_dump(st_split(input.geom, blade.ix))).geom as geomfrom st_union_lines inputinner join lots_of_points blade on st_intersects(input.geom, blade.ix);--then cleaning the messy tabledelete from messy_lines_segments awhere exists (select 1 from messy_lines_segments b where a.id != b.id and st_coveredby(b.geom,a.geom));source of this way / approach : Split lines at intersection points
3
I also found this function: https://github.com/Remi-C/PPPP_utilities/blob/master/postgis/rc_Split_Line_By_Points.sql
wich has the good thing that it does not leave a messy_result that then i needs to clean it up. But you do need st_memunion from both sides (lines table and points table)
Its kind of:
create table osm.calles_cortadas_03segmentos_sanluis as (SELECT result.geomFROM osm.calles_cortadas_01uniones_sanluis AS line, osm.calles_cortadas_00intersecciones_sanluis AS point,rc_split_line_by_points(input_line:=line.geom,input_points:=point.ix,tolerance:=4) AS result);But its also superlong hrs for get the results. And i also tried with longer tables (10k lines, 14k points) and i just get memory out issues.
I also tried esri's Arcgis with also bad results..
So, whats the best way of getting this done with postgis geom functions?
I mean, without stepping into topology.
Or whats your best recomendation?
أكثر...