5

i need some help with the following:

i have a linestring - geom
i have $lat,$lon (using some php syntax)
  • i have an osm table with the roads geom, i am trying to get closest point, and then split the road into two parts, by that closest point.

after using the lat,lon and st_closestpoint to retrieve the closest point on that line. the point which will be returned is not necessarily exist in the original linestring points.

ST_ClosestPoint (geom_way, ST_SetSRID (ST_MakePoint($lon, $lat), 4326)  ) as closest_point

after that i would like to split the geom(linestring) into two lines by closest_point, and this is where i need help.

i tried using 

 st_asgeojson (ST_Split (geom_way, ST_ClosestPoint (geom_way, ST_SetSRID (ST_MakePoint($lon, $lat), 4326)  )))

but i believe that because the point i use is not on the geom, its not spliting and always returns the entire geom.

if i use a point which exist in the line string, it works well.(but not what i need)

how can i solve this? any solution even if with totally different approach will do.

thanks for your help!

yaron
  • 293
  • 1
  • 6

1 Answers1

8

aha! i found the solution:

use:

ST_Line_Locate_Point(geom_way,closest_point)

to fetch the portion of the line and then can use the following to split it correctly:

ST_Line_Substring (geom_way,0,ST_Line_Locate_Point(geom_way,closest_point)) as first_half
ST_Line_Substring (geom_way,ST_Line_Locate_Point(geom_way,closest_point),1) as other_half
yaron
  • 293
  • 1
  • 6