[postgis-users] Extract start/end points

[Aurélien FILEZ]

Thank you,

I made :

create table table_tmp as select *,
 St_X(St_StartPoint(the_geom)) as x1,
 St_Y(St_StartPoint(the_geom)) as y1,
 St_X(St_EndPoint(the_geom)) as x2,
 St_Y(St_EndPoint(the_geom)) as y2;
from table;

drop table table;
alter table_tmp rename to table;

It takes 2 minutes ;)

Thanks !

On Sun, Mar 18, 2012 at 1:36 AM, Stephen Woodbridge <woodbri at swoodbridge.com
> wrote:

> On 3/17/2012 6:28 PM, Aurélien FILEZ wrote:
>
>> Hi all,
>>
>> I have a table of 3.200.000 linestrings and I have to extract the start
>> point (x1, y1) and the end point (x2, y2).
>>
>> So I make :
>> UPDATE myTable SET x1 = St_X(St_StartPoint(the_geom));
>>
>> But the query is running since 5 hours, and still not finished.
>>
>> The computer is an Ubuntu Server, i3, with 4Go of RAM..
>>
>> Is it normal ? Is there is something to do somewhere ?
>>
>
> A couple of thoughts on this:
>
> 1. yes, this is more or less normal as you have to modify every row in the
> table and because of row revisioning you actually have to copy every row
> and modify the copy.
>
> 2. it probably would be faster to do something like:
>
> create table newtable as select <list of your existing columns>,
>  St_X(St_StartPoint(the_geom)) as x1,
>  St_Y(St_StartPoint(the_geom)) as y1,
>  St_X(St_EndPoint(the_geom)) as x2,
>  St_Y(St_EndPoint(the_geom)) as y2;
>
> Or if you use the update method, you should add all 4 values on the update
> rather than do it 4 times. And make sure you vacuum the database afterwards
> to recover dead space etc.
>
> 3. if you are using the out of the box postgresql install it is probably
> not using much of your memory, and you should do some tuning. google for
> "postgresql tuning" and I'm sure you will get lots of links that will be
> helpful.
>
> -Steve
> ______________________________**_________________
> postgis-users mailing list
> postgis-users at postgis.**refractions.net<postgis-users at postgis.refractions.net>
> http://postgis.refractions.**net/mailman/listinfo/postgis-**users<http://postgis.refractions.net/mailman/listinfo/postgis-users>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120318/23d1c3c8/attachment.html>