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.
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
