# Get nearest node following segment

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.

• 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... Commented Jul 10, 2015 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. Commented Jul 10, 2015 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;` Commented Jul 14, 2015 at 9:26

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
``````
• 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` Commented Jul 14, 2015 at 15:08