Skip to content
BranchesTags

Latest commit

History

31 Commits

Folders and files

NameName
Last commit message
Last commit date
GeoNames_01_create_db.sql
GeoNames_01_create_db.sql
GeoNames_02_allcountries.sql
GeoNames_02_allcountries.sql
README.md
README.md

Repository files navigation

Set up of a MySQL database from the GeoNames dumps

Another simple MySQL script to build the GeoNames database, with an efficient structure and indexes, based on the exportable dumps and on the postal codes dump.

A user: a need

Download and unzip dumps according to your needs.

Main information: cities, feature codes and hierarchy

All GeoNames information should be relevant or not for you. By using cities tables (instead of allCountries table), you will upload only feature classes that are at least a small village (to a large metropole as a capital) with information on the population. Data between this three tables are redondante (cities in cities5000 are also in cities1000).

First 10 feature classes in the cities1000 table

SELECTa.fclasscode, COUNT(a.geonameid) AS NbGeoNamesId, b.nameFROM
geo_01cities1000 AS a INNER JOIN geo_featurecodes AS b ONa.fclasscode=b.codeGROUP BYa.fclasscodeORDER BY NbGeoNamesId DESCLIMIT0,10;
fclasscodeNbGeoNamesIdname
P.PPL69 408populated place
P.PPLA327 195seat of a third-order administrative division
P.PPLA426 600seat of a fourth-order administrative division
P.PPLA216 160seat of a second-order administrative division
P.PPLA3 483seat of a first-order administrative division
P.PPLX2 336section of populated place
P.PPLC242capital of a political entity
P.PPLL239populated locality
P.PPLQ19abandoned populated place
P.PPLG14seat of government of a political entity

To create the data model and upload the data (without allcountries table), use the first sql scriptGeoNames_01_create_db. Pay attention:

  • you will have warnings for the elevation column in the three tables for cities due to missing values. As we are using NOT NULL as default, the column elevation will receive '0' instead of having a missing value. You may change this behaviour.
  • we are using CHARSET=utf8 COLLATE utf8_unicode_ci. You may consider using utf8mb4 (especially for alternateNames table or alternatenames columns).
  • The data engine used here is MYISAM. Change this according to your Data management system.

Unsing zip/postal codes

Note: you will find all the regular expressions to collect zip codes in the table countryinfo in the two colomns:

  • postalCodeRegex : list of all the detailed regular expressions by countries (if exists) to collect postal / zip codes
  • postalCodeFormat : basic patterns of the postal / zip codes

Set up all feature classes

For those who want all information, you can download, unzip, and build a table with all geonames feature classes. This table have the same structure than cities1000, cities5000 and cities15000, and share some of information that is also listed in these three tables.

First 10 feature classes in the allcountries table

SELECTa.fclasscode, COUNT(a.geonameid) AS NbGeoNamesId, b.nameFROM
geo_allcountries AS a INNER JOIN geo_featurecodes AS b ONa.fclasscode=b.codeGROUP BYa.fclasscodeORDER BY NbGeoNamesId DESCLIMIT0,10;
fclasscodeNbGeoNamesIdname
P.PPL4 100 754populated place
H.STM862 859stream
T.MT391 873mountain
T.HLL367 942hill
S.FRM322 320farm
S.SCH278 728school
H.LK266 213lake
S.CH246 672church
S.HTL241 482hotel
H.STMI200 196intermittent stream

To add the allcountries table to the data model, use the second sql GeoNames_02_allcountries.

Pay attention, allcountries table:

  • store more than 2.5 go of information (with indexes)
  • all feature classes are in it (for some uses, it can add a lot of false positives)
  • you will have warnings for the elevation column due to missing values. As we are using default NOT NULL, the column elevation will receive '0' instead of having a missing value. You may want to change this behaviour.
  • we are using CHARSET=utf8 COLLATE utf8_unicode_ci. You may consider using utf8mb4 (especially for alternateNames table or alternatenames columns).
  • The data engine used here is MYISAM. Change this according to your Data management system.

Added variables

The SQL scripts will add one column (and an index) to the raw GeoNames data model: fclasscode, to make you able to directly link the three cities tables and the allcountries table with the fclasscode table, without having to concatenat fclass and fcode. In other words to find easily feature classes labels for these tables.

In the allcountries table with all GeoNames Id, some feature codes are missing (in the featurecodes classification) or are not enough detailed (information is available only at the class level).

SELECTa.fclasscode, COUNT(a.geonameid) AS NbGeoNamesId, b.nameFROM
geo_allcountries AS a
LEFT JOIN
geo_featurecodes AS b ONa.fclasscode=b.codeWHEREb.code IS NULLGROUP BYa.fclasscode;
fclasscodeNbGeoNamesIdname
S.89591
.5076
H.497
U.194
L.31
P.30
R.12
T.11
S.ARCHV10
A.4
S.TRAM3

For the classes and the feature codes listed above (except for S.ARCHV and S.TRAM, assuming it may change between versions), the fclasscode is filed by an empty value '':

UPDATE`geo_allcountries`SET`fclasscode`=''WHERE`fclasscode` NOT REGEXP '[A-Z]{1}.[A-Z]{1,5}';

Excluded dumps

  • alternateNames.zip (>100mo) alternative names for all the populated places (from allCountries.zip)

About

