V tomto článku navážeme na předchozí analýzu domén nejvyšší úrovně popisem takzvané regionální lokalizace, což je určení kraje, města či vysoké školy, ze které se návštěvník stránek připojuje. Krátce se zmíním také o zjištění poskytovatele připojení.

Hotovou aplikaci si můžete prohlédnout a vyzkoušet. V ukázkové verzi se provádí statistika přístupů stránek http://www.czechia.cz/help/.

Jediný způsob, jak provést regionální lokalizaci, spočívá ve hlubším zpracování doménové adresy počítače, o které jsem psal v předchozím článku. Celý postup je poměrně jednoduchý a podobá se například detekci prohlížeče. Doménovou adresu postupně porovnáváme s mnoha regulárními výrazy, které mohou označovat města, vysoké školy a řadu dalších subjektů.

Tabulka region

Vzhledem k tomu, že kromě měst budeme vytvářet i statistiku krajů, potřebujeme dvě vzájemně propojené databázové tabulky region a town. V tabulce region bude všech čtrnáct českých krajů, Slovensko a Vysoké školy, které na internetu tvoří poměrně velkou komunitu.

CREATE TABLE region (
  id int(11) NOT NULL,
  name varchar(50) NOT NULL,
  PRIMARY KEY (id)
);

  • id – unikátní označení každého kraje (primární klíč)
  • name – kraj

id name
0 Ostatní
1 Praha
2 Středočeský kraj
3 Jihočeský kraj
4 Plzeňský kraj
5 Karlovarský kraj
6 Ústecký kraj
7 Liberecký kraj
8 Královéhradecký kraj
9 Pardubický kraj
10 Vysočina
11 Jihomoravský kraj
12 Zlínský kraj
13 Olomoucký kraj
14 Moravskoslezský kraj
15 Vysoké školy
16 Slovensko

Tabulka town

V tabulce town budou informace o městech (okresech), vysokých školách a podobně. Vytvoříme ji pomocí tohoto SQL dotazu:

CREATE TABLE town (
  id int(11) NOT NULL,
  name varchar(70) NOT NULL,
  ip_regexp varchar(70) NOT NULL,
  region int(11) NOT NULL,
  PRIMARY KEY (id)
);

  • id – unikátní označení každého města, VŠ atd. (primární klíč)
  • name – město
  • ip_regexp – regulární výraz odpovídající danému městu
  • region – označení kraje (cizí klíč), obsahuje primární klíče z tabulky region

Soubor, ze kterého jsem při vytváření tabulky town vycházel, obsahuje několik stovek různých doménových adres. Pokud byste si ho chtěli prohlédnout a případně si seznam měst a dalších subjektů rozšířit, můžete si jej stáhnout. Kratičký výtah z tohoto souboru obsahuje následující tabulka.

ppp250.hk.worldonline.cz olcD001.nextra.cz
ppp96.brno.worldonline.cz ta005.velkemezirici.czech.net
ppp151.mb.worldonline.cz kxchav07.vse.cz
ostravaa-162.dialup.vol.cz a6-22.natur.cuni.cz
varya-61.dialup.vol.cz dial1-041.ba.nextra.sk
ustia-147.dialup.vol.cz diamant.sjf.tuke.sk
ppp250.hk.worldonline.cz olcD001.nextra.cz
ppp96.brno.worldonline.cz ta005.velkemezirici.czech.net
ppp151.mb.worldonline.cz kxchav07.vse.cz
ostravaa-162.dialup.vol.cz a6-22.natur.cuni.cz
varya-61.dialup.vol.cz dial1-041.ba.nextra.sk
ustia-147.dialup.vol.cz diamant.sjf.tuke.sk

Klíčová je v tabulce town položka ip_regexp, ve které jsou regulární výrazy detekující města či vysoké školy. Každý poskytovatel připojení používá v doménových adresách různé zkratky měst a krajů, proto se u některých měst objeví v položce ip_regexp hned několik regulárních výrazů oddělených metaznakem „|“. U delších názvů měst (například ostrava.worldonline.cz) stačí zapsat pouze název města (ostrava), u krátkých zkratek (například na.worldonline.cz) je dobré uvést celou adresu, aby nedošlo ke chybné detekci. V uvedeném příkladu by k ní mohlo dojít například u adresy neco.snake.cz.

V závěru tabulky town (od id 201) je několik největších poskytovatelů připojení, u kterých se detekce města, vzhledem k tvaru jejich doménových adres, nedá provést. Na samém konci (od id 301) je pak několik nejdůležitějších domén třetí úrovně. Tyto údaje se netýkají vlastní regionální lokalizace, jsou uvedeny spíše jako příklad, jak zjistit poskytovatele připojení, jehož služby návštěvník využívá, případně další subjekty, které navštěvují naše stránky.

