附录四GPS数据库与范例（1）车辆基本信息表 CarBaseInfo说明：这个表用于存放所有车辆的基本信息，通过字段GroupID与车队信息（2）车队所属单位信息表 UnitInfo说明：这个表用来存放车队的详细信息，如：车队的名称，编号，车队负责人姓（3）车辆GPS信息表 CarGpsInfo说明：这个表主要用于接收的GPS信息，从而用于车辆现阶段的定位或历史记录（4）单位里程表 MilTable说明：用于存放一个单位的车辆的里程信息，包括泊车信息、上下高速（5）事件信息表 Event说明：用于存放报警等事件信息，事件类型包括：0-报警、1-超速、2-离开泊车区域、3-开进泊车区域、4-上高速、5-下高速、6-超围栏事件、7-进围栏事件、8-泊车事件（6）结果分析表 Result说明：用于存放一些分析结果信息，包括泊车信息、上下高速存储过程： GPS登记、里程表的登记触发器：创建车辆（生成GPS表）、删除车辆（删除GPS表）、创建单位（生成MILINFO表、事件信息表 Event和结果分析表 Result）、删除单位（删除MILINFO 表、事件信息表 Event和结果分析表 Result）-- =============================================-- Author: <Author:hxw>-- Create date: <Create Date,20090528>-- Description: <Description,创建一车辆时候创建一个GPS表格存放该车辆的GPS 信息>-- =============================================ALTER TRIGGER [Insert_Car]ON [dbo].[CarBaseInfo]AFTER INSERTASdeclare @CarID intdeclare @GpsTableName varchar(20)declare @BakTableName varchar(20)declare @SqlStr varchar(300)BEGINselect @GpsTableName=CarGPS,@BakTableName=GPSBackUP from inserted IF(EXISTS(SELECT*FROM sysobjects WHERE NAME= @GpsTableName)) beginrollback tranprint('已经存在该辆车的GPS表')endset @SqlStr='Create table '+@GpsTableName+'(ID int IDENTITY (1, 1) NOT NULL Primary key,'+'CarID int NOT NULL ,'+'CarNumber varchar(15) NOT NULL,Longitude float NULL,'+'Latitude float NULL,'+'Speed float NULL,'+'Direction float NULL,'+'ReceiveTime datetime NULL ,'+'Interest int NULL,'+'InterestPoint varchar(40) NULL,'+'Area varchar(20) NULL)'exec(@SqlStr)if(@@error<> 0)beginrollback tranprint('添加车辆失败')returnendEND-- =============================================-- Author: <Author,,Name>-- Create date: <Create Date,,>-- Description: <Description,,>-- =============================================ALTER PROCEDURE [dbo].[AddMilRecord]@dip int,@StartTime datetime,@EndTime datetime,@Mil floatASdeclare @CarNumber varchar(15)declare @MilTableName varchar(15)declare @CarGPSTableName varchar(15)declare @DriveCode varchar(20)declare @SqlStr nvarchar(300)declare @RecordKey intBEGINBEGIN TRYBegin Tran SaveMilselect@RecordKey=UnitInfo.UnitID,@CarGPSTableName=CarBaseInfo.CarGPS,@CarNumber=CarBaseInfo.CarNumber,@MilTableName=TableName from CarBaseInfo,UnitInfowhere CarBaseInfo.TarID=@dip andCarBaseInfo.UnitID=UnitInfo.UnitIDif(@RecordKey is not null)beginif exists(SELECT*FROM sysobjects WHERE NAME=@MilTableName)beginset @RecordKey=NULLset @SqlStr='SELECT @RdKey=ID FROM '+@MilTableName+' WHERE Carnumber=@CarNum and DriveEndTime=@Start'exec sp_executesql @SqlStr, N'@RdKey int output, @CarNum varchar(15),@Start Datetime',@RecordKey,@CarNumber,@StartTimeif(@RecordKey is null)--找不到临接记录,则补里程exec FillMissing@CarNumber,@DriveCode,@StartTime,@MilTableName,@CarGPSTableName set @SqlStr='Insert into'+@MilTableName+'(Carnumber,DriveCode,DriveBeginTime,DriveEndTime,Mil) Values(@CarNum,@Drive,@Start,@End,@MMil)'exec sp_executesql @SqlStr,N'@CarNum varchar(15),@Drivevarchar(15),@Start Datetime,@End Datetime,@MMil float',@CarNumber ,@DriveCode ,@StartTime ,@EndTime,@MilendendCommit Tran SaveMilEND TRYBEGIN CATCHRollback Tran SaveMilEND CATCHEND-- =============================================-- Author: <Author,,Name>-- Create date: <Create Date,,>-- Description: <Description,,>-- =============================================CREATE PROCEDURE [dbo].[AddGpsRecord]@dip int,@RecTime datetime,@Latitude float,@longitude float,@Speed float,@Direction floatASdeclare @GpsTableName nvarchar(15)declare @EventTableName nvarchar(15)declare @ResultTableName nvarchar(15)declare @CarNumber nvarchar(15)declare @LastReceiveTime datetimedeclare @SpeedLimit floatdeclare @LastSpeed floatdeclare @LastDirection floatdeclare @LastLatitude floatdeclare @Lastlongitude floatdeclare @Interest intdeclare @EventName varchar(50)declare @SqlStr nvarchar(300)declare @Fencename varchar(20)declare @MaxStartTime datetimedeclare @MaxEndTime datetimedeclare @EventType intBEGINBEGIN TRYBegin Tran SaveGpsselect@GpsTableName=CarBaseInfo.CarGps,@CarNumber=CarBaseInfo.CarNumber,@Sp eedLimit=SpeedLimit,@EventTableName=UnitInfo.EventTableName,@ResultTa bleName=ResultTableNamefrom CarBaseInfo,UnitInfo where CarBaseInfo.TarID=@dip and CarBaseInfo.UnitID=UnitInfo.UnitIDset @SqlStr=N'SELECT @LastRecTime=MAX(ReceiveTime) FROM'+@GpsTableName+N' WHEREReceiveTime<'''+convert(varchar(40),@RecTime,121)++''''+' AND Speed>10.0 AND CarNumber='+''''+@CarNumber+''''exec sp_executesql @SqlStr, N'@LastRecTime datetimeoutput',@LastReceiveTime outputset @SqlStr=N'Select@LSpeed=Speed,@LDirection=Direction,@LLatitude=Latitude,@Llongitude=l ongitude FROM '+@GpsTableName+N' WHEREReceiveTime='+''''+convert(varchar(40),@LastReceiveTime,121)+''''exec sp_executesql @SqlStr, N'@LSpeed float output, @LDirection float output,@LLatitude float output,@Llongitude float output',@LastSpeed output, @LastDirection output, @LastLatitude output, @Lastlongitude outputif((@Speed>=100)OR(@LastSpeed>=10 and @Speed<=5)or(datediff(ss,@LastReceiveTime,@RecTime)>120 and @Speed>=10)OR(@Speed>=10.0 AND ABS(@Direction-@LastDirection)>=20))set @Interest=1--兴趣点,超速、停车、启动、方向变化大else set @Interest=0/*set @SqlStr=N'Select@LastRecTime=ReceiveTime,@LSpeed=Speed,@LDirection=Direction,@LLatitu de=Latitude,@Llongitude=longitude FROM '+@GpsTableName+ ' order by ReceiveTime 'exec sp_executesql @SqlStr, N'@LastRecTime datetime output,@LSpeed float output, @LDirection float output,@LLatitude floatoutput,@Llongitude float output',@LastReceiveTime output,@LastSpeed output , @LastDirection output, @LastLatitude output , @Lastlongitude output/*停车第一点或者少于间隔的停车点*/if((@Speed<=5 and @LastSpeed>=5) OR (@Speed<=5 and @LastSpeed<=5 and datediff(ss,@LastReceiveTime,@RecTime)<=20))set @Interest=1--兴趣点*/set @SqlStr='Insert into'+@GpsTableName+'(CarID,Carnumber,longitude,Latitude,Speed,Direction, ReceiveTime,Interest)Values(@Cardip,@CarNum,@Plongitude,@PLatitude,@PSpeed,@PDirection,@Re cTime,@PInterest)'exec sp_executesql @SqlStr,N'@Cardip int,@CarNumnvarchar(15),@Plongitude float,@PLatitude float,@PSpeedfloat,@PDirection float,@RecTime Datetime,@PInterest int',@dip ,@CarNumber ,@longitude ,@Latitude,@Speed,@Direction,@RecTime,@Interest/*希望能添加代码产生进出围栏事件，并登记到事件表中*/Commit Tran SaveGpsEND TRYBEGIN CATCHRollback Tran SaveGps--回滚END CATCHEND。
