我想写存储过程 [英] I want to write Stored Procedure For

查看：50 发布时间：2019/6/12 13:02:20 SQL Server

本文介绍了我想写存储过程的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在使用GPS车辆跟踪设备，该设备被编程为在不移动时每隔5分钟在DB中发送和存储车辆的位置，并且在移动时每100米发送和存储。

这个DB有一个名为vehicleTrans的表，用于存储数据，每个位置/记录的速度，位置，日期时间，地址，vehicle_punto_gps_id等值。

 vehicle_gps_id | datetime ---------- |纬度|经度|速度
 
 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）交通灯（如果持续时间低于时间参数，请不要显示）
 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）

我需要一个查询，显示车辆的所有位置已停止加上它在那里的时间。例如，使用上表，显示应如下所示：

时间|持续时间|地址|职位|路线时间
 05/16/2012 08:20:40 | 120分钟（8：20-10：20）|地址1 | 53.00540,53.0005430 | NULL 
 05/16/2012 10:30:40 | 90分钟（10：30-12：00）|地址2 | 53.00230,53.0423434 | 10分钟（10：20-10：30）

根据上述条件（停止时5分钟，运动100米），它可以显示车辆可能处于交通拥堵状态的记录。或者也许在加油站。

  ALTER   PROCEDURE  [dbo]。[sp_ChecarVehiculos] 
  @ carro   int ，
  @ f1   datetime ，
  @ f2   datetime  
  AS  
  BEGIN  
  IF  1 = 0  BEGIN  
  SET   FMTONLY   OFF  
  END  
   -    SET NOCOUNT添加ON以防止额外的结果集 
   -   干扰wi SELECT语句。 
  SET   NOCOUNT   ON ; 
  SELECT  cve_punto_gps，fecha_gps，latitud，longitudinal，velocidad，direccion 
  INTO ＃ temp 
  FROM  [desarrollo]。[dbo]。[vehiculo_punto_gps] 
  WHERE  cve_vehiculo = @ carro 
 和 fecha_gps> = DATEADD（HOUR，+ 7， @ f1 ） 
 和 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 ），
  @ longitudinal_inicio   as   decimal （ 18 ， 15 ），
  @ longitudinal_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，
纵向，
 velocidad，
 direccion 
  FROM  #temp 
  ORDER   BY  cve_punto_gps 
  OPEN  VehicleCursor  FETCH  NEXT  FROM  VehicleCursor  INTO   @ cve_inicio ， @fecha_inicio ， @ latitud_inicio ， @ longitudinal_inicio ， @ velocidad_inicio ， @ direccion_inicio  
  FETCH  NEXT  FROM  VehicleCursor  INTO   @ cve_final ， @ fecha_final ， @ latitud_final ， @ longitudinal_final ， @ velocidad_final ， @ direccion_final  
 
 声明  @ tempb   table （cve  int ，fecha  datetime ，posicion  nvarchar （ 60 ），velocidad  int ，direccion  nvarchar （ 150 ））
  WHILE   @@ FETCH_STATUS  =  0   / *  或@velocidad_final！= 0 * /   BEGIN  
 声明  @ lat1   as   decimal （ 18 ， 15 ）
 声明  @ lat2   as   decimal （ 18 ， 15 ）
 声明  @ lon1   as   decimal （ 18 ， 15 ）
 声明  @ lon2   as   decimal  （ 18 ， 15 ）
  select   @ lat1  =  @ latitud_inicio ， @ lat2  =  @ latitud_final ， @ lon1  =  @ longitudinal_inicio ， @ lon2  =  @ longitudinal_final  
  IF （ sELECT  geography :: Point（ @ lat1 ， @ lon1 ， 4326 ）。STDistance（geography :: Point（ @ lat2 ， @ lon2 ， 4326 ）））>  80  
  BEGIN  
  IF  DATEDIFF（MI， @ fecha_inicio ， @ fecha_final ）> = 1 
 < span class =code-keyword> BEGIN  
  IF   @ velocidad_final  = 0 或 @ velocidad_inicio = 0 
  BEGIN  
  declare   @ posicion   nvarchar （ 60 ）=（cast（ @ latitud_inicio   as   nvarchar （ 30 ））+ '  ' + cast（ @ longitudinal_inicio   a s   nvarchar （ 30 ）））
 
 插入 进入  @ tempb  值（ @ 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  @ longitudinal_inicio = @ longitudinal_final 
  SET  @ velocidad_inicio = @ velocidad_final 
  SET  @ direccion_inicio = @ direccion_final 
  FETCH  NEXT  FROM  VehicleCursor  INTO   @ cve_final ， @ fecha_final ， @ latitud_final ， @ longitudinal_final ， @ velocidad_final ， @ direccion_final  
 结束 
 关闭 VehicleCursor 
  DEALLOCATE  VehicleCursor 
 选择 DATEADD（HOUR，-7，fecha） as  '  Llegada'，direccion  as  '  Direccion'，posicion  as  '  Posicion'  / *  到#tempc * /  来自  @ tempb  
  / *   select * from #tempc * /  
 结束

我试过这个但是没有给出准确的结果。

我想要替代解决方案。

我使用的是Sql server 2008

解决方案

相当棘手的一个 - 我倾向于做类似的事情...

1.构建一个光标来选择所有记录（也许在日期钳位之间），通过车辆，速度为0

2.通过寻找连续性休息的步行，将发现的跨度的开始和结束放入临时表 - startId，stopId，vehicleId

你知道有一个临时表，每行确定车辆何时停止，你可以在闲暇时查询

额外积分
你可以把它变成一张真正的桌子并且有定期更新它的cron工作

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.

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

解决方案

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

这篇关于我想写存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文

我想写存储过程 [英] I want to write Stored Procedure For

问题描述

相关文章

其他开发语言最新文章

热门教程

热门工具

登录关闭

我想写存储过程 [英] I want to write Stored Procedure For

问题描述

相关文章

其他开发语言最新文章

热门教程

热门工具

登录 关闭

登录关闭