167 lines
12 KiB
SQL
167 lines
12 KiB
SQL
|
|
---- NAMED PLACES GEOCODING ---
|
|
--- ---
|
|
--- NOTE ---
|
|
--- insert order should be from lowest populated place rank to highest ---
|
|
--- this allows us to use table sort order instead of an explicit ORDER BY rank ---
|
|
--- in searches and reduces search cost / time ---
|
|
--- ---
|
|
--- please, generate a backup of the current global_cities_points_limited table ---
|
|
--- ---
|
|
|
|
-- clear all existing data from the table --
|
|
DELETE FROM global_cities_points_limited;
|
|
|
|
-- insert data from source table (allcountries) to the table
|
|
-- it assumes that the source is called "allcountries"
|
|
INSERT INTO global_cities_points_limited (the_geom, admin1, admin2, admin3, admin4, altnames, asciiname, cc2, featclass, featcode, geoname_id, gtopo30, iso2, lowername, name, population)
|
|
SELECT the_geom, admin1, admin2, admin3, admin4, altnames, asciiname, cc2, featclass, featcode, geonameid::int, gtopo30, country, lower(name) as lowername, name, population
|
|
FROM allcountries
|
|
WHERE featcode = '';
|
|
|
|
-- insert data from source table (allcountries) to the table
|
|
-- it assumes that the source is called "allcountries"
|
|
INSERT INTO global_cities_points_limited (the_geom, admin1, admin2, admin3, admin4, altnames, asciiname, cc2, featclass, featcode, geoname_id, gtopo30, iso2, lowername, name, population)
|
|
SELECT the_geom, admin1, admin2, admin3, admin4, altnames, asciiname, cc2, featclass, featcode, geonameid::int, gtopo30, country, lower(name) as lowername, name, population
|
|
FROM allcountries
|
|
WHERE featcode IS null;
|
|
|
|
-- capital of a political entity
|
|
-- insert PPLC data from source table (allcountries) to the table
|
|
-- it assumes that the source is called "allcountries"
|
|
INSERT INTO global_cities_points_limited (the_geom, admin1, admin2, admin3, admin4, altnames, asciiname, cc2, featclass, featcode, geoname_id, gtopo30, iso2, lowername, name, population)
|
|
SELECT the_geom, admin1, admin2, admin3, admin4, altnames, asciiname, cc2, featclass, featcode, geonameid::int, gtopo30, country, lower(name) as lowername, name, population
|
|
FROM allcountries
|
|
WHERE featcode = 'PPLC';
|
|
|
|
-- populated place a city, town, village, or other agglomeration of buildings where people live and work
|
|
-- insert PPL data from source table (allcountries) to the table
|
|
-- it assumes that the source is called "allcountries"
|
|
INSERT INTO global_cities_points_limited (the_geom, admin1, admin2, admin3, admin4, altnames, asciiname, cc2, featclass, featcode, geoname_id, gtopo30, iso2, lowername, name, population)
|
|
SELECT the_geom, admin1, admin2, admin3, admin4, altnames, asciiname, cc2, featclass, featcode, geonameid::int, gtopo30, country, lower(name) as lowername, name, population
|
|
FROM allcountries
|
|
WHERE featcode = 'PPL';
|
|
|
|
-- seat of a first-order administrative division (PPLC takes precedence over PPLA)
|
|
-- insert PPLA data from source table (allcountries) to the table
|
|
-- it assumes that the source is called "allcountries"
|
|
INSERT INTO global_cities_points_limited (the_geom, admin1, admin2, admin3, admin4, altnames, asciiname, cc2, featclass, featcode, geoname_id, gtopo30, iso2, lowername, name, population)
|
|
SELECT the_geom, admin1, admin2, admin3, admin4, altnames, asciiname, cc2, featclass, featcode, geonameid::int, gtopo30, country, lower(name) as lowername, name, population
|
|
FROM allcountries
|
|
WHERE featcode = 'PPLA';
|
|
|
|
-- seat of a second-order administrative division
|
|
-- insert PPLA2 data from source table (allcountries) to the table
|
|
-- it assumes that the source is called "allcountries"
|
|
INSERT INTO global_cities_points_limited (the_geom, admin1, admin2, admin3, admin4, altnames, asciiname, cc2, featclass, featcode, geoname_id, gtopo30, iso2, lowername, name, population)
|
|
SELECT the_geom, admin1, admin2, admin3, admin4, altnames, asciiname, cc2, featclass, featcode, geonameid::int, gtopo30, country, lower(name) as lowername, name, population
|
|
FROM allcountries
|
|
WHERE featcode = 'PPLA2';
|
|
|
|
-- seat of a third-order administrative division
|
|
-- insert PPLA3 data from source table (allcountries) to the table
|
|
-- it assumes that the source is called "allcountries"
|
|
INSERT INTO global_cities_points_limited (the_geom, admin1, admin2, admin3, admin4, altnames, asciiname, cc2, featclass, featcode, geoname_id, gtopo30, iso2, lowername, name, population)
|
|
SELECT the_geom, admin1, admin2, admin3, admin4, altnames, asciiname, cc2, featclass, featcode, geonameid::int, gtopo30, country, lower(name) as lowername, name, population
|
|
FROM allcountries
|
|
WHERE featcode = 'PPLA3';
|
|
|
|
-- seat of a fourth-order administrative division
|
|
-- insert PPLA4 data from source table (allcountries) to the table
|
|
-- it assumes that the source is called "allcountries"
|
|
INSERT INTO global_cities_points_limited (the_geom, admin1, admin2, admin3, admin4, altnames, asciiname, cc2, featclass, featcode, geoname_id, gtopo30, iso2, lowername, name, population)
|
|
SELECT the_geom, admin1, admin2, admin3, admin4, altnames, asciiname, cc2, featclass, featcode, geonameid::int, gtopo30, country, lower(name) as lowername, name, population
|
|
FROM allcountries
|
|
WHERE featcode = 'PPLA4';
|
|
|
|
-- historical capital of a political entity, a former capital of a political entity
|
|
-- insert PPLCH data from source table (allcountries) to the table
|
|
-- it assumes that the source is called "allcountries"
|
|
INSERT INTO global_cities_points_limited (the_geom, admin1, admin2, admin3, admin4, altnames, asciiname, cc2, featclass, featcode, geoname_id, gtopo30, iso2, lowername, name, population)
|
|
SELECT the_geom, admin1, admin2, admin3, admin4, altnames, asciiname, cc2, featclass, featcode, geonameid::int, gtopo30, country, lower(name) as lowername, name, population
|
|
FROM allcountries
|
|
WHERE featcode = 'PPLCH';
|
|
|
|
-- farm village a populated place where the population is largely engaged in agricultural activities
|
|
-- insert PPLF data from source table (allcountries) to the table
|
|
-- it assumes that the source is called "allcountries"
|
|
INSERT INTO global_cities_points_limited (the_geom, admin1, admin2, admin3, admin4, altnames, asciiname, cc2, featclass, featcode, geoname_id, gtopo30, iso2, lowername, name, population)
|
|
SELECT the_geom, admin1, admin2, admin3, admin4, altnames, asciiname, cc2, featclass, featcode, geonameid::int, gtopo30, country, lower(name) as lowername, name, population
|
|
FROM allcountries
|
|
WHERE featcode = 'PPLF';
|
|
|
|
-- seat of government of a political entity
|
|
-- insert PPLG data from source table (allcountries) to the table
|
|
-- it assumes that the source is called "allcountries"
|
|
INSERT INTO global_cities_points_limited (the_geom, admin1, admin2, admin3, admin4, altnames, asciiname, cc2, featclass, featcode, geoname_id, gtopo30, iso2, lowername, name, population)
|
|
SELECT the_geom, admin1, admin2, admin3, admin4, altnames, asciiname, cc2, featclass, featcode, geonameid::int, gtopo30, country, lower(name) as lowername, name, population
|
|
FROM allcountries
|
|
WHERE featcode = 'PPLG';
|
|
|
|
-- historical populated place a populated place that no longer exists
|
|
-- insert PPLH data from source table (allcountries) to the table
|
|
-- it assumes that the source is called "allcountries"
|
|
INSERT INTO global_cities_points_limited (the_geom, admin1, admin2, admin3, admin4, altnames, asciiname, cc2, featclass, featcode, geoname_id, gtopo30, iso2, lowername, name, population)
|
|
SELECT the_geom, admin1, admin2, admin3, admin4, altnames, asciiname, cc2, featclass, featcode, geonameid::int, gtopo30, country, lower(name) as lowername, name, population
|
|
FROM allcountries
|
|
WHERE featcode = 'PPLH';
|
|
|
|
-- populated locality an area similar to a locality but with a small group of dwellings or other buildings
|
|
-- insert PPLL data from source table (allcountries) to the table
|
|
-- it assumes that the source is called "allcountries"
|
|
INSERT INTO global_cities_points_limited (the_geom, admin1, admin2, admin3, admin4, altnames, asciiname, cc2, featclass, featcode, geoname_id, gtopo30, iso2, lowername, name, population)
|
|
SELECT the_geom, admin1, admin2, admin3, admin4, altnames, asciiname, cc2, featclass, featcode, geonameid::int, gtopo30, country, lower(name) as lowername, name, population
|
|
FROM allcountries
|
|
WHERE featcode = 'PPLL';
|
|
|
|
-- abandoned populated place
|
|
-- insert PPLQ data from source table (allcountries) to the table
|
|
-- it assumes that the source is called "allcountries"
|
|
INSERT INTO global_cities_points_limited (the_geom, admin1, admin2, admin3, admin4, altnames, asciiname, cc2, featclass, featcode, geoname_id, gtopo30, iso2, lowername, name, population)
|
|
SELECT the_geom, admin1, admin2, admin3, admin4, altnames, asciiname, cc2, featclass, featcode, geonameid::int, gtopo30, country, lower(name) as lowername, name, population
|
|
FROM allcountries
|
|
WHERE featcode = 'PPLQ';
|
|
|
|
-- religious populated place a populated place whose population is largely engaged in religious occupations
|
|
-- insert PPLR data from source table (allcountries) to the table
|
|
-- it assumes that the source is called "allcountries"
|
|
INSERT INTO global_cities_points_limited (the_geom, admin1, admin2, admin3, admin4, altnames, asciiname, cc2, featclass, featcode, geoname_id, gtopo30, iso2, lowername, name, population)
|
|
SELECT the_geom, admin1, admin2, admin3, admin4, altnames, asciiname, cc2, featclass, featcode, geonameid::int, gtopo30, country, lower(name) as lowername, name, population
|
|
FROM allcountries
|
|
WHERE featcode = 'PPLR';
|
|
|
|
-- populated places cities, towns, villages, or other agglomerations of buildings where people live and work
|
|
-- insert PPLS data from source table (allcountries) to the table
|
|
-- it assumes that the source is called "allcountries"
|
|
INSERT INTO global_cities_points_limited (the_geom, admin1, admin2, admin3, admin4, altnames, asciiname, cc2, featclass, featcode, geoname_id, gtopo30, iso2, lowername, name, population)
|
|
SELECT the_geom, admin1, admin2, admin3, admin4, altnames, asciiname, cc2, featclass, featcode, geonameid::int, gtopo30, country, lower(name) as lowername, name, population
|
|
FROM allcountries
|
|
WHERE featcode = 'PPLS';
|
|
|
|
-- destroyed populated place a village, town or city destroyed by a natural disaster, or by war
|
|
-- insert PPLW data from source table (allcountries) to the table
|
|
-- it assumes that the source is called "allcountries"
|
|
INSERT INTO global_cities_points_limited (the_geom, admin1, admin2, admin3, admin4, altnames, asciiname, cc2, featclass, featcode, geoname_id, gtopo30, iso2, lowername, name, population)
|
|
SELECT the_geom, admin1, admin2, admin3, admin4, altnames, asciiname, cc2, featclass, featcode, geonameid::int, gtopo30, country, lower(name) as lowername, name, population
|
|
FROM allcountries
|
|
WHERE featcode = 'PPLW';
|
|
|
|
-- section of populated place
|
|
-- insert PPLX data from source table (allcountries) to the table
|
|
-- it assumes that the source is called "allcountries"
|
|
INSERT INTO global_cities_points_limited (the_geom, admin1, admin2, admin3, admin4, altnames, asciiname, cc2, featclass, featcode, geoname_id, gtopo30, iso2, lowername, name, population)
|
|
SELECT the_geom, admin1, admin2, admin3, admin4, altnames, asciiname, cc2, featclass, featcode, geonameid::int, gtopo30, country, lower(name) as lowername, name, population
|
|
FROM allcountries
|
|
WHERE featcode = 'PPLX';
|
|
|
|
-- israeli settlement
|
|
-- insert STLMT data from source table (allcountries) to the table
|
|
-- it assumes that the source is called "allcountries"
|
|
INSERT INTO global_cities_points_limited (the_geom, admin1, admin2, admin3, admin4, altnames, asciiname, cc2, featclass, featcode, geoname_id, gtopo30, iso2, lowername, name, population)
|
|
SELECT the_geom, admin1, admin2, admin3, admin4, altnames, asciiname, cc2, featclass, featcode, geonameid::int, gtopo30, country, lower(name) as lowername, name, population
|
|
FROM allcountries
|
|
WHERE featcode = 'STLMT';
|
|
|
|
-- edits source name for New York: New York City in order to give priority to the result
|
|
UPDATE global_cities_points_limited SET (name, lowername) = ('New York', 'new york') WHERE geoname_id = 5128581;
|