Your query is not distinguishing between the left and right river linestring.
If there were TWO of them sharing the same name and ID then one way to do this
using PostgreSQL 8.4 is:
drop table river;
create table river (
id integer,
name varchar(32),
the_geom geometry
);
insert into river (id,name,the_geom)
values (1,'Barito',ST_GeomFromText('LINESTRING(-237.5 -138.5, -79.5 -31.5,
-119.5 101.5, 41.5 166.5, -51.5 251.5)',0)),
(1,'Barito',ST_GeomFromText('LINESTRING(42.5 271.5, 216.5 163.5, 70.5
84.5, 4.5 -4.5, -39.5 -58.5, -79.5 -151.5, -40.5 -243.5)',0));
select *
from river;
id;name;the_geom
integer;character varying;geometry
1;"Barito";"010200002000000000050000000000000000B06DC000000000005061C00000000000E053C00000000000803FC00000000000E05DC000000000006059400000000000C044400000000000D064400000000000C049C00000000000706F40"
1;"Barito";"0102000020000000000700000000000000004045400000000000F870400000000000106B4000000000007064400000000000A051400000000000205540000000000000124000000000000012C00000000000C043C00000000000404DC00000000000E053C00000000000F062C000000000004044C00000000000706EC0"
SELECT min(ST_Distance(r.the_geom, r.the_geom)) as Min_Width
FROM River as r
WHERE r.Name = 'Barito';
min_width
double precision
0
The above SQL is, for each row it selects, pushing the same geometry into the
ST_Distance function.
What we need to do is feed the left and the right linestring into the same
function as follows. Firstly we
need to give each row its own unique id....
select row_number() over (order by the_geom) as rin, the_geom
from River r
where r.Name = 'Barito';
rin;the_geom
integer;geometry
1;"010200002000000000050000000000000000B06DC000000000005061C00000000000E053C00000000000803FC00000000000E05DC000000000006059400000000000C044400000000000D064400000000000C049C00000000000706F40"
2;"0102000020000000000700000000000000004045400000000000F870400000000000106B4000000000007064400000000000A051400000000000205540000000000000124000000000000012C00000000000C043C00000000000404DC00000000000E053C00000000000F062C000000000004044C00000000000706EC0"
Now we can use this to separate our river into its two halves and then, via a
cross join, feed them into the ST_Distance function.
SELECT min(ST_Distance(l.the_geom,r.the_geom)) as Min_Width
FROM (select row_number() over (order by the_geom) as rin, the_geom
from River r
where r.Name = 'Barito' ) as l,
(select row_number() over (order by the_geom) as rin, the_geom
from River r
where r.Name = 'Barito' ) as r
WHERE l.rin = 1
AND r.rin = 2;
min_width
double precision
48.2597140480546
Hope this helps.
regards
Simon
On Wed, 14 Oct 2009 13:37:08 +1100, Surya Tarigan <[email protected]>
wrote:
Dear List,
I have polyline depicting river banks. I have queried the minimum distance
between left and right river bank as follows:
SELECT min(ST_Distance(r.the_geom, r.the_geom)) as Min_Width
FROM River as r
WHERE r.Name = 'Barito';
It returns zero ('0') result. The left and the right river bank have the same
attributes (ID, Name, etc), that is why I use ST_Distance(r.the_geom,
r.the_geom). Should I give different ID or Name for left and river bank. How
the argument of ST_distance should look like.. The argument that I use above
must be not correct (i.e. ST_Distance(r.the_geom, r.the_geom)).
Any clue would be appreciated
surya
________________________________
From: Kevin Neufeld <[email protected]>
To: PostGIS Users Discussion <[email protected]>
Sent: Fri, October 2, 2009 12:35:17 PM
Subject: Re: [postgis-users] polygon minimum width
The linestrings wouldn't need to be continuous as long as you attribute them
left and right bank. Then your query would be to find the shortest distance
between two linear datasets (left and right banks). A cross product query
would help you here. Or, ST_Collect your banks into two multilinestrings, one
for left and one for right bank. Then compute the distance between them.
But you are still going to have problems at the head and mouth of the river.
There, the left and right banks touch, so the minimum distance between the left
side of the river and right side of the river is zero.
-- Kevin
Surya Tarigan wrote:
Hallo Nicklas
fortunately I have linestring version of the polygon, but it seems that the
linestirings are not continuous. Does st_distance still apply? Or are there any
postgis function to make the polyline continuous?
kind regards,
------------------------------------------------------------------------
*From:* "[email protected]" <[email protected]>
*To:* PostGIS Users Discussion <[email protected]>
*Sent:* Thursday, October 1, 2009 3:50:37 PM
*Subject:* Re: [postgis-users] polygon minimum width
Hallo
I think the easiest way is to make a linestring of the polygon.boundary and
then cut the line in the start and end so you get two more or less paralell
lines.. Then you can use st_distance to find the shortest distance between them.
Hope that helps
Nicklas
2009-10-01 Surya Tarigan wrote:
>
>
Dear list,
>
>
I have a river polygon with polygon length about 20 km. How can I query the
minimum width of the river polygon. I tried to search previous threads, but I
could not find any clue.
>
>
kind regards,
>
>
surya
>
------------------------------------------------------------------------
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
--
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: [email protected]
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
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
