存储过程在考虑负经度的情况下获取边界框内的所有点 [英] stored procedure to get all points inside a bounding box taking into account a negative longitude

问题描述

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

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

  ALTER PROCEDURE dbo.GetPlacesInsideBoundingBox 
 
（
 @ swLat VarChar（11），/ *西南点纬度* / 
 @swLng VarChar（11），/ *西南点经度* / 
 @neLat VarChar（11），/ * north-东点经度* / 
 @neLng VarChar（11）/ *东北点经度* / 
）
 
 AS 
 / * SET NOCOUNT ON * / 
 
 SELECT * FROM dbo.Place WHERE 
 place_lat> = CONVERT（十进制（11,8），@swLat）
 AND place_lat <= CONVERT（十进制（11,8 ），@neLat）
 AND place_lng> = CONVERT（十进制（11,8），@swLng）
 AND place_lng< = CONVERT（十进制（11,8），@neLng）
返回
  

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

这很好！但是我读到，如果你对经度（Prime Prime子以西）有负值，那么你可能会遇到问题，如果是负值，简单的解决方案是将360加到经度上。

我有两个问题：

1. 程序（上面）考虑负
   经度？
   

2. 是否有任何其他修改我应该考虑对此
   存储过程进行如果你想知道从VarChar到Decimal的转换，我发现它更容易实现，它使得它万无一失。

在客户端使用简单的字符串（javascript），然后在需要执行计算时将它们转换为存储过程中的十进制数字。




预先感谢！ / p>


解决方案

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





这是我的存储过程：



  ALTER PROCEDURE dbo.GetPlaceClosestToMe 
（
 @my_lat十进制（11,8）， 
 @my_lng十进制（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 [距离（公里）] 
从地点
 ORDER BY @ my_point.STDistance（place_geo）
  



<那么它有什么作用？
首先我得到一个经度和纬度作为十进制格式的输入参数。我声明了一个新的变量（@my_point），其数据类型为geography。然后，我使用经度和纬度输入参数为刚刚创建的变量分配一个新的地理点。然后，我要求我的数据库表（place_geo）中的点最接近@my_point。 （STDistance返回两种地理类型之间的最短线）。除以1000将得到以公里为单位的距离。





如果您希望它返回多个结果，只需改变select：SELECT TOP 5或SELECT TOP 10。



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



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





希望你发现这个后续有用！




I'm working on a project where I have places with latitude and longitude coordinates stored in my database. I use Google Maps to plot these places as markers on a map. I don't want to plot any "invisible" markers (markers outside the current viewport/bounding box) to the map. Therefore I follow Googles advice regarding viewport marker management.

I have a working solution where i use AJAX to query an ASP.NET web service whenever the viewport of my map has changed. This web service calls a stored procedure in my MSSQL database to get all places that have coordinates that are inside the current viewport/bounding box. The stored procedure looks like this:

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

The parameters that are sent to the stored procedure are simply the latitude and longitude of the south-west and the north-east corners of the viewport/bounding box. Then I compare these points with the longitude and latitude values stored in my database to see which places are inside the current viewport/bounding box.

This works fine! But I read that you can run into problems if you have a negative value for the longitude (west of the Prime Meridian) and that the simple solution was to add 360 to the longitude if it was negative.

I have two questions:

1. How do I alter my stored procedure (above) to take into account negative longitudes?

2. Are there any other modifications I should consider making to this stored procedure to make it foolproof?

If you are wondering about the conversion from VarChar to Decimal, I found it much easier to work with simple strings on the client side (javascript) and then convert them to decimal numbers in my stored procedure when I needed to do the calculations.

Thanks in advance!

解决方案

Actually I found a better solution. SQL Server has built-in data types for working with spatial data. So I added a new value to my table and called it place_geo with the data type geography. Then I populated it with geography points (created from the latitudes and longitudes I already had in the database). When I had the points stored as geography points in my database, the stored procedure becomes much simpler in its syntax and I also think it will execute much faster.

Here is my stored procedure:

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)

So what does it do? First I get a longitude and a latitude as input parameters in decimal format. I declare a new variable (@my_point) that has the data type "geography". I then assign a new geography point to the variable I just created using the longitude and latitude input parameters.

Then I ask for the point in my database table (place_geo) that is closest to @my_point. (STDistance returns the shortest line between two geography types.) The division by 1000 is to get a distance expressed in kilometers.

If you want it to return several results, just alter the select: SELECT TOP 5 or SELECT TOP 10.

At the moment I don't have enough test data to test if this is faster than using the old method, but perhaps someone else have done some tests? I would guess that using the geography data type is much faster than the old method.

With my limited test data, the old stored procedure and this new stored procedure return the same results.

Hope you found this follow-up useful!

这篇关于存储过程在考虑负经度的情况下获取边界框内的所有点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！