[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] SQL syntax question
From: Manuel Kohout <manu.kohout () gmail ! com>
Date: 2015-03-10 0:30:19
Message-ID: CAD3RSZJ0xksC1nwPTB41+8P+gE449yD4PFOdLz7YhRw=GJxGww () mail ! gmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
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@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@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
>
[Attachment #5 (text/html)]
<div dir="ltr"><div class="gmail_default" \
style="font-family:arial,helvetica,sans-serif;font-size:small">Dear Remi,</div><div \
class="gmail_default" \
style="font-family:arial,helvetica,sans-serif;font-size:small"><br></div><div \
class="gmail_default" \
style="font-family:arial,helvetica,sans-serif;font-size:small">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.</div><div class="gmail_default" \
style="font-family:arial,helvetica,sans-serif;font-size:small"><br></div><div \
class="gmail_default" \
style="font-family:arial,helvetica,sans-serif;font-size:small">Thanks again</div><div \
class="gmail_default" \
style="font-family:arial,helvetica,sans-serif;font-size:small"><br></div><div \
class="gmail_default" \
style="font-family:arial,helvetica,sans-serif;font-size:small">/Manuel</div></div><div \
class="gmail_extra"><br><div class="gmail_quote">On Mon, Mar 9, 2015 at 11:03 PM, \
Rémi Cura <span dir="ltr"><<a href="mailto:remi.cura@gmail.com" \
target="_blank">remi.cura@gmail.com</a>></span> wrote:<br><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><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"><div><div class="h5">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></div></div><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><div><div class="h5"><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></div></div>_______________________________________________<br> postgis-users \
mailing list<br> <a href="mailto:postgis-users@lists.osgeo.org" \
target="_blank">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>
<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>
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic