Re: polygon minimum width

[Nicklas Avén]

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
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Surya Tarigan]

Some parts of this message have been removed. Learn more about Nabble's security policy.

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: "nicklas.aven@..." <nicklas.aven@...>
To: PostGIS Users Discussion <postgis-users@...>
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
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Kevin Neufeld]

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:* "nicklas.aven@..." <nicklas.aven@...>
> *To:* PostGIS Users Discussion <postgis-users@...>
> *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
> postgis-users@...
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>  

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[strk-2]

On Thu, Oct 01, 2009 at 10:35:17PM -0700, Kevin Neufeld wrote:

> 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.

You'll need to cut head and mouth. ST_Intersection is your friend here,
but still needs you to draw/define the clipping polygon.

--strk;

 Free GIS & Flash consultant/developer      ()  ASCII Ribbon Campaign
 http://foo.keybit.net/~strk/services.html  /\  Keep it simple!
_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Surya Tarigan]

Some parts of this message have been removed. Learn more about Nabble's security policy.

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 <kneufeld@...>
To: PostGIS Users Discussion <postgis-users@...>
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:* "nicklas.aven@..." <nicklas.aven@...>
> *To:* PostGIS Users Discussion <postgis-users@...>
> *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
> postgis-users@...
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Simon Greener-3]

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 <surya.tarigan@...> 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 <kneufeld@...>
> To: PostGIS Users Discussion <postgis-users@...>
> 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:* "nicklas.aven@..." <nicklas.aven@...>
>> *To:* PostGIS Users Discussion <postgis-users@...>
>> *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
>> postgis-users@...
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
> _______________________________________________
> postgis-users mailing list
> postgis-users@...
> 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: simon@...
   Voice: [phone]
Mobile: [phone]
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@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Surya Tarigan]

Some parts of this message have been removed. Learn more about Nabble's security policy.

Dear Simon,

 

Thank you for the clue. I tried the SQL taht you proposed:

 

select row_number() over (order by the_geom) as rin, the_geom
  from River r
  where r.Name = 'Barito';

 

it gives error message : syntax error at or near "over"

 

Could it be caused by PostgreSQLv ersion  8.2 that I am using?

 

kind regards,

 

surya

---

From: Simon Greener <simon@...>
To: PostGIS Users Discussion <postgis-users@...>
Sent: Wed, October 14, 2009 11:03:34 AM
Subject: Re: [postgis-users] polygon minimum width

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 <surya.tarigan@...> 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 <kneufeld@...>
> To: PostGIS Users Discussion <postgis-users@...>
> 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:* "nicklas.aven@..." <nicklas.aven@...>
>> *To:* PostGIS Users Discussion <postgis-users@...>
>> *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
>> postgis-users@...
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
> _______________________________________________
> postgis-users mailing list
> postgis-users@...
> 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: simon@...
  Voice: [phone]
Mobile: [phone]
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@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Simon Greener-3]

Surya,

Yes, SQL Analytics / Windowing functions are only available in version 8.4.

There are many methods for simulating a ROWNUMBER in Postgresql but, in this case, we could use the CTID field
to distinguish between the two rows.:

select CTID as rin, the_geom
   from River r
  where r.Name = 'Barito';

ctid;the_geom
tid;geometry
(0,1);"010200002000000000050000000000000000B06DC000000000005061C00000000000E053C00000000000803FC00000000000E05DC000000000006059400000000000C044400000000000D064400000000000C049C00000000000706F40"
(0,2);"0102000020000000000700000000000000004045400000000000F870400000000000106B4000000000007064400000000000A051400000000000205540000000000000124000000000000012C00000000000C043C00000000000404DC00000000000E053C00000000000F062C000000000004044C00000000000706EC0"


SELECT min(ST_Distance(l.the_geom,r.the_geom)) as Min_Width
   FROM (select ctid as rin, the_geom
           from River r
          where r.Name = 'Barito' ) as l,
        (select ctid as rin, the_geom
           from River r
          where r.Name = 'Barito' ) as r
  WHERE l.rin <> r.rin;

min_width
double precision
48.2597140480546

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@...
   Voice: [phone]
Mobile: [phone]
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@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Surya Tarigan]

Some parts of this message have been removed. Learn more about Nabble's security policy.

Dear Simon,

 

