Re: Frequent update - how to do?

On 12/19/2012 5:44 AM, grell wrote:
> Firstly I will tell you what I'm trying to do:
> I have a database. I have a table Person. Person has location and this
> location need to be updated f.e. every 5 min or with higher frequently.
> Should I have some special tool, table or whatever to keep this data? I want
> to have history of location so I nned to keep old values.

so you don't just want location for each person, you want their entire
path? 5 minute intervals will accumulate almost 9000 locations a month
for each person. how many 'persons' in the table ?

if this location data is latitude/longitude, I'd consider using the
PostGIS extension for storing it. I think I'd store locations in a
separate table, with (person_id integer, ts timestamptz, location
geography(POINT))

and insert each position in there.

to get the info on person $1 including his current location....

select p.*, l.location
from persons p join locations l on p.id=l.person_id
where p.id=$1
order by l.timestamptz desc limit 1;

to get person $1's path...

select ts,location from locations where person_id=$1 order by ts;

etc etc.