GeoNames Forum

I've loaded the dumps into an MS SQL Database and have no problems querying them. However, I'm a bit stuck trying to figure out the best way to query the Names given a specific language.

As an example let's look at the North America Continent in IsoLanguage = 'es':

6255149 es Norteamérica
6255149 es América del Norte
6255149 es América Septentrional

Both the IsPreferredName and IsShortName fields are 0 for the above results, so how would we write a single query to retrieve the name, and which one should we use?

As another example, the following query returns 14 rows due to the different languages:

SELECT G.GeoNameId, A.AlternateName, G.Longitude, G.Latitude
FROM GeoNames as G
JOIN AlternateNames as A ON G.GeoNameId = A.GeoNameId
WHERE
GeoNameId IN (SELECT GeoNameId FROM Continents)
AND
A.IsoLanguage = 'es'

If I add the IsPreferredName field to the query I get only 2 rows.

OK, I've built my own solution to this issue that is somewhat of a workaround.

I have created a new boolean field in the AlternateNames table called UseInDropDowns. I then wrote a procedure to set this field base on the IsPreferredName and IsShortName. I made the decision that my dropdowns would always prefer the preferred shortname, and hence I check for the first and then check the preferred name only, finally checking with both these fields off.

With that procedure in place I then use another procedure to pull a list of continents and loop through setting the UseInDropDowns field.

After running it for the continents when I execute:

SELECT G.GeoNameId, A.AlternateName, G.Longitude, G.Latitude
FROM GeoNames as G
JOIN AlternateNames as A ON G.GeoNameId = A.GeoNameId
WHERE
G.GeoNameId IN (SELECT GeoNameId FROM Continents)
AND
A.IsoLanguage = 'es'
AND
A.UseInDropDowns = 1

I get the following results:

GeoNameId AlternateName Longitude Latitude
6255149 Norteamérica -100.0000000 46.0000000
6255150 Sudamérica -57.0000000 -14.0000000
6255148 Europa 9.0000000 48.0000000
6255146 África 21.0000000 7.0000000
6255147 Asia 89.0000000 29.0000000
6255151 Oceanía 138.0000000 -18.0000000
6255152 Antártida 16.0000000 -83.0000000

I have also managed to do this with the Countries, although not all countries have entries in the AlternateNames table so it isn't a simple case of pulling the names from that table. However, as I use .Net for most of my development I can use a custom resourcemanager to pull a localized version and if the string is not found in that fall back to using an invariant version (which I get from the CountryInfo table).

The good thing about this solution is that I can have a stored procedure run once updates have been imported into the GeoName and AlternateNames table to recalculate the value for the UseInDropDown field.