这是我的设置:
我有一个如下的表:
Id, Lat, Long, GeoPoint, GeomPoint
GeoPoint
(地理)和 GeomPoint
(几何)都设置为 SRID 4326。
我有以下查询:
DECLARE @radiiCollection TABLE
( [ID] INT IDENTITY(1, 1) PRIMARY KEY,
[Radius] GEOMETRY,
[RefPoint] GEOMETRY,
[RefAddr] VARCHAR(255),
[MinLat] VARCHAR(255),
[MaxLat] VARCHAR(255),
[MinLng] VARCHAR(255),
[MaxLng] VARCHAR(255))
DECLARE @point GEOMETRY = geometry::Point(-111.84493459999999, 33.3902569, 4326)
DECLARE @gpoint GEOGRAPHY = geography::Point(33.3902569, -111.84493459999999, 4326);
INSERT INTO @radiicollection
(radius,
refpoint,
refaddr,
maxlat,
maxlng,
minlat,
minlng)
VALUES ( @point.MakeValid().STBuffer(16093.40),
@point,
'10 miles of 85210',
33.51734689767781,
-111.6923852740045,
33.26298081643247,
-111.99703818130439 )
SELECT
GeomPoint,
GeoPoint
INTO #temp
FROM (
SELECT row_number() OVER (
PARTITION BY [ds].[ADDR],
[ds].[APT],
[ds].[ZIP] ORDER BY [ds].[IND_ID] ASC
) recid1, rGeop1.geompoint, rgeop1.GeoPoint
FROM [r].[main] ds
JOIN [r].[GeoPoint] rGeoP1
ON rGeoP1.[UID] = ds.[UID]
JOIN @radiiCollection rr
ON GeomPoint.STWithin(rr.radius) = 1
WHERE 1 = 1
AND (
(
(
(
try_cast(latitude AS DECIMAL(9, 1)) BETWEEN CONVERT(DECIMAL(9, 1), 33.26298081643247)
AND CONVERT(DECIMAL(9, 1), 33.51734689767781)
AND try_cast(longitude AS DECIMAL(9, 1)) BETWEEN CONVERT(DECIMAL(9, 1), - 111.99703818130439)
AND CONVERT(DECIMAL(9, 1), - 111.6923852740045)
)
)
)
)
) f
WHERE recid1 = 1
因此,我同时拉取GeoPoint和GeomPoint的STDistance,如下所示:
select top 10
try_cast(GeoPoint.STDistance(@gpoint) as float) DistanceGeo,
try_cast(GeoMPoint.STDistance(@point.MakeValid()) as float) DistanceGeom
from #temp
但是我得到的有点奇怪:
DistanceGeo DistanceGeom
-----------------------------------------
10495.1674191715 0.111544285781801
10249.4175883919 0.100540150724826
12307.1907929483 0.1262738924781
11804.655587608 0.116453906202276
10249.4175883919 0.100540150724826
9607.03640753812 0.101614826463312
11130.8413059431 0.100596791997409
10249.4175883919 0.100540150724826
6973.69243171186 0.0644901191669685
9605.88647121359 0.0967178499759486
据我所知,SRID决定了空间列的测量单位,但显然,既然它们都是SRID 4326,那就不是这种情况了吗? 如果有帮助会很感激。我认为我在这里看到的是度数而不是米。在SQL中有没有一种简单的方法将度数转换为米?或者改变STDistance输出的单位的方法?