Výsledkem našeho snažení bude webová aplikace, která dokáže zobrazit obrysy krajů v ČR a najít obce, které leží ve vybraném kraji. U obcí máme k dispozici jen název a jejich geografické souřadnice, u krajů opět název a souřadnice popisující hranici kraje. Vzorová data pro kraje jsem použil z webu gc.fa-fa.org a souřadnice obcí, které jsou hodně přibližné, jsem našel kdysi na internetu, ale zdroj si již nepamatuji.

Cílem není vytvořit aplikaci připravenou k ostrému provozu, ale ukázat koncept. K zobrazení informací použijeme Virtual Earth Map Control, o zpracování dat na serveru se bude starat PHP a na klientovi jQuery. GIS funkce bude zajišťovat SQL Server 2008.

SQL Server 2008

Jednou z mnoha novinek v SQL Serveru 2008 je podpora geografických dat, která je postavena na doporučeních Open Geospatial Consortium. Podpora práce s geografickými informacemi je dostupná i v edici SQL Server 2008 Express, která je zdarma i ke komerčním účelům. Doporučuji stáhnout edici SQL Server 2008 Express with Advanced Services, která kromě databázového jádra obsahuje podporu pro fulltext a Reporting Services pro vytváření „tiskových“ sestav. SQL Server 2008 Express má oproti „dospělému“ SQL Serveru některá omezení:

• Maximální velikost databáze je 4 GB
• Nevyužije víc než 1 GB RAM
• Nevyužije víc než 1 procesor, resp. jádro

Všechny edice SQL Server 2008 mají identické jádro, takže není problém mezi edicemi databázi přenášet. Edice se liší v podporovaných službách a v limitech využití HW.

Instalace

Součástí instalace je nástroj System Configuration Checker, který ověří, zda počítač splňuje systémové požadavky pro SQL Server 2008.

Úvodní okno instalace

Kontrola systémových požadavků

Pravděpodobně nebudete mít nainstalovaný Powershell, který SQL Server 2008 využívá pro konfiguraci z příkazové řádky. Dále budete potřebovat .NET Framework 3.5, ten si ale umí instalátor SQL Serveru 2008 nainstalovat sám. Další, na co je třeba myslet, jsou účty pro běh jednotlivých služeb SQL Serveru. SQL Server 2008 se skládá z několika služeb (databázové jádro, fulltext, Reporting Services, Integration Services…) a v produkčním prostředí se pro běh každé služby doporučuje použít samostatný uživatelský účet se základními právy (skupina Users), který se k ničemu jinému nepoužívá. Na testovacím stroji můžete nechat vše běžet pod jedním účtem. Celý postup instalace si můžete prohlédnout v screencastu.

Připojení se k SQL Serveru 2008

Pro správu SQL Serveru 2008 se používá nástroj SQL Server Management Studio, který je součástí instalace. Slouží jak ke správě serveru, tak jednotlivých databází a k ladění dotazů a procedur. Při spuštění nastavíte umístění serveru a typ autentizace. Pokud se připojujete k lokálnímu počítači, můžete místo názvu počítače použít (local). Za zpětným lomítkem je uveden název instance SQL Serveru, který jste vytvořili při instalaci.

Vytvoření databáze

1. Pravým tlačítkem myši klikněte na větev Database v Object Explorer
   
2. Vyberte New Database…
   
3. V podstatě stačí jen vyplnit jméno databáze. V produkčním prostředí se doporučuje mít databázové soubory na jiném pevném disku než soubor pro transakční log. Stejně tak by datové soubory měly být jinde, než je disk s operačním systémem a aplikacemi. Pro vývoj můžeme klidně vše nechat na jednom disku.
4. Kliknutím na OK se databáze vytvoří ve vybraném umístění.

Alternativa

Veškerou správu SQL Serveru 2008 můžete dělat pomocí SQL příkazů z příkazové řádky nástrojem sqlps (klasický Powershell obsahující nástroje pro SQL Server 2008). Následující ukázka je vytvoření nové databáze. Stejně tak můžete vytvářet tabulky, uživatele, indexy,…

1. Spusťte sqlps
2. Spusťe příkaz: Invoke-Sqlcmd -Query "CREATE DATABASE CR;" -ServerInstance "(local)SQLEXPRESS"

Vytvoření tabulek

1. Rozbalte strom nově vytvořené databáze
   
