How to find a nearest line to a point

Question

I have a table with many points that need to be associated with street segments to calculate an average value. should I do a relationship for all points to a street segment that is closest. I have dealt with several queries but most is for a specific point with coordinates, I do take care for the whole set of points (61000), similar to the "spatial join" for proximity of ArcGIS

Attributes of the streets id integer NOT NULL DEFAULT nextval ('calles_osm_gid_seq' :: regclass), objectid integer, osm_id numeric, name character varying (48), Inventory character varying (16), type character varying (16), oneway smallint, bridge smallint, maxspeed smallint, shape_leng numeric, maxvel integer, MinSpd smallint, azimuth numeric, geom geometry (MultiLineString, 4326),

Point attributes "Latitude"numeric (50,10), "Longitude" numeric (50,10), "Azimuth" integer, "Speed" integer, "Date" timestamp without time zone, "Geometry" geometry (Point, 4326), id_calles integer

Any idea?

So you need a nearest line for each point? Or a line nearest to a set of points? — Jakub Kania, Aug 13 '14 at 07:56
Possible duplicate of algorithm to get the nearest line segment (link) to a point — raphael, May 02 '17 at 15:04

score 1 · Answer 1 · answered Aug 12 '14 at 21:43

If id_calles is your foregin key to streets table this could be resolution:

update point p
set id_calles = 
  (
  select s.id
  from streets s
  where st_dwithin(s.geom::geography, p.geometry::geography, 500) --in meters
  order by st_distance(s.geom, p.geometry)
  limit 1 
  )

If not please post some comment so I can improve it...

How to find a nearest line to a point

1 Answers1