sql-server - 将地理类型值与 STContains 函数一起使用

Question

好的，首先，我将 Lat/Long 位置存储在我的 SQL Server 表中的地理类型列中。

我尝试使用方法在特定的边界框中获取商店，STContains我发现使用地理点的唯一方法是将 Lat/Long 与STR转换连接：

DECLARE @boundingBox geography;  
SET @boundingBox = geography::Parse('POLYGON((...))');

...
SELECT Store.Id, Store.Name, Store.Location.Lat, Store.Location.Long, 
  @boundingBox.STContains(
    geography::Parse('POINT(' + STR(Store.Location.Lat, 20, 12) + ' ' 
                              + STR(Store.Location.Long, 20, 12) + ')'))

它有效，但它很丑陋，并且询问是否有更清洁的方式来写这个。

标签： sql-serversqlgeography

Answer 1

我设置了 2 个测试多边形geo和geo2. 一种使用Parse方法，另一种使用STPolyFromText方法。

declare @geo geography 
            = geography::STPolyFromText('POLYGON ((73.250684 34.198599, 73.250598 34.199324, 73.250343 34.200021, 73.249927 34.200663, 73.249369 34.201226, 73.248688 34.201688, 73.247912 34.202031, 73.247069 34.202243, 73.246193 34.202314, 73.245317 34.202243, 73.244474 34.202031, 73.243698 34.201688, 73.243017 34.201226, 73.242458 34.200663, 73.242043 34.200021, 73.241788 34.199324, 73.241701 34.198599, 73.241788 34.197874, 73.242043 34.197177, 73.242458 34.196535, 73.243017 34.195972, 73.243698 34.19551, 73.244474 34.195167, 73.245317 34.194956, 73.246193 34.194884, 73.247069 34.194956, 73.247912 34.195167, 73.248688 34.19551, 73.249369 34.195972, 73.249927 34.196535, 73.250343 34.197177, 73.250598 34.197874, 73.250684 34.198599, 73.250684 34.198599))', 4326)
declare @geo2 geography 
            = geography::Parse('POLYGON ((73.250684 34.198599, 73.250598 34.199324, 73.250343 34.200021, 73.249927 34.200663, 73.249369 34.201226, 73.248688 34.201688, 73.247912 34.202031, 73.247069 34.202243, 73.246193 34.202314, 73.245317 34.202243, 73.244474 34.202031, 73.243698 34.201688, 73.243017 34.201226, 73.242458 34.200663, 73.242043 34.200021, 73.241788 34.199324, 73.241701 34.198599, 73.241788 34.197874, 73.242043 34.197177, 73.242458 34.196535, 73.243017 34.195972, 73.243698 34.19551, 73.244474 34.195167, 73.245317 34.194956, 73.246193 34.194884, 73.247069 34.194956, 73.247912 34.195167, 73.248688 34.19551, 73.249369 34.195972, 73.249927 34.196535, 73.250343 34.197177, 73.250598 34.197874, 73.250684 34.198599, 73.250684 34.198599))')

declare @outsidePoint geography
            = geography::STPointFromText('POINT(-122.34900 47.65100)', 4326),
        @insidePoint geography
            = geography::STPointFromText('POINT(73.2438096 34.1989505)', 4326)
select 
    geo = @geo, 
    geoString = @geo.ToString(),
    IsValid = @geo.STIsValid(),
    doesContainOutsidePoint = @geo.STContains(@outsidePoint),
    doesIntersectOutsidePoint = @geo.STIntersects(@outsidePoint),
    doesContainInsidePoint = @geo.STContains(@insidePoint),
    doesIntersectInsidePoint = @geo.STIntersects(@insidePoint)

select 
    geo = @geo2, 
    geoString = @geo2.ToString(),
    IsValid = @geo2.STIsValid(),
    doesContainOutsidePoint = @geo2.STContains(@outsidePoint),
    doesIntersectOutsidePoint = @geo2.STIntersects(@outsidePoint),
    doesContainInsidePoint = @geo2.STContains(@insidePoint),
    doesIntersectInsidePoint = @geo2.STIntersects(@insidePoint)

它们似乎都对我有用 - 结果如下：

我可以使用geography 类型的STContains和方法从数据库中的 Polygon 表中查询。STIntersects

因此，如果为您.STContains返回0，则表示该点不在框内。也许您可以发布一个示例 Polygon 和 Point 它正在返回0但应该返回1这可能会有所帮助。