хранимая процедура для получения всех точек внутри ограничивающей рамки с учетом отрицательной долготы

Я работаю над проектом, в котором у меня есть места с координатами широты и долготы, хранящиеся в моей базе данных. Я использую Google Maps, чтобы отмечать эти места в качестве маркеров на карте. Я не хочу наносить на карту какие-либо «невидимые» маркеры (маркеры за пределами текущего окна просмотра/ограничивающей рамки). Поэтому я следую рекомендациям Google относительно управления маркерами области просмотра. .

У меня есть рабочее решение, в котором я использую AJAX для запроса веб-службы ASP.NET всякий раз, когда изменяется область просмотра моей карты. Этот веб-сервис вызывает хранимую процедуру в моей базе данных 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), а затем преобразовывать их в десятичные числа в моей хранимой процедуре, когда мне нужно выполнить вычисления.

Заранее спасибо!


google-maps stored-procedures bounding-box latitude-longitude viewport
person tkahn    schedule 29.11.2011    source источник
comment
спасибо за этот вдохновляющий вопрос и полезную ссылку! Я, вероятно, использовать тот же подход!   -  person Tomas    schedule 29.11.2011

Ответы (2)


arrow_upward
1
arrow_downward

1) Обычно долгота должна быть в диапазоне от -180 до 180, поэтому просто проверьте, соответствуют ли координаты из Google этому условию. Возможно - да. Если нет, просто нормализуйте их, чтобы они находились в пределах этого интервала, добавив или вычтя 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))
    )
)

P.S.: Обратите внимание, что вам, вероятно, следует использовать ГИС, например PostGIS, чтобы это было действительно эффективно :) Я собирался решить эту проблему, но все же отложил ее «до тех пор, пока у меня не будет ГИС». Вы вдохновили меня, и, возможно, я тоже выберу это решение.

P.S.: Я не уверен, как оптимизируется ваш механизм базы данных, но я думаю, что для повышения эффективности может помочь, если вы определите широту и долготу как индексы, чтобы он мог решить условие быстрее. Но я совсем не уверен.

person Tomas    schedule 29.11.2011
comment
Спасибо за ответ! Я просмотрел тип географических данных в SQL Server, но мне нужно было что-то быстрое и грязное. Если вам интересно, посмотрите: msdn.microsoft.com/en-us/ библиотека/bb895266.aspx - person tkahn; 02.12.2011
comment
@tkahn, да, я думаю, что быстрое и грязное решение может быть эффективным, если у вас нет миллионов маркеров ... Как только вы это сделаете, дайте мне знать здесь и, возможно, опубликуйте ссылку, если сможете. Меня интересует это решение! - person Tomas; 02.12.2011

arrow_upward
1
arrow_downward

На самом деле я нашел лучшее решение. 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)

Так что же он делает? Сначала я получаю долготу и широту в качестве входных параметров в десятичном формате. Я объявляю новую переменную (@my_point) с типом данных "geography". Затем я назначаю новую географическую точку только что созданной переменной, используя входные параметры долготы и широты.

Затем я запрашиваю точку в моей таблице базы данных (place_geo), ближайшую к @my_point. (STDistance возвращает кратчайшую линию между двумя географическими типами.) Деление на 1000 позволяет получить расстояние, выраженное в километрах.

Если вы хотите, чтобы он возвращал несколько результатов, просто измените выбор: SELECT TOP 5 или SELECT TOP 10.

На данный момент у меня недостаточно тестовых данных, чтобы проверить, быстрее ли это, чем использовать старый метод, но, возможно, кто-то еще провел некоторые тесты? Я предполагаю, что использование типа данных geography намного быстрее, чем старый метод.

С моими ограниченными тестовыми данными старая хранимая процедура и эта новая хранимая процедура возвращают одинаковые результаты.

Надеюсь, вы нашли это продолжение полезным!

person tkahn    schedule 05.12.2011