Find Closest Index in line string to insert new vertex using PostGIS

Question

I want to find the index from Line String to insert new vertex on it.

I am using ST_Add Point function but it requires specified index for inserting vertex.How to get those index based on given input points from line string.

My Input Line String: "LINE STRING Z (496328.772173402 4622876.01448448 208.4005,496327.883130287 4622814.67050952 208.4005,496494.578714408 4622814.67050952 208.4005,496497.898850613 4622875.89518039 208.4005)"

My Input Point: 'POINT Z(496458.12794668 4622814.67050952 208.4005)'

Expected Output: "LINE STRING Z (496328.772173402 4622876.01448448 208.4005,496327.883130287 4622814.67050952 208.4005,496458.12794668 4622814.67050952 208.4005,496494.578714408 4622814.67050952 208.4005,496497.898850613 4622875.89518039 208.4005)"

I need to insert on 3rd position on given line string. But i am not able to find the exact segment index for inserting new point.

I am using following query with manual index 2.But i need to get index automatically

update public.linetype
set geom=ST_AddPoint(geom, ST_Make Point(496458.12794668 4622814.67050952 208.4005),2) where id=4
from public.linetype 

public.linetype is my table name and geom is linestring geometry column.

PostGIS Version 2.4.4 and PostgreSQL 10.4

• JGH: if your point is always on the linestring, you can use this solution

Answer 1

Unfortunately there's no easy way to do this in PostGIS currently. See this answer for a possible solution.

UPDATE: You can use ST_Snap to do the point insertion in one step:

SELECT ST_AsText( ST_Snap( 
 'LINESTRING Z (496328.772173402 4622876.01448448 208.4005,496327.883130287 4622814.67050952 208.4005,496494.578714408 4622814.67050952 208.4005,496497.898850613 4622875.89518039 208.4005)',
 'POINT Z(496458.12794668 4622814.67050952 208.4005)',
 0.1
));