Skip site navigation (1) Skip section navigation (2)

Re: Beginner Question...

From: Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz>
To: James David Smith <james(dot)david(dot)smith(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Beginner Question...
Date: 2011-07-10 01:22:36
Message-ID: 4E18FEDC.2070908@archidevsys.co.nz (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

pgsql-novice by date

Next:From: JORGE MALDONADODate: 2011-07-10 18:08:25
Subject: pgpass.cong file becomes empty
Previous:From: James David SmithDate: 2011-07-09 22:31:19
Subject: Re: Beginner Question...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group