tag:blogger.com,1999:blog-13323404233159685032024-02-08T08:07:53.559-08:00ProgrammingAnonymoushttp://www.blogger.com/profile/12719308322674698519noreply@blogger.comBlogger10125tag:blogger.com,1999:blog-1332340423315968503.post-59448088544956799562016-12-06T07:17:00.002-08:002016-12-06T07:19:08.894-08:00База данных по странам, регионам и городам.<div dir="ltr" style="text-align: left;" trbidi="on">
<a href="https://github.com/Legostaev/contry_region_city">База данных</a> по странам, регионам и городам содержит:
<br />
<br />
<table border="0" cellpadding="0" cellspacing="0">
<tbody>
<tr><td>Страны</td><td width="20"></td><td>218</td></tr>
<tr><td>Регионы</td><td width="20"></td><td>1611</td></tr>
<tr><td>Города</td><td width="20"></td><td>17287</td></tr>
</tbody></table>
<br />
<br />
<table border="0" cellpadding="0" cellspacing="0" style="width: 100%px;">
<thead>
<tr><td width="33%">Страна</td><td width="10"></td><td width="33%" align="center">Количество регионов</td><td width="10"></td><td width="33%" align="center">Количество городов</td></tr>
</thead>
<tbody>
<tr><td>Абхазия</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">10</td></tr>
<tr><td>Австралия</td><td width="10"></td><td align="center">9</td><td width="10"></td><td align="center">208</td></tr>
<tr><td>Австрия</td><td width="10"></td><td align="center">9</td><td width="10"></td><td align="center">186</td></tr>
<tr><td>Азербайджан</td><td width="10"></td><td align="center">3</td><td width="10"></td><td align="center">76</td></tr>
<tr><td>Албания</td><td width="10"></td><td align="center">12</td><td width="10"></td><td align="center">41</td></tr>
<tr><td>Алжир</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">10</td></tr>
<tr><td>Ангола</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">7</td></tr>
<tr><td>Ангуилья</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">2</td></tr>
<tr><td>Андорра</td><td width="10"></td><td align="center">8</td><td width="10"></td><td align="center">19</td></tr>
<tr><td>Антигуа и Барбуда</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">8</td></tr>
<tr><td>Антильские о-ва</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Аргентина</td><td width="10"></td><td align="center">24</td><td width="10"></td><td align="center">184</td></tr>
<tr><td>Армения</td><td width="10"></td><td align="center">12</td><td width="10"></td><td align="center">247</td></tr>
<tr><td>Арулько</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Афганистан</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">6</td></tr>
<tr><td>Багамские о-ва</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Бангладеш</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">2</td></tr>
<tr><td>Барбадос</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Бахрейн</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">2</td></tr>
<tr><td>Беларусь</td><td width="10"></td><td align="center">6</td><td width="10"></td><td align="center">153</td></tr>
<tr><td>Белиз</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Бельгия</td><td width="10"></td><td align="center">11</td><td width="10"></td><td align="center">203</td></tr>
<tr><td>Бенин</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Бермуды</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Болгария</td><td width="10"></td><td align="center">29</td><td width="10"></td><td align="center">371</td></tr>
<tr><td>Боливия</td><td width="10"></td><td align="center">8</td><td width="10"></td><td align="center">34</td></tr>
<tr><td>Босния/Герцеговина</td><td width="10"></td><td align="center">3</td><td width="10"></td><td align="center">26</td></tr>
<tr><td>Ботсвана</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">3</td></tr>
<tr><td>Бразилия</td><td width="10"></td><td align="center">21</td><td width="10"></td><td align="center">99</td></tr>
<tr><td>Британские Виргинские о-ва</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Бруней</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Буркина Фасо</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">2</td></tr>
<tr><td>Бурунди</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Бутан</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Валлис и Футуна о-ва</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Вануату</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">2</td></tr>
<tr><td>Великобритания</td><td width="10"></td><td align="center">17</td><td width="10"></td><td align="center">468</td></tr>
<tr><td>Венгрия</td><td width="10"></td><td align="center">23</td><td width="10"></td><td align="center">83</td></tr>
<tr><td>Венесуэла</td><td width="10"></td><td align="center">23</td><td width="10"></td><td align="center">72</td></tr>
<tr><td>Восточный Тимор</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Вьетнам</td><td width="10"></td><td align="center">6</td><td width="10"></td><td align="center">11</td></tr>
<tr><td>Габон</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">2</td></tr>
<tr><td>Гаити</td><td width="10"></td><td align="center">6</td><td width="10"></td><td align="center">6</td></tr>
<tr><td>Гайана</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Гамбия</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Гана</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Гваделупа</td><td width="10"></td><td align="center">2</td><td width="10"></td><td align="center">2</td></tr>
<tr><td>Гватемала</td><td width="10"></td><td align="center">11</td><td width="10"></td><td align="center">20</td></tr>
<tr><td>Гвинея</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">2</td></tr>
<tr><td>Гвинея-Бисау</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Германия</td><td width="10"></td><td align="center">16</td><td width="10"></td><td align="center">2080</td></tr>
<tr><td>Гернси о-в</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Гибралтар</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">6</td></tr>
<tr><td>Гондурас</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Гонконг</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Гренада</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Гренландия</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Греция</td><td width="10"></td><td align="center">51</td><td width="10"></td><td align="center">333</td></tr>
<tr><td>Грузия</td><td width="10"></td><td align="center">2</td><td width="10"></td><td align="center">66</td></tr>
<tr><td>Дания</td><td width="10"></td><td align="center">16</td><td width="10"></td><td align="center">318</td></tr>
<tr><td>Джерси о-в</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Джибути</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Доминиканская республика</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Египет</td><td width="10"></td><td align="center">8</td><td width="10"></td><td align="center">10</td></tr>
<tr><td>Замбия</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">3</td></tr>
<tr><td>Западная Сахара</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">2</td></tr>
<tr><td>Зимбабве</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">2</td></tr>
<tr><td>Израиль</td><td width="10"></td><td align="center">8</td><td width="10"></td><td align="center">71</td></tr>
<tr><td>Индия</td><td width="10"></td><td align="center">22</td><td width="10"></td><td align="center">63</td></tr>
<tr><td>Индонезия</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Иордания</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Ирак</td><td width="10"></td><td align="center">3</td><td width="10"></td><td align="center">3</td></tr>
<tr><td>Иран</td><td width="10"></td><td align="center">12</td><td width="10"></td><td align="center">16</td></tr>
<tr><td>Ирландия</td><td width="10"></td><td align="center">26</td><td width="10"></td><td align="center">131</td></tr>
<tr><td>Исландия</td><td width="10"></td><td align="center">11</td><td width="10"></td><td align="center">17</td></tr>
<tr><td>Испания</td><td width="10"></td><td align="center">52</td><td width="10"></td><td align="center">590</td></tr>
<tr><td>Италия</td><td width="10"></td><td align="center">100</td><td width="10"></td><td align="center">814</td></tr>
<tr><td>Йемен</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">2</td></tr>
<tr><td>Кабо-Верде</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Казахстан</td><td width="10"></td><td align="center">19</td><td width="10"></td><td align="center">251</td></tr>
<tr><td>Камбоджа</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Камерун</td><td width="10"></td><td align="center">4</td><td width="10"></td><td align="center">4</td></tr>
<tr><td>Канада</td><td width="10"></td><td align="center">13</td><td width="10"></td><td align="center">248</td></tr>
<tr><td>Катар</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Кения</td><td width="10"></td><td align="center">5</td><td width="10"></td><td align="center">12</td></tr>
<tr><td>Кипр</td><td width="10"></td><td align="center">2</td><td width="10"></td><td align="center">6</td></tr>
<tr><td>Кирибати</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">9</td></tr>
<tr><td>Китай</td><td width="10"></td><td align="center">30</td><td width="10"></td><td align="center">255</td></tr>
<tr><td>Колумбия</td><td width="10"></td><td align="center">19</td><td width="10"></td><td align="center">54</td></tr>
<tr><td>Коморские о-ва</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Конго (Brazzaville)</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">3</td></tr>
<tr><td>Конго (Kinshasa)</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Коста-Рика</td><td width="10"></td><td align="center">7</td><td width="10"></td><td align="center">32</td></tr>
<tr><td>Кот-д''Ивуар</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">2</td></tr>
<tr><td>Куба</td><td width="10"></td><td align="center">15</td><td width="10"></td><td align="center">69</td></tr>
<tr><td>Кувейт</td><td width="10"></td><td align="center">2</td><td width="10"></td><td align="center">2</td></tr>
<tr><td>Кука о-ва</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Кыргызстан</td><td width="10"></td><td align="center">5</td><td width="10"></td><td align="center">73</td></tr>
<tr><td>Лаос</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Латвия</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">57</td></tr>
<tr><td>Лесото</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Либерия</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Ливан</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">2</td></tr>
<tr><td>Ливия</td><td width="10"></td><td align="center">2</td><td width="10"></td><td align="center">2</td></tr>
<tr><td>Литва</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">81</td></tr>
<tr><td>Лихтенштейн</td><td width="10"></td><td align="center">6</td><td width="10"></td><td align="center">6</td></tr>
<tr><td>Люксембург</td><td width="10"></td><td align="center">4</td><td width="10"></td><td align="center">38</td></tr>
<tr><td>Маврикий</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Мавритания</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Мадагаскар</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">6</td></tr>
<tr><td>Македония</td><td width="10"></td><td align="center">24</td><td width="10"></td><td align="center">28</td></tr>
<tr><td>Малави</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Малайзия</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Мали</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">3</td></tr>
<tr><td>Мальдивские о-ва</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Мальта</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">40</td></tr>
<tr><td>Марокко</td><td width="10"></td><td align="center">2</td><td width="10"></td><td align="center">2</td></tr>
<tr><td>Мартиника о-в</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Мексика</td><td width="10"></td><td align="center">32</td><td width="10"></td><td align="center">170</td></tr>
<tr><td>Мозамбик</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">4</td></tr>
<tr><td>Молдова</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">61</td></tr>
<tr><td>Монако</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">7</td></tr>
<tr><td>Монголия</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">3</td></tr>
<tr><td>Мьянма (Бирма)</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">2</td></tr>
<tr><td>Мэн о-в</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">6</td></tr>
<tr><td>Намибия</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">3</td></tr>
<tr><td>Науру</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">3</td></tr>
<tr><td>Непал</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Нигер</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">5</td></tr>
<tr><td>Нигерия</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">2</td></tr>
<tr><td>Нидерланды (Голландия)</td><td width="10"></td><td align="center">12</td><td width="10"></td><td align="center">280</td></tr>
<tr><td>Никарагуа</td><td width="10"></td><td align="center">4</td><td width="10"></td><td align="center">7</td></tr>
<tr><td>Новая Зеландия</td><td width="10"></td><td align="center">14</td><td width="10"></td><td align="center">21</td></tr>
<tr><td>Новая Каледония о-в</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Норвегия</td><td width="10"></td><td align="center">20</td><td width="10"></td><td align="center">248</td></tr>
<tr><td>Норфолк о-в</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>О.А.Э.</td><td width="10"></td><td align="center">2</td><td width="10"></td><td align="center">2</td></tr>
<tr><td>Оман</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">3</td></tr>
<tr><td>Пакистан</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">3</td></tr>
<tr><td>Панама</td><td width="10"></td><td align="center">5</td><td width="10"></td><td align="center">13</td></tr>
<tr><td>Папуа Новая Гвинея</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">2</td></tr>
<tr><td>Парагвай</td><td width="10"></td><td align="center">5</td><td width="10"></td><td align="center">9</td></tr>
<tr><td>Перу</td><td width="10"></td><td align="center">23</td><td width="10"></td><td align="center">62</td></tr>
<tr><td>Питкэрн о-в</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Польша</td><td width="10"></td><td align="center">60</td><td width="10"></td><td align="center">325</td></tr>
<tr><td>Португалия</td><td width="10"></td><td align="center">21</td><td width="10"></td><td align="center">277</td></tr>
<tr><td>Пуэрто Рико</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">15</td></tr>
<tr><td>Реюньон</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Россия</td><td width="10"></td><td align="center">78</td><td width="10"></td><td align="center">2533</td></tr>
<tr><td>Руанда</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Румыния</td><td width="10"></td><td align="center">42</td><td width="10"></td><td align="center">264</td></tr>
<tr><td>Сальвадор</td><td width="10"></td><td align="center">6</td><td width="10"></td><td align="center">6</td></tr>
<tr><td>Самоа</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">2</td></tr>
<tr><td>Сан-Марино</td><td width="10"></td><td align="center">3</td><td width="10"></td><td align="center">4</td></tr>
<tr><td>Сан-Томе и Принсипи</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Саудовская Аравия</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">6</td></tr>
<tr><td>Свазиленд</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Святая Люсия</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Святой Елены о-в</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Северная Корея</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Сейшеллы</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Сен-Пьер и Микелон</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Сенегал</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Сент-Винсент и Гренадины</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Сент Китс и Невис</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Сербия</td><td width="10"></td><td align="center">3</td><td width="10"></td><td align="center">20</td></tr>
<tr><td>Сингапур</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Сирия</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Словакия</td><td width="10"></td><td align="center">7</td><td width="10"></td><td align="center">16</td></tr>
<tr><td>Словения</td><td width="10"></td><td align="center">3</td><td width="10"></td><td align="center">12</td></tr>
<tr><td>Соломоновы о-ва</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Сомали</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Судан</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">8</td></tr>
<tr><td>Суринам</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>США</td><td width="10"></td><td align="center">53</td><td width="10"></td><td align="center">1591</td></tr>
<tr><td>Сьерра-Леоне</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Таджикистан</td><td width="10"></td><td align="center">5</td><td width="10"></td><td align="center">58</td></tr>
<tr><td>Таиланд</td><td width="10"></td><td align="center">3</td><td width="10"></td><td align="center">3</td></tr>
<tr><td>Тайвань</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Танзания</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">4</td></tr>
<tr><td>Того</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Токелау о-ва</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Тонга</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Тринидад и Тобаго</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Тувалу</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Тунис</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Туркменистан</td><td width="10"></td><td align="center">5</td><td width="10"></td><td align="center">40</td></tr>
<tr><td>Туркс и Кейкос</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">2</td></tr>
<tr><td>Турция</td><td width="10"></td><td align="center">36</td><td width="10"></td><td align="center">37</td></tr>
<tr><td>Уганда</td><td width="10"></td><td align="center">2</td><td width="10"></td><td align="center">2</td></tr>
<tr><td>Узбекистан</td><td width="10"></td><td align="center">13</td><td width="10"></td><td align="center">108</td></tr>
<tr><td>Украина</td><td width="10"></td><td align="center">26</td><td width="10"></td><td align="center">765</td></tr>
<tr><td>Уругвай</td><td width="10"></td><td align="center">11</td><td width="10"></td><td align="center">20</td></tr>
<tr><td>Фарерские о-ва</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Фиджи</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">2</td></tr>
<tr><td>Филиппины</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">7</td></tr>
<tr><td>Финляндия</td><td width="10"></td><td align="center">7</td><td width="10"></td><td align="center">301</td></tr>
<tr><td>Франция</td><td width="10"></td><td align="center">93</td><td width="10"></td><td align="center">546</td></tr>
<tr><td>Французская Гвинея</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">3</td></tr>
<tr><td>Французская Полинезия</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Хорватия</td><td width="10"></td><td align="center">12</td><td width="10"></td><td align="center">32</td></tr>
<tr><td>Чад</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">3</td></tr>
<tr><td>Черногория</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">7</td></tr>
<tr><td>Чехия</td><td width="10"></td><td align="center">15</td><td width="10"></td><td align="center">116</td></tr>
<tr><td>Чили</td><td width="10"></td><td align="center">13</td><td width="10"></td><td align="center">63</td></tr>
<tr><td>Швейцария</td><td width="10"></td><td align="center">26</td><td width="10"></td><td align="center">222</td></tr>
<tr><td>Швеция</td><td width="10"></td><td align="center">22</td><td width="10"></td><td align="center">285</td></tr>
<tr><td>Шри-Ланка</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Эквадор</td><td width="10"></td><td align="center">13</td><td width="10"></td><td align="center">28</td></tr>
<tr><td>Экваториальная Гвинея</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Эритрея</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Эстония</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">39</td></tr>
<tr><td>Эфиопия</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">3</td></tr>
<tr><td>ЮАР</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Южная Корея</td><td width="10"></td><td align="center">17</td><td width="10"></td><td align="center">31</td></tr>
<tr><td>Южная Осетия</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">2</td></tr>
<tr><td>Ямайка</td><td width="10"></td><td align="center">1</td><td width="10"></td><td align="center">1</td></tr>
<tr><td>Япония</td><td width="10"></td><td align="center">38</td><td width="10"></td><td align="center">122</td></tr>
</tbody></table>
<br /></div>
Anonymoushttp://www.blogger.com/profile/12719308322674698519noreply@blogger.com0tag:blogger.com,1999:blog-1332340423315968503.post-71466536816273085302014-11-30T08:38:00.000-08:002014-11-30T08:38:16.332-08:00Устанавливаем GPS координаты для объектов КЛАДР<b>Устанавливаем модули:</b><br /><br />
<h3>pgsql-http</h3>
<ol>
<li>git clone https://github.com/pramsey/pgsql-http</li>
<li>sudo yum install libcurl-devel</li>
<li>sudo PATH=/usr/pgsql-9.3/bin/:$PATH make USE_PGXS=1 install</li>
</ol>
<h3>postgis2_93</h3>
<ol>
<li>sudo yum install postgis2_93</li>
</ol><br />
<b>Устанавливаем расширения в PostgreSQL</b><br />
<ol>
<li>sudo su - postgres</li>
<li>psql</li>
<li>\c YourDatabase</li>
<li>CREATE EXTENSION xml2;</li>
<li>CREATE EXTENSION http;</li>
<li>CREATE EXTENSION postgis;</li>
<li>CREATE EXTENSION postgis_topology;</li>
<li>CREATE EXTENSION fuzzystrmatch;</li>
<li>CREATE EXTENSION postgis_tiger_geocoder;</li>
</ol><br />
<b>Добавляем поле в таблицу W_LOCALITY</b><br />
<ol>
<li>ALTER TABLE W_LOCALITY ADD COORD POINT DEFAULT NULL;</li>
</ol><br />
<b>Создаем функцию для получения gps координат</b><br /><br />
<table><tr><td>CREATE OR REPLACE FUNCTION update_yandex_gps_coord(v_cnt integer)<br />
RETURNS integer AS $$<br />
DECLARE<table style="padding-left:30px"><tr><td>
v_res integer;</td></tr></table>
BEGIN<table style="padding-left:30px"><tr><td>
WITH T1 AS (<table style="padding-left:30px"><tr><td>
SELECT<table style="padding-left:30px"><tr><td>
ID</td></tr></table>
FROM<table style="padding-left:30px"><tr><td>
W_LOCALITY_TYPE</td></tr></table>
WHERE<table style="padding-left:30px"><tr><td>
LOWER(NAME)='дом'),</td></tr></table></td></tr></table>
T2 AS (<table style="padding-left:30px"><tr><td>
SELECT<table style="padding-left:30px"><tr><td>
W_LOCALITY.ID AS ID,</td></tr><td>
(SELECT<table style="padding-left:30px"><tr><td>
xpath(<table style="padding-left:30px"><tr><td>
'/xmlns1:ymaps/xmlns1:GeoObjectCollection/xmlns2:featureMember/xmlns1:GeoObject/xmlns2:Point/xmlns2:pos/text()'::varchar,</td></tr><tr><td>
"content"::xml,</td></tr><tr><td>
ARRAY[<table style="padding-left:30px"><tr><td>
ARRAY['xmlns1', 'http://maps.yandex.ru/ymaps/1.x'],</td></tr><tr><td>
ARRAY['xmlns2', 'http://www.opengis.net/gml']]) AS COORD</td></tr></table></td></tr></table></td></tr></table>
FROM<table style="padding-left:30px"><tr><td>
http_get(<table style="padding-left:30px"><tr><td>
concat(<table style="padding-left:30px"><tr><td>
'http://geocode-maps.yandex.ru/1.x/?geocode=',</td></tr><tr><td>
get_kladr_full_address(W_LOCALITY.ID)))) AS CONTENT</td></tr></table></td></tr></table></td></tr></table></td></tr></table>
FROM<table style="padding-left:30px"><tr><td>
W_LOCALITY, T1</td></tr></table>
WHERE<table style="padding-left:30px"><tr><td>
W_LOCALITY_TYPE_ID=T1.ID AND</td></tr><tr><td>
W_LOCALITY.COORD IS NULL</td></tr></table>
LIMIT v_cnt),</td></tr></table>
T3 AS (<table style="padding-left:30px"><tr><td>
SELECT<table style="padding-left:30px"><tr><td>
T2.ID AS ID,</td></tr><tr><td>
unnest(T2.CONTENT) AS COORD</td></tr></table>
FROM
T2),</td></tr></table>
T4 AS (<table style="padding-left:30px"><tr><td>
SELECT<table style="padding-left:30px"><tr><td>
ID,</td></tr><tr><td>
string_to_array(T3.COORD::varchar, ' ') AS COORD</td></tr></table>
FROM T3),</td></tr></table>
T5 AS (<table style="padding-left:30px"><tr><td>
SELECT<table style="padding-left:30px"><tr><td>
ID,</td></tr><tr><td>
POINT(<table style="padding-left:30px"><tr><td>
COORD[1]::double precision,</td></tr><tr><td>COORD[2]::double precision) AS COORD</td></tr></table></td></tr></table>
FROM<table style="padding-left:30px"><tr><td>
T4)</td></tr></table></td></tr></table>
UPDATE<table style="padding-left:30px"><tr><td>
W_LOCALITY</td></tr></table>SET<table style="padding-left:30px"><tr><td>
COORD=T6.COORD</td></tr></table> FROM<table style="padding-left:30px"><tr><td>
(SELECT ID, COORD FROM T5) T6</td></tr></table>WHERE<table style="padding-left:30px"><tr><td>
W_LOCALITY.ID=T6.ID;</td></tr></table><br />
GET DIAGNOSTICS v_res = ROW_COUNT;<br /><br />
RETURN v_res;</td></tr></table>
END;<br />
$$ LANGUAGE plpgsql;</td></tr></table><br />
<b>Запускаем функцию</b><br /><br />
SELECT * FROM update_yandex_gps_coord(10);<br /><br />
Обновляем первые найденные 10 объектов W_LOCALITY у которых W_LOCALITY.COORD IS NULL.<br /><br />
Настраиваем планировщик задач, для циклического вызова функции update_yandex_gps_coord.Anonymoushttp://www.blogger.com/profile/12719308322674698519noreply@blogger.com0tag:blogger.com,1999:blog-1332340423315968503.post-85970322099797639152014-11-30T00:09:00.000-08:002014-11-30T00:09:48.894-08:00Поиск полного адреса в КЛАДР<b>Зафиксируем формат поисковой строки.</b><br /><br />
<table><tr><td>S</td><td> -> </td><td>(([тип адреса] [значение адреса])|([значение адреса] [тип адреса]))O</td></tr><tr><td>O</td><td> -> </td><td>((,[тип адреса] [значение адреса])|(,[значение адреса] [тип адреса]))O|ε</td></tr></table><br />
Для примера выберем адрес: Московская обл, г Жуковский, ул Дугина, дом 10<br /><br />
<b>Создадим индексы</b>
<ol>
<li>CREATE UNIQUE INDEX W_LOCALITY_CODE_LEVEL_IDX ON <table style="padding-left:30px"><tr><td>W_LOCALITY(<table style="padding-left:30px"><tr><td>LOWER(NAME),</td></tr><tr><td>get_locality_level(KLADR_ID),</td></tr><tr><td>ID);</td></tr></table></td></tr></table></li>
<li>CREATE UNIQUE INDEX W_LOCALITY_PARENT_TYPE_NAME_IDX ON <table style="padding-left:30px"><tr><td>W_LOCALITY(<table style="padding-left:30px"><tr><td>PARENT_ID,</td></tr><tr><td>W_LOCALITY_TYPE_ID,</td></tr><tr><td>LOWER(NAME),</td></tr><tr><td>ID);</td></tr></table></td></tr></table></li>
<li>CREATE UNIQUE INDEX W_LOCALITY_NAME_IDX ON <table style="padding-left:30px"><tr><td>W_LOCALITY(<table style="padding-left:30px"><tr><td>LOWER(NAME),</td></tr><tr><td>ID);</td></tr></table></td></tr></table></li>
<li>CREATE UNIQUE INDEX W_LOCALITY_TYPE_IDX ON <table style="padding-left:30px"><tr><td>W_LOCALITY_TYPE(<table style="padding-left:30px"><tr><td>ID,</td></tr><tr><td>LOWER(NAME));</td></tr></table></td></tr></table></li>
</ol>
<b>Создадим функцию</b><br /><br />
CREATE OR REPLACE FUNCTION get_locality_level(v_code varchar)<br />
RETURNS integer AS $$<br />
DECLARE<table style="padding-left: 15px"><tr><td>
v_res integer := NULL;</td></tr></table>
BEGIN<table style="padding-left: 15px"><tr><td>
IF v_code IS NOT NULL THEN</td></tr><tr><td><table style="padding-left: 15px"><tr><td>
CASE<table style="padding-left: 15px"><tr><td>
WHEN char_length(v_code)=13 THEN<br />
BEGIN<table style="padding-left: 15px"><tr><td>
v_res := 0;<br /><br />
IF substring(v_code from 1 for 2)<>lpad('', 2, '0') THEN
<table style="padding-left: 15px"><tr><td>v_res := v_res + 1;</td></tr></table>
END IF;<br /><br />
IF substring(v_code from 3 for 3)<>lpad('', 3, '0') THEN
<table style="padding-left: 15px"><tr><td>v_res := v_res + 1;</td></tr></table>
END IF;<br /><br />
IF substring(v_code from 6 for 3)<>lpad('', 3, '0') THEN
<table style="padding-left: 15px"><tr><td>v_res := v_res + 1;</td></tr></table>
END IF;<br /><br />
IF substring(v_code from 9 for 3)<>lpad('', 3, '0') THEN
<table style="padding-left: 15px"><tr><td>v_res := v_res + 1;</td></tr></table>
END IF;</td></tr></table>
END;<br /><br />
WHEN char_length(v_code)=17 THEN<br />
BEGIN<table style="padding-left: 15px"><tr><td>
v_res := 0;<br /><br />
IF substring(v_code from 1 for 2)<>lpad('', 2, '0') THEN
<table style="padding-left: 15px"><tr><td>v_res := v_res + 1;</td></tr></table>
END IF;<br /><br />
IF substring(v_code from 3 for 3)<>lpad('', 3, '0') THEN
<table style="padding-left: 15px"><tr><td>v_res := v_res + 1;</td></tr></table>
END IF;<br /><br />
IF substring(v_code from 6 for 3)<>lpad('', 3, '0') THEN
<table style="padding-left: 15px"><tr><td>v_res := v_res + 1;</td></tr></table>
END IF;<br /><br />
IF substring(v_code from 9 for 3)<>lpad('', 3, '0') THEN
<table style="padding-left: 15px"><tr><td>v_res := v_res + 1;</td></tr></table>
END IF;<br /><br />
IF substring(v_code from 12 for 4)<>lpad('', 4, '0') THEN
<table style="padding-left: 15px"><tr><td>v_res := v_res + 1;</td></tr></table>
END IF;</td></tr></table>
END;<br /><br />
WHEN char_length(v_code)=19 THEN<br />
BEGIN<table style="padding-left: 15px"><tr><td>
v_res := 0;<br /><br />
IF substring(v_code from 1 for 2)<>lpad('', 2, '0') THEN
<table style="padding-left: 15px"><tr><td>v_res := v_res + 1;</td></tr></table>
END IF;<br /><br />
IF substring(v_code from 3 for 3)<>lpad('', 3, '0') THEN
<table style="padding-left: 15px"><tr><td>v_res := v_res + 1;</td></tr></table>
END IF;<br /><br />
IF substring(v_code from 6 for 3)<>lpad('', 3, '0') THEN
<table style="padding-left: 15px"><tr><td>v_res := v_res + 1;</td></tr></table>
END IF;<br /><br />
IF substring(v_code from 9 for 3)<>lpad('', 3, '0') THEN
<table style="padding-left: 15px"><tr><td>v_res := v_res + 1;</td></tr></table>
END IF;<br /><br />
IF substring(v_code from 12 for 4)<>lpad('', 4, '0') THEN
<table style="padding-left: 15px"><tr><td>v_res := v_res + 1;</td></tr></table>
END IF;<br /><br />
IF substring(v_code from 16 for 4)<>lpad('', 4, '0') THEN
<table style="padding-left: 15px"><tr><td>v_res := v_res + 1;</td></tr></table>
END IF;</td></tr></table>
END;<br /><br />
ELSE<table style="padding-left: 15px"><tr><td>
v_res := NULL;</td></tr></table></td></tr></table>
END CASE;</td></tr></table>
END IF;<br /><br />
RETURN v_res;</td></tr></table>
END;<br />
$$ LANGUAGE plpgsql IMMUTABLE;<br /><br />
<b>Запрос для поиска адреса</b><br /><br />
WITH RECURSIVE T1 AS (<table style="padding-left:30px"><tr><td>
select<table style="padding-left:30px"><tr><td>
TRIM(<table style="padding-left:10px"><tr><td>unnest(<table style="padding-left:10px"><tr><td>string_to_array(<table style="padding-left:10px"><tr><td>'Московская обл, г Жуковский, ул Дугина, дом 10', ','))) as PART),</td></tr></table></td></tr></table></td></tr></table></td></tr></table></td></tr></table>
T2 AS (<table style="padding-left:30px"><tr><td>
SELECT<table style="padding-left:30px"><tr><td>
row_number() over() as RN,</td></tr><tr><td>
string_to_array(PART, ' ') AS PART</td></tr></table>
FROM<table style="padding-left:30px"><tr><td>
T1</td></tr></table>
WHERE<table style="padding-left:30px"><tr><td>
array_length(string_to_array(PART, ' '), 1) = 2),</td></tr></table></td></tr></table>
T2_1 AS (<table style="padding-left:30px"><tr><td>
SELECT COUNT(*) AS CNT FROM T1),</td></tr></table>
T2_2 AS (<table style="padding-left:30px"><tr><td>
SELECT COUNT(*) AS CNT FROM T2),</td></tr></table>
T2_3 AS (<table style="padding-left:30px"><tr><td>
SELECT<table style="padding-left:30px"><tr><td>
T2.RN AS RN,</td></tr><tr><td>
W_LOCALITY.ID AS ID,</td></tr><tr><td>
W_LOCALITY_TYPE.NAME AS PREFIX,</td></tr><tr><td>
W_LOCALITY.NAME AS NAME,</td></tr><tr><td>
W_LOCALITY.PARENT_ID AS PARENT_ID</td></tr></table>
FROM<table style="padding-left:30px"><tr><td>
W_LOCALITY INNER JOIN </td></tr><tr><td>
W_LOCALITY_TYPE ON W_LOCALITY_TYPE.ID=W_LOCALITY.W_LOCALITY_TYPE_ID, T2, T2_1, T2_2</td></tr></table>
WHERE<table style="padding-left:30px"><tr><td>
get_locality_level(W_LOCALITY.KLADR_ID) < 3 AND</td></tr><tr><td>
((LOWER(W_LOCALITY_TYPE.NAME)=LOWER(TRIM(T2.PART[1])) AND</td></tr><tr><td>
LOWER(W_LOCALITY.NAME)=LOWER(TRIM(T2.PART[2]))) OR</td></tr><tr><td>
(LOWER(W_LOCALITY_TYPE.NAME)=LOWER(TRIM(T2.PART[2])) AND</td></tr><tr><td>
LOWER(W_LOCALITY.NAME)=LOWER(TRIM(T2.PART[1])))) AND</td></tr><tr><td>
T2_1.CNT=T2_2.CNT),</td></tr></table></td></tr></table>
T3 AS (<table style="padding-left:30px"><tr><td>
SELECT<table style="padding-left:30px"><tr><td>
T2_3.ID AS ID,</td></tr><tr><td>
T2_3.PREFIX AS PREFIX,</td></tr><tr><td>
T2_3.NAME AS NAME,</td></tr><tr><td>
T2_3.PARENT_ID AS PARENT_ID</td></tr></table>
FROM<table style="padding-left:30px"><tr><td>
T2_3),</td></tr></table></td></tr></table>
T4(ID, PARENT_ID, LEVEL) AS (<table style="padding-left:30px"><tr><td>
SELECT<table style="padding-left:30px"><tr><td>
ID,</td></tr><tr><td>
PARENT_ID,</td></tr><tr><td>
1 AS LEVEL</td></tr></table>
FROM<table style="padding-left:30px"><tr><td>
T3</td></tr></table>
UNION ALL<br />
SELECT<table style="padding-left:30px"><tr><td>
T4.ID AS ID,</td></tr><tr><td>
W_LOCALITY.PARENT_ID AS PARENT_ID,</td></tr><tr><td>
T4.LEVEL + 1 AS LEVEL</td></tr></table>
FROM<table style="padding-left:30px"><tr><td>
W_LOCALITY INNER JOIN</td></tr><tr><td>
T4 ON T4.PARENT_ID=W_LOCALITY.ID</td></tr></table>
WHERE<table style="padding-left:30px"><tr><td>
T4.PARENT_ID IS NOT NULL AND</td></tr><tr><td>
T4.PARENT_ID IN (SELECT ID FROM T3)),</td></tr></table></td></tr></table>
T5(ID, LEVEL) AS (<table style="padding-left:30px"><tr><td>
SELECT<table style="padding-left:30px"><tr><td>
ID,</td></tr><tr><td>
MAX(LEVEL) AS LEVEL</td></tr></table>
FROM<table style="padding-left:30px"><tr><td>
T4</td></tr></table>
WHERE<table style="padding-left:30px"><tr><td>
PARENT_ID IN (SELECT ID FROM T3)</td></tr></table>
GROUP BY ID),</td></tr></table>
T6 AS (<table style="padding-left:30px"><tr><td>
SELECT COUNT(*) AS CNT FROM T3),</td></tr></table>
T7 AS (<table style="padding-left:30px"><tr><td>
SELECT<table style="padding-left:30px"><tr><td>
T5.ID AS ID,</td></tr><tr><td>
T5.LEVEL AS LEVEL</td></tr></table>
FROM<table style="padding-left:30px"><tr><td>
W_LOCALITY INNER JOIN</td></tr><tr><td>
T5 ON T5.ID=W_LOCALITY.ID,</td></tr><tr><td>
T6</td></tr></table>
WHERE<table style="padding-left:30px"><tr><td>
(T6.CNT > 1 AND</td></tr><tr><td> (SELECT ID FROM T3 WHERE T3.ID=W_LOCALITY.PARENT_ID) IS NOT NULL) OR</td></tr><tr><td>
T6.CNT=1),</td></tr></table></td></tr></table>
T8 AS (<table style="padding-left:30px"><tr><td>
SELECT<table style="padding-left:30px"><tr><td>
ID,</td></tr><tr><td>
LEVEL</td></tr></table>
FROM<table style="padding-left:30px"><tr><td>
T7</td></tr></table>
WHERE<table style="padding-left:30px"><tr><td>
(SELECT ID FROM T4 WHERE PARENT_ID=T7.ID LIMIT 1) IS NULL),</td></tr></table></td></tr></table>
T9(ROOT_ID, ID, PARENT_ID, LEVEL) AS (<table style="padding-left:30px"><tr><td>
SELECT<table style="padding-left:30px"><tr><td>
T8.ID AS ROOT_ID,</td></tr><tr><td>
W_LOCALITY.ID AS ID,</td></tr><tr><td>
W_LOCALITY.PARENT_ID AS PARENT_ID,</td></tr><tr><td>
T8.LEVEL AS LEVEL</td></tr></table>
FROM<table style="padding-left:30px"><tr><td>
T8 INNER JOIN</td></tr><tr><td>
W_LOCALITY ON T8.ID=W_LOCALITY.ID</td></tr></table>
UNION ALL<br />
SELECT<table style="padding-left:30px"><tr><td>
T9.ROOT_ID AS ROOT_ID,</td></tr><tr><td>
W_LOCALITY.ID AS ID,</td></tr><tr><td>
W_LOCALITY.PARENT_ID AS PARENT_ID,</td></tr><tr><td>
T9.LEVEL - 1 AS LEVEL</td></tr></table>
FROM<table style="padding-left:30px"><tr><td>
W_LOCALITY INNER JOIN</td></tr><tr><td>
T9 ON W_LOCALITY.ID=T9.PARENT_ID</td></tr></table>
WHERE<table style="padding-left:30px"><tr><td>
T9.PARENT_ID IS NOT NULL),</td></tr></table></td></tr></table>
T9_1 AS (<table style="padding-left:30px"><tr><td>
SELECT<table style="padding-left:30px"><tr><td>
RN,</td></tr><tr><td>
PART</td></tr></table>
FROM<table style="padding-left:30px"><tr><td>
T2</td></tr></table>
WHERE<table style="padding-left:30px"><tr><td>
T2.RN NOT IN (SELECT RN FROM T2_3 INNER JOIN T9 ON T9.ID=T2_3.ID)),</td></tr></table></td></tr></table>
T9_2 AS (<table style="padding-left:30px"><tr><td>
SELECT<table style="padding-left:30px"><tr><td>
MAX(LEVEL) AS LEVEL</td></tr></table>
FROM<table style="padding-left:30px"><tr><td>
T9),</td></tr></table></td></tr></table>
T9_3 AS (<table style="padding-left:30px"><tr><td>
SELECT<table style="padding-left:30px"><tr><td>
T9.ROOT_ID AS ROOT_ID,</td></tr><tr><td>
ID AS ID,</td></tr><tr><td>
T9.LEVEL + 1 AS LEVEL</td></tr></table>
FROM<table style="padding-left:30px"><tr><td>
T9, T9_2</td></tr></table>
WHERE<table style="padding-left:30px"><tr><td>
T9.LEVEL=T9_2.LEVEL),</td></tr></table></td></tr></table>
T9_4 AS (<table style="padding-left:30px"><tr><td>
SELECT<table style="padding-left:30px"><tr><td>
T9_1.RN AS RN,</td></tr><tr><td>
T9_3.ROOT_ID AS ROOT_ID,</td></tr><tr><td>
W_LOCALITY.ID AS ID,</td></tr><tr><td>
W_LOCALITY.PARENT_ID AS PARENT_ID,</td></tr><tr><td>
T9_3.LEVEL AS LEVEL</td></tr></table>
FROM<table style="padding-left:30px"><tr><td>
W_LOCALITY INNER JOIN</td></tr><tr><td>
W_LOCALITY_TYPE ON W_LOCALITY_TYPE.ID=W_LOCALITY.W_LOCALITY_TYPE_ID INNER JOIN</td></tr><tr><td>
T9_3 ON W_LOCALITY.PARENT_ID=T9_3.ID, T9_1</td></tr></table>
WHERE<table style="padding-left:30px"><tr><td>
((LOWER(W_LOCALITY_TYPE.NAME)=LOWER(TRIM(T9_1.PART[1])) AND</td></tr><tr><td>
LOWER(W_LOCALITY.NAME)=LOWER(TRIM(T9_1.PART[2]))) OR</td></tr><tr><td>
(LOWER(W_LOCALITY_TYPE.NAME)=LOWER(TRIM(T9_1.PART[2])) AND</td></tr><tr><td>
LOWER(W_LOCALITY.NAME)=LOWER(TRIM(T9_1.PART[1]))))),</td></tr></table></td></tr></table>
T9_5 AS (<table style="padding-left:30px"><tr><td>
SELECT<table style="padding-left:30px"><tr><td>
T9.ROOT_ID AS ROOT_ID,</td></tr><tr><td>
T9.ID AS ID,</td></tr><tr><td>
T9.PARENT_ID AS PARENT_ID,</td></tr><tr><td>
T9.LEVEL AS LEVEL</td></tr></table>
FROM<table style="padding-left:30px"><tr><td>
T9</td></tr></table>
UNION ALL<br />
SELECT<table style="padding-left:30px"><tr><td>
T9_4.ROOT_ID AS ROOT_ID,</td></tr><tr><td>
T9_4.ID AS ID,</td></tr><tr><td>
T9_4.PARENT_ID AS PARENT_ID,</td></tr><tr><td>
T9_4.LEVEL AS LEVEL</td></tr></table>
FROM
T9_4),</td></tr></table>
T9_6 AS (<table style="padding-left:30px"><tr><td>
SELECT<table style="padding-left:30px"><tr><td>
RN,</td></tr><tr><td>
PART</td></tr></table>
FROM<table style="padding-left:30px"><tr><td>
T2</td></tr></table>
WHERE<table style="padding-left:30px"><tr><td>
T2.RN NOT IN (SELECT RN FROM T2_3 INNER JOIN T9 ON T9.ID=T2_3.ID)),</td></tr></table></td></tr></table>
T9_7 AS (<table style="padding-left:30px"><tr><td>
SELECT<table style="padding-left:30px"><tr><td>
T9_6.RN AS RN,</td></tr><tr><td>
T9_4.ROOT_ID AS ROOT_ID,</td></tr><tr><td>
W_LOCALITY.ID AS ID,</td></tr><tr><td>
W_LOCALITY.PARENT_ID AS PARENT_ID,</td></tr><tr><td>
T9_4.LEVEL + 1 AS LEVEL</td></tr></table>
FROM<table style="padding-left:30px"><tr><td>
W_LOCALITY INNER JOIN</td></tr><tr><td>
W_LOCALITY_TYPE ON W_LOCALITY_TYPE.ID=W_LOCALITY.W_LOCALITY_TYPE_ID INNER JOIN</td></tr><tr><td>
T9_4 ON W_LOCALITY.PARENT_ID=T9_4.ID, T9_6</td></tr></table>
WHERE<table style="padding-left:30px"><tr><td>
((LOWER(W_LOCALITY_TYPE.NAME)=LOWER(TRIM(T9_6.PART[1])) AND</td></tr><tr><td>
LOWER(W_LOCALITY.NAME)=LOWER(TRIM(T9_6.PART[2]))) OR</td></tr><tr><td>
(LOWER(W_LOCALITY_TYPE.NAME)=LOWER(TRIM(T9_6.PART[2])) AND</td></tr><tr><td>
LOWER(W_LOCALITY.NAME)=LOWER(TRIM(T9_6.PART[1]))))),</td></tr></table></td></tr></table>
T9_8 AS (<table style="padding-left:30px"><tr><td>
SELECT<table style="padding-left:30px"><tr><td>
T9_5.ROOT_ID AS ROOT_ID,</td></tr><tr><td>
T9_5.ID AS ID,</td></tr><tr><td>
T9_5.PARENT_ID AS PARENT_ID,</td></tr><tr><td>
T9_5.LEVEL AS LEVEL</td></tr></table>
FROM<table style="padding-left:30px"><tr><td>
T9_5</td></tr></table>
UNION ALL<br />
SELECT<table style="padding-left:30px"><tr><td>
T9_7.ROOT_ID AS ROOT_ID,</td></tr><tr><td>
T9_7.ID AS ID,</td></tr><tr><td>
T9_7.PARENT_ID AS PARENT_ID,</td></tr><tr><td>
T9_7.LEVEL AS LEVEL</td></tr></table>
FROM<table style="padding-left:30px"><tr><td>
T9_7),</td></tr></table></td></tr></table>
T9_9 AS (<table style="padding-left:30px"><tr><td>
SELECT<table style="padding-left:30px"><tr><td>
ROOT_ID,</td></tr><tr><td>
MAX(LEVEL) AS LEVEL</td></tr></table>
FROM<table style="padding-left:30px"><tr><td>
T9_8</td></tr></table>
GROUP BY ROOT_ID</td></tr></table>),
T9_10(ROOT_ID, ID, PARENT_ID, LEVEL) AS (
SELECT<table style="padding-left:30px"><tr><td>
T9_8.ROOT_ID AS ROOT_ID,</td></tr><tr><td>
T9_8.ID AS ID,</td></tr><tr><td>
T9_8.PARENT_ID AS PARENT_ID,</td></tr><tr><td>
T9_8.LEVEL AS LEVEL</td></tr></table>
FROM<table style="padding-left:30px"><tr><td>
T9_8 INNER JOIN</td></tr><tr><td>
T9_9 ON (T9_9.ROOT_ID=T9_8.ROOT_ID AND T9_9.LEVEL=T9_8.LEVEL)</td></tr></table>
UNION ALL<br />
SELECT<table style="padding-left:30px"><tr><td>
T9_10.ROOT_ID AS ROOT_ID,</td></tr><tr><td>
T9_8.ID AS ID,</td></tr><tr><td>
T9_8.PARENT_ID AS PARENT_ID,</td></tr><tr><td>
T9_8.LEVEL AS LEVEL</td></tr></table>
FROM<table style="padding-left:30px"><tr><td>
T9_10 INNER JOIN</td></tr><tr><td>
T9_8 ON (T9_10.PARENT_ID=T9_8.ID AND T9_10.ROOT_ID=T9_8.ROOT_ID)),</td></tr></table>
T10 AS (<table style="padding-left:30px"><tr><td>
SELECT<table style="padding-left:30px"><tr><td>
1 AS ID</td></tr></table>
FROM<table style="padding-left:30px"><tr><td>
T6</td></tr></table>
WHERE<table style="padding-left:30px"><tr><td>
(SELECT COUNT(*) FROM T9_10)=(SELECT COUNT(*) FROM T1))</td></tr></table></td></tr></table>
SELECT<table style="padding-left:30px"><tr><td>
T9_10.ROOT_ID as ROOT_ID,</td></tr><tr><td>
T9_10.ID AS ID,</td></tr><tr><td>
T9_10.PARENT_ID AS PARENT_ID,</td></tr><tr><td>
T9_10.LEVEL AS LEVEL</td></tr><tr><td>
W_LOCALITY_VIEW.NAME AS NAME,</td></tr><tr><td>
W_LOCALITY_VIEW.PREFIX AS PREFIX</td></tr></table>
FROM<table style="padding-left:30px"><tr><td>
T9_10 INNER JOIN W_LOCALITY_VIEW ON W_LOCALITY_VIEW.ID=T9_10.ID, T10</td></tr></table>
WHERE<table style="padding-left:30px"><tr><td>
T10.ID=1;</td></tr></table><br />
<b>Скорость выполнения - 33.126 мсек</b><br /><br />
<b>Размер таблиц:</b><br /><br />
<table style="padding-left: 15px"><tr><td>W_LOCALITY</td><td style="width:40px"></td><td>23,244,680</td></tr><tr><td>W_LOCALITY_TYPE</td><td style="width:40px"></td><td>114</td></tr></table><br />
<b>Вывод данных:</b><br /><br />
<table><tr><th>ROOT_ID</th><th>ID</th><th>PARENT_ID</th><th>LEVEL</th><th>NAME</th><th>PREFIX</th></tr><tr><td>65487</td><td>14696415</td><td>1037965</td><td>3</td><td>10</td><td>ДОМ</td></tr><tr><td>65487</td><td>1037965</td><td>65487</td><td>2</td><td>Дугина</td><td>ул</td></tr><tr><td>65487</td><td>65487</td><td>195836</td><td>1</td><td>Жуковский</td><td>г</td></tr><tr><td>65487</td><td>195836</td><td>NULL</td><td>0</td><td>Московская</td><td>обл</td></tr></table><br />
<a href="https://github.com/Legostaev/import_kladr_postgresql" title="Скрипты для импорта КЛАДР в PostgreSQL">Скрипты для импорта КЛАДР (github)</a>Anonymoushttp://www.blogger.com/profile/12719308322674698519noreply@blogger.com0tag:blogger.com,1999:blog-1332340423315968503.post-46467928399355909282014-11-28T22:56:00.003-08:002014-11-28T22:56:46.852-08:00Поиск подчиненных адресов в КЛАДРДля поиска будем использовать заданную <a href="http://programming-s3.blogspot.ru/2014/11/blog-post.html">структуру</a>.<br /><br />
<b>Создадим представление:</b><br /><br />
CREATE OR REPLACE VIEW W_LOCALITY_VIEW(ID, PREFIX, NAME, PARENT_ID) AS <table style="padding-left:30px"><tr><td>SELECT<table style="padding-left:30px"><tr><td>W_LOCALITY.ID AS ID,</td></tr><tr><td>W_LOCALITY_TYPE.NAME AS PREFIX,</td></tr><tr><td>W_LOCALITY.NAME AS NAME,</td></tr><tr><td>W_LOCALITY.PARENT_ID AS PARENT_ID</td></tr></table></td></tr><tr><td>FROM</td></tr><tr><td><table style="padding-left:30px"><tr><td>W_LOCALITY INNER JOIN</td></tr><tr><td>W_LOCALITY_TYPE ON W_LOCALITY_TYPE.ID=W_LOCALITY.W_LOCALITY_TYPE_ID;</td></tr></table></td></tr></table><br />
<b>Поисковые поля:</b>
<ol>
<li>PARENT_ID</li>
</ol>
<b>Возможные значения:</b>
<ol>
<li>PARENT_ID IS NULL - корневые объекты.</li>
<li>PARENT_ID = 'value' - заданный объект.</li>
</ol>
<b>Создадим индексы:</b>
<ol>
<li>CREATE UNIQUE INDEX W_LOCALITY_PARENT_ID_IDX on W_LOCALITY(ID, PARENT_ID);</li>
<li>CREATE UNIQUE INDEX W_LOCALITY_FPARENT_ID_IDX on W_LOCALITY(PARENT_ID, ID);</li>
<li>CREATE UNIQUE INDEX W_LOCALITY_NULL_PARENT_ID_IDX on W_LOCALITY(ID) WHERE PARENT_ID IS NULL;</li>
</ol>
<b>Поисковые запросы:</b>
<ol>
<li>Поиск корневых объектов: <br />set enable_seqscan = off;<br />
SELECT * FROM W_LOCALITY_VIEW WHERE PARENT_ID IS NULL;</li>
<li>Поиск дочерних объектов заданного объекта:<br />set enable_seqscan = off;<br />
SELECT * FROM W_LOCALITY_VIEW WHERE PARENT_ID=112562;</li>
</ol>
<b>Скорость обработки: </b><br /><br />
<table style="padding-left: 15px"><tr><td>Поиск корневых объектов</td><td>30.248 мсек</td></tr><tr><td>Поиск дочерних объектов заданного объекта</td><td>15.166 мсек</td></tr></table><br />
<b>Размер таблиц: </b><br /><br />
<table style="padding-left: 15px"><tr><td>W_LOCALITY</td><td>23,244,680</td></tr><tr><td>W_LOCALITY_TYPE</td><td>114</td></tr></table><br />
<b>Вывод данных</b><br />
<ol>
<li>SELECT * FROM W_LOCALITY_VIEW WHERE PARENT_ID IS NULL;<table cellspacing=15><tr><th>ID</th><th>PREFIX</th><th>NAME</th><th>PARENT_ID</th></tr><tr><td>1331</td><td>Респ</td><td>Калмыкия</td><td>NULL</td></tr><tr><td>1770</td><td>Респ</td><td>Марий Эл</td><td>NULL</td></tr><tr><td>2992</td><td>Респ</td><td>Удмуртская</td><td>NULL</td></tr><tr><td>3775</td><td>край</td><td>Алтайский</td><td>NULL</td></tr><tr><td>....</td><td>....</td><td>....</td><td>....</td></tr><tr><td>208565</td><td>г</td><td>Севастополь</td><td>NULL</td></tr></table></li>
<li>SELECT * FROM W_LOCALITY_VIEW WHERE PARENT_ID=112562;<table cellspacing=15><tr><th>ID</th><th>PREFIX</th><th>NAME</th><th>PARENT_ID</th></tr><tr><td>112563</td><td>г</td><td>Воронеж</td><td>112562</td></tr><tr><td>164621</td><td>г</td><td>Воронеж-45</td><td>112562</td></tr><tr><td>34380</td><td>г</td><td>Лиски</td><td>112562</td></tr><tr><td>8262</td><td>г</td><td>Нововоронеж</td><td>112562</td></tr><tr><td>....</td><td>....</td><td>....</td><td>....</td></tr><tr><td>8314</td><td>р-н</td><td>Калачеевский</td><td>112562</td></tr></table></li>
<li>SELECT * FROM W_LOCALITY_VIEW WHERE ID=112562<table cellspacing=15><tr><th>ID</th><th>PREFIX</th><th>NAME</th><th>PARENT_ID</th></tr><tr><td>112562</td><td>обл</td><td>Воронежская</td><td>NULL</td></tr></table></li>
</ol>
Anonymoushttp://www.blogger.com/profile/12719308322674698519noreply@blogger.com0tag:blogger.com,1999:blog-1332340423315968503.post-84321826938564559252014-11-23T01:06:00.000-08:002014-11-23T01:17:46.826-08:00Преобразование номеров домов КЛАДР в множествоВ <a href="http://programming-s3.blogspot.ru/2014/11/postgresql.html" title="Импорт КЛАДР в базу данных PostgreSQL">КЛАДР</a> номера домов представлены пятью способами.
<ol>
<li>Непосредственно номер дома (1А)</li>
<li>Последовательность номеров домов (1А,1В)</li>
<li>Диапазоном номеров домов (1-10)</li>
<li>Последовательность домов с четными номера (Ч(1-10))</li>
<li>Последовательность домов с нечетными номерами (Н(1-10))</li>
</ol>
<b>Преобразуем 1 и 2 представление в множество.</b><br /><br />SELECT<table style="padding-left:30px"><tr><td>CODE,</td></tr><tr><td>TYPE,</td></tr><tr><td>HOUSE</td></tr></table>FROM<table style="padding-left:30px"><tr><td>(SELECT<table style="padding-left:30px"><tr><td>unnest(string_to_array(HOUSE, ',')) as HOUSE,</td></tr><tr><td>(SELECT ID FROM W_LOCALITY_TYPE WHERE NAME=TYPE) as TYPE,</td></tr><tr><td>CODE as CODE</td></tr></table>FROM<table style="padding-left:30px"><tr><td>kladr_base.w_doma_tbl) T1</td></tr></table></td></tr></table>WHERE<table style="padding-left:30px"><tr><td>HOUSE!~'^[0-9]+-[0-9]+$' AND</td></tr><tr><td>HOUSE!~'^(Н|Ч)\([0-9]+-[0-9]+\)$';</td></tr></table><br />
<b>Преобразуем 3 представление в множество.</b><br /><br />
WITH RECURSIVE T1 as (<table style="padding-left:30px"><tr><td>SELECT<table style="padding-left:30px"><tr><td>row_number() over() as RN,</td></tr><tr><td>string_to_array(HOUSE, '-') as HOUSE,</td></tr><tr><td>(SELECT ID FROM W_LOCALITY_TYPE WHERE NAME=TYPE) as TYPE,</td></tr><tr><td>CODE</td></tr></table>FROM<table style="padding-left:30px"><tr><td>(SELECT<table style="padding-left:30px"><tr><td>unnest(string_to_array(HOUSE, ',')) as HOUSE,</td></tr><tr><td>TYPE AS TYPE,</td></tr><tr><td>CODE as CODE</td></tr></table>FROM<table style="padding-left:30px"><tr><td>kladr_base.w_doma_tbl) T10</td></tr></table></td></tr></table>WHERE<table style="padding-left:30px"><tr><td>HOUSE~'^[0-9]+-[0-9]+$'),</td></tr></table></td></tr></table>T2(RN, CODE, TYPE, HOUSE) AS (<table style="padding-left:30px"><tr><td>SELECT<table style="padding-left:30px"><tr><td>RN,</td></tr><tr><td>CODE,</td></tr><tr><td>TYPE,</td></tr><tr><td>HOUSE[1]::INT AS HOUSE</td></tr></table>FROM<table style="padding-left:30px"><tr><td>T1</td></tr></table>UNION ALL<br />SELECT<table style="padding-left:30px"><tr><td>T2.RN,</td></tr><tr><td>T2.CODE,</td></tr><tr><td>T2.TYPE,</td></tr><tr><td>T2.HOUSE + 1 AS HOUSE</td></tr></table>FROM<table style="padding-left:30px"><tr><td>T1 INNER JOIN T2 ON T2.RN=T1.RN</td></tr></table>WHERE<table style="padding-left:30px"><tr><td>T2.HOUSE >= T1.HOUSE[1]::INT AND</td></tr><tr><td>(T2.HOUSE + 1) <= T1.HOUSE[2]::INT)</td></tr></table></td></tr></table>SELECT<table style="padding-left:30px"><tr><td>CODE,</td></tr><tr><td>TYPE,</td></tr><tr><td>HOUSE</td></tr></table>FROM<table style="padding-left:30px"><tr><td>T2;</td></tr></table><br />
<b>Преобразуем 4 и 5 представление в множество.</b><br /><br />
WITH RECURSIVE T3 as (<table style="padding-left:30px"><tr><td>SELECT<table style="padding-left:30px"><tr><td>row_number() over() as RN,</td></tr><tr><td>string_to_array(substring(HOUSE from '[0-9]+-[0-9]+'), '-') as HOUSE,</td></tr><tr><td>(SELECT ID FROM W_LOCALITY_TYPE WHERE NAME=TYPE) as TYPE,</td></tr><tr><td>CODE</td></tr></table>FROM<table style="padding-left:30px"><tr><td>(SELECT<table style="padding-left:30px"><tr><td>unnest(string_to_array(HOUSE, ',')) as HOUSE,</td></tr><tr><td>TYPE as TYPE,</td></tr><tr><td>CODE as CODE</td></tr></table>FROM<table style="padding-left:30px"><tr><td>kladr_base.w_doma_tbl) T10</td></tr></table></td></tr></table>WHERE<table style="padding-left:30px"><tr><td>HOUSE~'^(Н|Ч)\([0-9]+-[0-9]+\)$'),</td></tr></table></td></tr></table>T4(RN, CODE, TYPE, HOUSE) AS (<table style="padding-left:30px"><tr><td>SELECT<table style="padding-left:30px"><tr><td>RN,</td></tr><tr><td>CODE,</td></tr><tr><td>TYPE,</td></tr><tr><td>HOUSE[1]::INT AS HOUSE</td></tr></table>FROM<table style="padding-left:30px"><tr><td>T3</td></tr></table>UNION ALL<br />SELECT<table style="padding-left:30px"><tr><td>T4.RN,</td></tr><tr><td>T4.CODE,</td></tr><tr><td>T4.TYPE,</td></tr><tr><td>T4.HOUSE + 2 AS HOUSE</td></tr></table>FROM<table style="padding-left:30px"><tr><td>T3 INNER JOIN T4 ON T4.RN=T3.RN</td></tr></table>WHERE<table style="padding-left:30px"><tr><td>T4.HOUSE >= T3.HOUSE[1]::INT AND</td></tr><tr><td>(T4.HOUSE + 2) <= T3.HOUSE[2]::INT)</td></tr></table></td></tr></table>
SELECT<table style="padding-left:30px"><tr><td>CODE,</td></tr><tr><td>TYPE,</td></tr><tr><td>HOUSE</td></tr></table>FROM<table style="padding-left:30px"><tr><td>T4;</td></tr></table><br />
<b>Объединим получившиеся решения.</b><br /><br />
WITH RECURSIVE T1 as (
<table style="padding-left:30px"><tr><td>SELECT<table style="padding-left:30px"><tr><td>row_number() over() as RN,</td></tr><tr><td>string_to_array(HOUSE, '-') as HOUSE,</td></tr><tr><td>(SELECT ID FROM W_LOCALITY_TYPE WHERE NAME=TYPE) as TYPE,</td></tr><tr><td>CODE</td></tr></table>FROM<table style="padding-left:30px"><tr><td>(SELECT<table style="padding-left:30px"><tr><td> unnest(string_to_array(HOUSE, ',')) as HOUSE,</td></tr><tr><td>TYPE AS TYPE,</td></tr><tr><td>CODE as CODE</td></tr></table>FROM<table style="padding-left:30px"><tr><td>kladr_base.w_doma_tbl) T10</td></tr></table></td></tr></table>WHERE<table style="padding-left:30px"><tr><td>HOUSE~'^[0-9]+-[0-9]+$'),</td></tr></table></td></tr></table>T2(RN, CODE, TYPE, HOUSE) AS (<table style="padding-left:30px"><tr><td>SELECT<table style="padding-left:30px"><tr><td>RN,</td></tr><tr><td>CODE,</td></tr><tr><td>TYPE,</td></tr><tr><td>HOUSE[1]::INT AS HOUSE</td></tr></table>FROM<table style="padding-left:30px"><tr><td>T1</td></tr></table>UNION ALL<br />SELECT<table style="padding-left:30px"><tr><td>T2.RN,</td></tr><tr><td>T2.CODE,</td></tr><tr><td>T2.TYPE,</td></tr><tr><td>T2.HOUSE + 1 AS HOUSE</td></tr></table>FROM<table style="padding-left:30px"><tr><td>T1 INNER JOIN T2 ON T2.RN=T1.RN</td></tr></table>WHERE<table style="padding-left:30px"><tr><td>T2.HOUSE >= T1.HOUSE[1]::INT AND</td></tr><tr><td>(T2.HOUSE + 1) <= T1.HOUSE[2]::INT),</td></tr></table></td></tr></table>T3 as (<table style="padding-left:30px"><tr><td>SELECT<table style="padding-left:30px"><tr><td>row_number() over() as RN,</td></tr><tr><td>string_to_array(substring(HOUSE from '[0-9]+-[0-9]+'), '-') as HOUSE,</td></tr><tr><td>(SELECT ID FROM W_LOCALITY_TYPE WHERE NAME=TYPE) as TYPE,</td></tr><tr><td>CODE</td></tr></table>FROM<table style="padding-left:30px"><tr><td>(SELECT<table style="padding-left:30px"><tr><td>unnest(string_to_array(HOUSE, ',')) as HOUSE,</td></tr><tr><td>TYPE as TYPE,</td></tr><tr><td>CODE as CODE</td></tr></table>FROM<table style="padding-left:30px"><tr><td>kladr_base.w_doma_tbl) T10</td></tr></table></td></tr></table>WHERE<table style="padding-left:30px"><tr><td>HOUSE~'^(Н|Ч)\([0-9]+-[0-9]+\)$'),</td></tr></table></td></tr></table>T4(RN, CODE, TYPE, HOUSE) AS (<table style="padding-left:30px"><tr><td>SELECT<table style="padding-left:30px"><tr><td>RN,</td></tr><tr><td>CODE,</td></tr><tr><td>TYPE,</td></tr><tr><td>HOUSE[1]::INT AS HOUSE</td></tr></table>FROM<table style="padding-left:30px"><tr><td>T3</td></tr></table>UNION ALL<br />SELECT<table style="padding-left:30px"><tr><td>T4.RN,</td></tr><tr><td>T4.CODE,</td></tr><tr><td>T4.TYPE,</td></tr><tr><td>T4.HOUSE + 2 AS HOUSE</td></tr></table>FROM<table style="padding-left:30px"><tr><td>T3 INNER JOIN T4 ON T4.RN=T3.RN</td></tr></table>WHERE<table style="padding-left:30px"><tr><td>T4.HOUSE >= T3.HOUSE[1]::INT AND</td></tr><tr><td>(T4.HOUSE + 2) <= T3.HOUSE[2]::INT)</td></tr></table></td></tr></table>SELECT<table style="padding-left:30px"><tr><td>CODE,</td></tr><tr><td>TYPE,</td></tr><tr><td>to_char(HOUSE, '99999')</td></tr></table>FROM<table style="padding-left:30px"><tr><td>T2</td></tr></table>UNION ALL<br />SELECT<table style="padding-left:30px"><tr><td>CODE,</td></tr><tr><td>TYPE,</td></tr><tr><td>to_char(HOUSE, '99999')</td></tr></table>FROM<table style="padding-left:30px"><tr><td>T4</td></tr></table>UNION ALL<br />SELECT<table style="padding-left:30px"><tr><td>CODE,</td></tr><tr><td>TYPE,</td></tr><tr><td>HOUSE</td></tr></table>FROM<table style="padding-left:30px"><tr><td>(SELECT<table style="padding-left:30px"><tr><td>unnest(string_to_array(HOUSE, ',')) as HOUSE,</td></tr><tr><td>(SELECT ID FROM W_LOCALITY_TYPE WHERE NAME=TYPE) as TYPE,</td></tr><tr><td>CODE as CODE</td></tr></table>FROM<table style="padding-left:30px"><tr><td>kladr_base.w_doma_tbl) T1</td></tr></table></td></tr></table>WHERE<table style="padding-left:30px"><tr><td>HOUSE!~'^[0-9]+-[0-9]+$' AND</td></tr><tr><td>HOUSE!~'^(Н|Ч)\([0-9]+-[0-9]+\)$';</td></tr></table>
Anonymoushttp://www.blogger.com/profile/12719308322674698519noreply@blogger.com0tag:blogger.com,1999:blog-1332340423315968503.post-42705511780789533372014-11-12T06:13:00.001-08:002014-11-23T11:08:23.450-08:00Структура для поиска полного адреса по КЛАДРДля хранения информации из КЛАДР необходимо создать три таблицы.
<ol>
<li>CREATE TABLE W_LOCALITY_TYPE(<br /><table style="padding-left:40px"><tr><td>ID</td><td width="10px"></td><td>bigserial NOT NULL PRIMARY KEY,</td></tr><tr><td>NAME</td><td width="10px"></td><td>character varying(64) DEFAULT NULL UNIQUE,</td></tr><tr><td>CREATE_DATE</td><td width="10px"></td><td>TIMESTAMP with time zone NOT NULL DEFAULT NOW());</td></tr></table></li>
<li>CREATE TABLE W_LOCALITY(<br /><table style="padding-left:40px"><tr><td>ID</td><td width="10px"></td><td>bigserial NOT NULL PRIMARY KEY,</td></tr><tr><td>PARENT_ID</td><td width="10px"></td><td>bigint,</td></tr><tr><td>NAME</td><td width="10px"></td><td> character varying(64) DEFAULT NULL,</td></tr><tr><td>W_LOCALITY_TYPE_ID</td><td width="10px"></td><td>bigint NOT NULL references W_LOCALITY_TYPE(ID),</td></tr><tr><td>KLADR_ID</td><td width="10px"></td><td>VARCHAR(19),</td></tr><tr><td>CREATE_DATE</td><td width="10px"></td><td>TIMESTAMP with time zone NOT NULL DEFAULT NOW())</td></tr></table></li>
<li>CREATE TABLE W_LOCALITY_ALIAS(<br /><table style="padding-left:40px"><tr><td>ID</td><td width="10px"></td><td>bigserial NOT NULL PRIMARY KEY,</td></tr><tr><td>W_LOCALITY_ID</td><td width="10px"></td><td>bigint NOT NULL references W_LOCALITY(ID),</td></tr><tr><td>KLADR_ID</td><td width="10px"></td><td>VARCHAR(19),</td></tr><tr><td>NAME</td><td width="10px"></td><td>character varying(64) NOT NULL,</td></tr><tr><td>CREATE_DATE</td><td width="10px"></td><td>TIMESTAMP with time zone NOT NULL DEFAULT NOW());</td></tr></table></li>
</ol>
<a href="http://programming-s3.blogspot.ru/2014/11/postgresql.html" title="Импорт КЛАДР в базу данных PostgreSQL ">Импортируем данные из КЛАДР в базу данных</a><br /><br />
<b>Для загрузки данных из КЛАДР в созданную структуру необходимо выполнить следующие команды:</b>
<ol>
<li>INSERT INTO W_LOCALITY_TYPE(NAME)<br /><table style="padding-left:20px"><tr><td>SELECT DISTINCT<br /> trim(TYPE)<br /> FROM<br /> KLADR_BASE.W_KLADR_TBL<br />UNION<br />SELECT<br /> trim(TYPE)<br />FROM<br /> KLADR_BASE.W_STREET_TBL<br /> UNION<br />SELECT<br /> trim(C1)<br />FROM<br /> KLADR_BASE.W_DOMA_TBL;</td></tr></table></li>
<li>INSERT INTO W_LOCALITY(<table style="padding-left:30px"><tr><td>NAME,</td></tr><tr><td>W_LOCALITY_TYPE_ID,</td></tr><tr><td>KLADR_ID)</td></tr></table>
SELECT<table style="padding-left:30px"><tr><td>NAME,</td></tr><tr><td>(SELECT<table style="padding-left:30px"><tr><td>ID</td></tr></table>FROM<table style="padding-left:30px"><tr><td>W_LOCALITY_TYPE</td></tr></table>WHERE<table style="padding-left:30px"><tr><td>NAME=TRIM(KLADR_BASE.W_KLADR_TBL.TYPE)) AS type_id,</td></tr></table></td></tr><tr><td>TRIM(CODE)</td></tr></table>
FROM<table style="padding-left:30px"><tr><td>(SELECT<table style="padding-left:30px"><tr><td>TRIM(substring(CODE FROM 1 FOR 11)) bc,</td></tr><tr><td>min(cast(substring(CODE FROM 12 FOR 2) as integer)) ac</td></tr></table> FROM<table style="padding-left:30px"><tr><td>KLADR_BASE.W_KLADR_TBL</td></tr></table> GROUP BY TRIM(substring(CODE FROM 1 FOR 11))) T1 INNER JOIN</td></tr><tr><td>KLADR_BASE.W_KLADR_TBL ON<table style="padding-left:30px"><tr><td>TRIM(KLADR_BASE.W_KLADR_TBL.CODE)=<table style="padding-left:30px"><tr><td>CONCAT(TRIM(T1.BC), TRIM(TO_CHAR(T1.AC, '09')));</td></tr></table></td></tr></table></td></tr></table></li>
<li>CREATE INDEX W_LOCALITY_KLADR_ID on W_LOCALITY(KLADR_ID);</li>
<li>INSERT INTO W_LOCALITY_ALIAS(<table style="padding-left:30px"><tr><td>NAME,</td></tr><tr><td>W_LOCALITY_ID,</td></tr><tr><td>KLADR_ID)</td></tr></table>SELECT<table style="padding-left:30px"><tr><td>NAME,</td></tr><tr><td>(SELECT<table style="padding-left:30px"><tr><td>ID</td></tr></table>FROM<table style="padding-left:30px"><tr><td>W_LOCALITY</td></tr></table>WHERE<table style="padding-left:30px"><tr><td>KLADR_ID=CONCAT(TRIM(T1.BC), TRIM(TO_CHAR(T1.AC, '09')))),</td></tr></table></td></tr><tr><td>TRIM(CODE)</td></tr></table>
FROM<table style="padding-left:30px"><tr><td>(SELECT<table style="padding-left:30px"><tr><td>substring(CODE FROM 1 FOR 11) bc,</td></tr><tr><td>min(cast(substring(CODE FROM 12 FOR 2) as integer)) ac</td></tr></table>FROM<table style="padding-left:30px"><tr><td>KLADR_BASE.W_KLADR_TBL</td></tr></table>GROUP BY substring(CODE FROM 1 FOR 11)) T1 INNER JOIN</td></tr><tr><td>KLADR_BASE.W_KLADR_TBL ON<table style="padding-left:30px"><tr><td>(TRIM(substring(KLADR_BASE.W_KLADR_TBL.CODE FROM 1 FOR 11))=<table style="padding-left:30px"><tr><td>TRIM(T1.BC) AND</td></tr></table></td></tr><tr><td>cast(substring(CODE FROM 12 FOR 2) as integer)<>T1.ac);</td></tr></table></td></tr></table>
</li>
<li>INSERT INTO W_LOCALITY(<table style="padding-left:30px"><tr><td>NAME,</td></tr><tr><td>W_LOCALITY_TYPE_ID,</td></tr><tr><td>KLADR_ID)</td></tr></table>
SELECT<table style="padding-left:30px"><tr><td>NAME,</td></tr><tr><td>(SELECT<table style="padding-left:30px"><tr><td>ID</td></tr></table>FROM<table style="padding-left:30px"><tr><td>W_LOCALITY_TYPE</td></tr></table>WHERE<table style="padding-left:30px"><tr><td>NAME=TRIM(KLADR_BASE.W_STREET_TBL.TYPE)) AS type_id,</td></tr></table></td></tr><tr><td>TRIM(CODE)</td></tr></table>
FROM<table style="padding-left:30px"><tr><td>(SELECT<table style="padding-left:30px"><tr><td>TRIM(substring(CODE FROM 1 FOR 15)) bc,</td></tr><tr><td>min(cast(substring(CODE FROM 16 FOR 2) as integer)) ac</td></tr></table>FROM<table style="padding-left:30px"><tr><td>KLADR_BASE.W_STREET_TBL</td></tr></table> GROUP BY TRIM(substring(CODE FROM 1 FOR 15))) T1 INNER JOIN</td></tr><tr><td>KLADR_BASE.W_STREET_TBL ON<table style="padding-left:30px"><tr><td>TRIM(KLADR_BASE.W_STREET_TBL.CODE)=<table style="padding-left:30px"><tr><td>CONCAT(TRIM(T1.BC), TRIM(TO_CHAR(T1.AC, '09')));</td></tr></table></td></tr></table></td></tr></table></li>
<li>INSERT INTO W_LOCALITY_ALIAS(<table style="padding-left:30px"><tr><td>NAME,</td></tr><tr><td>W_LOCALITY_ID,</td></tr><tr><td>KLADR_ID)</td></tr></table>
SELECT<table style="padding-left:30px"><tr><td>NAME,</td></tr><tr><td>(SELECT<table style="padding-left:30px"><tr><td>ID</td></tr></table>FROM<table style="padding-left:30px"><tr><td>W_LOCALITY</td></tr></table>WHERE<table style="padding-left:30px"><tr><td>KLADR_ID=CONCAT(TRIM(T1.BC), TRIM(TO_CHAR(T1.AC, '09')))),</td></tr></table></td></tr><tr><td>TRIM(CODE)</td></tr></table>
FROM<table style="padding-left:30px"><tr><td>(SELECT<table style="padding-left:30px"><tr><td>substring(CODE FROM 1 FOR 15) bc,</td></tr><tr><td>min(cast(substring(CODE FROM 16 FOR 2) as integer)) ac</td></tr></table>FROM<table style="padding-left:30px"><tr><td>KLADR_BASE.W_STREET_TBL</td></tr></table> GROUP BY substring(CODE FROM 1 FOR 15)) T1 INNER JOIN</td></tr><tr><td>KLADR_BASE.W_STREET_TBL ON <table style="padding-left:30px"><tr><td>(TRIM(substring(KLADR_BASE.W_STREET_TBL.CODE FROM 1 FOR 15))=<table style="padding-left:30px"><tr><td>TRIM(T1.BC) AND</td></tr></table> cast(substring(CODE FROM 16 FOR 2) as integer)<>T1.ac);</td></tr></table></td></tr></table></li>
<li>DROP INDEX W_LOCALITY_KLADR_ID;</li></ol>
<b>Создание иерархической подчиненности между объектами КЛАДР</b><br /><br />
Структура кодового обозначения в поле KLADR_ID:<br /><br />
<table><tr><td><b>Длина</b></td><td></td><td><b>Формат</b></td></tr><tr><td>13</td><td width="30px"></td><td>СС РРР ГГГ ППП КК</td></tr><tr><td>17</td><td></td><td>СС РРР ГГГ ППП УУУУ КК</td></tr><tr><td>19</td><td></td><td>СС РРР ГГГ ППП УУУУ ДДДД</td></tr></table><br /><br />
<table><tr><td><b>Код</b></td><td width="40px"></td><td><b>Описание</b></td></tr><tr><td>СС</td><td></td><td>код субъекта Российской Федерации (региона)</td></tr><tr><td>РРР</td><td></td><td>код района</td></tr><tr><td>ГГГ</td><td></td><td>код города</td></tr><tr><td>ППП</td><td></td><td>код населенного пункта</td></tr><tr><td>УУУУ</td><td></td><td>код улицы</td></tr><tr><td>ДДДД</td><td></td><td>порядковый номер позиции классификатора с обозначениями домов</td></tr><tr><td>КК</td><td></td><td>код актуальности наименования</td></tr></table><br /><br />
Связываем районы с регионами.<br /><br />
CREATE INDEX W_LOCALITY_KLADR_ID_TMP_IDX on W_LOCALITY(<table style="padding-left:30px"><tr><td>char_length(KLADR_ID),</td></tr><tr><td>substring(KLADR_ID FROM 3 FOR 9),</td></tr><tr><td>substring(KLADR_ID FROM 1 FOR 2));</td></tr></table><br />
UPDATE W_LOCALITY SET<table style="padding-left:30px"><tr><td> PARENT_ID=(</td></tr><tr><td><table style="padding-left:30px"><tr><td>SELECT<table style="padding-left:30px"><tr><td>ID</td></tr></table>FROM<table style="padding-left:30px"><tr><td>W_LOCALITY T1</td></tr></table>WHERE<table style="padding-left:30px"><tr><td>char_length(T1.KLADR_ID)=13 AND</td></tr><tr><td>substring(T1.KLADR_ID FROM 3 FOR 9)=lpad('', 9, '0') AND</td></tr><tr><td>substring(T1.KLADR_ID FROM 1 FOR 2)=<table style="padding-left:30px"><tr><td>substring(W_LOCALITY.KLADR_ID FROM 1 FOR 2))</td></tr></table></td></tr></table></td></tr></table></td></tr></table>
WHERE<table style="padding-left:30px"><tr><td>char_length(W_LOCALITY.KLADR_ID)=13 AND</td></tr><tr><td>substring(W_LOCALITY.KLADR_ID FROM 3 FOR 3)<>lpad('', 3, '0') AND</td></tr><tr><td>substring(W_LOCALITY.KLADR_ID FROM 6 FOR 6)=lpad('', 6, '0');</td></tr></table><br />
DROP INDEX W_LOCALITY_KLADR_ID_TMP_IDX;<br /><br />
Связываем города с районами.<br /><br />
CREATE INDEX W_LOCALITY_KLADR_ID_TMP_IDX on W_LOCALITY(<table style="padding-left:30px"><tr><td>char_length(KLADR_ID),</td></tr><tr><td>substring(KLADR_ID FROM 6 FOR 6),</td></tr><tr><td>substring(KLADR_ID FROM 1 FOR 5));</td></tr></table><br />
UPDATE W_LOCALITY SET<br /><table style="padding-left:30px"><tr><td>
PARENT_ID=(</td></tr><tr><td><table style="padding-left:30px"><tr><td>
SELECT<table style="padding-left:30px"><tr><td>ID</td></tr></table>FROM<table style="padding-left:30px"><tr><td>W_LOCALITY T1</td></tr></table>WHERE<table style="padding-left:30px"><tr><td>char_length(T1.KLADR_ID)=13 AND</td></tr><tr><td>substring(T1.KLADR_ID FROM 6 FOR 6)=lpad('', 6, '0') AND</td></tr><tr><td>substring(T1.KLADR_ID FROM 1 FOR 5)=<table style="padding-left:30px"><tr><td>substring(W_LOCALITY.KLADR_ID FROM 1 FOR 5))</td></tr></table></td></tr></table></td></tr></table></td></tr></table>
WHERE<table style="padding-left:30px"><tr><td>char_length(W_LOCALITY.KLADR_ID)=13 AND</td></tr><tr><td>substring(W_LOCALITY.KLADR_ID FROM 6 FOR 3)<>lpad('', 3, '0') AND</td></tr><tr><td>substring(W_LOCALITY.KLADR_ID FROM 9 FOR 3)=lpad('', 3, '0');</td></tr></table><br />
DROP INDEX W_LOCALITY_KLADR_ID_TMP_IDX;<br /><br />
Связываем населенные пункты с городами.<br /><br />
CREATE INDEX W_LOCALITY_KLADR_ID_TMP_IDX on W_LOCALITY(<table style="padding-left:30px"><tr><td>char_length(KLADR_ID),</td></tr><tr><td>substring(KLADR_ID FROM 9 FOR 3),</td></tr><tr><td>substring(KLADR_ID FROM 1 FOR 8));</td></tr></table><br />
UPDATE W_LOCALITY SET<table style="padding-left:30px"><tr><td>
PARENT_ID=(</td></tr><tr><td><table style="padding-left:30px"><tr><td>
SELECT<table style="padding-left:30px"><tr><td>ID</td></tr></table>FROM<table style="padding-left:30px"><tr><td>W_LOCALITY T1</td></tr></table>WHERE<table style="padding-left:30px"><tr><td>char_length(T1.KLADR_ID)=13 AND</td></tr><tr><td>substring(T1.KLADR_ID FROM 9 FOR 3)=lpad('', 3, '0') AND</td></tr><tr><td>substring(T1.KLADR_ID FROM 1 FOR 8)=<table style="padding-left:30px"><tr><td>substring(W_LOCALITY.KLADR_ID FROM 1 FOR 8))</td></tr></table></td></tr></table></td></tr></table></td></tr></table>
WHERE<table style="padding-left:30px"><tr><td>char_length(W_LOCALITY.KLADR_ID)=13 AND</td></tr><tr><td>substring(W_LOCALITY.KLADR_ID FROM 9 FOR 3)<>lpad('', 3, '0');</td></tr></table><br />
DROP INDEX W_LOCALITY_KLADR_ID_TMP_IDX;<br /><br />
Связываем улицы с населенными пунктами<br /><br />
CREATE INDEX W_LOCALITY_KLADR_ID_TMP_IDX on W_LOCALITY(<table style="padding-left:30px"><tr><td>char_length(KLADR_ID),</td></tr><tr><td>substring(KLADR_ID FROM 1 FOR 11));</td></tr></table><br />
UPDATE W_LOCALITY SET<table style="padding-left:30px"><tr><td>
PARENT_ID=(</td></tr><tr><td><table style="padding-left:30px"><tr><td>
SELECT<table style="padding-left:30px"><tr><td>ID</td></tr></table>FROM<table style="padding-left:30px"><tr><td>W_LOCALITY T1</td></tr></table>WHERE<table style="padding-left:30px"><tr><td>char_length(T1.KLADR_ID)=13 AND</td></tr><tr><td>substring(T1.KLADR_ID FROM 1 FOR 11)=<table style="padding-left:30px"><tr><td>substring(W_LOCALITY.KLADR_ID FROM 1 FOR 11))</td></tr></table></td></tr></table></td></tr></table></td></tr></table>
WHERE<table style="padding-left:30px"><tr><td>char_length(W_LOCALITY.KLADR_ID)=17 AND</td></tr><tr><td>substring(W_LOCALITY.KLADR_ID FROM 12 FOR 4)<>lpad('', 4, '0');</td></tr></table><br />
DROP INDEX W_LOCALITY_KLADR_ID_TMP_IDX;<br /><br />
Связываем улицы с городами<br /><br />
CREATE INDEX W_LOCALITY_KLADR_ID_TMP_IDX on W_LOCALITY(<table style="padding-left:30px"><tr><td>char_length(KLADR_ID),</td></tr><tr><td>substring(KLADR_ID FROM 1 FOR 15));</td></tr></table><br />
UPDATE W_LOCALITY SET<table style="padding-left:30px"><tr><td>
PARENT_ID=(</td></tr><tr><td><table style="padding-left:30px"><tr><td>
SELECT<table style="padding-left:30px"><tr><td>ID</td></tr></table>FROM<table style="padding-left:30px"><tr><td>W_LOCALITY T1</td></tr></table>WHERE<table style="padding-left:30px"><tr><td>char_length(T1.KLADR_ID)=17 AND</td></tr><tr><td>substring(T1.KLADR_ID FROM 1 FOR 15)=<table style="padding-left:30px"><tr><td>substring(W_LOCALITY.KLADR_ID FROM 1 FOR 15))</td></tr></table></td></tr></table></td></tr></table></td></tr></table>
WHERE<table style="padding-left:30px"><tr><td>char_length(W_LOCALITY.KLADR_ID)=19 AND</td></tr><tr><td>substring(W_LOCALITY.KLADR_ID FROM 16 FOR 4)<>lpad('', 4, '0');</td></tr></table><br />
DROP INDEX W_LOCALITY_KLADR_ID_TMP_IDX;<br /><br />
Связываем дома с улицами<br /><br />
CREATE INDEX W_LOCALITY_KLADR_ID_TMP_IDX on W_LOCALITY(<table style="padding-left:30px"><tr><td>char_length(KLADR_ID),</td></tr><tr><td>substring(KLADR_ID FROM 1 FOR 11));</td></tr></table><br />
UPDATE W_LOCALITY SET<table style="padding-left:30px"><tr><td>
PARENT_ID=(</td></tr><tr><td><table style="padding-left:30px"><tr><td>
SELECT<table style="padding-left:30px"><tr><td>ID</td></tr></table>FROM<table style="padding-left:30px"><tr><td>W_LOCALITY T1</td></tr></table>WHERE<table style="padding-left:30px"><tr><td>char_length(T1.KLADR_ID)=13 AND</td></tr><tr><td>substring(T1.KLADR_ID FROM 1 FOR 11)=<table style="padding-left:30px"><tr><td>substring(W_LOCALITY.KLADR_ID FROM 1 FOR 11))</td></tr></table></td></tr></table></td></tr></table></td></tr></table>WHERE<table style="padding-left:30px"><tr><td>char_length(W_LOCALITY.KLADR_ID)=19 AND</td></tr><tr><td>substring(W_LOCALITY.KLADR_ID FROM 16 FOR 4)<>lpad('', 4, '0') AND</td></tr><tr><td>PARENT_ID IS NULL;</td></tr></table><br />
DROP INDEX W_LOCALITY_KLADR_ID_TMP_IDX;<br /><br />
Создание поисковых индексов<br /><br />
CREATE UNIQUE INDEX W_LOCALITY_PARENT_ID_IDX on W_LOCALITY(ID, PARENT_ID);<br />
CREATE UNIQUE INDEX W_LOCALITY_NAME_IDX ON W_LOCALITY(NAME, ID);<br /><br />
Пример поискового запроса:<br /><br />
WITH RECURSIVE T(ID, LEVEL) AS (<table style="padding-left:30px"><tr><td>SELECT<table style="padding-left:30px"><tr><td>710::BIGINT AS ID,</td></tr><tr><td>1 AS LEVEL</td></tr></table>
</td></tr><tr><td>UNION ALL<br />SELECT<br /><table style="padding-left:30px"><tr><td>W_LOCALITY.PARENT_ID,</td></tr><tr><td>T.LEVEL + 1</td></tr></table>FROM<br /><table style="padding-left:30px"><tr><td>T INNER JOIN</td></tr><tr><td>W_LOCALITY ON T.ID=W_LOCALITY.ID</td></tr></table>WHERE<table style="padding-left:30px"><tr><td>W_LOCALITY.PARENT_ID IS NOT NULL)</td></tr></table>
</td></tr></table>
SELECT<table style="padding-left:30px"><tr><td>array_to_string(<br /><table style="padding-left:30px"><tr><td>array_agg(<table style="padding-left:30px"><tr><td>concat(<table style="padding-left:30px"><tr><td>trim(w_locality_type.name),</td></tr><tr><td>' ',</td></tr><tr><td>trim(w_locality.name))</td></tr></table></td></tr><tr><td>ORDER BY LEVEL DESC),</td></tr></table> ',')</td></tr></table>FROM<br />
<table style="padding-left:30px"><tr><td>T INNER JOIN</td></tr><tr><td>
W_LOCALITY ON T.ID=W_LOCALITY.ID INNER JOIN</td></tr><tr><td>
W_LOCALITY_TYPE ON W_LOCALITY_TYPE.ID=w_locality.w_locality_type_id;</td></tr></table></td></tr></table>
<a href="https://github.com/Legostaev/import_kladr_postgresql" title="Скрипты для импорта КЛАДР в PostgreSQL">Скрипты для импорта КЛАДР (github)</a>
Anonymoushttp://www.blogger.com/profile/12719308322674698519noreply@blogger.com0tag:blogger.com,1999:blog-1332340423315968503.post-63727851636366325462014-11-09T03:54:00.000-08:002014-11-09T03:54:54.895-08:00Run Windows WDM Driver<b>The first method is to use the CreateService API.</b>
<br /><br />
<b>The second method is to manually create these values in the following registry location:</b>
<ul><li>HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\<driver name></li></ul>
<table>
<tr style="font-size:15px">
<td><b>Registry Value</b></td><td width="10px"></td><td><b>Description</b></td><td width="10px"></td><td><b>Example</b></td>
</tr>
<tr>
<td>DisplayName</td><td width="10px"></td><td>Name of your driver for service list</td><td width="10px"></td><td>my_driver</td>
</tr>
<tr>
<td valign="top">ImagePath</td><td width="10px"></td><td>Full NT-style path to the driver<br />(or just the filename if it lives in system32\drivers).</td><td width="10px"></td><td>\??\C:\drv\drv.sys</td>
</tr>
<tr>
<td valign="top">Start</td><td width="10px"></td><td>How the driver can be started. For testing the recommended value is Demand (3), which indicates that the driver can only be started manually.
<br /><br />
The other start values - <b>Boot</b> (0), <b>System</b> (1) and <b>Auto</b> (2) instruct Windows to load the driver at various points during the system startup.
<br /><br />
The last value - <b>Disabled</b> (4) - prevents the driver from loading at all.</td><td width="10px"></td><td valign="top">3</td>
</tr>
<tr>
<td valign="top">Type</td><td width="10px"></td><td>The type of service. Basic kernel drivers must have a value of "1" here.</td><td width="10px"></td><td valign="top">1</td>
</tr>
</table>
<br />
<b>Starting a Driver</b>
<br /><br />
Once a driver has been registered as a system-service, it can be loaded (and unloaded) using the Service Control Manager.
<br />
<ol>
<li>programmatically: <br />StartService API call.</li>
<li>command-prompt: <br />net start my_driver</li>
</ol>
<b>Unload driver</b>
<ol>
<li>programmatically: <br />ControlService API call.</li>
<li>command-prompt: <br />net stop my_driver</li>
</ol>
Anonymoushttp://www.blogger.com/profile/12719308322674698519noreply@blogger.com0tag:blogger.com,1999:blog-1332340423315968503.post-68690044756347300222014-11-08T06:51:00.000-08:002014-11-08T06:51:29.936-08:00DBLink PostgreSQL To MySQL<ol>
<li>Устанавливаем git <br /><code>sudo yum install git</code></li>
<li>Скачиваем модуль sqlite_fdw <br /><code>wget git clone git://github.com/atris/JDBC_FDW.git</code></li>
<li>Создаем ссылку libjvm.so<br /><code>sudo ln -s /usr/lib/jvm/java-1.7.0/jre/lib/i386/server/libjvm.so /usr/lib/libjvm.so</code></li>
<li>Заходим в каталог JDBC_FDW<br /><code>cd JDBC_FDW</code></li>
<li>Устанавливаем модуль<br /><code>sudo PATH=/usr/pgsql-9.3/bin/:$PATH make USE_PGXS=1 install</code></li>
<li>Входим в систему из под пользователя postgres <br /><code>sudo su - postgres</code></li>
<li>Входим в postgresql <br /><code>psql</code></li>
<li>Выбираем базу данных <br /><code>\c YourDatabase</code></li>
<li>Создаем расширение <br /><code>CREATE EXTENSION jdbc_fdw;</code></li>
<li>Создаем сервер <br /><code>CREATE SERVER jdbc_mysql FOREIGN DATA WRAPPER jdbc_fdw</code><br /><code> OPTIONS(</code><br /><code> drivername 'com.mysql.jdbc.Driver',</code><br /><code> url 'jdbc:mysql://192.168.1.2:3306/your_data_base',</code><br /><code> querytimeout '15',</code><br /><code> jarfile 'path_to/mysql-connector-java-5.1.22-bin.jar',</code><br /><code> maxheapsize '600'</code><br /><code> );</code></li>
<li>Создаем мапинг пользователя <br /><code>CREATE USER MAPPING FOR PUBLIC SERVER jdbc_mysql</code><br /><code> OPTIONS(</code><br /><code> username 'root',</code><br /><code> password '123'</code><br /><code> );</code></li>
<li>Создаем внешнюю таблицу<br /><code>CREATE FOREIGN TABLE test_tbl (</code><br /><code> a integer,</code><br /><code> b text)</code><br /><code> SERVER jdbc_mysql OPTIONS(table 'm_test');</code></li>
</ol>Anonymoushttp://www.blogger.com/profile/12719308322674698519noreply@blogger.com0tag:blogger.com,1999:blog-1332340423315968503.post-32064241775794913962014-11-08T04:22:00.000-08:002014-11-08T04:22:48.241-08:00Импорт КЛАДР в базу данных SQLITE<b>Конвертация КЛАДР в формат sqlite.</b>
<ol>
<li>Скачиваем кладр с официального сайта<br /><code>wget www.gnivc.ru/html/gnivcsoft/KLADR/Base.7z</code></li>
<li>Устанавливаем архиватор 7z<br /><code>sudo yum install p7zip</code></li>
<li>Распаковываем архив <br /><code>7za e Base.7z</code></li>
<li>Устанавливаем sqlite<br /><code>sudo yum install sqlite</code></li>
<li>Устанавливаем sqlite3-dbf<br /><code>sudo yum install sqlite3-dbf</code></li>
<li>Запускаем sqlite3 <br /><code>sqlite3 my_kladr.db</code></li>
<li>В sqlite загружаем модуль libspatialite <br /><code>.load libspatialite.so.2</code></li>
<li>Импорт данных из КЛАДР в sqlite <br /><code>CREATE VIRTUAL TABLE virt_street_tbl USING VirtualDbf('/home/developer/kladr/STREET.DBF', 'CP866');</code><br /><code>CREATE VIRTUAL TABLE virt_socrbase_tbl USING VirtualDbf('/home/developer/kladr/SOCRBASE.DBF', 'CP866');</code><br /><code>CREATE VIRTUAL TABLE virt_kladr_tbl USING VirtualDbf('/home/developer/kladr/KLADR.DBF', 'CP866');</code><br /><code>CREATE VIRTUAL TABLE virt_flat_tbl USING VirtualDbf('/home/developer/kladr/FLAT.DBF', 'CP866');</code><br /><code>CREATE VIRTUAL TABLE virt_doma_tbl USING VirtualDbf('/home/developer/kladr/DOMA.DBF', 'CP866');</code><br /><code>CREATE VIRTUAL TABLE virt_altnames_tbl USING VirtualDbf('/home/developer/kladr/ALTNAMES.DBF', 'CP866');</code><br /><br /><code>create table street_tbl as select * from virt_street_tbl;</code><br /><code>create table socrbase_tbl as select * from virt_socrbase_tbl;</code><br /><code>create table kladr_tbl as select * from virt_kladr_tbl;</code><br /><code>create table flat_tbl as select * from virt_flat_tbl;</code><br /><code>create table doma_tbl as select * from virt_doma_tbl;</code><br /><code>create table altnames_tbl as select * from virt_altnames_tbl;</code><br /><br /><code>drop table virt_street_tbl;</code><br /><code>drop table virt_socrbase_tbl;</code><br /><code>drop table virt_kladr_tbl;</code><br /><code>drop table virt_flat_tbl;</code><br /><code>drop table virt_doma_tbl;</code><br /><code>drop table virt_altnames_tbl;
</code></li>
<li>Выходим из sqlite <br /><code>.exit</code></li>
</ol>
Резлуьтат: файл my_kladr.db содержит КЛАДР в формате sqlite.<br /><br />Anonymoushttp://www.blogger.com/profile/12719308322674698519noreply@blogger.com0tag:blogger.com,1999:blog-1332340423315968503.post-26979721478249967832014-11-08T04:14:00.000-08:002014-11-08T04:14:57.041-08:00Импорт КЛАДР в базу данных PostgreSQL<div dir="ltr" style="text-align: left;" trbidi="on">
<b>Конвертация КЛАДР в формат sqlite.</b>
<br />
<ol>
<li>Скачиваем кладр с официального сайта<br /><code>wget www.gnivc.ru/html/gnivcsoft/KLADR/Base.7z</code></li>
<li>Устанавливаем архиватор 7z<br /><code>sudo yum install p7zip</code></li>
<li>Распаковываем архив <br /><code>7za e Base.7z</code></li>
<li>Устанавливаем sqlite<br /><code>sudo yum install sqlite</code></li>
<li>Устанавливаем sqlite3-dbf<br /><code>sudo yum install sqlite3-dbf</code></li>
<li>Запускаем sqlite3 <br /><code>sqlite3 my_kladr.db</code></li>
<li>В sqlite загружаем модуль libspatialite <br /><code>.load libspatialite.so.2</code></li>
<li>Импорт данных из КЛАДР в sqlite <br /><code>CREATE VIRTUAL TABLE virt_street_tbl USING VirtualDbf('/home/developer/kladr/STREET.DBF', 'CP866');</code><br /><code>CREATE VIRTUAL TABLE virt_socrbase_tbl USING VirtualDbf('/home/developer/kladr/SOCRBASE.DBF', 'CP866');</code><br /><code>CREATE VIRTUAL TABLE virt_kladr_tbl USING VirtualDbf('/home/developer/kladr/KLADR.DBF', 'CP866');</code><br /><code>CREATE VIRTUAL TABLE virt_flat_tbl USING VirtualDbf('/home/developer/kladr/FLAT.DBF', 'CP866');</code><br /><code>CREATE VIRTUAL TABLE virt_doma_tbl USING VirtualDbf('/home/developer/kladr/DOMA.DBF', 'CP866');</code><br /><code>CREATE VIRTUAL TABLE virt_altnames_tbl USING VirtualDbf('/home/developer/kladr/ALTNAMES.DBF', 'CP866');</code><br /><br /><code>create table street_tbl as select * from virt_street_tbl;</code><br /><code>create table socrbase_tbl as select * from virt_socrbase_tbl;</code><br /><code>create table kladr_tbl as select * from virt_kladr_tbl;</code><br /><code>create table flat_tbl as select * from virt_flat_tbl;</code><br /><code>create table doma_tbl as select * from virt_doma_tbl;</code><br /><code>create table altnames_tbl as select * from virt_altnames_tbl;</code><br /><br /><code>drop table virt_street_tbl;</code><br /><code>drop table virt_socrbase_tbl;</code><br /><code>drop table virt_kladr_tbl;</code><br /><code>drop table virt_flat_tbl;</code><br /><code>drop table virt_doma_tbl;</code><br /><code>drop table virt_altnames_tbl;
</code></li>
<li>Выходим из sqlite <br /><code>.exit</code></li>
</ol>
Резлуьтат: файл my_kladr.db содержит КЛАДР в формате sqlite.<br />
<br />
<b>Подключение файла my_kladr.db к базе данных PostgreSQL</b>
<br />
<ol>
<li>Скачиваем модуль sqlite_fdw <br /><code>wget https://github.com/gleu/sqlite_fdw/archive/master.zip</code></li>
<li>Устанавливаем unzip <br /><code>sudo yum install unzip</code></li>
<li>Распаковываем архив <br /><code>unzip master</code></li>
<li>Заходим в каталог sqlite_fdw-master <br /><code>cd sqlite_fdw-master</code></li>
<li>Устанавливаем модуль<br /><code>sudo PATH=/usr/pgsql-9.3/bin/:$PATH make USE_PGXS=1 install</code></li>
<li>Входим в систему из под пользователя postgres <br /><code>sudo su - postgres</code></li>
<li>Входим в postgresql <br /><code>psql</code></li>
<li>Выбираем базу данных <br /><code>\c YourDatabase</code></li>
<li>Создаем расширение <br /><code>CREATE EXTENSION sqlite_fdw;</code></li>
<li>Создаем сервер <br /><code>CREATE SERVER sqlite_kladr_server</code><br /><code>
FOREIGN DATA WRAPPER sqlite_fdw</code><br /><code>
OPTIONS (database 'path_to_my_kladr.db');</code></li>
<li>Создаем схему <br /><code>create schema kladr;</code></li>
<li>Создаем внешние таблицы <br /><code>CREATE FOREIGN TABLE kladr.street_tbl(</code><br /><code> id bigint,</code><br /><code> name varchar, </code><br /><code> type varchar, </code><br /><code> code varchar,</code><br /><code> c2 varchar,</code><br /><code> c3 varchar,</code><br /><code> c4 varchar,</code><br /><code> c5 varchar)</code><br /><code>
SERVER sqlite_kladr_server</code><br /><code>
OPTIONS (table 'street_tbl');</code><br /><br /><code>CREATE FOREIGN TABLE kladr.socrbase_tbl(</code><br /><code> id bigint,</code><br /><code> id1 bigint,</code><br /><code> short_name varchar,</code><br /><code> full_name varchar,</code><br /><code> id3 bigint)</code><br /><code>
SERVER sqlite_kladr_server</code><br /><code>
OPTIONS (table 'socrbase_tbl');</code><br /><br /><code>CREATE FOREIGN TABLE kladr.kladr_tbl(</code><br /><code> id bigint,</code><br /><code> name varchar,</code><br /><code> type varchar,</code><br /><code> code varchar,</code><br /><code> c4 varchar,</code><br /><code> c5 varchar,</code><br /><code> c6 varchar,</code><br /><code> c7 bigint)</code><br /><code>
SERVER sqlite_kladr_server</code><br /><code>
OPTIONS (table 'kladr_tbl');</code><br /><br /><code>CREATE FOREIGN TABLE kladr.doma_tbl(</code><br /><code> id bigint,</code><br /><code> house varchar,</code><br /><code> c1 varchar,</code><br /><code> c2 varchar,</code><br /><code> c3 varchar,</code><br /><code> c4 varchar,</code><br /><code> c5 varchar,</code><br /><code> c6 varchar,</code><br /><code> c7 varchar)</code><br /><code>
SERVER sqlite_kladr_server</code><br /><code>
OPTIONS (table 'doma_tbl');</code><br /><br /><code>CREATE FOREIGN TABLE kladr.altnames_tbl(</code><br /><code> id bigint,</code><br /><code> code1 varchar,</code><br /><code> code2 varchar,</code><br /><code> c1 varchar)</code><br /><code>
SERVER sqlite_kladr_server</code><br /><code>
OPTIONS (table 'altnames_tbl');</code></li>
<li>Проверяем работу <br /><code>select * from kladr.kladr_tbl limit 10;</code></li>
</ol>
Результат: Кладр подключен к базе данных PostgreSQL.</div>
Anonymoushttp://www.blogger.com/profile/12719308322674698519noreply@blogger.com0