id name ip_regexp region
0 Ostatní   0
1 Hradec Králové hk\.tiscali\.cz|hradec|hrk.*nextra\.cz 8
2 Brno brno|brn.*nextra\.cz 11
3 Mladá Boleslav mb\.tiscali\.cz|boleslav 2
4 Praha praha|pha\.gts\.cz|phu.*nextra\.cz 1
5 Ostrava ostrava|ova.*nextra\.cz 14
6 Kladno kladno 2
7 Tábor tabor 3
8 Karlovy Vary vary|kva.*nextra\.cz 5
9 Plzeň plzen|plz.*nextra\.cz 4
10 Pardubice pce\.tiscali\.cz|pardubice 9
11 České Budějovice cb\.tiscali\.cz|budejice 3
12 Opava opava 14
13 Třinec trinec 14
14 Ústí nad Labem usti.*vol\.cz|ul\.tiscali\.cz 6
15 Náchod na\.tiscali\.cz 8
16 Velké Meziříčí velkemezirici 10
17 Teplice teplice 6
18 Ústí nad Orlicí uo\.tiscali\.cz 9
19 Olomouc olomouc|olc.*nextra\.cz 13
20 Rychnov nad Kněžnou rychnov 8
21 Znojmo znojmo 11
22 Děčín decin 6
23 Přerov prerov 13
24 Liberec liberec|lbc.*nextra\.cz 7
25 Ždár nad Sázavou zdar 10
26 Příbram pribram 2
27 Chomutov chomutov 6
28 Písek pisek 3
29 Břeclav breclav 11
30 Kroměříž kromeriz 12
31 Zlín zlin 12
32 Polička policka 9
33 Sokolov sokolov 5
34 Havlíčkův Brod hb\.tiscali\.cz 10
35 Jablonec nad Nisou jablonec 7
36 Jihlava jihlava 10
37 Třebíč trebic 10
38 Kutná hora kutnahora 2
39 Trutnov trutnov 8
40 Chrudim chrudim 9
41 Šumperk sumperk 13
42 Krnov krnov 14
43 Cheb cheb 5
44 Kyjov kyjov 11
45 Brandýs nad Labem brandys 2
46 Prostějov prostejov 13
47 Sušice susice 4
48 Vsetín vsetin 12
49 VUT Brno vutbr\.cz 15
50 UK Praha cuni\.cz 15
51 ČVUT Praha cvut\.cz 15
52 VŠE Praha vse\.cz 15
53 VŠB TU Ostrava vsb\.cz 15
54 ZČU Plzeň zcu\.cz 15
55 MU Brno muni\.cz 15
57 Mendelova uni. Brno mendelu\.cz 15
58 JČU jcu\.cz 15
59 TU Liberec vslib\.cz 15
60 UHK uhk\.cz 15
61 Univerzita Pardubice upce\.cz 15
62 ČZU Praha czu\.cz 15
63 Uni. Palackého – Olomouc upol\.cz 15
64 Uni. J. E. Purkyně – UL ujep\.cz 15
65 VŠCHT Praha vscht\.cz 15
67 Ostravská univerzita osu\.cz 15
68 VŠ vojenská Vyškov vvs-pv\.cz 15
69 Vojenská akademie Brno vabo\.cz 15
70 Vojenská lék. ak. JEP – HK pmfhk\.cz 15
71 Slezská u. Opava slu\.cz 15
72 Košice ke\.nextra\.sk 16
73 Bratislava ba\.nextra\.sk 16
74 TU Košice tuke\.sk 16
75 Most most 6
76 Česká Lípa lipa 7
77 Uni. P. J. Šafárika Košice upjs\.sk 16
78 Uni. Komenského Bratislava uniba\.sk 16
79 Zvolen zv\.nextra\.sk 16
80 Trnava tt\.nextra\.sk 16
81 Žilina za\.nextra\.sk 16
82 Nitra nr\.nextra\.sk 16
83 Poprad po\.nextra\.sk 16
84 Trenčín tn\.nextra\.sk 16
85 Martin mt\.nextra\.sk 16
201 Contactel (cz) contactel\.cz 0
202 IOL (cz) iol\.cz 0
203 PVT Net (cz) pvtnet\.cz 0
204 CZ Com (cz) czcom\.cz 0
205 TELE2 (cz) tele2\.cz 0
206 Telecom (sk) telecom\.sk 0
207 Globtelnet (sk) globtelnet\.sk 0
208 PVT (sk) pvt\.sk 0
209 Kiwwi (sk) kiwwi\.sk 0
210 Mistral (UPC) mistral\.cz 0
211 Eurotel (GPRS) gprs.*eurotel\.cz 0
301 Ostatní CZ \.cz 0
302 Ostatní SK \.sk 0
303 Ostatní COM \.com 0
304 Ostatní NET \.net 0

