Post reply

How to check in SQL Server car in out, Circle GeoFence

  • June 29, 2016 at 6:06 am

    #319599

    i have query that reply with this that latitude and longitude are in Rectangle or not.

    My question is how to get result of circle?

    i have "lat":"25.38227896335241","lon":"68.3395897859009"},"radius":"141.4213562373095" in database

    DECLARE @g geography,

    @pIn geography,

    @pOut geography

    --my question is how to check full table where car_id = 12865 in which zone...

    DECLARE @minY varchar(10);

    DECLARE @maxY varchar(10);

    DECLARE @minX varchar(10);

    DECLARE @maxx varchar(10);

    DECLARE @carlat varchar(10);

    DECLARE @carlong varchar(10);

    DECLARE @CarIdx int;

    select

    @minY = g.minlatitude , --N'29.7071393481341'

    @maxY = g.minlongitude , --N'64.808349609375'

    @minX = g.maxlatitude , --N'28.2463279710488'

    @maxx = g.maxlongitude --N'63.292236328125'

    from tblgeofencing as g where ShapeType = 'rectangle'

    SET @g = geography::STPolyFromText('POLYGON((' + @minX + ' ' + @minY + ', ' +

    @maxx + ' ' + @minY + ', ' +

    @maxx + ' ' + @maxY + ', ' +

    @minX + ' ' + @maxY + ', ' +

    @minX + ' ' + @minY + '))', 4326);

    select TOP 1 @CarIdx= idx, @carlat = f.lat, @carlong = f.long from checkgeofence as f order by idx desc

    SET @pIn = geography::STPointFromText('POINT(' + @carlat +' ' + @carlong +' )',4326)

    SET @pOut = geography::STPointFromText('POINT( 28.709860843942856 63.643798828125 )',4326)

    SELECT Poly = @g,

    pIn = @pIn,

    pOut = @pOut

    SELECT DistanceInMetersIn = @g.STDistance( @pIn ),

    DistanceInMetersOut = @g.STDistance( @pOut ),

    STIntersectsIn = @g.STIntersects( @pIn ),

    STIntersectsOut = @g.STIntersects( @pOut )

    SELECT STIntersectionIn = @g.STIntersection( @pIn ).ToString(),

    STIntersectionOut = @g.STIntersection( @pOut ).ToString()

    if( @g.STIntersects( @pIn ) >= 1)

    update checkgeofence

    set IsGeofence = 1

    where idx = @CarIdx

  • June 29, 2016 at 7:33 am

    #1887620

    Create a point with your latitude and longitude, use STBuffer with the radius value.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply