GeoNames Forum

I posted another topic in another forum here, but it really relates to one goal I have in mind. Instead of having location data in my geonames table AND my postal_codes table, I'd rather have my postal_codes table structured as follows:

Code:

CREATE TABLE `postal_codes2` (
     `country_code` CHAR(2) NOT NULL,
     `postal_code` VARCHAR(10) NOT NULL,
     `geoname_id` INT(10) UNSIGNED NOT NULL,
     PRIMARY KEY (`country_code`, `postal_code`)
 );

Now I think I've set up my INSERT statement correctly for US postal codes only so far, but I need to verify that I'm doing it correctly (see my other post for details on this specific concern). I also would like to make it work for non-US postal codes (e.g.: CA); however, the select statement I used for US doesn't seem to work due to issues with admin code data.

Here's what I tried so far: (be warned, this query took 20 minutes)

Code:

INSERT INTO `postal_codes2` (`country_code`, `postal_code`, `geoname_id`)
   SELECT p.`country_code`, p.`postal_code`, g.`id`
   FROM `postal_codes` p, `geonames` g
     WHERE p.`country_code` = g.`country_code`
     AND p.`place_name` = g.`name`
     AND p.`admin_code1` = g.`admin1_code`
     AND g.`feature_class` = 'P'
     AND g.`feature_code` = 'PPL'
     AND p.`country_code` = 'US'
     GROUP BY p.`country_code`, p.`postal_code`;

The GROUP BY was used to resolve my original issue linked above. However, that doesn't mean it is the *right* way to do it. Has anyone tried anything similar to this rather than managing two tables worth of data? If so, care to share your process for creating the tables and inserting the data?

We are also trying to match postal code places with the toponyms from the main GeoNames database. Our goal is to have only one database. To have a seperate postal code download as we have now is not our goal, we want to unify the two databases.
As you say this is far from trivial and I don't believe it can be solved with a simple sql statement. We are using algorithms which look at the names in different languages, look for similar names (names are not always spellt exactly the same way), looking at the distance, feature code etc.

Best

Marc

Thanks for the response, marc, I greatly appreciate your input. I didn't realize this was an intended goal of the project. I definitely lack enough familiarity with geocoding to be able to perform what you describe here. I look forward to any updates that help progress Geonames in that direction!

I'm very interesting to associate all the postalcode with the geonameid.

Do you have an idea when it will be available ?

Thank's.

Best Regard

Hi Patou, I have no idea when it will be available, but certainly not anytime soon. I simply don't have time to work on it right now.

Marc

Something very usefull to help matching zipcodes to geonameids would be to use the same admin codes in the zip dump and in the main dump (country code is ok, but admin1 and admin2 are different, event names are different).

Lou

Lou

This is true it would be easier with the exact spelling. Do you want to help us with this?

Thanks.

Marc

Do you have an idea how I can associate a postal code with a geonameid ?

Thank's