2

I am trying to get the nearest node from a point(Id 1) . The coordinates (lat/long) are input by an user . I am using some functions like pgr_nearest_node() or get_nearest_node() from get_nearest_node function reference

1: http://www.sqlexamples.info/SPAT/postgis_nearest_point.htm. The Problem Is that the point take the nearest node using straight line between them(928-3670-317-929 Id nodes). How can i get the nearest node in the network ? . I am thinking about to use pgr_dijkstra function but it need to a source id so i return to functions commented before.

The 1 Id point is the input point.When i use the functions before, the result is Id 928 from my net_vertices_pgr. The objective is to get as result nearest node from Id1 ,the 317 Id node

  • 1
    Have your set up your network for pg_routing? In other words, are the columns source and targets populated? In that case you could use st_distance to get the nearest line on the network, and then select the closest of source or target for that line... – mlinth Jul 10 '15 at 13:53
  • Hello. Yes , i have two tables net_city and net_city_vertices_pgr. Net_city have source and target columns in addition to more data like ;osm_id.osm_name and the source,target coordinates(lat/long).Net_city_vertices_pgr have id and the_geom columns in adiction four more columns. What you are asking is interesting, i am going to try. I will comment on the result.Thanks for your comment. – user19566 Jul 10 '15 at 15:09
  • Hello. Following the comments indicated I obtained the following expression. As results i get d as distance,source and target of closest line .But unable to resolve the last step, select the closest point of the line selected node. SELECT (ST_Distance_Sphere(ST_GeomFromText('POINT(lon lat)',4326),(geom_way))) as d,source,target from segment_table order by d limit 1; – user19566 Jul 14 '15 at 9:26
2

Perhaps there's a more elegant way, but the following query should work:

with nearest_line as -- put the query to get the nearest line in a CTE
                     -- use the CTE twice

(select  source, target from segment_table order by 
 (ST_Distance_Sphere(ST_GeomFromText('POINT(lon lat)',4326) limit 1) 


select id from

-- A union query to get the source and target ids from vertices
(select id,the_geom from segment_table_vertices_pgr p1
inner join nearest_line l on source = id
union all
select id,the_geom from segment_table_vertices_pgr p1
inner join nearest_line l on target = id) a
-- sort it by distance from the point and return the one record
order by  st_distance_sphere(the_geom,ST_SetSRID(ST_Point(lon,lat),4326)) limit 1
  • 1
    Hello. It works very well. I've just added a parameter to calculate the distance to the line(geom column of segment_table-geom_way-) . Thank you very much . (select source, target from net_jaen order by (ST_Distance_Sphere(ST_GeomFromText('POINT(lon lat)',4326),geom_way)) limit 1 – user19566 Jul 14 '15 at 15:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.