<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0">
	<channel>
		<title><![CDATA[Latest posts for the topic "Multi-Lingual Results / MS SQL"]]></title>
		<link>http://forum.geonames.org/gforum/posts/list/4.page</link>
		<description><![CDATA[Latest messages posted in the topic "Multi-Lingual Results / MS SQL"]]></description>
		<generator>JForum - http://www.jforum.net</generator>
			<item>
				<title>Multi-Lingual Results / MS SQL</title>
				<description><![CDATA[ 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. 


]]></description>
				<guid isPermaLink="true">http://forum.geonames.org/gforum/posts/list/1162.page#5009</guid>
				<link>http://forum.geonames.org/gforum/posts/list/1162.page#5009</link>
				<pubDate><![CDATA[Sat, 22 Nov 2008 19:23:39]]> GMT</pubDate>
				<author><![CDATA[ Suneel]]></author>
			</item>
			<item>
				<title>Re:Multi-Lingual Results / MS SQL</title>
				<description><![CDATA[ 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.]]></description>
				<guid isPermaLink="true">http://forum.geonames.org/gforum/posts/list/1162.page#5011</guid>
				<link>http://forum.geonames.org/gforum/posts/list/1162.page#5011</link>
				<pubDate><![CDATA[Mon, 24 Nov 2008 15:57:15]]> GMT</pubDate>
				<author><![CDATA[ Suneel]]></author>
			</item>
	</channel>
</rss>