Kisebb elhavazós kihagyás után, szépen alszanak a gyerekek (némelyik), folytassuk hát a spatial adatokkal még egy kicsit. Ígértem, hogy valós adatokkal fogunk dolgozni. Ilyen adatokat hatalmas mennyiségben lehet találni a weben, csak persze nem Magyarországról. :)

Természetesen USA-hoz van a legtöbb. Lássunk párat:
Az államok határai és azon belüli területi bontás.
Általános iskolák.
Szavazókörök, stb. sok egyéb adat még.

Magyarországhoz és a környezetéhez innen nyertem ki adatokat. A térképen ki kell jelölni, mely területről kell infó, Next, Next, mely objektumok érdekelnek (ország körvonalak, folyók, városok, stb.), majd Generate File. A kimenet egy ún. ShapeFile, amelyet beimportálhatunk az SQL Serverbe. Hogyan?

Bátrabbak nekiállhatnak egy SSIS csomagot írni, lustábbak, mint én google-öznek, és találnak. Ez a kis jószág, Shape2SQL.exe ShapeFile-okat tud begyúrni SQL táblákba.

Az adatokat geography típusként érdemes beimportálni, mert szélesség-hosszúság adatokat töltöttünk le, és a 4326-os SRID is jó, mert abban vannak az adatok.
A következő táblákkal fogunk dolgozni:
-Contry: országhatárok
-City: főbb városok
-River: folyók
-WaterBody: állóvizek, értsd Balaton. :)
-Road: utak.

Fontos tudni, hogy az ingyenesen letölthető adatok szándékosan nagyon durva adatokat tartalmaznak, tessék fizetni a részletes térképekért. Ezt csak azért mondom, mert ez alapvetően befolyásolni fogja a kimenetek hitelességét.

No, kérdezzünk le. Válogassuk le a magyar városokat. Az adataimban nem csak Magyarország van benne, hanem a környező országokból is ez+az, így a lekérdezés jogos. Már kérdés, csak én vagyok ilyen idióta, hogy a földrajzi adatokból akarok ezt a listát előállítani. :)

select CITY_NAME
from City
where geom.STIntersects(
(select geom
from Country
where CNTRY_NAME = N'Hungary')) = 1

A Country táblából kiválasztjuk Mo. körvonalát, ez és a városok közül azokat válogatjuk ki, amelyek metszik az országot. A városok egyébként ebben az adatbázisban pontként vannak reprezentálva.

CITY_NAME
------------------------------
Salgotarjan
Miskolc
Nyiregyhaza
Eger
Gyor
Tatabanya
Debrecen
Budapest
Szombathely
Szekesfehervar
Szolnok
Veszprem
Kecskemet
Zalaegerszeg
Bekescsaba
Kaposvar
Szekszard
Szeged
Pecs
Arad
Pozsony

Az utolsó két bejegyzés csak vicc. :)

Mely városok vannak a Duna 10km-es körzetében?

declare @danube geography = geography::STPointFromText(‘POINT(0 0)’, 4326);

select
–geom.ToString()
–,*
@danube = @danube.STUnion(geom)
from River
where NAME = N’Danube’

select @danube.ToString()

