Jako malé hříbě mne fascinovala analytická geometrie. Idea, že rozličné plošné i prostorové tvary je možno matematicky popsat a elegantními rovnicemi řešit geometrické úlohy typu kde se co protíná, mne prostě fascinovala. Tím spíše, že maje obě přední nohy levé, jsem nikdy nebyl schopen úspěšně řešit úlohy typu "zkonstruujte pravítkem a kružítkem dvě tečny kružnice, protínající se v úhlu tom a tom". Ano, už tehdy mi moji spolužáci říkali, že jsem perverzní – a to ještě nevěděli o mnoha jiných věcech, ke kterým jsem později dospěl. S rozmachem různých geo-technologií jako je GPS, geotagging a geotracking přibývá případů, kdy se nám v databázi rodí data geografické nebo geometrické povahy a před programátory jsou kladeny úlohy jako "vypiš všechny body vzdálené méně než N kilometrů".
Již nějakou dobu je všeobecně známo, že Země není placatá. Ačkoliv to má své výhody, z hlediska řešení úloh shora popsaného typu je to poněkud nepraktické, protože si nevystačíme s prostou Pythagorovou větou a je nezbytné užívat poněkud ezoteričtějších matematických konstruktů. Již méně se ví, že Země není ani kulatá, lépe řečeno že nemá tvar koule, ale dosti obtížně popsatelného tvaru, kterému se říká geoid. Jestliže se někdy setkáte se zkratkou WGS84 (a to se jistě setkáte, například dále v tomto článku) pak vězte, že se jedná o demokraticky přijatý matematický model pro určování zeměpisných souřadnic, který sice – jak tomu už u demokratických řešení bývá – není zcela shodný s realitou, ale zato ho všichni používají. Pro nás z toho vyplývá smutná skutečnost, že při řešení výše popsaných problémů si nevystačíme ani se vzorečky, které bychom možná na základě středoškolsných znalostí dali dohromady pro kouli.
Nicméně, netřeba propadat trudnomyslnosti. Výše popsaný úvod je zde především proto, aby vás vyděsil a umožnil vám naplno si vychutnat úlevu ze skutečnosti, že díky Microsoftu a jeho SQL Serveru nebudete muset nic podobného řešit. Veškeré popsané matematické prostocviky totiž za vás udělá zmíněný program, a to od verze 2008 (ve všech edicích, včetně Express).
Disclaimer: Tento článek je jedním z mnoha mých výletů mimo domovský přístav ASP.NET a vrtám se do věcí, o kterých mnoho nevím. Články v tomto seriálu tedy berte spíše jako volnou inspiraci. Za pomoc při jejich tvorbě (zejména pak pozdějích pokračování s JavaScriptem) děkuji Petru Kaletovi.
Datové typy 'geometry' a 'geography'
SQL Server přináší datové typy geometry a geography. Říká se jim také spatial data types (prostorové datové typy). Datový typ geometry je zcela univerzální typ pro práci s jakýmikoliv daty zadanými pomocí prostorových souřadnic, v podstatě v jakémkoliv referenčním modelu. Tyto typy umějí reprezentovat v podstatě libovolnou prostorovou konstrukci – bod, polygon, trasu…
Já se budu nadále zabývat pouze typem geography, který je speciálním případem shora uvedeného pro potřeby práce s pozemskými zeměpisnými souřadnicemi.
K lokalizaci jakéhokoliv bodu na zemském povrchu potřebujeme znát jeho zeměpisnou šířku (anglicky latitude, obvykle se používá zkratka Lat) a zeměpisnou délku (longitude, zkratka Lon nebo Long). Zeměpisná šířka nabývá hodnot od -90 do 90 a nula leží na rovníku. Zeměpisná délka se pohybuje -180 do 180 s nulou na nultém poledníku. V případě pevninské Evropy budou obě čísla kladná, ČR se nachází přibližně na padesátém stupni severní šířky a čtrnáctém stupni východní délky.
Z vlastní zkušenosti mohu říct, že se při importu dat vyplatí ověřit, že jste souřadnice neprohodili, abyste se pak nedivili jako já, že vám to vychází nějak divně.
Pro další pokusy a ukázky jsem si vybral a do SQL serveru naimportoval data volně dostupná na serveru Geonames. Pro ČR jsou dostupné zeměpisné souřadnice obcí, některých ulic, význačných bodů a podobně. Je jich zhruba 19000, což je pro potřeby našich demonstrací až dost. Na konci tohoto článku najdete odkaz na stažení SQL skriptu, který vytvoří požadovanou strukturu databáze a naplní ji ukázkovými daty.
Začneme vytvořením prázdné databáze Geo a v ní dvou tabulek Classes a GeoPoints:
-- Vytvořit nové tabulky
CREATE TABLE Classes (
ClassCode char(1) NOT NULL,
Name varchar(200) NOT NULL,
)
CREATE TABLE GeoPoints(
GeoPointId int IDENTITY(1,1) NOT NULL,
Name varchar(200) NOT NULL,
Class char(1) NOT NULL,
Location geography NOT NULL,
)
-- Vytvořit indexy
ALTER TABLE Classes ADD
CONSTRAINT PK_Classes PRIMARY KEY CLUSTERED (ClassCode)
ALTER TABLE GeoPoints ADD
CONSTRAINT PK_GeoPoints PRIMARY KEY CLUSTERED (GeoPointId),
CONSTRAINT FK_GeoPoints_Classes FOREIGN KEY (Class) REFERENCES dbo.Classes (ClassCode)
-- Vložit data do pomocné tabulky Classes
INSERT [Classes] ([ClassCode], [Name]) VALUES (N'A', N'Územní celek')
INSERT [Classes] ([ClassCode], [Name]) VALUES (N'H', N'Vodní tok')
INSERT [Classes] ([ClassCode], [Name]) VALUES (N'L', N'Park, rezervace')
INSERT [Classes] ([ClassCode], [Name]) VALUES (N'P', N'Sídlo')
INSERT [Classes] ([ClassCode], [Name]) VALUES (N'R', N'Doprava')
INSERT [Classes] ([ClassCode], [Name]) VALUES (N'S', N'Budova, místo')
INSERT [Classes] ([ClassCode], [Name]) VALUES (N'T', N'Přírodní úkaz')
INSERT [Classes] ([ClassCode], [Name]) VALUES (N'V', N'Les')
Středem všehomíra je pro nás tabulka GeoPoints. Obsahuje numerický identifikátor, textový název, typ (třídu) objektu a především sloupec Location, který je výše zmíněného typu geography.
Přímé vkládání dat do tabulky jest činiti například pomocí funkce geography::STPointFromText. Ona na první pohled podezřelá syntaxe je způsobena tím, že spatial funkce zhusta využívají SQLCLR – jsou interně implmentovány v .NET Frameworku a referencovány ze SQL. Syntaxe INSERT dotazů pak bude vypadat následovně:
INSERT INTO GeoPoints (Name, Class, Location) VALUES ('Praha', 'P', geography::STPointFromText('POINT(14.424132 50.087837)', 4326))
První parametr funkce STPointFromText je textové vyjádření souřadnic. Podobně fungují i další funkce, které umožňují vytvářet složitější objekt, jako čáry a polygony. Druhý parametr je referenční model, ke kterému se údaje vztahují. Číslo 4326 reprezentuje již výše zmíněný model WGS84, se kterým se budete patrně setkávat nejčastěji, protože se typicky používá pro určování GPS souřadnic.
Pozor, z mně neznámých důvodů stringový formát jako první uvádí zeměpisnou délku a poté šířku, zatímco prakticky všude jinde je to naopak.
Data jsou interně ukládána binárně, jako binárku je možné je také vyexportovat a naimportovat zpět, což je výrazně rychlejší, než parsování textu, pokud data už někde máme. Ekvivalentní příkaz k výše uvedenému by byl:
INSERT INTO GeoPoints (Name, Class, Location) VALUES ('Praha', 'P', 0xE6100000010C7B30293E3E0B4940C85F5AD427D92C40)
Do databáze je možné se potom normálně dotazovat. Pokud chcete souřadnice dostat v nějaké příčetné podobě, musíte použít vlastnosti Latitude, Longitude a nebo metodu ToString:
SELECT
Name,
Location.Lat AS Latitude,
Location.Long AS Longitude,
Location.ToString() AS LocationString
FROM GeoPoints
Datové typy geometry a geography lze též indexovat pomocí takzvaných spatial indexů. Pokud hodláte data nejenom uchovávat, ale též podle nich vyhledávat, je dobrý nápad tento index vytvořit, protože to výrazně zrychlí dobu odezvy. Spatial indexy mají množství optimalizačních parametrů, do kterých nebudu blíže rýpat (primárně proto, že o nich mnoho nevím) takže se spokojím s jednoduchým vytvořením indexu:
SET ANSI_PADDING ON
CREATE SPATIAL INDEX IX_Location ON GeoPoints (Location)
Geografické podmínky
Jakmile máme jednou data v databázi, je snadné páchat elegantní dotazy založené na vzájemné poloze jednotlivých bodů. Nejtypičtějším zadáním je patrně nalézt body v blízkosti zadaných souřadnic. K tomuto účelu lze využít metodu STDistance, která vrátí vzdálenost dvou bodů, v případě typu geography v metrech.
Následujícím dotazem vybereme položky do vzdálenosti @delta metrů od bodu @point – v tomto případě do deseti kilometrů od středu Brna:
DECLARE @delta AS int
DECLARE @point AS geography
SET @delta = 10000
SET @point = geography::STPointFromText('POINT(16.607552 49.199541)', 4326)
SELECT
Name,
@point.STDistance(Location) AS Distance,
Location.Lat AS Latitude,
Location.Long AS Longitude
FROM GeoPoints
WHERE @point.STDistance(Location) <= @delta
ORDER BY 2
Tento dotaz budete typicky využívat pro zadání typu "najdi nejbližší…". Druhý základní typ dotazu je "najdi body ve vyznačeném území" – například v aktuálně zobrazeném výřezu mapy. V takovém případě podobným způsobem jako bod nadefinujeme polygon (v níže uvedeném příkladu obdélník @rect, odpovídající přibližně ČR) a poté využijeme metodu STIntersects, která vrátí nulu nebo jedničku v závislosti na tom, zda se dva objekty protínají (v našem případě tedy zda je bod uvnitř polygonu):
DECLARE @rect AS geography
SET @rect = geography::STPolyFromText('POLYGON ((11.381836 51.869708, 11.381836 47.613570, 19.621582 47.613570, 19.621582 51.869708, 11.381836 51.869708))', 4326)
SELECT
Name,
Location.Lat AS Latitude,
Location.Long AS Longitude
FROM GeoPoints
WHERE Location.STIntersects(@rect) = 1
Spatial funkce SQL serveru toho samozřejmě umí mnohem víc, další informace najdete například na webu Microsoftu. V příštím pokračování se podíváme, jak se na data tohoto typu ptát z .NETu a jak si poradit s výsledkem.
Příklady ke stažení
Příklady k tomuto seriálu si můžete stáhnout na http://www.aspnet.cz/files/20100603-GeoSamples.zip (760 kB).