2

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?

closed as too broad by whyzar, mgri, Mapperz May 2 '17 at 15:46

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

1

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...

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