[postgis-users] Choosing a database scheme for huge amount of points

[Rick]

I have a similar problem with Radar data.  I'd be interested in
hearing any ideas on this topic.

On Wed, May 27, 2009 at 12:43 PM, Stephen Woodbridge
<woodbri at swoodbridge.com> wrote:
> Alexander Cohagen wrote:
>>
>> Hi,
>>
>> I have the following data in the system:
>> 1. Points - retrieved from users GPS devices every 10 seconds. (x,y,z -
>> lon,lat,alt)
>> 2. timestamp for each point
>>
>>
>> So my amount could potentially grow to:
>> 1 day, 1 user = 8640 coordinates
>> 1 day, 10 000 users = 8 640 000 coordinates
>> A year, 10 000 users = 3 153 600 000
>>
>> How could i store such amount of data using postgis in the most effective
>> way?
>
> "effective" with respect to what? It might be "effective" to dump them all
> into a table if you never need to access them. You have only define one half
> of your problem. Lets say we have them all stored in the most "effective"
> possible way, now what do you need to do with all these points? How do you
> need to access them? what is the most common query you will need to do? What
> is the most obscure query? what performance criteria do you have? etc
>
> -Steve
>
>> Breaking into several little qustions:
>> 1. Should i store all points in 1 table or in several tables each for
>> exact time period(all inheriting 1 main table with no data)?
>> 2. Should i create the GIST index and make VACUUM ANALYZE?
>> 3. What other performance tips could be helpful? (if you point me to
>> information about clustering tables or computers with postgis, to best
>> practises of other people handling such huge amount of data - i appreciate
>> it).
>>
>> Anticipating some questions:
>> 1. Yes, I can afford a computing cloud or own computer cluster if
>> needed(But I dont know how to cluster the postegre)
>> 2. Yes, there is some more data for each coordinate - user_id, timestamp.
>> 3. Yes, there will be enough writting and quiering this database. The
>> queries will extract points with distance not more than X from a given point
>> for a given user_id and timestamp.
>>
>>
>>
>>
>>
>> ------------------------------------------------------------------------
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>



-- 
Cheers!
Rick