Click here to Skip to main content
15,886,689 members
Search within:


I want to write Stored Procedure For
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
SQL
server
I'm using a GPS vehicle tracking device, which is programmed to send and store in a DB the position of a vehicle every 5 min when not moving, and every 100 meters when moving.

This DB has a table called "vehicleTrans" that stores the data, with values such as speed, position, datetime, address, vehicle_punto_gps_id, etc. of each position/record.

vehicle_gps_id  | datetime---------- | latitude | longitude | speed

1000------------| 05/16/2012 08:00:00|50.0000   |50.00000   |40 (km/h)
1001------------| 05/16/2012 08:01:00|51.0000   |51.00000   |38 (km/h)
1002------------| 05/16/2012 08:01:23|51.0045   |50.000054  |40 (km/h)
1003------------| 05/16/2012 08:01:40|51.00540  |51.0005430 |39 (km/h)
.
.
.
1040------------| 05/16/2012 08:20:40|53.00540  |53.0005430 |0 (km/h)
1041------------| 05/16/2012 08:25:40|53.00540  |53.0005430 |0 (km/h)
1042------------| 05/16/2012 08:30:40|53.00540  |53.0005430 |0 (km/h)
.
.
.
1060------------| 05/16/2012 10:20:40|53.00540  |53.0005430 |20 (km/h)
1061------------| 05/16/2012 10:20:58|53.0000   |53.00023   |40 (km/h)
1062------------| 05/16/2012 10:21:30|53.0000   |53.00000   |0 (km/h) TRAFFIC LIGHT(DO NOT DISPLAY IF THE DURATION IS LOWER THAN THE TIME PARAMETER)
1063------------| 05/16/2012 10:22:40|53.0045   |53.000054  |40 (km/h)
1064------------| 05/16/2012 10:23:00|53.00540  |53.0005430 |39 (km/h)
. 
.
.
1080------------| 05/16/2012 10:30:40|53.00540  |53.0005430 |0 (km/h)
1081------------| 05/16/2012 10:35:40|53.00540  |53.0005430 |0 (km/h)
1082------------| 05/16/2012 10:40:40|53.00540  |53.0005430 |0 (km/h)
. 
.
.
1100------------| 05/16/2012 12:00:40|53.00540  |53.0005430 |20 (km/h)

And I need a query that will show all the positions where the vehicle had stopped plus the time that it was there. For example, using the above table, the display should look like this:

Time                |Duration         | Address   |Position            |Route Time
05/16/2012 08:20:40 |120 min(8:20-10:20)|Address 1|53.00540,53.0005430 |NULL
05/16/2012 10:30:40 |90 min(10:30-12:00)|Address 2|53.00230,53.0423434 |10 min(10:20-10:30)

With the conditions mentioned above (5 min. when stopped, 100 m. in movement), it can display records where the vehicle was maybe in a traffic jam. Or maybe at a gas station.

SQL
ALTER PROCEDURE [dbo].[sp_ChecarVehiculos]
@carro int,
@f1 datetime,
@f2 datetime
AS
BEGIN
IF 1=0 BEGIN
SET FMTONLY OFF
END
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT cve_punto_gps,fecha_gps, latitud, longitud, velocidad,direccion
INTO #temp
FROM [desarrollo].[dbo].[vehiculo_punto_gps]
WHERE cve_vehiculo=@carro
and fecha_gps>=DATEADD(HOUR,+7,@f1)
and fecha_gps<=DATEADD(HOUR, +7,(DATEADD(MI,+7,@f2)))
DECLARE @cve_inicio as int,
@cve_final as int,
@fecha_inicio as datetime,
@fecha_final as datetime,
@latitud_inicio as decimal(18,15),
@latitud_final as decimal (18,15),
@longitud_inicio as decimal (18,15),
@longitud_final as decimal (18,15),
@velocidad_inicio as int,
@velocidad_final as int,
@direccion_inicio as nvarchar(150),
@direccion_final as nvarchar(150)
DECLARE VehicleCursor CURSOR FAST_FORWARD FOR 
SELECT cve_punto_gps, 
fecha_gps,
latitud,
longitud,
velocidad,
direccion
FROM #temp
ORDER BY cve_punto_gps
OPEN VehicleCursor FETCH NEXT FROM VehicleCursor INTO @cve_inicio, @fecha_inicio, @latitud_inicio, @longitud_inicio, @velocidad_inicio, @direccion_inicio
FETCH NEXT FROM VehicleCursor INTO @cve_final, @fecha_final, @latitud_final, @longitud_final,@velocidad_final, @direccion_final

