PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Add point to linestring with postgis

Using PostgreSQL/PostGIS, given a point position I need extract the nearest linestring from a db table and update the founded geometry adding the given point based on the point position.
For these purposes the PostGIS function ST_AddPoint can be used, but, as far as this function requires the index position to assign to the new point I want to add, I would need to get the maximum index of the two nodes of the nearest segment of the linestring but it seams not so easy to me.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    After a few attempt this is the solution a found:

    with dot as (select st_geomfromtext('POINT (X Y)', CRS) as pp)
select
    condotta,
    (bar.path[1]) as idx,
    st_addpoint(condotta, dot.pp, (bar.path[1])) as new_condotta
from (select
    foo.geom as condotta,
    (ST_DumpSegments(foo.geom)).*
    from (select geom
        from acquedotto.rete_idrica ri, dot
        order by pp<->geom asc limit 1) as foo) as bar, dot
order by dot.pp<->bar.geom asc
limit 1

  2. 0

    You can add a point that is / is almost on a line using st_snap and a small tolerance

    WITH src (line, point) as (values ('linestring(0 0, 10 10)'::geometry, 'point(5.01 5)'::geometry))
SELECT st_asText(st_snap(line,point, 0.1))
FROM src;
         st_astext
---------------------------
  LINESTRING(0 0,5.01 5,10 10)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search