存储过程，用于获取边界框内的所有点，并考虑负经度

Question

我正在开发一个项目，其中我的数据库中存储有纬度和经度坐标的位置。我使用 Google 地图将这些地点绘制为地图上的标记。我不想在地图上绘制任何“不可见”标记（当前视口/边界框之外的标记）。因此，我遵循Google 关于视口标记管理的建议 。

我有一个可行的解决方案，只要地图的视口发生变化，我就使用 AJAX 来查询 ASP.NET Web 服务。此 Web 服务调用我的 MSSQL 数据库中的存储过程来获取具有当前视口/边界框内坐标的所有位置。存储过程如下所示：

ALTER PROCEDURE dbo.GetPlacesInsideBoundingBox

    (
    @swLat VarChar(11), /* south-west point latitude */
    @swLng VarChar(11), /* south-west point longitude */
    @neLat VarChar(11), /* north-east point latitude */
    @neLng VarChar(11) /* north-east point longitude */
    )

AS
    /* SET NOCOUNT ON */

    SELECT * FROM dbo.Place WHERE 
    place_lat >= CONVERT(Decimal(11,8), @swLat) 
    AND place_lat <= CONVERT(Decimal(11,8), @neLat) 
    AND place_lng >= CONVERT(Decimal(11,8), @swLng)  
    AND place_lng <= CONVERT(Decimal(11,8), @neLng)
    RETURN

发送到存储过程的参数只是视口/边界框的西南角和东北角的纬度和经度。然后，我将这些点与数据库中存储的经度和纬度值进行比较，以查看哪些位置位于当前视口/边界框内。

这很好用！但我读到，如果经度为负值（本初子午线以西），则可能会遇到问题，简单的解决方案是在经度为负值时将 360 添加到经度上。

我有两个问题：

1. 如何更改我的存储过程（上面）以考虑负面影响 经度？

2. 我还应该考虑对此进行任何其他修改吗 存储过程以使其万无一失？

如果您想知道从 VarChar 到 Decimal 的转换，我发现在客户端 (javascript) 使用简单字符串，然后在需要进行计算时在我的存储过程中将它们转换为十进制数字要容易得多。

提前致谢！

Answer 1

1）经度通常应该在-180到180之间，所以只需检查谷歌的坐标是否满足这个条件。可能是的。如果不是，只需通过加或减 360 将它们规范化到此区间内。在调用存储过程之前对它们进行规范化。

2) 通常@swLng <= @neLng，但您还必须处理@swLng >的情况； @neLng。因此，在您的存储过程中，您的经度条件将如下所示：

AND 
(
    (CONVERT(Decimal(11,8), @swLng) <= CONVERT(Decimal(11,8), @neLng)
        AND place_lng >= CONVERT(Decimal(11,8), @swLng) 
        AND place_lng <= CONVERT(Decimal(11,8), @neLng)
    ) 
    OR 
    (CONVERT(Decimal(11,8), @swLng) > CONVERT(Decimal(11,8), @neLng)
        AND (place_lng >= CONVERT(Decimal(11,8), @swLng) 
             OR place_lng <= CONVERT(Decimal(11,8), @neLng))
    )
)

PS：请注意，您可能应该使用像 PostGIS 这样的 GIS，这样才能真正高效:) 我本来打算解决这个问题，但我仍然推迟了“直到我拥有 GIS”。你启发了我，也许我也会寻求这个解决方案。

PS：我不确定您的数据库引擎如何优化，但我想如果您将纬度和经度定义为索引，那么为了提高效率，它可能会有所帮助 - 以便它可以更快地解决问题。但我一点也不确定。

Answer 2

事实上我找到了更好的解决方案。 SQL Server 具有用于处理空间数据的内置数据类型。因此，我向表中添加了一个新值，并将其命名为 place_geo，数据类型为 geography。然后我用地理点填充它（根据数据库中已有的纬度和经度创建）。当我将点作为地理点存储在数据库中时，存储过程的语法变得更加简单，而且我还认为它的执行速度会快得多。

这是我的存储过程：

ALTER PROCEDURE dbo.GetPlaceClosestToMe
    (
    @my_lat Decimal(11,8),
    @my_lng Decimal(11,8)
    )
AS
    DECLARE @my_point geography;
    SET @my_point = geography::Point(@my_lat, @my_lng , 4326); /* SRID 4326 */
    SET NOCOUNT ON

    SELECT TOP 1 
        place_id, 
        place_name,
        @my_point.STDistance(place_geo)/1000 [Distance in km]
FROM Places
ORDER BY @my_point.STDistance(place_geo)

那么它有什么作用呢？
首先，我获取经度和纬度作为十进制格式的输入参数。我声明了一个数据类型为“geography”的新变量 (@my_point)。然后，我将一个新的地理点分配给我刚刚使用经度和纬度输入参数创建的变量。

然后我询问数据库表 (place_geo) 中最接近 @my_point 的点。 （STDistance 返回两种地理类型之间的最短线。）除以 1000 即可得到以公里表示的距离。

如果你希望它返回多个结果，只需更改选择：SELECT TOP 5 或 SELECT TOP 10。

目前我没有足够的测试数据来测试这是否比使用旧方法更快，但也许其他人有做了一些测试吗？我猜想使用地理数据类型比旧方法要快得多。

根据我有限的测试数据，旧存储过程和这个新存储过程返回相同的结果。

希望您发现此后续内容有用！