[GIS] How join attributes for point feature from nearest points in road network

Question

I have two layers: 'linear' – road network and 'point' – points in each linear features intersections.

Point layer have in attribute table name for each point features: A,B,C,D,E,F, etc.
How add in point layer names of nearest points of road network?
For example:

for point A in attribute table should be attributes B,E;
for point B in attribute table should be attributes A,C;
for point C in attribute table should be attributes B,D;
for point D in attribute table should be attributes C,H,N,I;

In QGIS using Distance Matrix possible similar task, but there is "nearest distance", not road network.
I think it's done in PostgreSQL/PostGIS, but don't find solution.

Thanks!

Answer 1

Read point data for closest road in PostGIS:

SELECT cp.*, r.the_geom FROM roads r, (SELECT p.* ,r.id from point p, roads ro ORDER BY ST_Distance(p.the_geom, ro.the_geom) ASC LIMIT 1) as cp WHERE cp.id = r.id 

cp alias should have one closest road for every point, WHERE ST_Dwithin(geom,geom) < 1 should make search faster

Little closer answer is (in PostGIS)

SELECT r.*, p.* FROM roads r, points p WHERE ST_Intersects(r.geom,p.geom) IS TRUE 

That returns roads rows several times if there are more than one intersection on road

Solution A

SELECT r.the_geom, r.gid , p.name INTO roads_with_names  WHERE ST_Intersects(r.geom,p.geom) IS TRUE GROUP BY r.gid, p.name

Return only one row per road (i'm not 100% how crosstab works)

select * from crosstab('select r.the_geom, 'name' ,  r.name from roads_with_names r ORDER 1,2) AS ct(the_geom geometry, name text, name2 text , name3 text , name 4 text ); 

Or use LEFT join on road id. There is several ways to do it

Should create new table which has road geoms, gid and name from point table. There should not be any dublicate r.gid + p.name rows. In QGIS you can define label field from database (assuming that you have PostGIS source) from Layers properties.

You may need to use first answer if your data does not intersect.

Closest point on road in PostGIS is

SELECT ST_ClosestPoint(r.geom, po.geom) as closest_point, r.id  FROM roads r,  (SELECT p.* ,r.id from point p, roads ro ORDER BY ST_Distance(p.the_geom, ro.the_geom) ASC LIMIT 1) as po  where r.id = po.id

Above should return point geometry on road which is closest to point on point table which is closest to road.