Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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!

enter image description here

share|improve this question
    
You put the arcgis tag, have you looked at the network analyst? –  nadya Jul 31 '13 at 17:17
    
@nadya, I have not worked with Network Analyst, if there is possible to perform this task, please write how to do it. Thanks! –  HasT Aug 1 '13 at 7:11

2 Answers 2

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.

share|improve this answer
    
Thanks for the answer! But I have some problems with the queries. After the query [SELECT r.the_geom, r.gid , p.nam INTO roads_with_names FROM roads r, point p WHERE ST_Intersects(r.the_geom,p.the_geom) IS TRUE GROUP BY r.gid, p.nam] I receive roads_with_names table. But don't can successfully run next sql requests. For query [SELECT * FROM crosstab('SELECT r.the_geom, r.nam FROM roads_with_names r ORDER BY 1, 2') AS ct(the_geom geometry, name1 TEXT, name2 TEXT, name3 TEXT, name4 TEXT);] don't exist crosstab function, and I don't find solution how create. In post I added sample of shapefiles. –  HasT Aug 1 '13 at 8:07
    
I install crosstab function, but after run [SELECT * FROM crosstab('SELECT r.the_geom, nam, r.nam FROM roads_with_names r ORDER BY 1,2') AS ct(the_geom geometry, name1 TEXT, name2 TEXT, name3 TEXT, name4 TEXT);] query receive 'return and sql tuple descriptions are incompatible' exception. –  HasT Aug 1 '13 at 11:36
    
try stackoverflow.com/questions/2847849/… and stackoverflow.com/questions/43870/…. I have to admit that i havent used crosstab , i just assumed that it would be nice solution, but above looks much easier –  simplexio Aug 1 '13 at 12:11
    
Thanks for the answer/comments, it helped me. –  HasT Aug 23 '13 at 14:16
up vote 0 down vote accepted

I decided use the following composed algorithm in PostgreSQL/PostGIS:

---------------------------------------------------------------------
--create point layer for linear layer in intersections with unique name (number) in 'nam' field

DROP TABLE IF EXISTS "point";

CREATE TABLE stp AS SELECT ST_StartPoint(the_geom) AS the_geom FROM roads;
CREATE TABLE enp AS SELECT ST_EndPoint(the_geom) AS the_geom FROM roads;
CREATE TABLE point AS 
SELECT * FROM stp
UNION 
SELECT * FROM enp;

ALTER TABLE "point" ADD COLUMN nam TEXT;

DROP TABLE IF EXISTS stp;
DROP TABLE IF EXISTS enp;

DROP SEQUENCE IF EXISTS point_gid_seq;

CREATE SEQUENCE point_gid_seq START 1;
UPDATE "point" SET nam = NEXTVAL('point_gid_seq');


---------------------------------------------------------------------
--spatial join point feature name to linear feature

SELECT r.the_geom, r.gid , p.nam INTO roads_with_names FROM roads r, point p WHERE ST_Intersects(r.the_geom,p.the_geom) IS TRUE GROUP BY r.gid, p.nam;

---------------------------------------------------------------------
--remove duplicated 'the_gom' and 'agg' name for linear layer

CREATE TABLE roadsdupl AS
SELECT the_geom, array_to_string(array_agg(nam), ',') AS name
FROM roads_with_names
GROUP BY the_geom;

---------------------------------------------------------------------
--spatial join linear attributes to point layer



CREATE TABLE poiwithduplname AS
SELECT
poi.the_geom,
poi."nam",
array_to_string(array_agg(name), ',') AS pointsum
FROM
point AS poi,
roadsdupl AS lin
WHERE ST_Intersects(lin.the_geom, poi.the_geom)
GROUP BY poi."nam", lin.name, poi.the_geom;

---------------------------------------------------------------------
 --remove duplicated 'the_gom' and 'agg' name for point layer

CREATE TABLE poidupl AS
SELECT the_geom, nam, array_to_string(array_agg(pointsum), ',') AS fullname
FROM poiwithduplname
GROUP BY nam, the_geom;

---------------------------------------------------------------------
--remove in point layer duplicated substring

DROP TABLE IF EXISTS "poifin";

CREATE TABLE poifin AS
SELECT the_geom, nam, REPLACE(REPLACE(fullname, nam||',', ''), ','||nam, '') AS sosedi FROM poidupl;

---------------------------------------------------------------------

DROP TABLE IF EXISTS "roads_with_names";
DROP TABLE IF EXISTS "poiwithduplname";
DROP TABLE IF EXISTS "roadsdupl";
DROP TABLE IF EXISTS "poidupl";

---------------------------------------------------------------------

Thanks simplexio, and www.sql.ru forum

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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