|
abk
Starting Member
29 Posts |
 Posted - 2012-03-27 : 05:13:19
|
| We have GPS devices which send data in many different formats . The formats of the packets and the length of the data packet varies. The delimiter string also is different ( could be , or pipe)Examples :357464031469825,GPRMC,183615.00,A,1256.3340,N,08011.1400,E,0.0,0.0,190312,,,A*51,0,5014,0,0,0,0,0,0,0,0,0,0,0,c4dRFID^0628|90|12345|20|0.0|2|0|34700|20|0|0.0|3|1259.43332|07735.37427|0.275|090711054431|A|1|0|1.00|35202402773555 These strings are stored in a db table( rawtable) with the timestamp and the table can have 15-20 million records.These delimited strings represent values in a table ( datapacket) shown below . The strings must be parsed and stored,but the way in which they appear in the raw data packet differs . So, we need to parse depending on format of packet. What tables would be appropriate to create ? The Should I have a packet format table? How should it look .Raw table can contains about 15-20 million records. So performance and efficiency is an important issue. Any ideas? Thanks===========================================Table datapacket ([Id] [int] NOT NULL,[I1] [varchar](20) NULL,[I2] [varchar](20) NULL,[I3] [varchar](20) NULL,[I4] [varchar](20) NULL,[I5] [varchar](20) NULL,[I6] [varchar](20) NULL,[I7] [varchar](20) NULL,[I8] [varchar](20) NULL,[I9] [varchar](20) NULL,[I10] [varchar](20) NULL,[O1] [varchar](20) NULL,[O2] [varchar](20) NULL,[O3] [varchar](20) NULL,[O4] [varchar](20) NULL,[O5] [varchar](20) NULL,[O6] [varchar](20) NULL,[O7] [varchar](20) NULL,[O8] [varchar](20) NULL,[O9] [varchar](20) NULL,[O10] [varchar](20) NULL,[O11] [varchar](20) NULL,[O12] [varchar](20) NULL,[A1] [varchar](20) NULL,[A2] [varchar](20) NULL,[A3] [varchar](20) NULL,[A4] [varchar](20) NULL,[A5] [varchar](20) NULL,[UnitNo] varchar](30) NULL,.................... and so on |
|
different data formats