2. Vyberte New Table…
   
3. Po vytvoření struktury tabulky stačí kliknout na tlačítko Save nebo Ctrl+S a tabulku pojmenovat.

Indexy

Indexy výrazně urychlují zpracování dotazů. Bez dobře navržených indexů jsou objemné databáze a databáze pro GIS naprosto nepoužitelné z hlediska výkonu. I v našem jednoduchém příkladu na použití GIS funkcí se budou dotazy vykonávat v desítkách sekund, pokud nebudeme mít vytvořené indexy pro geografická data. Pro ladění indexů se používá okno Execution plan, které zobrazíte z nabídky Query. V tomto okně vidíte, zda se používají indexy. Následující screenshot ukazuje dotaz bez použití indexů. Vidíte, že se to „zaseklo“ na filtrování dat z tabulky Obce. Doba trvání dotazu byla cca 35 sekund:

Tady jsem přidal index na sloupec Obce.Souradnice, který obsahuje geografické informace. Doba trvání dotazu byla těsně pod 2 sekundy:

Z ukázek je jasně vidět, že stačilo přidat pouze jeden jediný index a odpověď z databáze je výrazně rychlejší. A výsledek by šel dál zlepšit.

Vytvoření indexu

1. Rozbalte větev tabulky v Object Explorer
2. Pravým tlačítkem myši klikněte na Indexes
   
3. Dále je třeba index pojmenovat a přidat sloupce, nad kterými chcete index vytvořit. Pokud chcete vytvořit index pro datový typ geography, geometry nebo xml, je třeba také změnit vlastnost Index type.
   

Relace

U vytvoření relace mezi dvěma tabulkami je třeba začít otevřením stromu objektů tabulky, která bude obsahovat cizí klíč (Foreign Key).

1. Klikněte pravým tlačítkem myši na seznam klíčů (Keys) a vyberte New Foreign Key…
   
2. Vyplňte jméno klíče (Name)
   
3. V řádku Table and Columns Specification klikněte na tlačítko v pravé části řádku
   
4. Vlevo nastavte tabulku s primárním klíčem a pole, které ho obsahuje, vpravo pak nastavte provázané pole

Několik dobře míněných rad pro vytváření databází

Na co se často zapomíná u popisu jak vytvořit databázi, je, jak ji navrhnout. Nečiním si nárok zde popsat teorii relačních databází, ale spíše se jedná o dobře míněné rady, které jsou často ignorovány:

• Každá tabulka by měla obsahovat jen informace jednoho typu. Viděl jsem, jak se někteří snažili celou databázi nacpat do jedné tabulky. Bohužel jde o zlozvyk z tabulkových kalkulátorů.
• Pole tabulky by mělo být dále nedělitelné. Klasickou ukázkou je ukládání celého jména osoby do jednoho pole. Jeden můj kamarád se jmenuje Petr Štěpán. Co je jeho křestní jméno?
• Pokud mám jednu informaci, na kterou se váže více informací jiných (osoba – poštovní adresa, fakturační adresa, doručovací adresa), tak je třeba informace rozdělit do více tabulek a nastavit mezi nimi relaci. Už jsem viděl tabulku, kde bylo pole Ulice1 až Ulice 7, PSC1 až PSC7, atd. V příkladu adres, který jsem uvedl, bych vytvořil tři tabulky a ty provázal přes klíče:
  • Osoba
  • Adresa
  • Typ adresy
• Chybějící indexy a špatně sestavené dotazy nedoženete sebelepším HW. Jeden můj kolega, po kterém jsem v předchozím zaměstnání přebíral projekt, vytvořil report, který se generoval 5 hodin. Po přidání indexů se generoval 30 minut a po dalším odladění 4 minuty…
• Pokud neděláte relace mezi souvisejícími tabulkami, tak je pro vás relační databáze zbytečná a dřív nebo později si porušíte referenční integritu dat. V kódu to neuhlídáte.
• Soubory, které mají vztah k informacím v databázi, ukládejte do databáze, zjednodušíte si zálohování a konzistenci dat se zbytkem databáze. Pomocí atributu FILESTREAM u pole typu varbinary(MAX) můžete ukládat soubory do velikosti, kterou podporuje souborový systém na serveru.

Zabezpečení

Abyste databázi mohli používat pro webovou aplikaci, musí do ní mít přístup uživatel, pod kterým běží některý Application Pool na IIS.

