Linux за българи: Форуми

В помощ на всеки, на който му трябва списък на населените места по области и общини. :)

От http://www.nsi.bg/EKATTE/Ekatte.htm съм конвертирал към почти нормализирана база данни.
Прилагам SQL dump-a на три таблици в UTF-8.
Поради ограниченията на форума файлът е прикачен като .ZIP, НО НЕ Е ZIP, а tar.gz ...

5298 населени места, 264 общини, 28 области
type = {
1 - град
2 - село
3 - манастир(!)
}

Браво и аз имам такава mysql база. Правих я от ГРАО http://www.grao.bg/tna/tab02.txt  http://www.grao.bg/tables.html с данни от 2007. Само че в мойта фигурират общо 5150 населени места: градове + села + манастири.

Къде е истината. къде се губят 150 селища? или е имало неточности през 2007. или е имало деление на селища и възникване на нови. или пък има селища фантоми. ;D

И кое трябва да се взима за по-авторитетен източник ГРАО или ЕКАТТЕ?

Нямам идея  ;D

Ако имам време ще се поровя да видя на какво се дължат разликите. Хубаво е тази тема да се направи sticky, щото е много полезна.

Редактирал съм списъка от населени места в оригиналната база на ЕКАТТЕ, защото има населени места, които се различават единствено по ЕКАТТЕ номера си - име, област, община и тип са еднакви.
Дублираните записи са:

Сега, ако някой каже и откъде да извадя пощенските кодове за тази база - златен е! :)

http://www.geonames.org/
Тези имат информация за всички населени места в света.
От там исках да допълвам базата със географска ширина и дължина, ама не ми стигна времето и нервите.

проблемът им е че нямат точно съотвествие на 'официалното' /каноничното име в бг. Например някакво село: от bg.zip изглежда така:
728355   Pechinska   Pechinska   4912,Pechinska,Petschinska,Печинска   41.4666667   24.9666667   P   PPL   BG   BG   57   SML16   SML16-19

Но 4-та колонка съсдържа 'Алтернативни имена' и там има  и кирилското изписване на името и ако трябва да се прави съотвествие трябва да се търси там. Но пък ако с такова име няколко села в бг, трябва да им се направи съотвествие и по териториялното деление, за да се определи дали точно това е населенето място.

Става много сложно и без някой по-интелигентен скрипт нама да мине :'(.

http://download.geonames.org/export/dump/
bg.zip e дъмп само за България от целят им архив llCountries.zip

'Алтернативни имена' ги има също и в отделна таблица alternateNames.zip
от там също може да се вземе пощенският код

например: ако в третата колонка има 'post' то в четвъртата е пощенският код
436680   728355      Petschinska   
2083895   728355   bg   Печинска   
2179503   728355   post   4912   

Не съм сигурен и за лиценза. пише че е CC, базата е свободна, но това означава ли че може части от нея да се използват в друга база.

Струва ми се, че в bg.zip има ЕКАТТЕ кодовете, така че може би ще стане по-лесно отколкото изглежда :) Макар, че не съм сигурен, че за всички населени места има пощенски кодове.

Ще се пробвам да направя една пълна база всички налични данни (дори и ненормализираните).

Струва ми се, че в bg.zip има ЕКАТТЕ кодовете.

Не разбирам, би ли пояснил с някой пример.

6461039   Gorna krepost   Gorna krepost   Gorna krepost,Горна крепост   41.725   25.475   A   ADM3   BG      44   KRZ16   KRZ16-08      0332   Europe/Sofia   2007-04-23

Това KRZ16 съответства точно на ЕКАТТЕ кода за общината:

GeSHi (SQL):
SELECT * FROM ekatte WHERE name = "Горна крепост"

=>

'ekatte', 'tcm', 'name', 'oblast', 'obshtina'
'16267', 'с.', 'Горна крепост', 'KRZ', 'KRZ16'

