Как сделать пакет STDistance, используя тип таблицы с Lat Longs?

Я пытаюсь передать тип таблицы в хранимую процедуру и хотел бы, чтобы sproc искал каждую строку широты/длины и возвращал мне ближайшую точку для этой строки.

Тип:

CREATE TYPE dbo.LatLongRoadLinkType AS TABLE
( 
    Id INT NOT NULL,
    Latitude FLOAT NOT NULL,
    Longitude FLOAT NOT NULL
);

Сохраненная процедура:

ALTER PROCEDURE [dbo].[BatchNearestRoadNodes]
    @Input dbo.LatLongRoadLinkType READONLY
AS
BEGIN
    -- do stuff here
    -- return a table of id from input, nodeid and distance 
END

Для всей таблицы нужно сделать то, что здесь делается для одной широты/долготы:

DECLARE @g geography = 'POINT(13.5333414077759 54.549524307251)';
DECLARE @region geography = @g.STBuffer(5000)

SELECT TOP 1 NodeID, Point.STDistance(@g) as 'Distance'
FROM Location 
WHERE Point.Filter(@region) = 1
ORDER BY Point.STDistance(@g)

В таблице Location есть важный столбец Point типа Geography, который пространственно индексирован и с которым выполняются сравнения. Я отправляю таблицу lat/longs из кода в sproc, и код ожидает возврата:

Id (original point passed in)
NodeID (of nearest point in location table)
Distance

Как мне подойти к этому? Возможно, чтобы сделать это немного проще, я мог бы просто передать SqlGeography из моего кода в sproc вместо Lat/Long, однако это убьет производительность, поскольку преобразование в это очень дорого.

EDIT: это работает, но не знаю, является ли это наиболее оптимальным решением.

ALTER PROCEDURE [dbo].[BatchNearestRoadNodes]
    @Input dbo.LatLongRoadLinkType READONLY
AS
BEGIN

SELECT x.Id, x.LocationName, x.NodeID, x.Distance
FROM (SELECT I.Id,
        L.LocationName,
        L.NodeId,
        L.Point.STDistance(geography::Point(I.Latitude, I.Longitude, 4326)) AS Distance,
        ROW_NUMBER ()  OVER (PARTITION BY I.Id ORDER BY L.Point.STDistance(geography::Point(I.Latitude, I.Longitude, 4326)) ASC) AS Ranking
        FROM @Input AS I
        JOIN Location AS L 
        ON L.Point.STIntersects(geography::Point(I.Latitude, I.Longitude, 4326).STBuffer(5000)) = 1
    ) AS x WHERE Ranking = 1
END

Производительность – версия 1 и версия Джона

V1
============
original:643 found:627 in:1361 ms
original:1018 found:999 in:1700 ms
original:1801 found:1758 in:2628 ms
original:4098 found:3973 in:5271 ms
original:16388 found:15948 in:19624 ms

Jon's Edit
==========
original:643 found:627 in:1333 ms
original:1018 found:999 in:1689 ms
original:1801 found:1758 in:2559 ms
original:4098 found:3973 in:5114 ms
original:16388 found:15948 in:19054 ms

Разница минимальна. Нужно убрать последнюю цифру.


sql-server stored-procedures tsql geospatial spatial
person sprocket12    schedule 01.12.2014    source источник
comment
В любом случае вам придется в какой-то момент перейти на SqlGeography, если вы хотите воспользоваться преимуществами пространственного индекса. Независимо от того, передаете ли вы его как float/float, а затем конвертируете его, или просто передаете его как SqlGeography, вам нужно его преобразовать. Если у вас нет огромных чисел в таблице, накладные расходы не так уж и плохи. Какую версию SQL вы используете?   -  person Jon Bellamy    schedule 01.12.2014
comment
@Мухаммед. Выглядит хорошо, и рад, что мой дал некоторую основу. Единственное, что я хотел бы сказать, это то, что вы дважды конвертируете географию в запросе. Рассмотрите возможность использования подзапроса / CTE, как я сделал, чтобы сначала вычислить экземпляры geography один раз, а затем повторно использовать его. Какое время вы получаете с каким количеством строк?   -  person Jon Bellamy    schedule 01.12.2014
comment
Нет проблем, все сделано. Мне действительно понравилось ваше решение ROWNUMBER().... По какой-то причине я не рассматривал его, но он эффективен. Ответ обновлен ниже. Надеюсь, на этот раз это сработает, и география будет преобразована только один раз, а не три раза. Сэкономит несколько мс. Дайте мне знать, как это улучшается.   -  person Jon Bellamy    schedule 02.12.2014