Webová aplikace pak musí být přiřazena do příslušného Application Pool:

A uživatel ApplicationPoolu musí mít vytvořen Login na databázovém serveru, případně musí být ve skupině, která na databázovém serveru Login už má:

Jak je vidět, pro testovací účely jsem si vytvořil Application Pool, který běží pod mým doménovým účtem, který má nastavenu roli sysadmin databázového serveru. Pro produkční nasazení je třeba vytvořit účet, který bude mít ta nejnižší nutná práva jak k systému, tak databázi (role db_datareader), aby se snížilo riziko zneužití účtu.

Podpora GIS

Jedna z novinek SQL Serveru 2008 je nový typ geography určený pro uchovávání geografických informací a práci s nimi. Typ geography je .NET CLR objekt, který zároveň umí s geografickou informací i manipulovat (hledat průniky, převádět na text, z textu na geografický objekt, počítat rozměry atd.). Pro vkládání dat do sloupce s typem geography se používá Open Geospatial Consortium (OGC) Well-Known Text (WKT). Ukázka:

INSERT INTO Obce
           (Nazev
           ,Souradnice)
     VALUES
           ('Praha',
            geography::STGeomFromText('POINT(50.0668 14.4662)', 4326)) 

Druhý parametr metody STGeomFromText určuje použitý souřadnicový systém, zde konkrétně WGS 84. Pro zpětné získání geografických dat se požívají metody pro naformátování výstupu buď do WKT, nebo GML:

select Souradnice from Obce where ObecId = 1 

vrátí

0xE6100000010C58CA32C4B1EE2C40B8AF03E78C084940 

select Souradnice.ToString() from Obce where ObecId = 1 

vrátí

POINT (50.0668 14.4662) 

select Souradnice.AsGml() from Obce where ObecId = 1 

vrátí

<Point xmlns="http://www.opengis.net/gml"><pos>14.4662 50.0668</pos></Point> 

Seznam podporovaných metod pro práci s geografickou informací (průnik, plocha, vzdálenost …) najdete na MSDN.

Pár ukázek (pro přehlednost jsou některé rozepsané do více kroků, vše lze samozřejmě udělat i jedním SELECTem):

Plocha Karlovarského kraje:

select Nazev, Oblast.STArea() as Plocha from Kraje where KrajId = 5 

Vzdálenost Praha – Liberec:

DECLARE @Praha geography;
DECLARE @Liberec geography;
select @Praha = Souradnice from Obce where Nazev = 'Praha'
select @Liberec = Souradnice from Obce where Nazev = 'Liberec'
SELECT @Praha.STDistance(@Liberec); 

Obce v okruhu 10 km od Vlašimi:

DECLARE @Vlasim geography;
SELECT @Vlasim = Souradnice From Obce Where Nazev = 'Vlašim'
SELECT Nazev From Obce Where @Vlasim.STBuffer(10000).STIntersects(Souradnice) = 1; 

Další informace o SQL Serveru 2008 najdete v elektronické knize Introducing Microsoft SQL Server 2008, která je zdarma ke stažení.

SQL Server 2005 Driver for PHP

Abychom mohli využívat SQL Server 2008, je třeba si stáhnout SQL Server 2005 Driver for PHP. I když je v názvu uveden SQL Server 2005, tak ovladač funguje i s SQL Server 2008. K připojení využívá SQL Native Client. Instalace obsahuje dvě knihovy ovladače php_sqlsrv.dll (non-thread safe), php_sqlsrv_ts.dll (tread safe) a dokumentaci. Instalace je velmi jednoduchá. Ovladač nakopírujete k ostatním knihovnám PHP, typicky adresář ext v instalačním adresáři PHP a knihovnu přidáte do php.ini. Ve výstupu funkce phpinfo() byste měli pak najít sekci sqlsrv.

Pomocí funkce sqlsrv_connect získáte kontext připojení k databázi. Funkce má dva parametry – adresu serveru a pole parametrů, ve kterém můžete specifikovat vlastnosti připojení – jméno databáze, přihlašovací údaje, využití connection pool atd. Pokud neuvedete přihlašovací údaje, použije se Windows authentication.

$serverName = '(local)SQLEXPRESS'; // adresa serveru
$connectionInfo = array( 'Database'=>'CR'); // parametry pripojeni
$conn = sqlsrv_connect( $serverName, $connectionInfo); 