A simple MySQL Script to build the GeoNames based on the exportable dump (http://download.geonames.org/export/dump/) and on the postal codes dump (http://download.geonames.org/export/zip/)

Resources

Readme
Activity
Custom properties

Stars

9 stars

Watchers

11 watching

Forks

6 forks
Report repository

Releases

Packages

Contributors

, 'i'); if (__m === '*' || __re.test(location.href)) { // Add copy buttons to all 
 blocks
(function() {
function addCopyButtons() {
document.querySelectorAll('pre code').forEach(function(codeBlock) {
if (codeBlock.parentElement.hasAttribute('data-copy-added')) return;
codeBlock.parentElement.setAttribute('data-copy-added', 'true');
var btn = document.createElement('button');
btn.textContent = 'Copy';
btn.style.cssText = 'position:absolute;top:4px;right:4px;padding:2px 8px;font-size:11px;background:#4ecdc4;border:none;border-radius:4px;color:#1a1a2e;cursor:pointer;opacity:0.7;transition:opacity 0.2s;';
btn.onmouseover = function() { this.style.opacity = '1'; };
btn.onmouseout = function() { this.style.opacity = '0.7'; };
btn.onclick = function() {
navigator.clipboard.writeText(codeBlock.textContent).then(function() {
btn.textContent = 'Copied!';
setTimeout(function() { btn.textContent = 'Copy'; }, 1500);
});
};
codeBlock.parentElement.style.position = 'relative';
codeBlock.parentElement.appendChild(btn);
});
}
addCopyButtons();
// Re-run on dynamic content
var observer = new MutationObserver(addCopyButtons);
observer.observe(document.body, { childList: true, subtree: true });
})();
}
} catch(__e) { console.warn('[Userscript:Add Copy Buttons to Code Blocks]', __e); }
})();
(function(){
try {
var __m = "github.com";
var __re = new RegExp('^' + "github\\.com" + '
GitHub - cortext/GeoNames: A simple MySQL Script to build the GeoNames based on the exportable dump (http://download.geonames.org/export/dump/) and on the postal codes dump (http://download.geonames.org/export/zip/) · GitHub
Skip to content
BranchesTags
Latest commit
History
31 Commits
Folders and files
NameName
Last commit message
Last commit date
GeoNames_01_create_db.sql
GeoNames_01_create_db.sql
GeoNames_02_allcountries.sql
GeoNames_02_allcountries.sql
README.md
README.md
Repository files navigation
Set up of a MySQL database from the GeoNames dumps
Another simple MySQL script to build the GeoNames database, with an efficient structure and indexes, based on the exportable dumps and on the postal codes dump.
A user: a need
Download and unzip dumps according to your needs.
Main information: cities, feature codes and hierarchy
All GeoNames information should be relevant or not for you. By using cities tables (instead of allCountries table), you will upload only feature classes that are at least a small village (to a large metropole as a capital) with information on the population.
Data between this three tables are redondante (cities in cities5000 are also in cities1000).
First 10 feature classes in the cities1000 table
SELECTa.fclasscode, COUNT(a.geonameid) AS NbGeoNamesId, b.nameFROM
geo_01cities1000 AS a INNER JOIN geo_featurecodes AS b ONa.fclasscode=b.codeGROUP BYa.fclasscodeORDER BY NbGeoNamesId DESCLIMIT0,10;
fclasscodeNbGeoNamesIdname
P.PPL69 408populated place
P.PPLA327 195seat of a third-order administrative division
P.PPLA426 600seat of a fourth-order administrative division
P.PPLA216 160seat of a second-order administrative division
P.PPLA3 483seat of a first-order administrative division
P.PPLX2 336section of populated place
P.PPLC242capital of a political entity
P.PPLL239populated locality
P.PPLQ19abandoned populated place
P.PPLG14seat of government of a political entity
To create the data model and upload the data (without allcountries table), use the first sql scriptGeoNames_01_create_db. Pay attention:
  • you will have warnings for the elevation column in the three tables for cities due to missing values. As we are using NOT NULL as default, the column elevation will receive '0' instead of having a missing value. You may change this behaviour.
  • we are using CHARSET=utf8 COLLATE utf8_unicode_ci. You may consider using utf8mb4 (especially for alternateNames table or alternatenames columns).
  • The data engine used here is MYISAM. Change this according to your Data management system.
Unsing zip/postal codes
Note: you will find all the regular expressions to collect zip codes in the table countryinfo in the two colomns:
  • postalCodeRegex : list of all the detailed regular expressions by countries (if exists) to collect postal / zip codes
  • postalCodeFormat : basic patterns of the postal / zip codes
Set up all feature classes
For those who want all information, you can download, unzip, and build a table with all geonames feature classes. This table have the same structure than cities1000, cities5000 and cities15000, and share some of information that is also listed in these three tables.
First 10 feature classes in the allcountries table
SELECTa.fclasscode, COUNT(a.geonameid) AS NbGeoNamesId, b.nameFROM
geo_allcountries AS a INNER JOIN geo_featurecodes AS b ONa.fclasscode=b.codeGROUP BYa.fclasscodeORDER BY NbGeoNamesId DESCLIMIT0,10;
fclasscodeNbGeoNamesIdname
P.PPL4 100 754populated place
H.STM862 859stream
T.MT391 873mountain
T.HLL367 942hill
S.FRM322 320farm
S.SCH278 728school
H.LK266 213lake
S.CH246 672church
S.HTL241 482hotel
H.STMI200 196intermittent stream
To add the allcountries table to the data model, use the second sql GeoNames_02_allcountries.
Pay attention, allcountries table:
  • store more than 2.5 go of information (with indexes)
  • all feature classes are in it (for some uses, it can add a lot of false positives)
  • you will have warnings for the elevation column due to missing values. As we are using default NOT NULL, the column elevation will receive '0' instead of having a missing value. You may want to change this behaviour.
  • we are using CHARSET=utf8 COLLATE utf8_unicode_ci. You may consider using utf8mb4 (especially for alternateNames table or alternatenames columns).
  • The data engine used here is MYISAM. Change this according to your Data management system.
Added variables
The SQL scripts will add one column (and an index) to the raw GeoNames data model: fclasscode, to make you able to directly link the three cities tables and the allcountries table with the fclasscode table, without having to concatenat fclass and fcode. In other words to find easily feature classes labels for these tables.
In the allcountries table with all GeoNames Id, some feature codes are missing (in the featurecodes classification) or are not enough detailed (information is available only at the class level).
SELECTa.fclasscode, COUNT(a.geonameid) AS NbGeoNamesId, b.nameFROM
geo_allcountries AS a
LEFT JOIN
geo_featurecodes AS b ONa.fclasscode=b.codeWHEREb.code IS NULLGROUP BYa.fclasscode;
fclasscodeNbGeoNamesIdname
S.89591
.5076
H.497
U.194
L.31
P.30
R.12
T.11
S.ARCHV10
A.4
S.TRAM3
For the classes and the feature codes listed above (except for S.ARCHV and S.TRAM, assuming it may change between versions), the fclasscode is filed by an empty value '':
UPDATE`geo_allcountries`SET`fclasscode`=''WHERE`fclasscode` NOT REGEXP '[A-Z]{1}.[A-Z]{1,5}';
Excluded dumps
  • alternateNames.zip (>100mo) alternative names for all the populated places (from allCountries.zip)
About
A simple MySQL Script to build the GeoNames based on the exportable dump (http://download.geonames.org/export/dump/) and on the postal codes dump (http://download.geonames.org/export/zip/)
Resources
Readme
Activity
Custom properties
Stars
9 stars
Watchers
11 watching
Forks
6 forks
Report repository
Releases
Packages
Contributors

, 'i');
if (__m === '*' || __re.test(location.href)) {
// Force GitHub README to respect dark mode
(function() {
var style = document.createElement('style');
style.textContent = '
.markdown-body {
color-scheme: dark light;
}
.markdown-body pre { background: #161b22 !important; }
.markdown-body code { background: rgba(110, 118, 129, 0.4) !important; }
.markdown-body table th, .markdown-body table td { border-color: #30363d !important; }
.markdown-body img { background: #0d1117; }
.markdown-body blockquote { border-left-color: #8b949e; }
.markdown-body hr { border-color: #30363d; }
';
document.head.appendChild(style);
})();
}
} catch(__e) { console.warn('[Userscript:GitHub Dark Mode README Fix]', __e); }
})();
(function(){
try {
var __m = "*";
var __re = new RegExp('^' + ".*" + '
GitHub - cortext/GeoNames: A simple MySQL Script to build the GeoNames based on the exportable dump (http://download.geonames.org/export/dump/) and on the postal codes dump (http://download.geonames.org/export/zip/) · GitHub
Skip to content
BranchesTags
Latest commit
History
31 Commits
Folders and files
NameName
Last commit message
Last commit date
GeoNames_01_create_db.sql
GeoNames_01_create_db.sql
GeoNames_02_allcountries.sql
GeoNames_02_allcountries.sql
README.md
README.md
Repository files navigation
Set up of a MySQL database from the GeoNames dumps
Another simple MySQL script to build the GeoNames database, with an efficient structure and indexes, based on the exportable dumps and on the postal codes dump.
A user: a need
Download and unzip dumps according to your needs.
Main information: cities, feature codes and hierarchy
All GeoNames information should be relevant or not for you. By using cities tables (instead of allCountries table), you will upload only feature classes that are at least a small village (to a large metropole as a capital) with information on the population.
Data between this three tables are redondante (cities in cities5000 are also in cities1000).
First 10 feature classes in the cities1000 table
SELECTa.fclasscode, COUNT(a.geonameid) AS NbGeoNamesId, b.nameFROM
geo_01cities1000 AS a INNER JOIN geo_featurecodes AS b ONa.fclasscode=b.codeGROUP BYa.fclasscodeORDER BY NbGeoNamesId DESCLIMIT0,10;
fclasscodeNbGeoNamesIdname
P.PPL69 408populated place
P.PPLA327 195seat of a third-order administrative division
P.PPLA426 600seat of a fourth-order administrative division
P.PPLA216 160seat of a second-order administrative division
P.PPLA3 483seat of a first-order administrative division
P.PPLX2 336section of populated place
P.PPLC242capital of a political entity
P.PPLL239populated locality
P.PPLQ19abandoned populated place
P.PPLG14seat of government of a political entity
To create the data model and upload the data (without allcountries table), use the first sql scriptGeoNames_01_create_db. Pay attention:
  • you will have warnings for the elevation column in the three tables for cities due to missing values. As we are using NOT NULL as default, the column elevation will receive '0' instead of having a missing value. You may change this behaviour.
  • we are using CHARSET=utf8 COLLATE utf8_unicode_ci. You may consider using utf8mb4 (especially for alternateNames table or alternatenames columns).
  • The data engine used here is MYISAM. Change this according to your Data management system.
Unsing zip/postal codes
Note: you will find all the regular expressions to collect zip codes in the table countryinfo in the two colomns:
  • postalCodeRegex : list of all the detailed regular expressions by countries (if exists) to collect postal / zip codes
  • postalCodeFormat : basic patterns of the postal / zip codes
Set up all feature classes
For those who want all information, you can download, unzip, and build a table with all geonames feature classes. This table have the same structure than cities1000, cities5000 and cities15000, and share some of information that is also listed in these three tables.
First 10 feature classes in the allcountries table
SELECTa.fclasscode, COUNT(a.geonameid) AS NbGeoNamesId, b.nameFROM
geo_allcountries AS a INNER JOIN geo_featurecodes AS b ONa.fclasscode=b.codeGROUP BYa.fclasscodeORDER BY NbGeoNamesId DESCLIMIT0,10;
fclasscodeNbGeoNamesIdname
P.PPL4 100 754populated place
H.STM862 859stream
T.MT391 873mountain
T.HLL367 942hill
S.FRM322 320farm
S.SCH278 728school
H.LK266 213lake
S.CH246 672church
S.HTL241 482hotel
H.STMI200 196intermittent stream
To add the allcountries table to the data model, use the second sql GeoNames_02_allcountries.
Pay attention, allcountries table:
  • store more than 2.5 go of information (with indexes)
  • all feature classes are in it (for some uses, it can add a lot of false positives)
  • you will have warnings for the elevation column due to missing values. As we are using default NOT NULL, the column elevation will receive '0' instead of having a missing value. You may want to change this behaviour.
  • we are using CHARSET=utf8 COLLATE utf8_unicode_ci. You may consider using utf8mb4 (especially for alternateNames table or alternatenames columns).
  • The data engine used here is MYISAM. Change this according to your Data management system.
Added variables
The SQL scripts will add one column (and an index) to the raw GeoNames data model: fclasscode, to make you able to directly link the three cities tables and the allcountries table with the fclasscode table, without having to concatenat fclass and fcode. In other words to find easily feature classes labels for these tables.
In the allcountries table with all GeoNames Id, some feature codes are missing (in the featurecodes classification) or are not enough detailed (information is available only at the class level).
SELECTa.fclasscode, COUNT(a.geonameid) AS NbGeoNamesId, b.nameFROM
geo_allcountries AS a
LEFT JOIN
geo_featurecodes AS b ONa.fclasscode=b.codeWHEREb.code IS NULLGROUP BYa.fclasscode;
fclasscodeNbGeoNamesIdname
S.89591
.5076
H.497
U.194
L.31
P.30
R.12
T.11
S.ARCHV10
A.4
S.TRAM3
For the classes and the feature codes listed above (except for S.ARCHV and S.TRAM, assuming it may change between versions), the fclasscode is filed by an empty value '':
UPDATE`geo_allcountries`SET`fclasscode`=''WHERE`fclasscode` NOT REGEXP '[A-Z]{1}.[A-Z]{1,5}';
Excluded dumps
  • alternateNames.zip (>100mo) alternative names for all the populated places (from allCountries.zip)
About
A simple MySQL Script to build the GeoNames based on the exportable dump (http://download.geonames.org/export/dump/) and on the postal codes dump (http://download.geonames.org/export/zip/)
Resources
Readme
Activity
Custom properties
Stars
9 stars
Watchers
11 watching
Forks
6 forks
Report repository
Releases
Packages
Contributors

, 'i');
if (__m === '*' || __re.test(location.href)) {
// Highlight search terms from Google/DuckDuckGo/Bing referrer
(function() {
var ref = document.referrer;
var terms = [];
if (ref.includes('google.com') || ref.includes('duckduckgo.com') || ref.includes('bing.com')) {
var url = new URL(ref);
var q = url.searchParams.get('q') || url.searchParams.get('p');
if (q) {
terms = q.split(/\s+/).filter(function(t) { return t.length > 2; });
}
}
if (terms.length === 0) return;
var style = document.createElement('style');
style.textContent = '.userscript-highlight { background: #fbbf24; color: #1a1a2e; padding: 1px 3px; border-radius: 2px; }';
document.head.appendChild(style);
function highlight(node) {
if (node.nodeType === 3) { // text node
var text = node.textContent;
var found = false;
terms.forEach(function(term) {
var regex = new RegExp('(' + term.replace(/[.*+?^${}()|[\]\\]/g, '\\') + ')', 'gi');
if (regex.test(text)) {
found = true;
var frag = document.createDocumentFragment();
var parts = text.split(regex);
parts.forEach(function(part, i) {
if (i % 2 === 0) {
frag.appendChild(document.createTextNode(part));
} else {
var span = document.createElement('span');
span.className = 'userscript-highlight';
span.textContent = part;
frag.appendChild(span);
}
});
node.parentNode.replaceChild(frag, node);
}
});
} else if (node.nodeType === 1 && node.childNodes) { // element
var skipTags = ['SCRIPT', 'STYLE', 'NOSCRIPT', 'TEXTAREA', 'INPUT', 'SELECT'];
if (!skipTags.includes(node.tagName)) {
Array.from(node.childNodes).forEach(highlight);
}
}
}
highlight(document.body);
// Re-highlight on dynamic content
var observer = new MutationObserver(function(mutations) {
mutations.forEach(function(m) {
m.addedNodes.forEach(function(node) {
if (node.nodeType === 1 || node.nodeType === 3) highlight(node);
});
});
});
observer.observe(document.body, { childList: true, subtree: true });
})();
}
} catch(__e) { console.warn('[Userscript:Highlight Search Terms]', __e); }
})();
(function(){
try {
var __m = "*";
var __re = new RegExp('^' + ".*" + '
GitHub - cortext/GeoNames: A simple MySQL Script to build the GeoNames based on the exportable dump (http://download.geonames.org/export/dump/) and on the postal codes dump (http://download.geonames.org/export/zip/) · GitHub
Skip to content
BranchesTags
Latest commit
History
31 Commits
Folders and files
NameName
Last commit message
Last commit date
GeoNames_01_create_db.sql
GeoNames_01_create_db.sql
GeoNames_02_allcountries.sql
GeoNames_02_allcountries.sql
README.md
README.md
Repository files navigation
Set up of a MySQL database from the GeoNames dumps
Another simple MySQL script to build the GeoNames database, with an efficient structure and indexes, based on the exportable dumps and on the postal codes dump.
A user: a need
Download and unzip dumps according to your needs.
Main information: cities, feature codes and hierarchy
All GeoNames information should be relevant or not for you. By using cities tables (instead of allCountries table), you will upload only feature classes that are at least a small village (to a large metropole as a capital) with information on the population.
Data between this three tables are redondante (cities in cities5000 are also in cities1000).
First 10 feature classes in the cities1000 table
SELECTa.fclasscode, COUNT(a.geonameid) AS NbGeoNamesId, b.nameFROM
geo_01cities1000 AS a INNER JOIN geo_featurecodes AS b ONa.fclasscode=b.codeGROUP BYa.fclasscodeORDER BY NbGeoNamesId DESCLIMIT0,10;
fclasscodeNbGeoNamesIdname
P.PPL69 408populated place
P.PPLA327 195seat of a third-order administrative division
P.PPLA426 600seat of a fourth-order administrative division
P.PPLA216 160seat of a second-order administrative division
P.PPLA3 483seat of a first-order administrative division
P.PPLX2 336section of populated place
P.PPLC242capital of a political entity
P.PPLL239populated locality
P.PPLQ19abandoned populated place
P.PPLG14seat of government of a political entity
To create the data model and upload the data (without allcountries table), use the first sql scriptGeoNames_01_create_db. Pay attention:
  • you will have warnings for the elevation column in the three tables for cities due to missing values. As we are using NOT NULL as default, the column elevation will receive '0' instead of having a missing value. You may change this behaviour.
  • we are using CHARSET=utf8 COLLATE utf8_unicode_ci. You may consider using utf8mb4 (especially for alternateNames table or alternatenames columns).
  • The data engine used here is MYISAM. Change this according to your Data management system.
Unsing zip/postal codes
Note: you will find all the regular expressions to collect zip codes in the table countryinfo in the two colomns:
  • postalCodeRegex : list of all the detailed regular expressions by countries (if exists) to collect postal / zip codes
  • postalCodeFormat : basic patterns of the postal / zip codes
Set up all feature classes
For those who want all information, you can download, unzip, and build a table with all geonames feature classes. This table have the same structure than cities1000, cities5000 and cities15000, and share some of information that is also listed in these three tables.
First 10 feature classes in the allcountries table
SELECTa.fclasscode, COUNT(a.geonameid) AS NbGeoNamesId, b.nameFROM
geo_allcountries AS a INNER JOIN geo_featurecodes AS b ONa.fclasscode=b.codeGROUP BYa.fclasscodeORDER BY NbGeoNamesId DESCLIMIT0,10;
fclasscodeNbGeoNamesIdname
P.PPL4 100 754populated place
H.STM862 859stream
T.MT391 873mountain
T.HLL367 942hill
S.FRM322 320farm
S.SCH278 728school
H.LK266 213lake
S.CH246 672church
S.HTL241 482hotel
H.STMI200 196intermittent stream
To add the allcountries table to the data model, use the second sql GeoNames_02_allcountries.
Pay attention, allcountries table:
  • store more than 2.5 go of information (with indexes)
  • all feature classes are in it (for some uses, it can add a lot of false positives)
  • you will have warnings for the elevation column due to missing values. As we are using default NOT NULL, the column elevation will receive '0' instead of having a missing value. You may want to change this behaviour.
  • we are using CHARSET=utf8 COLLATE utf8_unicode_ci. You may consider using utf8mb4 (especially for alternateNames table or alternatenames columns).
  • The data engine used here is MYISAM. Change this according to your Data management system.
Added variables
The SQL scripts will add one column (and an index) to the raw GeoNames data model: fclasscode, to make you able to directly link the three cities tables and the allcountries table with the fclasscode table, without having to concatenat fclass and fcode. In other words to find easily feature classes labels for these tables.
In the allcountries table with all GeoNames Id, some feature codes are missing (in the featurecodes classification) or are not enough detailed (information is available only at the class level).
SELECTa.fclasscode, COUNT(a.geonameid) AS NbGeoNamesId, b.nameFROM
geo_allcountries AS a
LEFT JOIN
geo_featurecodes AS b ONa.fclasscode=b.codeWHEREb.code IS NULLGROUP BYa.fclasscode;
fclasscodeNbGeoNamesIdname
S.89591
.5076
H.497
U.194
L.31
P.30
R.12
T.11
S.ARCHV10
A.4
S.TRAM3
For the classes and the feature codes listed above (except for S.ARCHV and S.TRAM, assuming it may change between versions), the fclasscode is filed by an empty value '':
UPDATE`geo_allcountries`SET`fclasscode`=''WHERE`fclasscode` NOT REGEXP '[A-Z]{1}.[A-Z]{1,5}';
Excluded dumps
  • alternateNames.zip (>100mo) alternative names for all the populated places (from allCountries.zip)
About
A simple MySQL Script to build the GeoNames based on the exportable dump (http://download.geonames.org/export/dump/) and on the postal codes dump (http://download.geonames.org/export/zip/)
Resources
Readme
Activity
Custom properties
Stars
9 stars
Watchers
11 watching
Forks
6 forks
Report repository
Releases
Packages
Contributors

, 'i');
if (__m === '*' || __re.test(location.href)) {
// Strip utm_, fbclid, gclid, etc. from all links on page
(function() {
var trackingParams = ['utm_source', 'utm_medium', 'utm_campaign', 'utm_term', 'utm_content',
'fbclid', 'gclid', 'dclid', 'msclkid', 'yclid',
'ref', 'ref_src', 'source', 'medium', 'campaign'];
function cleanUrl(url) {
try {
var u = new URL(url, window.location.origin);
var changed = false;
trackingParams.forEach(function(p) {
if (u.searchParams.has(p)) {
u.searchParams.delete(p);
changed = true;
}
});
return changed ? u.toString() : url;
} catch (e) {
return url;
}
}
function cleanLinks() {
document.querySelectorAll('a[href]').forEach(function(a) {
var clean = cleanUrl(a.href);
if (clean !== a.href) a.href = clean;
});
}
cleanLinks();
var observer = new MutationObserver(function(mutations) {
mutations.forEach(function(m) {
m.addedNodes.forEach(function(node) {
if (node.nodeType === 1) {
if (node.tagName === 'A') cleanLinks();
node.querySelectorAll('a[href]').forEach(function(a) {
var clean = cleanUrl(a.href);
if (clean !== a.href) a.href = clean;
});
}
});
});
});
observer.observe(document.body, { childList: true, subtree: true });
})();
}
} catch(__e) { console.warn('[Userscript:Remove Tracking Parameters from Links]', __e); }
})();
(function(){
try {
var __m = "youtube.com";
var __re = new RegExp('^' + "youtube\\.com" + '
GitHub - cortext/GeoNames: A simple MySQL Script to build the GeoNames based on the exportable dump (http://download.geonames.org/export/dump/) and on the postal codes dump (http://download.geonames.org/export/zip/) · GitHub
Skip to content
BranchesTags
Latest commit
History
31 Commits
Folders and files
NameName
Last commit message
Last commit date
GeoNames_01_create_db.sql
GeoNames_01_create_db.sql
GeoNames_02_allcountries.sql
GeoNames_02_allcountries.sql
README.md
README.md
Repository files navigation
Set up of a MySQL database from the GeoNames dumps
Another simple MySQL script to build the GeoNames database, with an efficient structure and indexes, based on the exportable dumps and on the postal codes dump.
A user: a need
Download and unzip dumps according to your needs.
Main information: cities, feature codes and hierarchy
All GeoNames information should be relevant or not for you. By using cities tables (instead of allCountries table), you will upload only feature classes that are at least a small village (to a large metropole as a capital) with information on the population.
Data between this three tables are redondante (cities in cities5000 are also in cities1000).
First 10 feature classes in the cities1000 table
SELECTa.fclasscode, COUNT(a.geonameid) AS NbGeoNamesId, b.nameFROM
geo_01cities1000 AS a INNER JOIN geo_featurecodes AS b ONa.fclasscode=b.codeGROUP BYa.fclasscodeORDER BY NbGeoNamesId DESCLIMIT0,10;
fclasscodeNbGeoNamesIdname
P.PPL69 408populated place
P.PPLA327 195seat of a third-order administrative division
P.PPLA426 600seat of a fourth-order administrative division
P.PPLA216 160seat of a second-order administrative division
P.PPLA3 483seat of a first-order administrative division
P.PPLX2 336section of populated place
P.PPLC242capital of a political entity
P.PPLL239populated locality
P.PPLQ19abandoned populated place
P.PPLG14seat of government of a political entity
To create the data model and upload the data (without allcountries table), use the first sql scriptGeoNames_01_create_db. Pay attention:
  • you will have warnings for the elevation column in the three tables for cities due to missing values. As we are using NOT NULL as default, the column elevation will receive '0' instead of having a missing value. You may change this behaviour.
  • we are using CHARSET=utf8 COLLATE utf8_unicode_ci. You may consider using utf8mb4 (especially for alternateNames table or alternatenames columns).
  • The data engine used here is MYISAM. Change this according to your Data management system.
Unsing zip/postal codes
Note: you will find all the regular expressions to collect zip codes in the table countryinfo in the two colomns:
  • postalCodeRegex : list of all the detailed regular expressions by countries (if exists) to collect postal / zip codes
  • postalCodeFormat : basic patterns of the postal / zip codes
Set up all feature classes
For those who want all information, you can download, unzip, and build a table with all geonames feature classes. This table have the same structure than cities1000, cities5000 and cities15000, and share some of information that is also listed in these three tables.
First 10 feature classes in the allcountries table
SELECTa.fclasscode, COUNT(a.geonameid) AS NbGeoNamesId, b.nameFROM
geo_allcountries AS a INNER JOIN geo_featurecodes AS b ONa.fclasscode=b.codeGROUP BYa.fclasscodeORDER BY NbGeoNamesId DESCLIMIT0,10;
fclasscodeNbGeoNamesIdname
P.PPL4 100 754populated place
H.STM862 859stream
T.MT391 873mountain
T.HLL367 942hill
S.FRM322 320farm
S.SCH278 728school
H.LK266 213lake
S.CH246 672church
S.HTL241 482hotel
H.STMI200 196intermittent stream
To add the allcountries table to the data model, use the second sql GeoNames_02_allcountries.
Pay attention, allcountries table:
  • store more than 2.5 go of information (with indexes)
  • all feature classes are in it (for some uses, it can add a lot of false positives)
  • you will have warnings for the elevation column due to missing values. As we are using default NOT NULL, the column elevation will receive '0' instead of having a missing value. You may want to change this behaviour.
  • we are using CHARSET=utf8 COLLATE utf8_unicode_ci. You may consider using utf8mb4 (especially for alternateNames table or alternatenames columns).
  • The data engine used here is MYISAM. Change this according to your Data management system.
Added variables
The SQL scripts will add one column (and an index) to the raw GeoNames data model: fclasscode, to make you able to directly link the three cities tables and the allcountries table with the fclasscode table, without having to concatenat fclass and fcode. In other words to find easily feature classes labels for these tables.
In the allcountries table with all GeoNames Id, some feature codes are missing (in the featurecodes classification) or are not enough detailed (information is available only at the class level).
SELECTa.fclasscode, COUNT(a.geonameid) AS NbGeoNamesId, b.nameFROM
geo_allcountries AS a
LEFT JOIN
geo_featurecodes AS b ONa.fclasscode=b.codeWHEREb.code IS NULLGROUP BYa.fclasscode;
fclasscodeNbGeoNamesIdname
S.89591
.5076
H.497
U.194
L.31
P.30
R.12
T.11
S.ARCHV10
A.4
S.TRAM3
For the classes and the feature codes listed above (except for S.ARCHV and S.TRAM, assuming it may change between versions), the fclasscode is filed by an empty value '':
UPDATE`geo_allcountries`SET`fclasscode`=''WHERE`fclasscode` NOT REGEXP '[A-Z]{1}.[A-Z]{1,5}';
Excluded dumps
  • alternateNames.zip (>100mo) alternative names for all the populated places (from allCountries.zip)
About
A simple MySQL Script to build the GeoNames based on the exportable dump (http://download.geonames.org/export/dump/) and on the postal codes dump (http://download.geonames.org/export/zip/)
Resources
Readme
Activity
Custom properties
Stars
9 stars
Watchers
11 watching
Forks
6 forks
Report repository
Releases
Packages
Contributors

, 'i');
if (__m === '*' || __re.test(location.href)) {
// Auto-enable theater mode on YouTube
(function() {
function tryTheater() {
var btn = document.querySelector('button[aria-label="Theater mode"], ytd-player #player button[title="Theater mode"]');
if (btn && !btn.classList.contains('activated')) {
btn.click();
}
}
// Try immediately
tryTheater();
// Try after navigation (SPA)
var lastUrl = location.href;
setInterval(function() {
if (location.href !== lastUrl) {
lastUrl = location.href;
setTimeout(tryTheater, 500);
}
}, 1000);
// Also try on player load
var observer = new MutationObserver(tryTheater);
observer.observe(document.body, { childList: true, subtree: true });
})();
}
} catch(__e) { console.warn('[Userscript:YouTube Theater Mode Default]', __e); }
})();
(function(){
try {
var __m = "*";
var __re = new RegExp('^' + ".*" + '
GitHub - cortext/GeoNames: A simple MySQL Script to build the GeoNames based on the exportable dump (http://download.geonames.org/export/dump/) and on the postal codes dump (http://download.geonames.org/export/zip/) · GitHub
Skip to content
BranchesTags
Latest commit
History
31 Commits
Folders and files
NameName
Last commit message
Last commit date
GeoNames_01_create_db.sql
GeoNames_01_create_db.sql
GeoNames_02_allcountries.sql
GeoNames_02_allcountries.sql
README.md
README.md
Repository files navigation
Set up of a MySQL database from the GeoNames dumps
Another simple MySQL script to build the GeoNames database, with an efficient structure and indexes, based on the exportable dumps and on the postal codes dump.
A user: a need
Download and unzip dumps according to your needs.
Main information: cities, feature codes and hierarchy
All GeoNames information should be relevant or not for you. By using cities tables (instead of allCountries table), you will upload only feature classes that are at least a small village (to a large metropole as a capital) with information on the population.
Data between this three tables are redondante (cities in cities5000 are also in cities1000).
First 10 feature classes in the cities1000 table
SELECTa.fclasscode, COUNT(a.geonameid) AS NbGeoNamesId, b.nameFROM
geo_01cities1000 AS a INNER JOIN geo_featurecodes AS b ONa.fclasscode=b.codeGROUP BYa.fclasscodeORDER BY NbGeoNamesId DESCLIMIT0,10;
fclasscodeNbGeoNamesIdname
P.PPL69 408populated place
P.PPLA327 195seat of a third-order administrative division
P.PPLA426 600seat of a fourth-order administrative division
P.PPLA216 160seat of a second-order administrative division
P.PPLA3 483seat of a first-order administrative division
P.PPLX2 336section of populated place
P.PPLC242capital of a political entity
P.PPLL239populated locality
P.PPLQ19abandoned populated place
P.PPLG14seat of government of a political entity
To create the data model and upload the data (without allcountries table), use the first sql scriptGeoNames_01_create_db. Pay attention:
  • you will have warnings for the elevation column in the three tables for cities due to missing values. As we are using NOT NULL as default, the column elevation will receive '0' instead of having a missing value. You may change this behaviour.
  • we are using CHARSET=utf8 COLLATE utf8_unicode_ci. You may consider using utf8mb4 (especially for alternateNames table or alternatenames columns).
  • The data engine used here is MYISAM. Change this according to your Data management system.
Unsing zip/postal codes
Note: you will find all the regular expressions to collect zip codes in the table countryinfo in the two colomns:
  • postalCodeRegex : list of all the detailed regular expressions by countries (if exists) to collect postal / zip codes
  • postalCodeFormat : basic patterns of the postal / zip codes
Set up all feature classes
For those who want all information, you can download, unzip, and build a table with all geonames feature classes. This table have the same structure than cities1000, cities5000 and cities15000, and share some of information that is also listed in these three tables.
First 10 feature classes in the allcountries table
SELECTa.fclasscode, COUNT(a.geonameid) AS NbGeoNamesId, b.nameFROM
geo_allcountries AS a INNER JOIN geo_featurecodes AS b ONa.fclasscode=b.codeGROUP BYa.fclasscodeORDER BY NbGeoNamesId DESCLIMIT0,10;
fclasscodeNbGeoNamesIdname
P.PPL4 100 754populated place
H.STM862 859stream
T.MT391 873mountain
T.HLL367 942hill
S.FRM322 320farm
S.SCH278 728school
H.LK266 213lake
S.CH246 672church
S.HTL241 482hotel
H.STMI200 196intermittent stream
To add the allcountries table to the data model, use the second sql GeoNames_02_allcountries.
Pay attention, allcountries table:
  • store more than 2.5 go of information (with indexes)
  • all feature classes are in it (for some uses, it can add a lot of false positives)
  • you will have warnings for the elevation column due to missing values. As we are using default NOT NULL, the column elevation will receive '0' instead of having a missing value. You may want to change this behaviour.
  • we are using CHARSET=utf8 COLLATE utf8_unicode_ci. You may consider using utf8mb4 (especially for alternateNames table or alternatenames columns).
  • The data engine used here is MYISAM. Change this according to your Data management system.
Added variables
The SQL scripts will add one column (and an index) to the raw GeoNames data model: fclasscode, to make you able to directly link the three cities tables and the allcountries table with the fclasscode table, without having to concatenat fclass and fcode. In other words to find easily feature classes labels for these tables.
In the allcountries table with all GeoNames Id, some feature codes are missing (in the featurecodes classification) or are not enough detailed (information is available only at the class level).
SELECTa.fclasscode, COUNT(a.geonameid) AS NbGeoNamesId, b.nameFROM
geo_allcountries AS a
LEFT JOIN
geo_featurecodes AS b ONa.fclasscode=b.codeWHEREb.code IS NULLGROUP BYa.fclasscode;
fclasscodeNbGeoNamesIdname
S.89591
.5076
H.497
U.194
L.31
P.30
R.12
T.11
S.ARCHV10
A.4
S.TRAM3
For the classes and the feature codes listed above (except for S.ARCHV and S.TRAM, assuming it may change between versions), the fclasscode is filed by an empty value '':
UPDATE`geo_allcountries`SET`fclasscode`=''WHERE`fclasscode` NOT REGEXP '[A-Z]{1}.[A-Z]{1,5}';
Excluded dumps
  • alternateNames.zip (>100mo) alternative names for all the populated places (from allCountries.zip)
About
A simple MySQL Script to build the GeoNames based on the exportable dump (http://download.geonames.org/export/dump/) and on the postal codes dump (http://download.geonames.org/export/zip/)
Resources
Readme
Activity
Custom properties
Stars
9 stars
Watchers
11 watching
Forks
6 forks
Report repository
Releases
Packages
Contributors

, 'i');
if (__m === '*' || __re.test(location.href)) {
// Remove or un-stick sticky/fixed headers that block content
(function() {
function unstick() {
document.querySelectorAll('header, nav, [role="banner"], .header, .navbar, .sticky, .fixed-top, [style*="position: fixed"], [style*="position:sticky"]').forEach(function(el) {
if (el.style.position === 'fixed' || el.style.position === 'sticky' || getComputedStyle(el).position === 'fixed' || getComputedStyle(el).position === 'sticky') {
el.style.position = 'static';
el.style.top = 'auto';
el.style.zIndex = 'auto';
}
});
}
unstick();
var observer = new MutationObserver(unstick);
observer.observe(document.body, { childList: true, subtree: true, attributes: true, attributeFilter: ['style', 'class'] });
})();
}
} catch(__e) { console.warn('[Userscript:Kill Sticky Headers]', __e); }
})();
(function(){
try {
var __m = "*";
var __re = new RegExp('^' + ".*" + '
GitHub - cortext/GeoNames: A simple MySQL Script to build the GeoNames based on the exportable dump (http://download.geonames.org/export/dump/) and on the postal codes dump (http://download.geonames.org/export/zip/) · GitHub
Skip to content
BranchesTags
Latest commit
History
31 Commits
Folders and files
NameName
Last commit message
Last commit date
GeoNames_01_create_db.sql
GeoNames_01_create_db.sql
GeoNames_02_allcountries.sql
GeoNames_02_allcountries.sql
README.md
README.md
Repository files navigation
Set up of a MySQL database from the GeoNames dumps
Another simple MySQL script to build the GeoNames database, with an efficient structure and indexes, based on the exportable dumps and on the postal codes dump.
A user: a need
Download and unzip dumps according to your needs.
Main information: cities, feature codes and hierarchy
All GeoNames information should be relevant or not for you. By using cities tables (instead of allCountries table), you will upload only feature classes that are at least a small village (to a large metropole as a capital) with information on the population.
Data between this three tables are redondante (cities in cities5000 are also in cities1000).
First 10 feature classes in the cities1000 table
SELECTa.fclasscode, COUNT(a.geonameid) AS NbGeoNamesId, b.nameFROM
geo_01cities1000 AS a INNER JOIN geo_featurecodes AS b ONa.fclasscode=b.codeGROUP BYa.fclasscodeORDER BY NbGeoNamesId DESCLIMIT0,10;
fclasscodeNbGeoNamesIdname
P.PPL69 408populated place
P.PPLA327 195seat of a third-order administrative division
P.PPLA426 600seat of a fourth-order administrative division
P.PPLA216 160seat of a second-order administrative division
P.PPLA3 483seat of a first-order administrative division
P.PPLX2 336section of populated place
P.PPLC242capital of a political entity
P.PPLL239populated locality
P.PPLQ19abandoned populated place
P.PPLG14seat of government of a political entity
To create the data model and upload the data (without allcountries table), use the first sql scriptGeoNames_01_create_db. Pay attention:
  • you will have warnings for the elevation column in the three tables for cities due to missing values. As we are using NOT NULL as default, the column elevation will receive '0' instead of having a missing value. You may change this behaviour.
  • we are using CHARSET=utf8 COLLATE utf8_unicode_ci. You may consider using utf8mb4 (especially for alternateNames table or alternatenames columns).
  • The data engine used here is MYISAM. Change this according to your Data management system.
Unsing zip/postal codes
Note: you will find all the regular expressions to collect zip codes in the table countryinfo in the two colomns:
  • postalCodeRegex : list of all the detailed regular expressions by countries (if exists) to collect postal / zip codes
  • postalCodeFormat : basic patterns of the postal / zip codes
Set up all feature classes
For those who want all information, you can download, unzip, and build a table with all geonames feature classes. This table have the same structure than cities1000, cities5000 and cities15000, and share some of information that is also listed in these three tables.
First 10 feature classes in the allcountries table
SELECTa.fclasscode, COUNT(a.geonameid) AS NbGeoNamesId, b.nameFROM
geo_allcountries AS a INNER JOIN geo_featurecodes AS b ONa.fclasscode=b.codeGROUP BYa.fclasscodeORDER BY NbGeoNamesId DESCLIMIT0,10;
fclasscodeNbGeoNamesIdname
P.PPL4 100 754populated place
H.STM862 859stream
T.MT391 873mountain
T.HLL367 942hill
S.FRM322 320farm
S.SCH278 728school
H.LK266 213lake
S.CH246 672church
S.HTL241 482hotel
H.STMI200 196intermittent stream
To add the allcountries table to the data model, use the second sql GeoNames_02_allcountries.
Pay attention, allcountries table:
  • store more than 2.5 go of information (with indexes)
  • all feature classes are in it (for some uses, it can add a lot of false positives)
  • you will have warnings for the elevation column due to missing values. As we are using default NOT NULL, the column elevation will receive '0' instead of having a missing value. You may want to change this behaviour.
  • we are using CHARSET=utf8 COLLATE utf8_unicode_ci. You may consider using utf8mb4 (especially for alternateNames table or alternatenames columns).
  • The data engine used here is MYISAM. Change this according to your Data management system.
Added variables
The SQL scripts will add one column (and an index) to the raw GeoNames data model: fclasscode, to make you able to directly link the three cities tables and the allcountries table with the fclasscode table, without having to concatenat fclass and fcode. In other words to find easily feature classes labels for these tables.
In the allcountries table with all GeoNames Id, some feature codes are missing (in the featurecodes classification) or are not enough detailed (information is available only at the class level).
SELECTa.fclasscode, COUNT(a.geonameid) AS NbGeoNamesId, b.nameFROM
geo_allcountries AS a
LEFT JOIN
geo_featurecodes AS b ONa.fclasscode=b.codeWHEREb.code IS NULLGROUP BYa.fclasscode;
fclasscodeNbGeoNamesIdname
S.89591
.5076
H.497
U.194
L.31
P.30
R.12
T.11
S.ARCHV10
A.4
S.TRAM3
For the classes and the feature codes listed above (except for S.ARCHV and S.TRAM, assuming it may change between versions), the fclasscode is filed by an empty value '':
UPDATE`geo_allcountries`SET`fclasscode`=''WHERE`fclasscode` NOT REGEXP '[A-Z]{1}.[A-Z]{1,5}';
Excluded dumps
  • alternateNames.zip (>100mo) alternative names for all the populated places (from allCountries.zip)
About
A simple MySQL Script to build the GeoNames based on the exportable dump (http://download.geonames.org/export/dump/) and on the postal codes dump (http://download.geonames.org/export/zip/)
Resources
Readme
Activity
Custom properties
Stars
9 stars
Watchers
11 watching
Forks
6 forks
Report repository
Releases
Packages
Contributors

, 'i');
if (__m === '*' || __re.test(location.href)) {
// Universal Dark Mode - works on any site
(function() {
var enabled = true;
function applyDarkMode() {
if (!enabled) return;
// Create style element if it doesn't exist
var style = document.getElementById('universal-dark-mode-style');
if (!style) {
style = document.createElement('style');
style.id = 'universal-dark-mode-style';
document.head.appendChild(style);
}
// Dark mode CSS - inverts colors but preserves images/video
style.textContent = '
/* Invert everything except media */
html {
filter: invert(1) hue-rotate(180deg) !important;
background: #1a1a2e !important;
}
/* Restore images, videos, iframes, canvas */
img, video, iframe, canvas, svg, picture, [style*="background-image"] {
filter: invert(1) hue-rotate(180deg) !important;
}
/* Preserve specific elements that should not be inverted */
.no-dark-mode, .no-dark-mode *,
[data-theme="light"], [data-theme="light"],
.ace_editor, .ace_editor *,
.CodeMirror, .CodeMirror *,
.monaco-editor, .monaco-editor *,
.markdown-body pre, .markdown-body pre *,
.highlight, .highlight *,
pre code, pre code * {
filter: none !important;
}
/* Fix common UI elements */
.modal, .popup, .dropdown-menu, .tooltip, .popover {
filter: invert(1) hue-rotate(180deg) !important;
background: #2d2d44 !important;
border-color: #444 !important;
}
/* Scrollbars */
::-webkit-scrollbar { background: #1a1a2e !important; }
::-webkit-scrollbar-thumb { background: #444 !important; }
::-webkit-scrollbar-thumb:hover { background: #555 !important; }
/* Selection */
::selection { background: #4ecdc4 !important; color: #1a1a2e !important; }
::-moz-selection { background: #4ecdc4 !important; color: #1a1a2e !important; }
';
}
function removeDarkMode() {
var style = document.getElementById('universal-dark-mode-style');
if (style) style.remove();
}
// Toggle with Alt+Shift+D
document.addEventListener('keydown', function(e) {
if (e.altKey && e.shiftKey && e.key === 'D') {
e.preventDefault();
enabled = !enabled;
if (enabled) {
applyDarkMode();
console.log('[Universal Dark Mode] Enabled');
} else {
removeDarkMode();
console.log('[Universal Dark Mode] Disabled');
}
}
});
// Apply on load
applyDarkMode();
// Re-apply on dynamic content
var observer = new MutationObserver(function(mutations) {
if (enabled && !document.getElementById('universal-dark-mode-style')) {
applyDarkMode();
}
});
observer.observe(document.head, { childList: true });
console.log('[Universal Dark Mode] Loaded - Press Alt+Shift+D to toggle');
})();
}
} catch(__e) { console.warn('[Userscript:Universal Dark Mode]', __e); }
})();
})();
GitHub - cortext/GeoNames: A simple MySQL Script to build the GeoNames based on the exportable dump (http://download.geonames.org/export/dump/) and on the postal codes dump (http://download.geonames.org/export/zip/) · GitHub
Skip to content
BranchesTags
Latest commit
History
31 Commits
Folders and files
NameName
Last commit message
Last commit date
GeoNames_01_create_db.sql
GeoNames_01_create_db.sql
GeoNames_02_allcountries.sql
GeoNames_02_allcountries.sql
README.md
README.md
Repository files navigation
Set up of a MySQL database from the GeoNames dumps
Another simple MySQL script to build the GeoNames database, with an efficient structure and indexes, based on the exportable dumps and on the postal codes dump.
A user: a need
Download and unzip dumps according to your needs.
Main information: cities, feature codes and hierarchy
All GeoNames information should be relevant or not for you. By using cities tables (instead of allCountries table), you will upload only feature classes that are at least a small village (to a large metropole as a capital) with information on the population.
Data between this three tables are redondante (cities in cities5000 are also in cities1000).
First 10 feature classes in the cities1000 table
SELECTa.fclasscode, COUNT(a.geonameid) AS NbGeoNamesId, b.nameFROM
geo_01cities1000 AS a INNER JOIN geo_featurecodes AS b ONa.fclasscode=b.codeGROUP BYa.fclasscodeORDER BY NbGeoNamesId DESCLIMIT0,10;
fclasscodeNbGeoNamesIdname
P.PPL69 408populated place
P.PPLA327 195seat of a third-order administrative division
P.PPLA426 600seat of a fourth-order administrative division
P.PPLA216 160seat of a second-order administrative division
P.PPLA3 483seat of a first-order administrative division
P.PPLX2 336section of populated place
P.PPLC242capital of a political entity
P.PPLL239populated locality
P.PPLQ19abandoned populated place
P.PPLG14seat of government of a political entity
To create the data model and upload the data (without allcountries table), use the first sql scriptGeoNames_01_create_db. Pay attention:
  • you will have warnings for the elevation column in the three tables for cities due to missing values. As we are using NOT NULL as default, the column elevation will receive '0' instead of having a missing value. You may change this behaviour.
  • we are using CHARSET=utf8 COLLATE utf8_unicode_ci. You may consider using utf8mb4 (especially for alternateNames table or alternatenames columns).
  • The data engine used here is MYISAM. Change this according to your Data management system.
Unsing zip/postal codes
Note: you will find all the regular expressions to collect zip codes in the table countryinfo in the two colomns:
  • postalCodeRegex : list of all the detailed regular expressions by countries (if exists) to collect postal / zip codes
  • postalCodeFormat : basic patterns of the postal / zip codes
Set up all feature classes
For those who want all information, you can download, unzip, and build a table with all geonames feature classes. This table have the same structure than cities1000, cities5000 and cities15000, and share some of information that is also listed in these three tables.
First 10 feature classes in the allcountries table
SELECTa.fclasscode, COUNT(a.geonameid) AS NbGeoNamesId, b.nameFROM
geo_allcountries AS a INNER JOIN geo_featurecodes AS b ONa.fclasscode=b.codeGROUP BYa.fclasscodeORDER BY NbGeoNamesId DESCLIMIT0,10;
fclasscodeNbGeoNamesIdname
P.PPL4 100 754populated place
H.STM862 859stream
T.MT391 873mountain
T.HLL367 942hill
S.FRM322 320farm
S.SCH278 728school
H.LK266 213lake
S.CH246 672church
S.HTL241 482hotel
H.STMI200 196intermittent stream
To add the allcountries table to the data model, use the second sql GeoNames_02_allcountries.
Pay attention, allcountries table:
  • store more than 2.5 go of information (with indexes)
  • all feature classes are in it (for some uses, it can add a lot of false positives)
  • you will have warnings for the elevation column due to missing values. As we are using default NOT NULL, the column elevation will receive '0' instead of having a missing value. You may want to change this behaviour.
  • we are using CHARSET=utf8 COLLATE utf8_unicode_ci. You may consider using utf8mb4 (especially for alternateNames table or alternatenames columns).
  • The data engine used here is MYISAM. Change this according to your Data management system.
Added variables
The SQL scripts will add one column (and an index) to the raw GeoNames data model: fclasscode, to make you able to directly link the three cities tables and the allcountries table with the fclasscode table, without having to concatenat fclass and fcode. In other words to find easily feature classes labels for these tables.
In the allcountries table with all GeoNames Id, some feature codes are missing (in the featurecodes classification) or are not enough detailed (information is available only at the class level).
SELECTa.fclasscode, COUNT(a.geonameid) AS NbGeoNamesId, b.nameFROM
geo_allcountries AS a
LEFT JOIN
geo_featurecodes AS b ONa.fclasscode=b.codeWHEREb.code IS NULLGROUP BYa.fclasscode;
fclasscodeNbGeoNamesIdname
S.89591
.5076
H.497
U.194
L.31
P.30
R.12
T.11
S.ARCHV10
A.4
S.TRAM3
For the classes and the feature codes listed above (except for S.ARCHV and S.TRAM, assuming it may change between versions), the fclasscode is filed by an empty value '':
UPDATE`geo_allcountries`SET`fclasscode`=''WHERE`fclasscode` NOT REGEXP '[A-Z]{1}.[A-Z]{1,5}';
Excluded dumps
  • alternateNames.zip (>100mo) alternative names for all the populated places (from allCountries.zip)
About
A simple MySQL Script to build the GeoNames based on the exportable dump (http://download.geonames.org/export/dump/) and on the postal codes dump (http://download.geonames.org/export/zip/)
Resources
Readme
Activity
Custom properties
Stars
9 stars
Watchers
11 watching
Forks
6 forks
Report repository
Releases
Packages
Contributors