how to check longitude latitude database sql server

Question

i have longitude and latitude in geofence table how to check longitude and latitude in database level. if car longitude latitude are in Zone, update geofance column zones = 'car in zone area'

CREATE TRIGGER Tr_CheckGeoFance
ON CheckGeoFance
AFTER INSERT AS
BEGIN
Update tblgeofencing
Set CarZone ='Car In Zone Area'
END
GO
ShapesString ={"shapes":[{"type":"rectangle","color":"#1E90FF","bounds":{"northEast":{"lat":"32.379961464357315","lon":"70.99365234375"},"southWest":{"lat":"31.840232667909362","lon":"70.2685546875"}}}]}

Stop storing Lat/Lon in JSON and store it in as a Geography data type instead, it will make this an order of magnitude easier. Of course that does require SQL Server 2008 R2. — RBarryYoung, May 31 '16 at 17:47
See here: https://msdn.microsoft.com/en-us/library/cc280766(v=sql.105).aspx — RBarryYoung, May 31 '16 at 17:56
The stated JSON seems to represent an area, not a position. Is that the boundary of your geofence? — Ben Thul, May 31 '16 at 19:05

score 0 · Answer 1 · edited May 23 '17 at 11:52

0

First You have to decode the ShapesString which is currently in json format and then find out the latitude and longitude out of the decoded json. Then check the lat-long lie in your defined range to update the column.

Refer this to decode the json:- Parse JSON in TSQL

edited May 23 '17 at 11:52

Community

1
1

answered May 31 '16 at 17:40

vinod meena

67
6

how to check longitude latitude database sql server

1 Answers1