the previous SQL ((select row_number() over (order by the_geom) as rin) gave good result, meanwhile the second SQL ( (select ctid as rin, the_geom) still return '0' min_width.  But I still need to check my river polyline, because both min and max give exactly  the  same result, which it should not..

 

After I run :

 

select row_number() over (order by the_geom) as rin, the_geom
  from River r
  where r.Name = 'Barito';

 

it gave result something like :

rin, the_geom

1; -----(empty)---

2;-----(empty)---

3;010500000000........

4;010500000000.............

5;010500000.........

 

could it be caused by empty the_geom in rin 1 and 2?

 

kind regards,

 

sury

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Simon Greener-3]

Sury,

Sorry for the delay: you must have solved this by now.

My "test case" only had two linestrings as you didn't give us any real data to play with. Looking at your 5 row data result below seems to indicate that your river covers more than two rows. So, question to you, what is stored in each row? Which rows have the left data and which the right?

And if there are null geometries then you should do this:

select row_number() over (order by the_geom) as rin, the_geom
   from River r
  where r.Name = 'Barito'
     and r.the_geom is not null;

regards
Simon
On Wed, 14 Oct 2009 20:43:45 +1100, Surya Tarigan <surya.tarigan@...> wrote:

> Dear Simon,
>
> the previous SQL ((select row_number() over (order by the_geom) as rin) gave good result, meanwhile the second SQL ( (select ctid as rin, the_geom) still return '0' min_width.  But I still need to check my river polyline, because both min and max give exactly  the  same result, which it should not..
>
> After I run :
>
> select row_number() over (order by the_geom) as rin, the_geom
>   from River r
>   where r.Name = 'Barito';
>  it gave result something like :
> rin, the_geom
> 1; -----(empty)---
> 2;-----(empty)---
> 3;010500000000........
> 4;010500000000.............
> 5;010500000.........
>  could it be caused by empty the_geom in rin 1 and 2?
>  kind regards,
>  sury
>
>
>
>

--
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@...
   Voice: [phone]
Mobile: [phone]
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@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Surya Tarigan]

Some parts of this message have been removed. Learn more about Nabble's security policy.

Dear list,

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.

 

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;

kind regards,

 

surya

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Surya Tarigan]

Some parts of this message have been removed. Learn more about Nabble's security policy.

Simon,

 

Yes, I have solved the problem by merging left linestrings and also the right linestring aswell.

 

kind regards,

 

surya

---

From: Simon Greener <simon@...>
To: PostGIS Users Discussion <postgis-users@...>
Sent: Thu, October 15, 2009 4:46:40 PM
Subject: Re: [postgis-users] polygon minimum width

Sury,

Sorry for the delay: you must have solved this by now.

My "test case" only had two linestrings as you didn't give us any real data to play with. Looking at your 5 row data result below seems to indicate that your river covers more than two rows. So, question to you, what is stored in each row? Which rows have the left data and which the right?

And if there are null geometries then you should do this:

select row_number() over (order by the_geom) as rin, the_geom
  from River r
  where r.Name = 'Barito'
    and r.the_geom is not null;

regards
Simon
On Wed, 14 Oct 2009 20:43:45 +1100, Surya Tarigan <surya.tarigan@...> wrote:

> Dear Simon,
>
> the previous SQL ((select row_number() over (order by the_geom) as rin) gave good result, meanwhile the second SQL ( (select ctid as rin, the_geom) still return '0' min_width.  But I still need to check my river polyline, because both min and max give exactly  the  same result, which it should not..
>
> After I run :
>
> select row_number() over (order by the_geom) as rin, the_geom
>  from River r
>  where r.Name = 'Barito';
>  it gave result something like :
> rin, the_geom
> 1; -----(empty)---
> 2;-----(empty)---
> 3;010500000000........
> 4;010500000000.............
> 5;010500000.........
>  could it be caused by empty the_geom in rin 1 and 2?
>  kind regards,
>  sury
>
>
>
>

--
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@...
  Voice: [phone]
Mobile: [phone]
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@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Simon Greener-3]

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@...
   Voice: [phone]
Mobile: [phone]
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@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Surya Tarigan]

Some parts of this message have been removed. Learn more about Nabble's security policy.

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@...>
To: PostGIS Users Discussion <postgis-users@...>
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@...
  Voice: [phone]
Mobile: [phone]
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@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users