Re: Beginner Question...

On 10/07/11 04:41, James David Smith wrote:

- gps_id (primary key)
- date_time
- crimes_link (foreign key)
- osgb36_geom

The data in the table involves lots of gps tracks of different
journeys. They are grouped into journeys by the 'crimes_link' field.
So for example the first 50 rows have an identical 'crimes_link'
field, then the next 50 rows a different value in 'crimes_link', then
the next 50 rows a different one, etc. What I would like to do is to
select the beginning location of each journey. This query gives me the
date_time of the beginning of the journey:

SELECT crimes_link, MIN(date_time)
FROM camdengps3
GROUP BY crimes_link;

However I need to add the osgb36_geom column into the query and am unable too.

Any ideas how to do this please?

Thank you

James Smith

Hi ,

I would recommend adding an index, as follows:

CREATE INDEX ON location(crimes_link, date_time);


I tested the followiunmg query, I and observed that the above index improves performance.  I used pg 9.1beta2, but this should also work for any pg veresion that imoplements the 'WITH' clause on 'SELECT'.


WITH
    start (crimes_link, date_time) AS
    (
        SELECT crimes_link, min(date_time)
        FROM location
        GROUP BY crimes_link
    )
SELECT
    l.gps_id,
    l.date_time,
    l.crimes_link,
    l.osgb36_geom
FROM
    location l,
    start    s
WHERE
    l.crimes_link = s.crimes_link AND
    l.date_time = s.date_time
ORDER BY
    l.gps_id;
   
   
Cheers,
Gavin