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.
UPDATE - QGIS3:
Since this seems to be an important topic, I would like to share my answer on a similar question.
If you are using the current version of QGIS (3), you can use the plugin QNEAT3. It offers multiple processing algorithms that produce origin-destination matrices (OD-Matrix) as line layer, table or csv file out of the box. All algorithms rely on the dijkstra() method in the qgis.analysis module, therefore all costs are calculated on the basis of shortest paths and make use of C++ code for faster execution.
You can get more information about the plugin at the qgis plugin repository and at the plugins documentation.
Best Answer
To not leave this unanswered - thanks to @Babel - for those with similar tasks and different routing profiles, i ended up converting a reasonable number of geographical near destinations as points and used not a network function in qgis itself, but the OpenRouteService plugin.