Latest posts for the topic "Forcing postal codes to reference geoname_id?"

Forcing postal codes to reference geoname_id?

mhuggins — GMT

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?

Re:Forcing postal codes to reference geoname_id?

marc — GMT

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

Re:Forcing postal codes to reference geoname_id?

mhuggins — GMT

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!

Re:Forcing postal codes to reference geoname_id?

patou — GMT

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

Re:Forcing postal codes to reference geoname_id?

marc — GMT

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

Re:Forcing postal codes to reference geoname_id?

Lou — GMT

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

Re:Forcing postal codes to reference geoname_id?

marc — GMT

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

Re:Forcing postal codes to reference geoname_id?

patou — GMT

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