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

[chris mollis]

At those rates, you would need to shard out across a few servers.. so
picking your sharding/splitting function is crucial..

On Wed, May 27, 2009 at 1:25 PM, Rick <graham.rick at gmail.com> wrote:

> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20090527/99b0063a/attachment.html>