如何检查lat long in circle or not SQL server [英] How to check lat long in circle or not SQL server

问题描述

i have query that reply with this that latitude and longitude are in Rectangle or not.please check below code and image file.

My question is how to get result of circle? mylat long in circle or out i have "lat":"25.38227896335241","lon":"68.3395897859009"},"radius":"141.4213562373095" in database

example : "i have latitude longitude of car i am checking latitude ,longitude are in circle or not if in circle result 'Car in Circle' if not 'Car out of Circle area' this logic perfect in my rectangle but i want circle result also"

我尝试了什么：



DECLARE @g地理位置，

@pIn地理位置，

@pOut地理





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'

从tblgeofencing为g其中ShapeType ='rectangle'

SET @g = geography :: STPolyFromText（'POLYGON（''+ @minX +''+ @minY +'，'+

@maxX +''+ @minY +'，'+

@maxX +''+ @maxY +'，'+

@minX +''+ @maxY +'，'+

@minX +''+ @minY +'））'，4326）;



选择TOP 1 @ CarIdx = idx，@ carlat = f.lat ，@carlong = f.long从checkgeofence作为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）

更新checkgeofence

设置IsGeofence = 1

其中idx = @CarIdx

What I have tried:

DECLARE @g geography,
@pIn geography,
@pOut geography


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

推荐答案

您需要计算圆心和汽车之间的距离。使用大圆距离

大圆距离 - 维基百科，免费百科全书 [ ^ ]

You need to calculate the distance between the center of your circle and the car. Using the Great-circle distance
Great-circle distance - Wikipedia, the free encyclopedia[^]

这篇关于如何检查lat long in circle or not SQL server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！