select CITY_NAME,
geom.ToString() ‘A város eredeti adata’,
geom.STStartPoint().ToString() ‘A város mint pont’,
geom.STStartPoint().STDistance(@danube) ‘Távolság a folyótól’
from City
where geom.STStartPoint().STDistance(@danube) < 10000 [/source] [source='c'] CITY_NAME A város eredeti adata A város mint pont Távolság a folyótól ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------- Bratislava MULTIPOINT ((48.15000118311 17.129999448 POINT (48.15000118311 17.129999448299998 1691.81121899415 Budapest MULTIPOINT ((47.51499918311 19.093999448 POINT (47.51499918311 19.093999448299996 1167.59360136753 Belgrade MULTIPOINT ((44.79968218311 20.412555448 POINT (44.79968218311 20.412555448299997 5289.83252090268 Drobeta- Turmu Sererin MULTIPOINT ((44.63800018311 22.656000448 POINT (44.63800018311 22.656000448300006 2010.83235902907 Vienna MULTIPOINT ((48.202118183109995 16.32098 POINT (48.202118183109995 16.32098544830 9002.0262024942 [/source] Kicsit hekkelős lett a megoldás. A folyók sok darabban vannak letárolva a River táblában, ezért az STUnion-nal össze kell őket rakni. Igen ám, de az skaláris függvény, nekünk meg aggregálni kellene. Mivel nincs STUnionAggregate (majd írok egyet valamelyik nap), azért marad a select hekk, vagy cursor. A 0,0-ra inicializálás azért kell, mert NULL.STUnion(akármi) az NULL. Ha pont az egyenlítőn és 0 szélességnél lenne adatunk, akkor cumi, mert a mesterséges adat oda esik. Kell majd ide egy rendes CLR aggregáló fv. Az STStartPoint azért kell, mert az STDistance megköveteli, hogy az egyik szereplő pont legyen. A City táblában a városok multipontként vannak felvéve, de csak 1 pontot tartalmaznak. Ezzel gyakorlatilag multipont->pont konverziót csináltam. Az STDistance a mininális távolságot adja meg egy pont és egy alakzat között, így annak használata érthető.

Magyar városok egymástól való távolság-mátrixa (régi útikönyvekben volt ilyen, de persze nem légvonalban, mint nálam):

select
c1.CITY_NAME ‘Kiinduló város’,
c2.CITY_NAME ‘Cél város’,
cast(c1.geom.STStartPoint().STDistance(c2.geom) as int) ‘Távolság [m]’
from City c1
cross join
City c2
where c1.CNTRY_NAME = ‘Hungary’
and c2.CNTRY_NAME = ‘Hungary’
and c1.ID < c2.ID order by c1.CITY_NAME, c2.CNTRY_NAME [/source] [source='c'] Kiinduló város Cél város Távolság [m] ------------------------------ ------------------------------ ------------- Bekescsaba Kaposvar 256101 Bekescsaba Szekszard 186865 Bekescsaba Szeged 86001 Bekescsaba Pecs 231001 Budapest Szombathely 189239 Budapest Szekesfehervar 62993 Budapest Szolnok 91135 Budapest Veszprem 101121 Budapest Kecskemet 81505 Budapest Zalaegerszeg 186420 Budapest Bekescsaba 178882 Budapest Kaposvar 162033 Budapest Szekszard 133373 Budapest Szeged 161716 Budapest Pecs 173280 Debrecen Budapest 191815 Debrecen Szombathely 380340 Debrecen Szekesfehervar 246982 ... [/source] Milyen messze van egymástól a 10 legtávolabbi településpár? [source='sql'] select top 10 with ties c1.CITY_NAME 'Kiinduló város', c2.CITY_NAME 'Cél város', cast(c1.geom.STStartPoint().STDistance(c2.geom) as int) 'Távolság [m]' from City c1 cross join City c2 where c1.CNTRY_NAME = 'Hungary' and c2.CNTRY_NAME = 'Hungary' and c1.ID < c2.ID order by 3 desc [/source] [source='c'] Kiinduló város Cél város Távolság [m] ------------------------------ ------------------------------ ------------- Nyiregyhaza Szombathely 391756 Nyiregyhaza Zalaegerszeg 388659 Debrecen Szombathely 380340 Debrecen Zalaegerszeg 371748 Nyiregyhaza Kaposvar 346639 Szombathely Bekescsaba 346612 Nyiregyhaza Pecs 338276 Miskolc Zalaegerszeg 329734 Miskolc Szombathely 328254 Zalaegerszeg Bekescsaba 326055 [/source] Szemmel láthatóan Nyíregyháza a világ vége. Most némi alvás következik, aztán folytatjuk. :)

Could you hire me? Contact me if you like what I’ve done in this article and think I can create value for your company with my skills.