stored procedure to get all points inside a boundi

问题:

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!

回答1:

1) Longitude normaly should be between -180 to 180 so just check if the coordinates from google fulfil this condition. Probably yes. If not, just normalize them to be within this interval by adding or subtracting 360. Normalize them before you call the stored procedure.

2) Normally @swLng <= @neLng, but you must also handle the case @swLng > @neLng. So inside your stored procedure, your longitude condition would look like:

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))
    )
)

P.S.: Note that you should probably use GIS like PostGIS for this to be really efficient :) I was about to solve this problem but I still postponed it "until I have a GIS". You inspired me and maybe I'll go for this solution too.

P.S.: I'm not sure how your database engine optimizes but I guess for increased efficiency it might help if you define latitude and longitude as indexes - so that it can solve the condition faster. But I'm not sure at all.

回答2:

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!