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