[postgis-users] Extract start/end points

[Stephen Woodbridge]

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