[postgis-users] Extract start/end points

[Mr. Puneet Kishor]

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 <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