On Mar 17, 2012, at 8:34 PM, Aurélien FILEZ wrote: > 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 ;)
that does sound more reasonable (good call Steve). 3.2 million rows are not a lot. Just extracting them and updating them from a text file using a simple Perl script should take about a couple of minutes. > > Thanks ! > > On Sun, Mar 18, 2012 at 1:36 AM, Stephen Woodbridge <[email protected] >> 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 [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
