Question regarding the database of countries, regions and cities

Question

PHP

There are many such databases, I settled on https://github.com/x88/i18nGeoNamesDB.
As in all similar ones, such as KLADR , etc., it has the following complexity:
The entire database is built on 3 tables, countries - regions - cities, and the problem is that, for example, Moscow or St. Petersburg do not have a binding to the region, these are independent subjects of the federation, when I wrote something like this:
< select > Country
< select > Region
< select > City
... I again stepped on the same rake, I will not be able to get independent subjects of the federation in this situation either in one country.
Have you faced such a problem, how do you solve such a problem? I think it's a bad idea to group cities by country and region, it weighs more than 1 GB, it has millions of records...

Answer 1

The base was parsed by me from VKontakte. A bunch of country <-> city, regions are not indicated for all settlements. Now we are working on it, we plan to specify the regions for all settlements so that we can work on the link country - region - city.
It describes how you can parse yourself Article on habré

Answer 2

The base is crooked.

SELECT * FROM geodata._cities WHERE `country_id` = 1 AND `region_id` IS NULL;

We get 230 lines with a bunch of unnecessary options.

SELECT * FROM geodata._cities WHERE `country_id` = 1 AND `region_ru` IS NULL;

We get 39 lines with a bunch of unnecessary options.

SELECT * FROM geodata._cities WHERE `country_id` = 1 AND `region_ru` IS NULL AND  `region_id` IS NULL;

We get

+-------------------------------+---------+------------------------------+
| title_ru                      | area_ru | title_ru                     |
+-------------------------------+---------+------------------------------+
| Москва                        | NULL    | Москва                       |
| Девяткино, Ленинградская обл. | NULL    | Девяткино,Ленинградская обл. |
| Санкт-Петербург               | NULL    | Санкт-Петербург              |
+-------------------------------+---------+------------------------------+