Hey, this is more a postgres/SQL question. Anyway SELECT a.gid, b.gid, st_shortestline(a.geom,b.geom), ST_Distance(a.geom,b.geom) AS distance FROM test.nfanwood AS a, test.nfanwood AS b WHERE ST_DWithin(a.geom,b.geom,2000) = TRUE
The syntax is : SELECT choose what you see as final result SELECT * -- : all possible column/attributes SELECT column1, column2, ... --list of column or expression/function FROM choose what table you use and how FROM --one table, or several table using join WHERE choose a way to filter the result ot keep only a part of the result. WHERE --condition to aply to row, only row respecting conditions are kept in the result. This querry means in human langage: For all rows (1,n) of the table *a*, take all row (1,n) of table *b*. (so this give you the row : 1,1 1,2 , ... 1,n 2,1 2,2 ... 2,n .. n,1 n,2 ... n,n) For this pairs of rows, keep only those that are spatially close enough (2 km) Then, for remaining pair of rows get me the column gid from a and b, the shortest line from a and b, the (min) distance from a to b Now what you askis to have the distance between all pairs, thus you must remove the filtering condition SELECT a.gid, b.gid, ST_Distance(a.geom,b.geom) AS distance FROM test.nfanwood AS a, test.nfanwood AS b As for your id, your sentence is totally unclear to me. Cheers, Rémi-C 2015-03-08 22:52 GMT+01:00 Manuel Kohout <manu.koh...@gmail.com>: > Hi, > > I have found following SQL query for measuring distances between polygon > edges: > > select a.gid from_gid, b.gid to_gid, st_shortestline(a.geom,b.geom), > st_distance(a.geom,b.geom) as distance > from > test.nfanwood as a, > (select * from test.nfanwood) as b > where st_dwithin(a.geom,b.geom,2000) > > > https://duncanjg.wordpress.com/2012/09/23/edge-to-edge-distance-using-postgis/ > > I am a newbie to GIS and wonder, if you could explain the syntax of the > query to me? > > It works fine, when I run it with my own polygon shapefile: I receive a > table with 3 columns a-gid, b.gid and distance, containg rows with all > distances of all a.gid to all b.gid. > > However, what I'd like is to add the distance from a.gid 1 to b.gid 2, > a.gid 1 to b.gid 3, ... to my existing attribute table where my 103 patches > have ID name "Island" m001 - m103. > > Thus all 102 distances of island m001 to all the other 102 islands should > be added as rows with ID name m001. > > Is this possible? > /Manu > > _______________________________________________ > postgis-users mailing list > postgis-users@lists.osgeo.org > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users