GeoNames Forum

Thanks, it has been inserted.

Marc

Greetings - Thank you.

To finish this up for now. I will mention that the SQL above matched a large portion of the records. Things that did not match by comparison or where not in the feature class of 'P' where not matched.

Enclosed bellow is the SQL that will show you the records that still are not matched.

Code:

 SELECT 
   MAX(geoname_id)                           AS geoname_id,
   MAX(geoname_name_id)                      AS geoname_name_id,
   MAX(places_full_name)                     AS places_full_name,
   country_code                              AS country_code,
   postal_code                               AS postal_code,
   MAX(postal_full_name)                     AS postal_full_name
   --
 FROM (
   SELECT *
   FROM (
     SELECT 
       gplaces.geoname_id                    AS geoname_id,
       gnames.geoname_name_id                AS geoname_name_id, 
       trim(gplaces.full_name)               AS places_full_name, 
       null::text                            AS postal_full_name,
       trim(gplaces.country_code)            AS country_code, 
       trim(gnames.alternate_name)           AS postal_code 
       -- 
     FROM     xwor.geoname_places            AS gplaces
     JOIN     xwor.geoname_places_names      AS gnames  ON gnames.geoname_id = gplaces.geoname_id
     WHERE    gnames.iso_language = 'post'
     ORDER BY gplaces.country_code, gnames.alternate_name
   ) AS places
 UNION 
   SELECT *
   FROM (
     SELECT 
       null::bigint                          AS geoname_id,
       null::bigint                          AS geoname_name_id,
       null::text                            AS places_full_name, 
       trim(gpostal.place_name)              AS postal_full_name, 
       trim(gpostal.country_code)            AS country_code, 
       trim(gpostal.postal_code)             AS postal_code 
       -- 
     FROM     xwor.geoname_places_postal     AS gpostal
     ORDER BY gpostal.country_code, gpostal.postal_code
   ) AS postal
 ) AS x
 WHERE country_code = 'US' AND geoname_id IS NULL
 GROUP BY country_code, postal_code
 ORDER BY country_code, postal_code
 ;

In my copious

spare time I will see what other countries I can do an original match.

Of course adding what is ---not--- there will take a bit better SQL since you do not want to add what ---is--- already there.

Kindest Regards,

While I understand this postal code linking is useful for a lot of people, let me just express my arguments for the record. Postal codes are not alternate names in any way, I now need to strip them out from the DB before I use it to match on a city name. They are separate geographical entities with a M:M mapping with cities. Using them as alternate names gives the false illusion of simplicity. A different solution should have been devised.

Plus on a minor note, look up New York City, the alternate names list looks crappy.

I'm sorry I never got back to you on this. I don't have a perfect solution for postal codes in geonames. In my mind, geonames is missing two fundamental things for an ideal semantic solution: a concept of boundaries and a concept of relationships. Boundaries are rough because they get very complex and not that you shouldn't do it yourself if your system requires it, but it might be too much for geonames. Geonames has a niche as a logical point-in-space datasource. It would have to fairly dramatically change it's focus to handle geometric space.

What I think geonames needs is a more generic relationship table. The alternate names is currently being overloaded for this purpose for postal codes. Right now there are only a couple relationships that are really available in the system: admin1, admin2, and country. If those columns weren't part of the main table and instead were in a linked table you could do more interesting things. This could solve a lot of issues that are being brought up in other places on the forum too. Concepts of neighborhoods, postal codes, nesting of levels beyond admin2 in depth - all of these things can be solved/improved with another table. In my mind, this is going to be necessary going forward to be a useful service. So postal code would be another feature code and placed at it's centroid and then linked via a postal code-city relationship to the PPLs they cover.

The question is the availability of free (and open) data. There is hardy any data available for a data model as you would like to have. So it does not make sense defining a data model when there is no data to fill it with. An empty data model is not so interesting in my opinion, I prefer a simpler model with sufficient data rather than a complex model with no data.

Marc

I sort of disagree with both the lack of data and the need for data. Isn't the point of geonames to be a repository of sharing and updating location data? It seems silly to hold back what data can be put in because you personally don't have it. Maybe I want to sit down for a night and link points with relationships. I've seen people laboriously putting their hotels in and I don't think you have a hotel data feed in geonames.

The most obvious short-term advantage is the merge of the postal code DB into the location DB to make them editable points. It's not a good long term solution to tell you every time I find an individual postal code centroid is off (though you do respond very quickly).

Plus this is not exactly a complex model. I'm talking about one extra self join relationship table for a lot of extra expression. If you control the relationship types that exist much like feature codes are controlled, it won't get much more complex than it currently is.

Hello,

Having a multiple-to-multiple link table between postal codes and geonameids would be pretty helpful for me, and I think others would appreciate to have it in their applications as well.

About Marc's concern on the availability of free, open data. Can't we just populate this new table with the data we already have in alternateNames table as a first step? That is, the first release of the new table will provide a table that supports m-to-m relationships, but only has 1-to-m data. However, as it will be editable, we could gradually fill in the missing links as we go.

I see it as a halfway solution. Could we reach an agreement at these terms?

I don't see much benefit in a m:n model, it is more a theoretical discussion. There are tons of other more urgent ways to improve the database. Making the datamodel more complex would only be a pain and I don't think anybody would add data in a m:n user interface, as this is already possible with the alt names, there would be no advantage for this situation.

Best

Marc