Stored Procedure to save variables and display records and time differences

Question

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 "vehiculo_punto_gps" 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.

So far i tried using SQL cursors as shown on this post. and then edit to this :

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

But this query is not very accurate, since it shows the first moving position but not the actual position where the vehicle reached zero speed. And if I want to calculate the duration I would substract the time of each displayed record and that would not be accurate either. So, instead of 120 min it shows 130 min. (120 + 10 min of commute).

Hope I was clear enough, as this is hard to explain because I'm not a native english speaker, but I will be glad to hand you out more details.

Thanks in advance.

Stored Procedure to save variables and display records and time differences

0 Answers0