Хубаво е да има флаг в таблицата който да показва официален статут на селище в бг. Например нещо подобно 0- ЕКАТТЕ 1-грао 3-друго ..
Защото в geonames има много повече данни от град/село/манстир, които не са за изхвърляне. Там има названия на географски местности, върхове и разни други. Може даже да има названия на селища,куротрти които според бг официално да се водат към друго селище или под друго име.

Относно разликите между ЕКАТТЕ и грао възможно е да има много официлано заличени селища. Такова  заличаване може да продължи и за в бъдеще. Ако например някое село бъде заличено, то трябва само да му се смени флага на неофициален статут. А пък името му и къде се е намирало може да си стои.

Географската ширина/дължина също са много важни данни защото може да се определи местоположенито на селището по bitmap карта. Може също да се пресмята и разтоянито по права линия между две селища.

Нова база попълнена с EKATTE кодовете (цифровите и буквено-цифровите), геогр. ширина и дължина с точност до 7-мия знак след запетаята, както и пощенските кодове.

Тъй като съм почти сигурен, че има грешки, се надявам хората тук да помогнат с необходимите корекции :)

Прикаченият файл е необходимо да се преименува към .gz разширение (.т.е. да отпадне .zip разширението)

PS: Изчаквам администраторите да ми разрешат качване на файлове > 128К :)

Как успя да филтрираш информацията от geonames.org? През EKATTE кодовете ли?

За всеки EKATTE код ли има съответствен запис в geonames?

Пуснах няколко бота да дърпат инфо :)

geonames се отказах да го ползвам.

Използвах google maps и още един сайт с пощенски кодове.

Ето го и файлът (gzip формат):

Похвален труд!

Само една молба, ако може.
Засяга самият .sql файл в архива - ако може "multi-insert"-a да е разделен на много редове, имам предвид всеки запис да е на отделен ред:
(..,..,..),
(..,..,..),
(..,..,..);

Пробвам вече 10 различни неща в UBUNTU-то и нито едно не успява да отвори файл с толкова дълги редове.
Всички умират  :(

Исках просто да погледна файла и имената на таблиците, например, преди да пусна заявката да вмъкне всичко в базата данни.
Исках да вмъквам в съществуваща база и не ми се правеше нова.

vim?

GeSHi (SQL):
CREATE TABLE `country` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` char(25) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UQ_record` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
 
CREATE TABLE `province` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `country_id` int(10) UNSIGNED DEFAULT NULL,
  `name` char(25) NOT NULL,
  `EKATTE` char(3) NOT NULL,
  `EKATTE_code` char(5) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UQ_record` (`country_id`,`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8
 
CREATE TABLE `municipality` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `province_id` int(10) UNSIGNED DEFAULT NULL,
  `name` char(25) NOT NULL,
  `EKATTE` char(5) NOT NULL,
  `EKATTE_code` char(5) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UQ_record` (`province_id`,`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=265 DEFAULT CHARSET=utf8
 
CREATE TABLE `city` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `municipality_id` int(10) UNSIGNED DEFAULT NULL,
  `name` char(25) NOT NULL,
  `type` char(4) NOT NULL,
  `zip` char(4) DEFAULT NULL,
  `atitude` smallint(5) UNSIGNED NOT NULL,
  `latitude` float(10,7) DEFAULT NULL,
  `longitude` float(10,7) DEFAULT NULL,
  `population` bigint(20) UNSIGNED DEFAULT NULL,
  `EKATTE` char(8) NOT NULL,
  `EKATTE_code` char(5) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UQ_record` (`municipality_id`,`name`,`type`) USING BTREE,
  KEY `IX_EKATTE` (`EKATTE`),
  KEY `IX_zip` (`zip`),
  KEY `IX_name` (`name`),
  CONSTRAINT `FK_municipality_id` FOREIGN KEY (`municipality_id`) REFERENCES `municipality` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5303 DEFAULT CHARSET=utf8
 
 

Хм ... сега видях, че не съм създал foreign-keys constraint-ите за всички таблици :)

PS: Явно това е по-стара версия (като гледам съм я ползвал за експорт) - има разни тъпизми от сорта на "type varchar(4)" :) а то си е smallint