June 1, 2016 at 4:49 am
How to check in Sql server if longitude and latitude are in zone or not.
I have trigger that I perform this task. please help me.
CREATE TRIGGER Tr_CheckZoneAfterInsert
ON tblCheckGeoFance
AFTER INSERT AS
BEGIN
Update tblgeofencingData
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"}}}]}
June 3, 2016 at 5:52 pm
This example will check 2 points compared to the zone.
Check the spatial results tab:
You can see the cars on the map as tiny dots, car1 about the center(inside the zone) and car2 outside the zone at middle right.
/* Geography type polygons needs to be constructed counter clockwise
The last point must be the same as the first.*/
-- this is the zone you posted starting form northeast going counter
--clockwise through southwest and back to northeast to commplete the polygon.
DECLARE@zone GEOGRAPHY = GEOGRAPHY::STPolyFromText('POLYGON((70.99365234375 32.379961464357315,
70.2685546875 32.379961464357315,
70.2685546875 31.840232667909362,
70.99365234375 31.840232667909362,
70.99365234375 32.379961464357315))',4326);
DECLARE @car GEOGRAPHY = GEOGRAPHY::STGeomFromText('POINT(70.69365234375 32.179961464357315)',4326)
DECLARE @car2 GEOGRAPHY = GEOGRAPHY::STGeomFromText('POINT(71.05500000000 32.150961464357315)',4326)
SELECT
CASEWHEN @car.STIntersects(@zone) = 1
THEN 'Inside'
ELSE 'Outside'
END AS CarInZone
SELECT
CASEWHEN @car2.STIntersects(@zone) = 1
THEN 'Inside'
ELSE 'Outside'
END AS CarInZone
SELECT @car AS geoItem
UNION all
SELECT @car2
UNION ALL
SELECT @zone
You can use the result of the STIntersect function in a where clause.
So you could run an update table Set CarInZone = 0
where car.StIntersects(zone) = 0
hth,
Tom
April 6, 2020 at 6:10 am
This was removed by the editor as SPAM
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply