Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1287

Re: table design for massive GPS data

Show all headers | View raw

On Friday, September 14, 2012 4:35:11 AM UTC-7, Bob Barrows wrote:
> migurus wrote:
> 
> > Windows Server/SQL Server 2008, we need to store GPS readings from
> 
> > roughly 1000 vehicles, ~ 300 readings per vehicle per day. The data
> 
> > comes in in real-time. Records are never updated after insert. That
> 
> > gives us roughly 120 million records per year. We need to keep last 5
> 
> > years of records.
> 
> >
> 
> > The usage of stored data is to query vehicle positions and event
> 
> > attributes within entire day by given vehicle and date. Geospatial
> 
> > features and functions are not really needed for the reporting.
> 
> >
> 
> > I'd like to discuss table design for these requirements.
> 
> >
> 
> > Here is a preliminary record content:
> 
> > - vehicle id
> 
> > - gps reading X and Y
> 
> > - gps reading time
> 
> > - one-letter event attribute
> 
> > - event id (nullable)
> 
> >
> 
> > First idea is to keep data normalized in one table, one record per
> 
> > GPS reading. It looks very simple to implement, but I doubt the
> 
> > server will be happy with this high number of small records.
> 
> 
> 
> Why not? Narrow and long tables are perfect for relational databases
> 
> 

Thanks, I did not know that, as I have limited exposure in db field.

> 
> > I we
> 
> > were to do this, what would be a good candidate for primary key here?
> 
> 
> 
> What combination of columns identifies a row? Vehicle_ID and GPS_TIME? If 
> 
> so, there's your clustered primary key (wtp?)
> 
> What is that event id and why is it nullable?

Some events do not require id, this will not be any foreign key or anything

> 
> 
> 
> 
> 
> > Just for illustration I show table def for idea# 1
> 
> > create table idea1 (
> 
> > VEHICLE_ID int not null
> 
> > , GPS_X real not null
> 
> > , GPS_Y real not null
> 
> > , GPS_TIME datetime not null
> 
> > , EVENT_LETTER char not null
> 
> > , EVENT_ID int null
> 
> > );
> 
> > I don't show indexes as I am not clear what to use.
> 
> > The typical query would look like this:
> 
> > select
> 
> > GPS_X
> 
> > , GPS_Y
> 
> > , GPS_TIME
> 
> > , EVENT_LETTER
> 
> > , EVENT_ID
> 
> > from
> 
> > idea1
> 
> > where
> 
> > VEHICLE_ID=@id
> 
> > and GPS_TIME between @from and @to
> 
> 
> 
> The proposed clustered primary key above would suit this query perfectly
> 
> 
> 
> >
> 
> >
> 
> > Second idea is to keep a small table where records are inserted as
> 
> > they come in the structure shown above and then on the daily basis
> 
> > aggregate it and store as one record per vehicle in the another
> 
> > table, which will be used for queries. By doing this we shrink number
> 
> > of records from 120 million to be ~ 350 thousands per year. This
> 
> > looks much more manageable. The cons here are additional effort to
> 
> > come up with the way of storing and retrieving gps readings as a
> 
> > blob.
> 
> >
> 
> Nah, this data isn't really suitable for aggregation, IMO.

Thanks for your input.

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

table design for massive GPS data migurus <migurus@yahoo.com> - 2012-09-13 14:47 -0700
  Re: table design for massive GPS data bradbury9 <ray.bradbury9@gmail.com> - 2012-09-14 00:06 -0700
  Re: table design for massive GPS data "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2012-09-14 07:33 -0400
    Re: table design for massive GPS data migurus <migurus@yahoo.com> - 2012-09-14 10:13 -0700
  Re: table design for massive GPS data rja.carnegie@gmail.com - 2012-09-14 05:05 -0700
    Re: table design for massive GPS data migurus <migurus@yahoo.com> - 2012-09-14 10:25 -0700
  Re: table design for massive GPS data Erland Sommarskog <esquel@sommarskog.se> - 2012-09-15 19:21 +0200
    Re: table design for massive GPS data rja.carnegie@gmail.com - 2012-09-17 08:46 -0700

csiph-web