Skript, který provede detekci města, je poměrně krátký a jednoduchý. Z předchozího článku připomenu, že doménová adresa se nachází v proměnné $ip_name. V SQL dotazu, který porovnává doménovou adresu s regulárními výrazy, použijeme příkaz REGEXP, jehož princip jsem se vysvětloval již v článku o detekci prohlížeče. Příkaz ORDER BY id je použit proto, že v tabulce town postupně přecházíme od konkrétnímu k obecnému (města -> poskytovatelé připojení -> domény třetí úrovně). Někdy se může stát, že doménová adresa odpovídá třeba dvěma záznamům. Z tohoto důvodu je použito omezení LIMIT 0,1. Proměnná $area, do které vložíme id města, bude součástí SQL dotazu, který do tabulky access uloží veškeré informace o každém přístupu.

// OBLAST
// odpovida domenova adresa nejakemu zaznamu v databazi ?
$area = 0;
if($ip_name != ‚0‘) {
  $query = mysql_query(„SELECT id FROM town WHERE ‚$ip_name‘ REGEXP ip_regexp ORDER BY id LIMIT 0,1“);
  if ($result = mysql_fetch_array($query))
    $area = $result[‚id‘];
}

Vyhodnocení

Statistiku zapíšeme do tabulky o třech sloupečcích – Oblast (město nebo kraj), Počet přístupů z této oblasti a Podíl přístupů z této oblasti na celkovém počtu přístupů. Statistika krajů a měst se od vyhodnocení jiných statistických údajů liší tím, že v prvním SQL dotazu, který zjišťuje počet přístupů z jednotlivých krajů, spojujeme tabulky region, town a access. Na první pohled může podmínka spojující tři tabulky vypadat krkolomně, ale pokud si uvědomíte vztahy mezi tabulkami (primární a cizí klíče) znázorněné už v prvním článku, je vše jasné. V druhém SQL dotazu, zjišťujícím počet přístupů z měst nacházejících se v daném kraji, nesmíme zapomenout na podmínku WHERE town.region = '$id', kde $id je právě zpracovávaný kraj. Další postup, tedy výpočet podílu a šířky obrázku, byl podrobně komentován v článku o detekci operačního systému.

// OBLAST
echo ‚<h2>Kraje, města, oblasti</h2>‘;
echo ‚<table width=“680″ cellspacing=“0″>‘;
echo ‚<tr><th>Oblast</th><th>Počet</th><th>Podíl</th></tr>‘;
// vybirame kraje podle jejich zastoupeni
$query = mysql_query(„SELECT region.id AS id, region.name AS name, count(region.id) AS count_it FROM access JOIN town ON (area = town.id) JOIN region ON (town.region = region.id) WHERE $sql_access_date GROUP BY region.id ORDER BY count_it DESC“);
$max = mysql_result($query, 0, ‚count_it‘); // pocet pristupu z nejvice zastoupeneho kraje
$move = mysql_data_seek($query, 0); // presuneme se znovu na zacatek
while($result = mysql_fetch_array($query)) {
  $rate = sprintf(‚%.2f‘, $result[‚count_it‘]/($pageviews/100)); // sirka obrazku znazornujiciho podil
  $width = round($result[‚count_it‘]/$max*380); // podil v procentech
  echo ‚<tr class=“tableheader“><td>‘ . $result[‚name‘] . ‚</td><td>‘ . $result[‚count_it‘] . ‚</td><td><img src=“1.gif“ width=“‚ . $width . ‚“ height=“8″ alt=““ /> ‚ . $rate . ‚ %</td></tr>‘;
  $id = $result[‚id‘]; // id prave zpracovavaneho kraje
  // vybirame mesta podle jejich zastoupeni
  $query2 = mysql_query(„SELECT town.name AS name, count(town.id) AS count_it FROM access JOIN town ON (area = town.id) WHERE town.region = ‚$id‘ AND $sql_access_date GROUP BY town.id ORDER BY count_it DESC“);
  while($result = mysql_fetch_array($query2)) {
    $width = round($result[‚count_it‘]/$max*380);// sirka obrazku
    $rate = sprintf(‚%.2f‘, $result[‚count_it‘]/($pageviews/100)); // podil v procentech
    echo ‚<tr><td>‘ . $result[‚name‘] . ‚</td><td>‘ . $result[‚count_it‘] . ‚</td><td><img src=“1.gif“ width=“‚ . $width . ‚“ height=“8″ alt=““ /> ‚ . $rate . ‚ %</td></tr>‘;
  }
}
echo „</table>\n\n“;

Pozn. red.: Tento článek vyšel poprvé 1. 8. 2002. Původní verze článku a k němu vedené diskuse jsou vám k dispozici v ZIP archivech.

Starší komentáře ke článku

Pokud máte zájem o starší komentáře k tomuto článku, naleznete je zde.

Žádný příspěvek v diskuzi

Odpovědět