For a GPS utility project we are planning on extracting certain attributes from a huge "GPS Raw Data" read only database which we have access to containing GPS data from several years from several devices attached to vehicles.
The data is time stamped. Where the time gap between pieces of data is more than 10 minutes, a new trip is instance is assumed and in our write access "Trip" database we create a new instance for the data clump with a newtrip idalong with the time range of the data.
The process is to be run hourly to update the "Trip" database with new trips and append to overlapping trips.
We've some questions:
a) Is it easy to read from one database and write into another in c# hourly
b) How would one go about running a C# program automatically every hour on the server?
c) Is there a better way to do this than an hourly update? (dynamically perhaps??)
d) When querying the database and comparing the time stamps, how for instance would we go about identifying a 10 minute gap when the time/date is in the format "22/12/2007 11:25:00". I can't get my head around actually writing this - it's probably ridiculously simple
what type of database r u using ?
If its SQL, why not create a storedprocedure and make it to run in a job every hour, it will best for performance .
|||Perhaps you can look into solutions like Replication where your GPS Raw Data can be the publisher and your target db can be the subscriber. Any transactions on publisher are replicated on to the subscriber. and the subscriber can be used for querying.
>>a) Is it easy to read from one database and write into another in c# hourly
Depends on how much data there is to read/write.
b) How would one go about running a C# program automatically every hour on the server?
>>You can write a stored procedure that has the logic to query for modified/new records and does an INSERT into the target db.
>>c) Is there a better way to do this than an hourly update? (dynamically perhaps??)
Perhaps. Look into the replication technology and see if it suits your requirements.
>>d) When querying the database and comparing the time stamps, how for instance would we go about identifying a 10 minute gap when the time/date is in the format "22/12/2007 11:25:00". I can't get my head around actually writing this - it's probably ridiculously simple
You can use the DATEDIFF function : IF DATEDIFF(mi, <valueinrow>, Getdate()) = 10. But you'd have to have some kind of service running constantly to trigger this data transfer when the 10 minute interval happens.
|||Thank you for your help Ramzi!
We are using MS SQL 2005 with Microsoft Visual Web Dev 2005.
I must read into stored procedures if you think this is the best way forward as a solution. Is there a particular utility you would advise for creating/managing stored procedures?
I just downloaded MS SQL Management Studio. However, I cannot find the ASPNETDB.MDF database created in Visual Web Developer on the local machine when I look for it.
Thanks ndinakar
Perhaps you can look into solutions like Replication where your GPS Raw Data can be the publisher and your target db can be the subscriber. Any transactions on publisher are replicated on to the subscriber. and the subscriber can be used for querying.
>>a) Is it easy to read from one database and write into another in c# hourly
Depends on how much data there is to read/write.
Once the main "lot" from the past year is indexed, then each hourly update would be rather small with the only perhaps a hundred co-ordinates coming in over an hour. At times there will be no new data.
b) How would one go about running a C# program automatically every hour on the server?
>>You can write a stored procedure that has the logic to query for modified/new records and does an INSERT into the target db.
We will look into the stored procedure function. This looks like it could be very useful.. Would this be used alongside or instead of replication? (bear in mind I've not yet read up on replication)
>>d) When querying the database and comparing the time stamps, how for instance would we go about identifying a 10 minute gap when the time/date is in the format "22/12/2007 11:25:00". I can't get my head around actually writing this - it's probably ridiculously simple
You can use the DATEDIFF function : IF DATEDIFF(mi, <valueinrow>, Getdate()) = 10. But you'd have to have some kind of service running constantly to trigger this data transfer when the 10 minute interval happens.
In the stored procedure that runs hourly could this be used to run through the data from the past hour and identify these if/when these intervals happen or are you saying that it needs to be constantly checking (every few seconds)?
I'm looking for info on stored procedures now.
On the MS website there is mention of a "CREATE TRIGGER" procedure... Is this what you refer to as something we could use with the DATEDIFF comparison?
Is it possible to set a timed execution? Could someone give a small example of this?
All of your help has been much appreciated!
Going by your replies perhaps its best to create a job, have the job call a stored procedure. You can schedule the job to run every 10 minutes and in your stored procedure, query all the new/modified records after the previous run and migrate the data. You might need to store the timestamp of the last run in case the job does not run for any reason or you need to stop the job for a few hours. You can always catch up if you have the timestamp of the previous run.
Check out Books On Line (the documentation that comes with SQL Server) for CREATE PROCEDURE.
For creating/scheduling/managing jobs look for "Jobs" under SQL Server Agent.
|||I have managed to do some basic copying from one database to another, however, I'm having some problems with the datediff function.
We are selecting the time and vehicle ID from the "GPS raw data" database. We want to compare each entry with the previous one and, where there is a gap of more than 15 mins, return it.
We will group the associated data into another "trip" table, with a primary key of "trip id". So any gap in GPS transmission of more than 15 mins will be considered the end/start of a trip.
This is just vague code.
I want to compare each row really.. I've no idea how to though..(I've put in time & time+1 where time+1 is the next row - Don't know how to do this)
SELECT Time, deviceID,
FROM [gps.db]
WHERE datediff(n, time, time+1) > 15
No comments:
Post a Comment