On 2018/06/08 12:52 PM, Mukesh Kumar wrote:
Hi Ryan,
Thanks for the advice.
I cant share the exact Schema, however i can give an idea.
I am trying to store GNSS satellite info, which is streaming every second.
My Primary Key is the Timestamp at which i have got the information.. And
other columns in the table are the satellite information like
SNR,elevation,abc,def,ghi...... for each of the satellite.
max number of satellite at a given timestamp can be 64.
So in my schema, all the satellite parameters are getting repeated 64 times.
I want a easy way to extract the data, lets say get timestamp when any of
the satellite has SNR > 30.
My Idea was to write a simple query like
Select SNR* from Table where SNR*> 30;
where * takes value from 1-64.
Could you please suggest how to handle such scenarios ?
It's hard to suggest a way to do what you want given the schema you
have, BUT, it's easy to achieve that if you can change the schema.
Why not have the table store the values for each satellite in a new row?
So in stead of having:
TimeStamp-Sat1 | SNR-Sat1 | elevation-Sat1 | abc-Sat1 | etc. | etc...
Go for:
SatNumber | Timestamp | SNR | elevation | abc | etc....
Now the obvious next question is, how to deal with the fact that the
Timestamp is no longer unique? Well, that can be fixed by declaring a
multiple-column Primary key, since the timestamp will still be unique
per satellite, and the satellite number becomes a query-able entity -
making for a schema like this:
CREATE TABLE SatLog(
SatNum INTEGER NOT NULL,
TimeStamp NUMERIC NOT NULL,
SNR REAL,
elevation REAL,
abc REAL,
etc.,
PRIMARY KEY (SatNum, TimeStamp)
);
Your insert syntax will be very much easier, and all queries will be
easier. since after this change you can query individual satellites
easily, getting satellite-specific stats using the powerful aggregate
querying in sqlite.
Also, if later you drop or add new satellites, this schema will handle
it painlessly, whereas the other one will require a re-make every time.
Hope that helps, but please ask if anything is unclear or you have any
more questions.
Cheers,
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