SQL dotaz se provádí funkcí sqlsrv_query, která má jako jeden z parametrů vlastní dotaz, dále pak kontext připojení. Pokud používáte parametrizované dotazy, což je z hlediska bezpečnosti před útokem typu SQL Injection naprosto nezbytné, budete jako další parametr funkce předávat pole hodnot, které nahradí parametry v dotazu. Nahrazení parametrů v dotazu za konkrétní hodnoty se provádí až na SQL Serveru, v době, kdy je hotový Execution Plan. Tím pádem není už možné vykonat podstrčené dotazy v parametrech.

$sql = 'SELECT o.Nazev, o.Souradnice.ToString()
                FROM Obce o, Kraje k
                WHERE k.Oblast.STIntersects(o.Souradnice) = 1
                    AND k.KrajId = ?
                ORDER BY o.Nazev';
 
 $stmt = sqlsrv_query($conn, $sql, array($_GET['id'])); 

Pro čtení výsledku dotazu se používá smyčka while a funkce sqlsrv_fetch, které jako parametr předáte dotaz.

while(sqlsrv_fetch( $stmt))
{
...
} 

Pro získání konkrétní hodnoty použijete funkci sqlsrv_get_field, kde první parametr určuje sloupec (indexováno od nuly), druhý sloupec je dotaz, který zpracováváte, a třetí parametr je datový typ navrácené hodnoty. To potřebujete ve chvíli, kdy máte textová data v databázi uložena v Unicode (datová pole nvarchar, nchar, ntext). Zpracování textu uloženého v Unicode:

$name = iconv("utf-16le"
                    "utf-8",
                    sqlsrv_get_field($stmt,
                        0,
                        SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_BINARY))); 

Další funkce pro načítání dat jsou sqlsrv_fetch_array a sqlsrv_fetch_ob­ject. Práce s úložnými procedurami (Stored Procedures) je v podstatě identická s prací s klasickým SQL:

CREATE PROCEDURE [dbo].[GetPolygon]
@id int
AS
BEGIN
SELECT Nazev, Oblast.ToString() as polygon FROM Kraje WHERE KrajId = @id
END
 
$sql = 'execute GetPolygon ?';
$stmt = sqlsrv_query($conn, $sql, array($_GET['id'])); 

Vizualizace dat

Pro vizualizaci geografických dat je asi nejdostupnější použít některou z veřejných mapových služeb. Pro toto demo jsem zvolil Virtual Earth Map Control. Vložení mapy do stránky a její ovládní pomocí JavaScriptu vyžaduje v podstatě dvě věci.

• Přidat odkaz na javascriptovou knihovnu Virtual Earth Map Control
• Vytvořit element <div>, kde se bude mapa zobrazovat

Základní zobrazení mapy ve stránce:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
    <meta http-equiv="content-type" content="text/html; charset=utf-8" />
    <title>Virtual Earth Example</title>
 
    <script src="http://dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=6.2" type="text/javascript"></script>
    <script type="text/javascript">
    var map = null;
 
    function OnPageLoad()
    {
        map = new VEMap('map');                 
        map.LoadMap(new VELatLong(50.0, 14.5));                                
    }
    </script>
 
</head>
<body onload="OnPageLoad()">
    <div id="map" style="width: 800px; height: 600px; overflow: hidden;">
    </div>
</body>
</html> 

Ve chvíli, kdy máme mapu zobrazenou, můžeme na ni přidávat další vrstvy s vlastní grafikou. Následující ukázka přidá bod na mapu ve vlastní vrstvě:

var layer = new VEShapeLayer();
var shape = new VEShape(VEShapeType.Pushpin, new VELatLong(50, 14.5));
shape.SetTitle('Praha');
layer.AddShape(shape);                                         
map.AddShapeLayer(layer); 

Co může zobrazení dat usnadnit, je podpora formátů GeoRSS a KML.

var layer = new VEShapeLayer();
var veLayerSpec = new VEShapeSourceSpecification(VEDataType.GeoRSS, 'rss.xml', layer);
map.ImportShapeLayerData(veLayerSpec); 

Závěr

Ukázkovou aplikaci si můžete stáhnout, popřípadě vyzkoušet online.

Autorem článku je Štěpán Bechynský, specialista pro vývojové nástroje ve společnosti Microsoft v České republice. Informace pro vývojáře také najdete na blogu odborníků z českého Microsoftu.