x

need db design suggestions

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

)
more ▼

asked Mar 27 '12 at 07:33 AM in Default

abk gravatar image

abk
1 3 4 4

How is the data presented to the SQL server before landing in the bulk table? Are the GPS Devices diretly accessing the server or dropping binary/text files that are then imported by a separate process?

If the data is stored statically outside SQL Server and then imported into the raw table, I would suggest doing the parsing outside SQL Server too (SSIS or LogParser spring to mind for this).

This way you are presenting the pre-cleaned data to SQL Server for storage instead of bending SQL Server to your will to perform complex data/string manipulation (not really SQL Server's speciality).
Mar 27 '12 at 11:01 AM WilliamD
Right now, the data is pooled from gps device and raw ata is dumped to database directly. How can we do the parsing outside before dumping into db ? Thanks
Mar 27 '12 at 11:26 AM abk
You must have some sort of program gathering the data from the GPS devices before pumping this into SQL Server. I would suggest you program the data manipulation on that level. You could go the way of defining different data structures per GPS device and matching "masks" to take the required data from each stream.
Mar 27 '12 at 11:48 AM WilliamD
Thanks a lot. I have never done this type of programming. Is it possible to send a small piece of code ( even an example) so that I can undertand better? Please do help only if it is feasible. Thanks again
Mar 27 '12 at 02:26 PM abk

I need to get an output like For this string 357464031469825,183615.00,1256.3340,0,0

output must be @UnitNo = 357464031469825, @Lat = 183615.00 @Lon = 1256.3340, @IO1 = 0 @IO2 = 0;

For this string ^|1|1|1259.43332|07735.37427|35202402773555

output must be @UnitNo=35202402773555 @lat=1259.43332 @lon=07735.37427 @IO1=1 @IO2=1

These outputs will be sent to another stored procedure

As you can see the position in which they appear in the two strings are different. How can I parse it and populate the variables correctly using sql server store procedure. Since the raw data table has many records , I need a procedure which is fast.

Also,depending upon the device the position of unitno in the string can vary. We have about 50 devices right now( could increase) and also the number of substrings can be upto 42( unitno, lat,lan is just an example) . So I will need a general sql procedure which will be able to parse based on a format. I hope I am able to clearly state what I am looking for.
Mar 28 '12 at 08:56 AM abk
show all comments (comments are locked)
10|1200 characters needed characters left

0 answers: sort newest
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1816

asked: Mar 27 '12 at 07:33 AM

Seen: 616 times

Last Updated: Mar 28 '12 at 11:10 AM