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 <wood...@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@postgis.**refractions.net<postgis-users@postgis.refractions.net> > http://postgis.refractions.**net/mailman/listinfo/postgis-**users<http://postgis.refractions.net/mailman/listinfo/postgis-users> >
_______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