declare @tempb table(cve int, fecha datetime, posicion nvarchar(60), velocidad int, direccion nvarchar(150) )
WHILE @@FETCH_STATUS = 0 /*or @velocidad_final !=0*/ BEGIN
Declare @lat1 as decimal (18,15)
Declare @lat2 as decimal (18,15)
Declare @lon1 as decimal (18,15)
Declare @lon2 as decimal(18,15)
select @lat1 = @latitud_inicio , @lat2 = @latitud_final , @lon1 = @longitud_inicio, @lon2 = @longitud_final 
IF (sELECT geography::Point(@lat1, @lon1, 4326).STDistance(geography::Point(@lat2, @lon2, 4326)) ) > 80
BEGIN 
IF DATEDIFF(MI,@fecha_inicio,@fecha_final) >=1
BEGIN 
IF @velocidad_final =0 or @velocidad_inicio=0 
BEGIN
declare @posicion nvarchar(60)=(cast(@latitud_inicio as nvarchar(30)) +' '+ cast(@longitud_inicio as nvarchar(30)))

insert into @tempb values(@cve_inicio,@fecha_inicio, @posicion,@velocidad_inicio, @direccion_inicio)
--PRINT 'Posición: '+cast(@fecha_inicio as nvarchar(30))+' en '+@direccion_inicio+'('+@posicion+')';
END 
END
END
SET @cve_inicio = @cve_final
SET @fecha_inicio = @fecha_final
SET @latitud_inicio=@latitud_final
SET @longitud_inicio=@longitud_final
SET @velocidad_inicio=@velocidad_final
SET @direccion_inicio=@direccion_final
FETCH NEXT FROM VehicleCursor INTO @cve_final, @fecha_final, @latitud_final, @longitud_final, @velocidad_final, @direccion_final
END 
CLOSE VehicleCursor 
DEALLOCATE VehicleCursor
select DATEADD(HOUR,-7,fecha) as 'Llegada', direccion as 'Direccion', posicion as 'Posicion' /*into #tempc */from @tempb
/*select * from #tempc*/
END


I tried this but Its not giving accurate result.
I want alternate solution for this.
I am using Sql server 2008
Posted
Updated 7-Apr-15 8:18am
v3
Add a Solution
Comments
OriginalGriff 7-Apr-15 3:53am    
And?
What have you tried?
Where are you stuck?
What help do you need?
codemasterSp 7-Apr-15 4:54am    
If possible please give me solution or algorithm to get this done.
CHill60 7-Apr-15 3:58am    
And what version of SQL Server are you using?
codemasterSp 7-Apr-15 4:52am    
ALTER PROCEDURE [dbo].[sp_ChecarVehiculos]
@carro int,
@f1 datetime,
@f2 datetime
AS
BEGIN
IF 1=0 BEGIN
SET FMTONLY OFF
END
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT cve_punto_gps,fecha_gps, latitud, longitud, velocidad,direccion
INTO #temp
FROM [desarrollo].[dbo].[vehiculo_punto_gps]
WHERE cve_vehiculo=@carro
and fecha_gps>=DATEADD(HOUR,+7,@f1)
and fecha_gps<=DATEADD(HOUR, +7,(DATEADD(MI,+7,@f2)))
DECLARE @cve_inicio as int,
@cve_final as int,
@fecha_inicio as datetime,
@fecha_final as datetime,
@latitud_inicio as decimal(18,15),
@latitud_final as decimal (18,15),
@longitud_inicio as decimal (18,15),
@longitud_final as decimal (18,15),
@velocidad_inicio as int,
@velocidad_final as int,
@direccion_inicio as nvarchar(150),
@direccion_final as nvarchar(150)
DECLARE VehicleCursor CURSOR FAST_FORWARD FOR
SELECT cve_punto_gps,
fecha_gps,
latitud,
longitud,
velocidad,
direccion
FROM #temp
ORDER BY cve_punto_gps
OPEN VehicleCursor FETCH NEXT FROM VehicleCursor INTO @cve_inicio, @fecha_inicio, @latitud_inicio, @longitud_inicio, @velocidad_inicio, @direccion_inicio
FETCH NEXT FROM VehicleCursor INTO @cve_final, @fecha_final, @latitud_final, @longitud_final,@velocidad_final, @direccion_final

