<div dir="ltr"><div><div><div><div><div><div><div><div><div><div><div><div><div><div><div><div>Hey,<br></div> this is more a postgres/SQL question.<br></div>Anyway<br><br></div>SELECT a.gid, b.gid, st_shortestline(a.geom,b.geom), ST_Distance(a.geom,b.geom) AS distance<br></div>FROM test.nfanwood AS a, test.nfanwood AS b<br></div>WHERE ST_DWithin(a.geom,b.geom,2000) = TRUE<br><br><br></div>The syntax is :<br></div><div>SELECT choose what you see as final result<br></div>SELECT * -- : all possible column/attributes<br></div>SELECT column1, column2, ... --list of column or expression/function<br><br></div><div>FROM choose what table you use and how<br></div>FROM --one table, or several table using join<br><br></div><div>WHERE choose a way to filter the result ot keep only a part of the result.<br></div>WHERE --condition to aply to row, only row respecting conditions are kept in the result.<br><br></div>This querry means in human langage:<br></div><div>For all rows (1,n) of the table <i>a</i>, take all row (1,n) of table <i>b</i>.<br></div><div>(so this give you the row : 1,1 1,2 , ... 1,n 2,1 2,2 ... 2,n .. n,1 n,2 ... n,n)<br></div><div>For this pairs of rows, keep only those that are spatially close enough (2 km)<br></div><div>Then, for remaining pair of rows<br></div>get me the column gid from a and b, the shortest line from a and b, the (min) distance from a to b<br><br><br></div>Now what you askis to have the distance between all pairs, thus you must remove the filtering condition<br>SELECT a.gid, b.gid, ST_Distance(a.geom,b.geom) AS distance<br>FROM test.nfanwood AS a, test.nfanwood AS b <br><br></div>As for your id, your sentence is totally unclear to me.<br><br></div>Cheers,<br></div>Rémi-C<br><div><div><div><br></div></div></div></div><div class="gmail_extra"><br><div class="gmail_quote">2015-03-08 22:52 GMT+01:00 Manuel Kohout <span dir="ltr"><<a href="mailto:manu.kohout@gmail.com" target="_blank">manu.kohout@gmail.com</a>></span>:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div class="gmail_default"><div class="gmail_default"><font face="arial, helvetica, sans-serif">Hi,</font></div><div class="gmail_default"><font face="arial, helvetica, sans-serif"><br></font></div><div class="gmail_default"><font face="arial, helvetica, sans-serif">I have found following SQL query for measuring distances between polygon edges:</font></div><div class="gmail_default"><font face="arial, helvetica, sans-serif"><br></font></div><div class="gmail_default"><pre style="border:0px;font-family:Consolas,Monaco,'Lucida Console',monospace;font-size:12px;margin-top:0px;margin-bottom:20px;outline:0px;padding:15px 20px;vertical-align:baseline;line-height:20px;overflow:auto;max-width:100%;font-stretch:normal;color:rgb(64,64,64);background:rgb(245,245,245)"><code style="border:0px;font-family:Consolas,Monaco,'Lucida Console',monospace;margin:0px;outline:0px;padding:0px;vertical-align:baseline;font-stretch:normal">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)</code></pre></div><div class="gmail_default"><font face="arial, helvetica, sans-serif"><a href="https://duncanjg.wordpress.com/2012/09/23/edge-to-edge-distance-using-postgis/" target="_blank">https://duncanjg.wordpress.com/2012/09/23/edge-to-edge-distance-using-postgis/</a><br></font></div><div class="gmail_default"><font face="arial, helvetica, sans-serif"><br></font></div><div class="gmail_default"><span style="font-family:arial,helvetica,sans-serif">I am a newbie to GIS and wonder, if you could explain the syntax of the query to me? </span><br></div><div class="gmail_default"><font face="arial, helvetica, sans-serif"><br></font></div><div class="gmail_default"><font face="arial, helvetica, sans-serif">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.</font></div><div class="gmail_default"><font face="arial, helvetica, sans-serif"> </font></div><div class="gmail_default"><font face="arial, helvetica, sans-serif">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. </font></div><div class="gmail_default"><font face="arial, helvetica, sans-serif"><br></font></div><div class="gmail_default"><font face="arial, helvetica, sans-serif">Thus all 102 distances of island m001 to all the other 102 islands should be added as rows with ID name m001.</font></div><div class="gmail_default"><font face="arial, helvetica, sans-serif"><br></font></div><div class="gmail_default"><font face="arial, helvetica, sans-serif">Is this possible? </font></div><div class="gmail_default"><font face="arial, helvetica, sans-serif">/Manu</font></div></div></div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>