[postgis-users] SQL syntax question

[Rémi Cura]

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.kohout at 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 at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150309/84185b51/attachment.html>