declare @tempb table(cve int, fecha datetime, posicion nvarchar(60), velocidad int, direccion nvarchar(150) )
WHILE @@FETCH_STATUS = 0 /*or @velocidad_final !=0*/ BEGIN
Declare @lat1 as decimal (18,15)
Declare @lat2 as decimal (18,15)
Declare @lon1 as decimal (18,15)
Declare @lon2 as decimal(18,15)
select @lat1 = @latitud_inicio , @lat2 = @latitud_final , @lon1 = @longitud_inicio, @lon2 = @longitud_final
IF (sELECT geography::Point(@lat1, @lon1, 4326).STDistance(geography::Point(@lat2, @lon2, 4326)) ) > 80
BEGIN
IF DATEDIFF(MI,@fecha_inicio,@fecha_final) >=1
BEGIN
IF @velocidad_final =0 or @velocidad_inicio=0
BEGIN
declare @posicion nvarchar(60)=(cast(@latitud_inicio as nvarchar(30)) +' '+ cast(@longitud_inicio as nvarchar(30)))

insert into @tempb values(@cve_inicio,@fecha_inicio, @posicion,@velocidad_inicio, @direccion_inicio)
--PRINT 'Posición: '+cast(@fecha_inicio as nvarchar(30))+' en '+@direccion_inicio+'('+@posicion+')';
END
END
END
SET @cve_inicio = @cve_final
SET @fecha_inicio = @fecha_final
SET @latitud_inicio=@latitud_final
SET @longitud_inicio=@longitud_final
SET @velocidad_inicio=@velocidad_final
SET @direccion_inicio=@direccion_final
FETCH NEXT FROM VehicleCursor INTO @cve_final, @fecha_final, @latitud_final, @longitud_final, @velocidad_final, @direccion_final
END
CLOSE VehicleCursor
DEALLOCATE VehicleCursor
select DATEADD(HOUR,-7,fecha) as 'Llegada', direccion as 'Direccion', posicion as 'Posicion' /*into #tempc */from @tempb
/*select * from #tempc*/
END

I tried this but Its not giving accurate result.
I want alternate solution for this.
I am using Sql server 2008

1 solution

quite a thorny one - i'd be inclined to do something like ...

1. build a cursor to select all records (between date clamp perhaps), by vehicle, that have a speed of 0
2. walk thru that looking for continuity breaks, put the start and end of the spans found into a temp table - startId, stopId, vehicleId

you know have a temp table that, per row, identifies when the vehicle was stopped, which you can query at your leisure

for extra points you could make this a real table and have cron job that updates it regularly
 
Share this answer
 
Posted
Comments
codemasterSp 10-Apr-15 9:33am    
Thank you I will try and Will post my work here
Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month
Related Questions
i want to write the stored procedure in C# with if loop
store procedure
how to write the stored procedure?
How to write a stored procedure in SQL
To write a stored procedure to count
How to write stored procedure?
how to write a stored procedure for transactions
how to write stored procedure
Help me on Write Stored Procedure
how to write stored procedure in ms access 2007

Advertise
Privacy
Cookies
Terms of Use
Last Updated 7 Apr 2015
Layout: fixed | fluid
Copyright © CodeProject, 1999-2024
All Rights Reserved.

Web04 2.8:2024-04-02:1

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900