GPS数据库范例

1、附录四GPS数据库与范例（1）车辆基本信息表 CarBaseInfo说明：这个表用于存放所有车辆的基本信息，通过字段GroupID与车队信息表GroupCar相联系。字段名称字段类型是否主键/关联字段说明CarIDint是自增CarNumberVarchar(15)车牌号TarIDint外，TarBaseInfoUnitIDintUnit.UnitID单位IDGroupIDintCarGroup.GroupID车队IDUserIDVarchar（16）User.UserID用户账号：个人用户时有效SpeedLimitFloat车速限制FactoryTypeVarchar（20）厂牌TypeVa

2、rcar（20）车型LicenceDateDateTime机动车行驶证日期RegisterDateDateTime系统登记日期CarGPSVarchar(15)GPS_CARID该车辆GPS信息表的名字GPSBaskUpVarchar(15)BAKGPS_CarID该车辆历史GPS信息表BackUpDateDataTimeGPS信息的备份截至时间（2）车队所属单位信息表 UnitInfo说明：这个表用来存放车队的详细信息，如：车队的名称，编号，车队负责人姓名，性别，联系电话，车队地址等。字段名称字段类型是否主键/关联字段说明UnitIDint是/ CarGroup r. UnitID单位IDU

3、nitNameVarchar（50）单位名MilTableNameVarchar(15)MilInfo车队里程表EventTableNameVarchar(15)EventTableName事件表ResultTableNameVarchar(15)ResultTableName结果表PrincipalNameVarchar（50）单位负责人Gender Char（2）性别EmailVarchar（50）用户EmailBirthDayDateTime出生日期ContactTelVarchar（15）联系电话AddressVarchar（50）单位地址QqVarchar（11）QQMSNVarch

4、ar(50)MSNEducationVarchar(10)教育程度BusTypeIntBusInfo. BusID行业类型MonitorPageVarchar(50)监控界面ManagePageVarchar(50)管理界面（3）车辆GPS信息表 CarGpsInfo说明：这个表主要用于接收的GPS信息，从而用于车辆现阶段的定位或历史记录的定位。字段名称字段类型是否主键/关联字段说明IDInt是自增标识号CarNumberVarchar(15)车辆号Longitudefloat经度Latitudefloat纬度Speedfloat速度Directionfloat方向ReceiveTimeDat

5、eTime接收GPS信息时间Interestbit是否为兴趣点InterestPointbit兴趣点名称（4）单位里程表 MilTable 说明：用于存放一个单位的车辆的里程信息，包括泊车信息、上下高速字段名称字段类型是否主键/关联字段说明IDInt是自增标识号CarNumberVarchar(15)车牌MilStartTimeDateTime开始时间MilEndTimeDateTime结束时间MilInt里程数（5）事件信息表 Event 说明：用于存放报警等事件信息，事件类型包括：0-报警、1-超速、2-离开泊车区域、3-开进泊车区域、4-上高速、5-下高速、6-超围栏事件、7-进围栏事件

6、、8-泊车事件字段名称字段类型是否主键/关联字段说明IDInt是自增标识号CarNumberVarchar(15)车牌EventStartTimeDateTime接收事件时间handleBit默认值0是否处理（0未处理，1处理）EventTypeInt事件类型EventNameVarchar（50）事件名称（6）结果分析表 Result 说明：用于存放一些分析结果信息，包括泊车信息、上下高速字段名称字段类型是否主键/关联字段说明IDInt是自增标识号CarNumberVarchar(15)车牌EventStartTimeDateTime事件的开始时间EventEndTimeDateTime事件

7、的结束时间EventTypeInt事件类型EventNameVarchar（50）事件名称存储过程： GPS登记、里程表的登记触发器：创建车辆（生成GPS表）、删除车辆（删除GPS表）、创建单位（生成MILINFO表、事件信息表 Event和结果分析表 Result）、删除单位（删除MILINFO表、事件信息表 Event和结果分析表 Result） - =- Author:<Author:hxw>- Create date: <Create Date,20090528>- Description:<Description,创建一车辆时候创建一个GPS表格存放该车

