Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

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

Avatar of sebadlington
sebadlingtonFlag for United Kingdom of Great Britain and Northern Ireland asked on
ASPGIS/GPS ProgrammingMicrosoft SQL Server 2005
5 Comments1 Solution565 ViewsLast Modified:
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