SQL Server - can I run this VBscript to clean up GPS data

sebadlington
sebadlington used Ask the Experts™
on
Hi,

 I have a GPS asp application that tracks trucks - I want to be able to work out when a truck is stopped so have used the code below on my ASP page that works out the distance between two lat/long points - this works fine.
 
 I want to keep the SQL database in the background tidy and was wondering if I can implement this kind of code on a trigger within the database. The table I have as below end up containing a lot of zero movements as a truck is parked up and I don't want to store them all.
 
 Ideally I want to run the script below to see if the truck has moved since the last time it polled - if not, then delete the record or don't add it to the db at all
 
 this is the data from the database (normally all the the truck registrations are interspersed with each other. The system polls every minute to give a lat/long position.
 
 
ID            TruckID             Lat            Long            LocationDate
1574979      XY11ABC         55.83513224      -4.07418026      7/5/2010 8:22:53 AM      
1574987      XY11ABC         55.83631566      -4.06571035      7/5/2010 8:23:56 AM      
1574995      XY11ABC         55.83668057      -4.06582142      7/5/2010 8:24:59 AM      
1575005      XY11ABC         55.83668052      -4.06582194      7/5/2010 8:26:02 AM      
1575016      XY11ABC         55.83693195      -4.06581692      7/5/2010 8:27:05 AM      
1575030      XY11ABC         55.83693303      -4.06581585      7/5/2010 8:28:08 AM      
1575042      XY11ABC         55.83693189      -4.06581739      7/5/2010 8:29:25 AM      
1575054      XY11ABC         55.83693144      -4.06581942      7/5/2010 8:30:28 AM      
1575065      XY11ABC         55.83693061      -4.06581951      7/5/2010 8:31:31 AM      
1575077      XY11ABC         55.83693093      -4.06581852      7/5/2010 8:32:34 AM      
1575089      XY11ABC         55.83692971      -4.06581949      7/5/2010 8:33:37 AM      
1575101      XY11ABC         55.83692967      -4.06581927      7/5/2010 8:34:40 AM      
1575113      XY11ABC         55.83624991      -4.06572767      7/5/2010 8:35:43 AM      
1575125      XY11ABC         55.8349177      -4.0744535      7/5/2010 8:36:46 AM      
1575138      XY11ABC         55.84388632      -4.07185126      7/5/2010 8:37:49 AM      
1575149      XY11ABC         55.84737055      -4.0695392      7/5/2010 8:38:52 AM      
1575161      XY11ABC         55.85210767      -4.08723739      7/5/2010 8:39:55 AM      
1575173      XY11ABC         55.86060313      -4.10380805      7/5/2010 8:40:58 AM      
1575185      XY11ABC         55.86403085      -4.12406581      7/5/2010 8:42:02 AM      
1575196      XY11ABC         55.86608214      -4.13157355      7/5/2010 8:43:05 AM
1575208      XY11ABC         55.86608162      -4.13157292      7/5/2010 8:44:08 AM
1575220      XY11ABC         55.86361596      -4.13831228      7/5/2010 8:45:11 AM      
1575232      XY11ABC         55.86651528      -4.14260283      7/5/2010 8:46:13 AM      



This is the data that I show on my Web Page
The number on the end is the distance  between the last Lat/Long position

as you can see from 8:28 to 8:34 the truck hasn't moved and I don't need to record all those zeroes Just saving one instance would be fine. We have 40ish trucks and the database can end up with a million records over 2 or three months, a large %age of the entries will be zero values  

XY11ABC      05/07/2010 08:22:53      51 A752, North Lanarkshire G71 5, UK      436
XY11ABC      05/07/2010 08:23:56      Tannochside Dr, North Lanarkshire G71 5, UK      544
XY11ABC      05/07/2010 08:24:59      Tannochside Dr, North Lanarkshire G71 5, UK      41
XY11ABC      05/07/2010 08:26:02      Tannochside Dr, North Lanarkshire G71 5, UK      0
XY11ABC      05/07/2010 08:27:05      Tannochside Dr, North Lanarkshire G71 5, UK      27
XY11ABC      05/07/2010 08:28:08      Tannochside Dr, North Lanarkshire G71 5, UK      0
XY11ABC      05/07/2010 08:29:25      Tannochside Dr, North Lanarkshire G71 5, UK      0
XY11ABC      05/07/2010 08:30:28      Tannochside Dr, North Lanarkshire G71 5, UK      0
XY11ABC      05/07/2010 08:31:31      Tannochside Dr, North Lanarkshire G71 5, UK      0
XY11ABC      05/07/2010 08:32:34      Tannochside Dr, North Lanarkshire G71 5, UK      0
XY11ABC      05/07/2010 08:33:37      Tannochside Dr, North Lanarkshire G71 5, UK      0
XY11ABC      05/07/2010 08:34:40      Tannochside Dr, North Lanarkshire G71 5, UK      0
XY11ABC      05/07/2010 08:35:43      Tannochside Dr, North Lanarkshire G71 5, UK      75
XY11ABC      05/07/2010 08:36:46      A752, North Lanarkshire G71 5, UK      563
XY11ABC      05/07/2010 08:37:49      62 Roseneath Dr, North Lanarkshire G69 7, UK      1008
XY11ABC      05/07/2010 08:38:52      Dunnachie Drive, North Lanarkshire ML5 5, UK      412
XY11ABC      05/07/2010 08:39:55      Mainhill Rd, Bargeddie, North Lanarkshire G69 7, UK      1221
XY11ABC      05/07/2010 08:40:58      M8, Glasgow City G69 6, UK      1398
XY11ABC      05/07/2010 08:42:02      Easterhouse, Ware Road (N-bound), Glasgow, Glasgow City G34 9, UK      1318
XY11ABC      05/07/2010 08:43:05      Baldoven Crescent, Glasgow, Glasgow City G33 4, UK      520
XY11ABC      05/07/2010 08:44:08      Baldoven Crescent, Glasgow, Glasgow City G33 4, UK      0
XY11ABC      05/07/2010 08:45:11      Wellhouse Primary School, Glasgow, Glasgow City G33 4, UK      501
XY11ABC      05/07/2010 08:46:13      Easterhouse, Easter Queenslie Road (NE-bound), Glasgow, Glasgow City G33 4, UK      418


this is the code I use to work out the distance between the 2 points

<%
lat1 = (Recordset1.Fields.Item("Latitude").Value)
lon1 = (Recordset1.Fields.Item("Longitude").Value)
if (testloop > 0) then
dim R, dLat, dLon, a, c, d
 
 ' Gives distance in meters between 2 points
 latlong = Sqr(((111 * (lat2 - lat1)) * (111 * (lat2 - lat1))) + (111 * (lon2 - lon1) * Cos(lat1 / 57.3)) * (111 * (lon2 - lon1) * Cos(lat1 / 57.3))) * 1000
 SebDistance = latlong
 
end if
%>

lat2 and lon2 are set at the end of the overall while loop

Is it possible for me to build this into the database either as a trigger or a scheduled task to clean up the data - ie - if there are 2 or more zero movements, delete all but the first.

Thanks

Seb
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
Give this a try
declare @threshold float
set @threshold = 0.0
 delete A
 from tbl A
 cross apply ( -- the prev reading
	select top(1) Lat as lat2, long as lon2
	from tbl B
	where A.TruckID=B.TruckID and B.ID < A.ID
	order by B.ID desc) B
 where Sqrt(((111 * (lat2 - lat)) * (111 * (lat2 - lat))) + (111 * (lon2 - long) * Cos(lat / 57.3)) * (111 * (lon2 - long) * Cos(lat / 57.3))) * 1000 < @threshold

Open in new window

Author

Commented:
Thanks CyberKiwi - I will give this a try, I take it this is a Stored procedure or do I put in as a trigger.

Cheers
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
This is a once off deletion, which you probably want to test for speed.  If it is acceptable, then run it every night as a Sql Server Agent job step (takes Sql code).

Author

Commented:
CyberKiwi - you are an Uber guru - that worked like a charm - took a coupld of hours to run though so I will shedule it to run over the weekends

Fantastic

Thankyou

Author

Commented:
Worked like a dream

Thankyou

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial