[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] Help determining position
From: Surya Tarigan <surya.tarigan () yahoo ! com>
Date: 2009-10-19 4:04:29
Message-ID: 239334.41521.qm () web111901 ! mail ! gq1 ! yahoo ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
Simon,
Thanks for the SQL, I will first figure it out and then exercise it on my dataset. It \
seems that to be able to use previous SQLs, I needed to merge/clean first my \
linestring in order to get exactly two rows for each river. By the way, river mouth \
means the start point of the river banks.
kind regards,
surya
________________________________
From: Simon Greener <simon@spatialdbadvisor.com>
To: PostGIS Users Discussion <postgis-users@postgis.refractions.net>
Sent: Mon, October 19, 2009 10:17:34 AM
Subject: Re: [postgis-users] Help determining position
Surya,
> with help of the list I am able to determine minimum width of a river using SQL \
> below. Especially thanks to Mr. Simon Greener.. I still need to determine position \
> (in meter and also latlon) of this point from the river mounth. Can anybody give me \
> suggestion what Postgis function I should use together with the following SQL.
Not going to do everything for you (after all you have not supplied us a definition \
of the river mouth), but I would probably start by trying to find the actual points \
in the left/right bank that are closest via something like this:
-- Which points are closest to each other?
--
SELECT l.rin,ST_Point((l.coord).x,(l.coord).y),
r.rin,ST_Point((r.coord).x,(r.coord).y),
ST_Distance(ST_Point((l.coord).x,(l.coord).y),
ST_Point((r.coord).x,(r.coord).y)) as PDistance
FROM (select row_number() over (order by the_geom) as rin,
ST_DumpPoints(the_geom) as coord
from River r
where r.Name = 'Barito' ) as l,
(select row_number() over (order by the_geom) as rin,
ST_DumpPoints(the_geom) as coord
from River r
where r.Name = 'Barito' ) as r
WHERE l.rin <> r.rin
ORDER BY 5 ASC
LIMIT 1;
Note, the ST_DumpPoints() function is a user defined one you can get from \
http://www.spatialdbadvisor.com/postgis_tips_tricks/109/implementing-oracles-getvertices-function-in-postgis-st_dumppoints
But this will return the left and right points that are closest. Then you need to \
calculate the distance from each point to the point that defines the mouth of your \
river and chose the closest.
regards
Simon
--SpatialDB Advice and Design, Solutions Architecture and Programming,
Oracle Database 10g Administrator Certified Associate; Oracle Database 10g SQL \
Certified Professional Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE, Manifold \
GIS, FME, Radius Topology and Studio Specialist. 39 Cliff View Drive, Allens Rivulet, \
7150, Tasmania, Australia.
Website: www.spatialdbadvisor.com
Email: simon@spatialdbadvisor.com
Voice: +61 362 396397
Mobile: +61 418 396391
Skype: sggreener
Longitude: 147.20515 (147° 12' 18" E)
Latitude: -43.01530 (43° 00' 55" S)
GeoHash: r22em9r98wg
NAC:W80CK 7SWP3
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[Attachment #5 (text/html)]
<html><head><style type="text/css"><!-- DIV {margin:0px;} \
--></style></head><body><div style="font-family:arial, helvetica, \
sans-serif;font-size:12pt"><DIV>Simon,</DIV> <DIV> </DIV>
<DIV>Thanks for the SQL, I will first figure it out and then exercise it on my \
dataset. It seems that to be able to use previous SQLs, I needed to merge/clean first \
my linestring in order to get exactly two rows for each river. By the way, river \
mouth means the start point of the river banks. </DIV> <DIV> </DIV>
<DIV>kind regards,</DIV>
<DIV><BR>surya</DIV>
<DIV style="FONT-SIZE: 12pt; FONT-FAMILY: arial, helvetica, sans-serif"><BR>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial, helvetica, sans-serif"><FONT \
face=Tahoma size=2> <HR SIZE=1>
<B><SPAN style="FONT-WEIGHT: bold">From:</SPAN></B> Simon Greener \
<simon@spatialdbadvisor.com><BR><B><SPAN style="FONT-WEIGHT: \
bold">To:</SPAN></B> PostGIS Users Discussion \
<postgis-users@postgis.refractions.net><BR><B><SPAN style="FONT-WEIGHT: \
bold">Sent:</SPAN></B> Mon, October 19, 2009 10:17:34 AM<BR><B><SPAN \
style="FONT-WEIGHT: bold">Subject:</SPAN></B> Re: [postgis-users] Help determining \
position<BR></FONT><BR>Surya,<BR><BR>> with help of the list I am able to \
determine minimum width of a river using SQL below. Especially thanks to Mr. Simon \
Greener.. I still need to determine position (in meter and also latlon) of this point \
from the river mounth. Can anybody give me suggestion what Postgis function I should \
use together with the following SQL.<BR><BR>Not going to do everything for you (after \
all you have not supplied us a definition of the river mouth), but I would probably \
start by trying to find the actual points in the left/right bank that are closest \
via something like this:<BR><BR><BR>-- Which points are closest to each \
other?<BR>--<BR>SELECT l.rin,ST_Point((l.coord).x,(l.coord).y),<BR> \
r.rin,ST_Point((r.coord).x,(r.coord).y),<BR> \
ST_Distance(ST_Point((l.coord).x,(l.coord).y),<BR> \
ST_Point((r.coord).x,(r.coord).y)) as PDistance<BR> \
FROM (select row_number() over (order by the_geom) as rin,<BR> \
ST_DumpPoints(the_geom) as coord<BR> \
from River r<BR> where r.Name = 'Barito' ) \
as l,<BR> (select row_number() over (order by the_geom) as \
rin,<BR> ST_DumpPoints(the_geom) as \
coord<BR> from River r<BR> \
where r.Name = 'Barito' ) as r<BR>WHERE l.rin <> r.rin<BR>ORDER BY 5 \
ASC<BR>LIMIT 1;<BR><BR>Note, the ST_DumpPoints() function is a user defined one you \
can get from http://www.spatialdbadvisor.com/postgis_tips_tricks/109/implementing-oracles-getvertices-function-in-postgis-st_dumppoints<BR><BR>But \
this will return the left and right points that are closest. Then you need to \
calculate the distance from each point to the point that defines the mouth of your \
river and chose the closest.<BR><BR>regards<BR>Simon<BR><BR>--SpatialDB Advice and \
Design, Solutions Architecture and Programming,<BR>Oracle Database 10g Administrator \
Certified Associate; Oracle Database 10g SQL Certified Professional<BR>Oracle \
Spatial, SQL Server, PostGIS, MySQL, ArcSDE, Manifold GIS, FME, Radius Topology and \
Studio Specialist.<BR>39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, \
Australia.<BR>Website: <A href="http://www.spatialdbadvisor.com/" \
target=_blank>www.spatialdbadvisor.com</A><BR> Email: <A \
href="mailto:simon@spatialdbadvisor.com" \
ymailto="mailto:simon@spatialdbadvisor.com">simon@spatialdbadvisor.com</A><BR> \
Voice: +61 362 396397<BR>Mobile: +61 418 396391<BR>Skype: sggreener<BR>Longitude: \
147.20515 (147° 12' 18" E)<BR>Latitude: -43.01530 (43° 00' 55" S)<BR>GeoHash: \
r22em9r98wg<BR>NAC:W80CK \
7SWP3<BR>_______________________________________________<BR>postgis-users mailing \
list<BR><A href="mailto:postgis-users@postgis.refractions.net" \
ymailto="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractio \
ns.net</A><BR>http://postgis.refractions.net/mailman/listinfo/postgis-users<BR></DIV></DIV></div><br>
</body></html>
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/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