GeoNames Home | Postal Codes | Download / Webservice | About 

GeoNames Forum
  [Search] Search   [Recent Topics] Recent Topics   [Groups] Back to home page 
[Register] Register / 
[Login] Login 
"Metropolis" division  XML
Forum Index -> Administrative Divisions
Author Message
vadimberman



Joined: 01/07/2008 02:51:43
Messages: 3
Location: Melbourne, Australia
Offline

Hi forumites,

First and foremost, thank you very much for the incredibly useful and comprehensive dataset.

A question: while the administrative divisions are very well-marked, is there any designation to conglomerates of cities that have grown into one large metropolis? E.g., Melbourne Metro, Greater London, Greater Tokyo, Metro Manila, etc. These territories are frequently de-facto administrative divisions when it comes to transportation, infrastructure, etc.

Regards, Vadim
[WWW]
marc



Joined: 08/12/2005 07:39:47
Messages: 4412
Offline

Hi Vadim

We have some relations between PPLX and their PPL, but we don't yet have any information about 'Metropolis' where PPL are considered part of a larger entity.

Best

Marc

[WWW]
vadimberman



Joined: 01/07/2008 02:51:43
Messages: 3
Location: Melbourne, Australia
Offline

Hi Marc,

Thanks for tuning in.

I have already done this meanwhile, in a somewhat clumsy way, but suitable for my purposes.

If you or someone else needs it (all different names and structure, so just adjust it for your environment):

SELECT 'SUBRB', g.StandardizedFullName, g.Latitude, g.Longitude, city.LocationID
FROM tGazetteerUSMil g
INNER JOIN tLocation country
ON country.LocationKind = 'CNTRY' AND country.ExternalReference = g.CountryCode
INNER JOIN tLocation prov
ON prov.LocationKind = 'STATE' AND prov.ParentLocation = country.LocationID
INNER JOIN tLocation adm2
ON adm2.LocationKind = 'ADM2' AND adm2.ParentLocation = prov.LocationID
INNER JOIN tLocation city
ON city.LocationKind = 'CITY' AND city.ParentLocation = adm2.LocationID
INNER JOIN tCity1000 c
ON c.Country_Code = g.CountryCode AND c.Name = city.Name AND c.Population > 500000
WHERE (g.FeatureDesignationCode IN ('PPL', 'PPLX', 'ADMX', 'ADMD', 'ADM3')) AND ROUND(city.LatitudeDegrees / 7,1) = ROUND(g.Latitude / 7,1) AND ROUND(city.LongitudeDegrees / 7,1) = ROUND(g.Longitude / 7,1)
GROUP BY g.StandardizedFullName, city.LocationID


I'm adding 'PPL' because in many cases they are mis-marked. For example, Camberwell in Victoria, Australia is a suburb of Melbourne for 90 years or so (I would expect it to be ADMX), and it's marked as a PPL.

I'm assuming that only cities with the population over 500,000 become metropolises (the population is available in your file of cities with the population over 1,000).

I'm merely rounding the coordinates instead of the ACOS formulas, because the distances are really close (within 10 km) so I'm assuming the earth is "flat" .

Obviously, a bunch of very crude assumptions, but maybe if you decide to implement it one day, this is something to start working with. Also, if you need a way to build hierarchies, I also have some scripts.

PS. Correcting myself: do not use the ROUND approach, it is horrendously slow for real-world datasets. Use some kind of range, or MBRContains in MySql, etc.
[WWW]
 
Forum Index -> Administrative Divisions
Go to:   
Powered by JForum 2.1.5 © JForum Team