[postgis-users] SQL syntax question

[Manuel Kohout]

Dear Remi,

Many thanks for your reply - makes it much more clear. I actually managed
to solve the issue by a table join following the sql query.

Thanks again

/Manuel

On Mon, Mar 9, 2015 at 11:03 PM, Rémi Cura <remi.cura at gmail.com> wrote:

> 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
>>
>
>
> _______________________________________________
> 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/20150310/43d82d26/attachment.html>