[postgis-users] Make line from points...

[Mark Cave-Ayland]

On Fri, 2007-12-07 at 14:35 +0100, LuVar wrote:
> Hi. I have table with points. The points reprezents my ride on bike...
> 
> table of points:
> 
> id    |   possition   |   time    |  trip   | speed
> ---------------------------------------------------
> 0     |  point        | datetime  | null    |  12.33 km/h
> 
> 
> The possition I insert somethink like this:
> insert ... GeomFromText('POINT(4972.90 5005.85)',-1)) ...
> time is datetime from my GPS device...
> trip is allways null.
> 
> This is nice. But after I insert a few points from trip (this is done
> on the fly, by bluetooth) I want to split my points to trips...
> 
> So I want to copy (move) my data to another table. First I mark the
> points as a trip (I find groups of points with speed not null, and I
> give them some trip number. So trip collumn wil not be numll) and than
> I want to make some:
> 
> GeomFromText('LINE((4972.90 5005.85,4974.08 5007.98,4973.49 5008.31,4972.31 5006.18,4972.90 5005.85))',-1));
> 
> and store it to some trips table. I want to do this, because I want to
> postgis to calculate length of trip to me, which is possible only from
> line (if I am not wrong).
> 
> trips table:
> 
> id    |  tripDate   |   timeLenght   | lenghtOfTrip   | averageSpeed
> --------------------------------------------------------------------
> 0     | datetime    |   1h 25m       | 7.5 km         | 5 km/h
> 
> 
> how can I convert rows with equal trip collumn from table of points,
> to one row in trips table? Is it possible to do it by sql query? (If
> not, I probbably make it in my program)
> 
> PS: Do you think, that this way (style) of solving this problem is the
> best? (If no, please, give me some hints)


Hi LuVar,

How about something like this (where X is the id of the trip length you
are trying to calculate):


SELECT ST_Length2D((SELECT ST_MakeLine(possition) FROM (SELECT possition
FROM points WHERE speed IS NOT NULL AND trip = X ORDER BY time) AS
foo));


By casting to numeric to control the decimal places, you could even do
something like this to perform the UPDATE directly (assuming your SRID
units are in metres and your lenghtOfTrip column is a varchar):


UPDATE trips SET lenghtOfTrip = cast(cast((SELECT ST_Length2D((SELECT
ST_MakeLine(possition) FROM (SELECT possition FROM points WHERE speed IS
NOT NULL AND trip = X ORDER BY time) AS foo))) AS numeric(4,2)) AS
varchar) || ' km' WHERE id = X;


Note: I would suggest removing the "km" units from your trips table and
storing the numbers as a floating point column, since otherwise you will
find it much harder working with everything as a string.


HTH,

Mark.

-- 
ILande - Open Source Consultancy
http://www.ilande.co.uk