GeoNames Home | Postal Codes | Download / Webservice | About 

GeoNames Forum
  [Search] Search   [Recent Topics] Recent Topics   [Groups] Back to home page 
[Register] Register / 
[Login] Login 
Duplicate Alternate Names  XML
Forum Index -> Administrative Divisions
Author Message
PJonDevelopment



Joined: 06/03/2008 01:24:26
Messages: 14
Location: Rio de Janeiro, Brazil
Offline

Below there is a list of duplicated Alternate Names.

I based the uniqueness of the Alternate Names on GeoNameID, IDOLanguage where both the isPreferredName and isShortName is true.

Code:
  GeoNameID ISOLanguage isPreferredName isShortName  CountNames
 ----------- ----------- --------------- ----------- -----------
     3336903             1               1                     2
     2543878 fr          1               1                     2
     2509951 jp          1               1                     2
     2510910 jp          1               1                     2
     2515271 jp          1               1                     2
     2593110 jp          1               1                     2
     2593111 jp          1               1                     2
     3119840 jp          1               1                     2
     3125881 jp          1               1                     2
     3128759 jp          1               1                     2
     6355233 jp          1               1                     2

Paulo Santos
http://pjondevelopment.50webs.com
[WWW]
PJonDevelopment



Joined: 06/03/2008 01:24:26
Messages: 14
Location: Rio de Janeiro, Brazil
Offline

to fix this you can use a query like this:

Code:
Delete tbAlternateNames
  where idAlternateName in 
          (select Max(idAlternateName)
              from tbAlternateNames
             where isPreferredName = 1
               and isShortName = 1
             group
                by idGeoName
                  ,ISOLanguage
                  ,isShortName
                  ,isPreferredName
            having Count(idAlternateName) > 1
             )

Paulo Santos
http://pjondevelopment.50webs.com
[WWW]
 
Forum Index -> Administrative Divisions
Go to:   
Powered by JForum 2.1.5 © JForum Team