8、辆的GPS信息>- =ALTER TRIGGER Insert_Car ON dbo.CarBaseInfo AFTER INSERTAS declare CarID int declare GpsTableName varchar(20) declare BakTableName varchar(20)declare SqlStr varchar(300)BEGIN select GpsTableName=CarGPS,BakTableName=GPSBackUP from inserted IF(EXISTS(SELECT * FROM sysobjects WHERE NAME=

9、GpsTableName) begin rollback tran print('已经存在该辆车的GPS表') end set 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,'

10、+ '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) begin rollback tran print('添加车辆失败') return endEND

11、- =- Author:<Author,Name>- Create date: <Create Date,>- Description:<Description,>- =ALTER PROCEDURE dbo.AddMilRecorddip int,StartTime datetime,EndTime datetime,Mil floatAS declare CarNumber varchar(15) declare MilTableName varchar(15) declare CarGPSTableName varchar(15) declare Dr

12、iveCode varchar(20)declare SqlStr nvarchar(300) declare RecordKey intBEGIN BEGIN TRY Begin Tran SaveMil select RecordKey=UnitInfo.UnitID,CarGPSTableName=CarBaseInfo.CarGPS,CarNumber=CarBaseInfo.CarNumber,MilTableName=UnitInfo.MilTableName from CarBaseInfo,UnitInfo where CarBaseInfo.TarID=dip and Car

13、BaseInfo.UnitID=UnitInfo.UnitID if(RecordKey is not null) begin if exists(SELECT * FROM sysobjects WHERE NAME= MilTableName) begin set RecordKey=NULL set SqlStr='SELECT RdKey=ID FROM '+MilTableName+ ' WHERE Carnumber=CarNum and DriveEndTime=Start' exec sp_executesql SqlStr, N'RdK

14、ey int output, CarNum varchar(15),Start Datetime',RecordKey,CarNumber,StartTime if(RecordKey is null)-找不到临接记录,则补里程 exec FillMissing CarNumber,DriveCode,StartTime,MilTableName,CarGPSTableName set SqlStr='Insert into '+MilTableName+'(Carnumber,DriveCode,DriveBeginTime,DriveEndTime,Mil)

15、 Values(CarNum,Drive,Start,End,MMil)' exec sp_executesql SqlStr,N'CarNum varchar(15),Drive varchar(15),Start Datetime,End Datetime,MMil float' ,CarNumber , DriveCode ,StartTime ,EndTime,Mil end end Commit Tran SaveMil END TRY BEGIN CATCH Rollback Tran SaveMil END CATCHEND- =- Author:<

16、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 nv

17、archar(15)declare CarNumber nvarchar(15)declare LastReceiveTime datetimedeclare SpeedLimit floatdeclare LastSpeed float declare LastDirection float declare LastLatitude floatdeclare Lastlongitude floatdeclare Interest intdeclare EventName varchar(50)declare SqlStr nvarchar(300)declare Fencename varc

18、har(20)declare MaxStartTime datetimedeclare MaxEndTime datetimedeclare EventType intBEGINBEGIN TRY Begin Tran SaveGps select GpsTableName=CarBaseInfo.CarGps,CarNumber=CarBaseInfo.CarNumber,SpeedLimit=SpeedLimit,EventTableName=UnitInfo.EventTableName,ResultTableName=ResultTableName from CarBaseInfo,U

19、nitInfo where CarBaseInfo.TarID=dip and CarBaseInfo.UnitID=UnitInfo.UnitID set SqlStr=N'SELECT LastRecTime=MAX(ReceiveTime) FROM '+GpsTableName+N' WHERE ReceiveTime<'''+convert(varchar(40),RecTime,121)+''''+' AND Speed>10.0 AND CarNumber='+'&

20、#39;''+CarNumber+'''' exec sp_executesql SqlStr, N'LastRecTime datetime output',LastReceiveTime output set SqlStr=N'Select LSpeed=Speed,LDirection=Direction,LLatitude=Latitude,Llongitude=longitude FROM '+GpsTableName+N' WHERE ReceiveTime='+''&#

21、39;'+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 output if(Speed>=10

22、0)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=Direct

23、ion,LLatitude=Latitude,Llongitude=longitude FROM '+GpsTableName+ ' order by ReceiveTime ' exec sp_executesql SqlStr, N'LastRecTime datetime output,LSpeed float output, LDirection float output,LLatitude float output,Llongitude float output',LastReceiveTime output,LastSpeed output , LastDirect