Soci blog

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

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

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

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
...

Milyen messze van egymástól a 10 legtávolabbi településpár?

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

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

Szemmel láthatóan Nyíregyháza a világ vége.

Most némi alvás következik, aztán folytatjuk. :)

This entry was posted on Wednesday, February 6th, 2008 at 12:56 am and is filed under Adatbázisok, SQL Server 2008, Szakmai élet. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.