Ответы (1)


arrow_upward
0
arrow_downward

Попробуйте что-то вроде этого, чтобы получить частичные результаты:

WITH PreQuery AS
(
I.Id,
GEOGRAPHY::STPointFromText(I.PointAsWKT).STBuffer(5000) AS Geog,
L.NodeId,
L.Point
FROM
@Input AS I
JOIN
Location AS L ON L.Point.STIntersects(I.Geog) = 1
)
SELECT
P.Id,
P.NodeId,
P.Geog.STDistance(P.Point) AS Distance
FROM
PreQuery P

Я написал его с головы и без каких-либо тестовых данных, поэтому могут быть небольшие ошибки, но в основном он даст вам каждый узел и его расстояние (в пределах 5000 метров) от каждой точки. Вам все равно нужно отфильтровать их, чтобы получить только тот, у которого минимальное расстояние для каждого идентификатора - это не должно быть слишком сложно ;-)

Надеюсь, это немного поможет, даже если не полностью.

ИЗМЕНИТЬ (2 декабря)

Я уже вижу проблему с моим первым решением, вы не можете получить расстояние, потому что оно предварительно буферизовано (заметим главное). Однако это объединение должно быть наиболее эффективной комбинацией обеих попыток.

WITH PreQuery AS
(
SELECT
I.Id,
geography::Point(I.Latitude, I.Longitude, 4326) AS InputGeography
FROM
@input AS I
)
SELECT x.Id, x.LocationName, x.NodeId, x.Distance
FROM
(
SELECT
PQ.Id,
L.LocationName,
L.NodeId,
L.Point.STDistance(PQ.InputGeography) AS Distance,
ROWNUMBER() OVER (PARTITION BY I.Id ORDER BY L.Point.Distance(PQ.InputGeography) ASC) AS Ranking
FROM
Prequery AS PQ
JOIN
Location AS L
-- ON L.Point.STIntersects(PQ.InputGeography.STBuffer(5000)) = 1 -- Slower
ON L.Point.STDistance(PQ.InputGeography) <= 5000 -- Faster
) AS X WHERE Ranking = 1

Таким образом, вы предварительно создаете входную географию только один раз, а не три раза, как при вашей попытке. Опять же, это не проверено, но должно оказаться наиболее эффективным.

person Jon Bellamy    schedule 01.12.2014
comment
Привет, Джон, пожалуйста, ознакомьтесь с результатами производительности в моем редактировании. Пытаюсь найти способ повысить точность и скорость. Возможно, сработает рекурсивный подход, то есть с небольшим буфером, затем все больше и больше с меньшим количеством точек. - person sprocket12; 02.12.2014
comment
Я не уверен, что рекурсия сработает, так как вам приходится иметь дело с несколькими вызовами и фильтрацией на каждом уровне. Попробуйте изменить соединение на L.Point.STDistance(PQ.InputGeography) <= 5000. Вы также проверили план запроса, чтобы убедиться, что используется пространственный индекс? - person Jon Bellamy; 02.12.2014
comment
Да, это улучшило ситуацию, теперь последняя составляет 14475 мс :) Я отредактирую ваш ответ, чтобы добавить V3. - person sprocket12; 02.12.2014