## Re: Beginner Question...

From: James David Smith Michael Wood , tgl(at)sss(dot)pgh(dot)pa(dot)us pgsql-novice(at)postgresql(dot)org Re: Beginner Question... 2011-07-09 22:31:19 CAMu32ABJJZtkhiYhHu2JX3Bfi_FQTOd___y6YDJcXXk8cmrqXg@mail.gmail.com (view raw or whole thread) 2011-07-09 16:41:36 from James David Smith  2011-07-09 17:50:13 from Michael Wood   2011-07-09 22:31:19 from James David Smith  2011-07-09 17:53:48 from Tom Lane  2011-07-10 01:22:36 from Gavin Flower pgsql-novice
```Dear Michael, Tom ( et al),

Thanks for your help. Once I had edited Michael's code slightlyit
worked perfectly! Thank you! I had just this minute managed to achieve
a similar result, but by using two temp tables and about 10 more lines
of code..! Haha.

Unfortunately I am presented with 3425 rows of starting locations,
when I know that there are only 2233 journeys in the table. It seems
that some of the journeys have more than one starting point according
to the GPS. Bad data. I think what I now need to do are to find the
journeys that have more than one starting location, and to take some
sort of average of their GEOM column. This code identifies how many
starting points there are for each journey:

________________________
DROP TABLE IF EXISTS temp;

SELECT l.camdencrimes_link, l.osgb36_geom, date_time INTO TEMP TABLE temp
FROM camdengps3 AS l INNER JOIN (
SELECT camdencrimes_link, MIN(date_time) AS start
FROM camdengps3
GROUP BY camdencrimes_link) AS r
ON l.date_time = r.start AND l.camdencrimes_link = r.camdencrimes_link;

SELECT a.*, b
FROM temp AS a
INNER JOIN
FROM temp
GROUP BY camdencrimes_link) AS c
WHERE b >1;
________________________

But I now need to take an average of the starting points for each
journey where there are more than one starting point? Any ideas guys?

Thank you so much for your help...

James

On 9 July 2011 18:50, Michael Wood <esiotrot(at)gmail(dot)com> wrote:
> On 9 July 2011 18:41, James David Smith <james(dot)david(dot)smith(at)gmail(dot)com> wrote:
>> Hi everyone,
>>
>> Could someone help me with this question please? I have a table with
>> four columns:
>>
>> - 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?
>
> Sorry, I don't have time to test this now and maybe someone else has a
> better way, but maybe it will give you and idea:
>
> SELECT l.date_time, l.crimes_link, l.osgb36_geom
> FROM camdengps3 AS l INNER JOIN (
>    SELECT crimes_link, MIN(date_time) AS start
>    FROM camdengps3
>    GROUP BY crimes_link) AS r
>        ON l.date_time = s.start AND l.crimes_link = s.crimes_link;
>
> --
> Michael Wood <esiotrot(at)gmail(dot)com>
>

```

### pgsql-novice by date

 Next: From: Gavin Flower Date: 2011-07-10 01:22:36 Subject: Re: Beginner Question... Previous: From: Tom Lane Date: 2011-07-09 17:53:48 Subject: Re: Beginner Question...