Многие администраторы баз данных SQL Server испытывают трудности при начале работы с типами данных пространственной информации без реального примера. В этой статье мы рассмотрим, как найти расстояние между разными городами с использованием типа данных географии SQL Server и Google Earth.

Требования

Для расчета расстояния между двумя городами мы будем использовать SQL Server 2012. Однако предоставленный образец кода также должен работать с SQL Server 2008. Обратите внимание, что более ранние версии SQL Server не включают тип данных географии.

Кроме того, нам понадобится инструмент, такой как Google Earth, для получения координат городов. В качестве альтернативы вы можете использовать другие инструменты, такие как findlatitudelongitud.com или itouchmap.com.

Начало работы

Давайте начнем с создания таблицы для хранения городов и их координат:

CREATE TABLE [dbo].[CitiesWorld](
 [Id] [smallint] IDENTITY(1,1) NOT NULL,
 [City] [nchar](40) NULL,
 [Coordinates] [geography] NULL,
 CONSTRAINT [PK_CitiesWorld] PRIMARY KEY CLUSTERED 
(
 [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

В этом примере мы используем тип данных географии для столбца “Coordinates”. Для наших расчетов рассмотрим два города: Кочабамба (где я живу) и Лондон. Чтобы получить координаты каждого города, мы будем использовать Google Earth.

Например, давайте переместимся в Кочабамбу, Боливия, и увеличим масштаб, пока не увидим аэропорт. Проверьте широту и долготу, которые в данном случае составляют соответственно 17°24’45.00 S и 66°10’16.06” W. Нам нужно преобразовать эти координаты в формат SQL Server. Существует различные способы сделать это, но для этого примера мы будем использовать сайт earthpoint.us.

Вставьте информацию “Degrees Lat Long” в нашу таблицу CitiesWorld:

INSERT INTO [dbo].[CitiesWorld] ([City],[Coordinates])
VALUES ('Cochabamba', geography::STGeomFromText('POINT(-66.1711278 -17.4125)', 4326));
GO

Теперь давайте перейдем к Лондону. Увеличьте масштаб, пока не увидите аэропорт. Широта и долгота для Лондона составляют соответственно 51°30’23.29” N и 00°01’39.13 E”. Повторите шаги для преобразования этих координат и вставьте информацию в таблицу:

INSERT INTO [dbo].[CitiesWorld]([City],[Coordinates])
VALUES('London', geography::STGeomFromText('POINT(0.0275361 51.5064694)', 4326));
GO

Расчет расстояния

Чтобы найти расстояние по воздуху между Лондоном и Кочабамбой, мы создадим хранимую процедуру:

CREATE PROCEDURE Distance
@cityor varchar(50),@citydest varchar(50),@unit varchar(5)
as
   declare @or geography, @dest  geography
   SET @or = (select coordinates from [dbo].[CitiesWorld] where city=@cityor)
   SET @dest = (select coordinates from [dbo].[CitiesWorld] where city=@citydest)
IF @unit='miles'
 SELECT @or.STDistance(@dest)/1609.344
ELSE
 --Else show the distance in km
 SELECT @or.STDistance(@dest)/1000

Вышеуказанная процедура находит расстояние между двумя точками, хранящимися в таблице CitiesWorld. Она использует функцию STDistance и позволяет вам найти расстояние в километрах или милях.

Чтобы найти расстояние в милях, используйте следующий код:

EXECUTE Distance 'Cochabamba','London','miles'

Расстояние между этими двумя городами в милях составляет примерно 6186.99.

Чтобы найти расстояние в километрах, используйте следующий код:

EXECUTE Distance 'Cochabamba','London','km'

Расстояние между этими двумя городами в километрах составляет примерно 9957.01.

Используя тип данных географии SQL Server и инструменты, такие как Google Earth, мы легко можем рассчитать расстояние между городами. Это может быть полезно в различных сценариях, таких как определение расстояний путешествий или оптимизация логистики.