Thailand Provinces and Districts

This topic was automatically closed 365 days after the last reply. New replies are no longer allowed.
10 Jahre weitere
A small code that may make publishing in Thai easier...

I took the information from here: http://www.statoids.com/uth.html, and google translated names into Thai. I have doubled check the names with my Thai friends and they are correct. Thanks to Gwillim Law to allow me to post this content!

This simple code inserts the Provinces and Districts as "States". 


declare @provinces xml

set @provinces=N'
<provinces>
  <Province NameUs="Amnat Charoen" nameth="อำนาจเจริญ" HASC="TH.AC" ISO="37" FIPS="TH77" REG="NE" />
  <Province NameUs="Ang Thong" nameth="อ่างทอง" HASC="TH.AT" ISO="15" FIPS="TH35" REG="C" />
  <Province NameUs="Bangkok Metropolis" nameth="กรุงเทพมหานคร" HASC="TH.BM" ISO="10" FIPS="TH40" REG="C" />
  <Province NameUs="Bueng Kan" nameth="บึงกาญจน์" HASC="TH.BK" ISO="" FIPS="TH81" REG="NE" />
  <Province NameUs="Buri Ram" nameth="บุรีรัมย์" HASC="TH.BR" ISO="31" FIPS="TH28" REG="NE" />
  <Province NameUs="Chachoengsao" nameth="ฉะเชิงเทรา" HASC="TH.CC" ISO="24" FIPS="TH44" REG="C" />
  <Province NameUs="Chai Nat" nameth="ชัยนาท" HASC="TH.CN" ISO="18" FIPS="TH32" REG="C" />
  <Province NameUs="Chaiyaphum" nameth="ชัยภูมิ" HASC="TH.CY" ISO="36" FIPS="TH26" REG="NE" />
  <Province NameUs="Chanthaburi" nameth="จันทบุรี" HASC="TH.CT" ISO="22" FIPS="TH48" REG="E" />
  <Province NameUs="Chiang Mai" nameth="เชียงใหม่" HASC="TH.CM" ISO="50" FIPS="TH02" REG="N" />
  <Province NameUs="Chiang Rai" nameth="เชียงราย" HASC="TH.CR" ISO="57" FIPS="TH03" REG="N" />
  <Province NameUs="Chon Buri" nameth="ชลบุรี" HASC="TH.CB" ISO="20" FIPS="TH46" REG="E" />
  <Province NameUs="Chumphon" nameth="ชุมพร" HASC="TH.CP" ISO="86" FIPS="TH58" REG="S" />
  <Province NameUs="Kalasin" nameth="กาฬสินธุ์" HASC="TH.KL" ISO="46" FIPS="TH23" REG="NE" />
  <Province NameUs="Kamphaeng Phet" nameth="กำแพงเพชร" HASC="TH.KP" ISO="62" FIPS="TH11" REG="N" />
  <Province NameUs="Kanchanaburi" nameth="กาญจนบุรี" HASC="TH.KN" ISO="71" FIPS="TH50" REG="C" />
  <Province NameUs="Khon Kaen" nameth="ขอนแก่น" HASC="TH.KK" ISO="40" FIPS="TH22" REG="NE" />
  <Province NameUs="Krabi" nameth="กระบี่" HASC="TH.KR" ISO="81" FIPS="TH63" REG="S" />
  <Province NameUs="Lampang" nameth="ลำปาง" HASC="TH.LG" ISO="52" FIPS="TH06" REG="N" />
  <Province NameUs="Lamphun" nameth="ลำพูน" HASC="TH.LN" ISO="51" FIPS="TH05" REG="N" />
  <Province NameUs="Loei" nameth="เลย" HASC="TH.LE" ISO="42" FIPS="TH18" REG="NE" />
  <Province NameUs="Lop Buri" nameth="ลพบุรี" HASC="TH.LB" ISO="16" FIPS="TH34" REG="C" />
  <Province NameUs="Mae Hong Son" nameth="แม่ฮ่องสอน" HASC="TH.MH" ISO="58" FIPS="TH01" REG="N" />
  <Province NameUs="Maha Sarakham" nameth="มหาสารคาม" HASC="TH.MS" ISO="44" FIPS="TH24" REG="NE" />
  <Province NameUs="Mukdahan" nameth="มุกดาหาร" HASC="TH.MD" ISO="49" FIPS="TH78" REG="NE" />
  <Province NameUs="Nakhon Nayok" nameth="นครนายก" HASC="TH.NN" ISO="26" FIPS="TH43" REG="C" />
  <Province NameUs="Nakhon Pathom" nameth="นครปฐม" HASC="TH.NP" ISO="73" FIPS="TH53" REG="C" />
  <Province NameUs="Nakhon Phanom" nameth="นครพนม" HASC="TH.NF" ISO="48" FIPS="TH73" REG="NE" />
  <Province NameUs="Nakhon Ratchasima" nameth="นครราชสีมา" HASC="TH.NR" ISO="30" FIPS="TH27" REG="NE" />
  <Province NameUs="Nakhon Sawan" nameth="นครสวรรค์" HASC="TH.NS" ISO="60" FIPS="TH16" REG="N" />
  <Province NameUs="Nakhon Si Thammarat" nameth="นครศรีธรรมราช" HASC="TH.NT" ISO="80" FIPS="TH64" REG="S" />
  <Province NameUs="Nan" nameth="น่าน" HASC="TH.NA" ISO="55" FIPS="TH04" REG="N" />
  <Province NameUs="Narathiwat" nameth="นราธิวาส" HASC="TH.NW" ISO="96" FIPS="TH31" REG="S" />
  <Province NameUs="Nong Bua Lam Phu" nameth="ภูหนองบัวลำ" HASC="TH.NB" ISO="39" FIPS="TH79" REG="NE" />
  <Province NameUs="Nong Khai" nameth="หนองคาย" HASC="TH.NH" ISO="43" FIPS="TH17" REG="NE" />
  <Province NameUs="Nonthaburi" nameth="นนทบุรี" HASC="TH.NO" ISO="12" FIPS="TH38" REG="C" />
  <Province NameUs="Pathum Thani" nameth="ปทุมธานี" HASC="TH.PT" ISO="13" FIPS="TH39" REG="C" />
  <Province NameUs="Pattani" nameth="ปัตตานี" HASC="TH.PI" ISO="94" FIPS="TH69" REG="S" />
  <Province NameUs="Phangnga" nameth="พังงา" HASC="TH.PG" ISO="82" FIPS="TH61" REG="S" />
  <Province NameUs="Phatthalung" nameth="พัทลุง" HASC="TH.PL" ISO="93" FIPS="TH66" REG="S" />
  <Province NameUs="Phayao" nameth="พะเยา" HASC="TH.PY" ISO="56" FIPS="TH41" REG="N" />
  <Province NameUs="Phetchabun" nameth="เพชรบูรณ์" HASC="TH.PH" ISO="67" FIPS="TH14" REG="N" />
  <Province NameUs="Phetchaburi" nameth="เพชรบุรี" HASC="TH.PE" ISO="76" FIPS="TH56" REG="C" />
  <Province NameUs="Phichit" nameth="พิจิตร" HASC="TH.PC" ISO="66" FIPS="TH13" REG="N" />
  <Province NameUs="Phitsanulok" nameth="พิษณุโลก" HASC="TH.PS" ISO="65" FIPS="TH12" REG="N" />
  <Province NameUs="Phrae" nameth="แพร่" HASC="TH.PR" ISO="54" FIPS="TH07" REG="N" />
  <Province NameUs="Phra Nakhon Si Ayutthaya" nameth="พระนครศรีอยุธยา" HASC="TH.PA" ISO="14" FIPS="TH36" REG="C" />
  <Province NameUs="Phuket" nameth="ภูเก็ต" HASC="TH.PU" ISO="83" FIPS="TH62" REG="S" />
  <Province NameUs="Prachin Buri" nameth="ปราจีนบุรี" HASC="TH.PB" ISO="25" FIPS="TH74" REG="C" />
  <Province NameUs="Prachuap Khiri Khan" nameth="ประจวบคีรีขันธ์" HASC="TH.PK" ISO="77" FIPS="TH57" REG="C" />
  <Province NameUs="Ranong" nameth="ระนอง" HASC="TH.RN" ISO="85" FIPS="TH59" REG="S" />
  <Province NameUs="Ratchaburi" nameth="ราชบุรี" HASC="TH.RT" ISO="70" FIPS="TH52" REG="C" />
  <Province NameUs="Rayong" nameth="ระยอง" HASC="TH.RY" ISO="21" FIPS="TH47" REG="E" />
  <Province NameUs="Roi Et" nameth="ร้อยเอ็ด" HASC="TH.RE" ISO="45" FIPS="TH25" REG="NE" />
  <Province NameUs="Sa Kaeo" nameth="แก้วสา" HASC="TH.SK" ISO="27" FIPS="TH80" REG="C" />
  <Province NameUs="Sakon Nakhon" nameth="สกลนคร" HASC="TH.SN" ISO="47" FIPS="TH20" REG="NE" />
  <Province NameUs="Samut Prakan" nameth="สมุทรปราการ" HASC="TH.SP" ISO="11" FIPS="TH42" REG="C" />
  <Province NameUs="Samut Sakhon" nameth="สมุทรสาคร" HASC="TH.SS" ISO="74" FIPS="TH55" REG="C" />
  <Province NameUs="Samut Songkhram" nameth="สมุทรสงคราม" HASC="TH.SM" ISO="75" FIPS="TH54" REG="C" />
  <Province NameUs="Saraburi" nameth="สระบุรี" HASC="TH.SR" ISO="19" FIPS="TH37" REG="C" />
  <Province NameUs="Satun" nameth="สตูล" HASC="TH.SA" ISO="91" FIPS="TH67" REG="S" />
  <Province NameUs="Sing Buri" nameth="สิงห์บุรี" HASC="TH.SB" ISO="17" FIPS="TH33" REG="C" />
  <Province NameUs="Si Sa Ket" nameth="ศรีสะเกษ" HASC="TH.SI" ISO="33" FIPS="TH30" REG="NE" />
  <Province NameUs="Songkhla" nameth="สงขลา" HASC="TH.SG" ISO="90" FIPS="TH68" REG="S" />
  <Province NameUs="Sukhothai" nameth="สุโขทัย" HASC="TH.SO" ISO="64" FIPS="TH09" REG="N" />
  <Province NameUs="Suphan Buri" nameth="สุพรรณบุรี" HASC="TH.SH" ISO="72" FIPS="TH51" REG="C" />
  <Province NameUs="Surat Thani" nameth="สุราษฎร์ธานี" HASC="TH.ST" ISO="84" FIPS="TH60" REG="S" />
  <Province NameUs="Surin" nameth="สุรินทร์" HASC="TH.SU" ISO="32" FIPS="TH29" REG="NE" />
  <Province NameUs="Tak" nameth="ตาก" HASC="TH.TK" ISO="63" FIPS="TH08" REG="N" />
  <Province NameUs="Trang" nameth="ตรัง" HASC="TH.TG" ISO="92" FIPS="TH65" REG="S" />
  <Province NameUs="Trat" nameth="ตราด" HASC="TH.TT" ISO="23" FIPS="TH49" REG="E" />
  <Province NameUs="Ubon Ratchathani" nameth="อุบลราชธานี" HASC="TH.UR" ISO="34" FIPS="TH75" REG="NE" />
  <Province NameUs="Udon Thani" nameth="อุดรธานี" HASC="TH.UN" ISO="41" FIPS="TH76" REG="NE" />
  <Province NameUs="Uthai Thani" nameth="อุทัยธานี" HASC="TH.UT" ISO="61" FIPS="TH15" REG="N" />
  <Province NameUs="Uttaradit" nameth="อุตรดิตถ์" HASC="TH.UD" ISO="53" FIPS="TH10" REG="N" />
  <Province NameUs="Yala" nameth="ยะลา" HASC="TH.YL" ISO="95" FIPS="TH70" REG="S" />
  <Province NameUs="Yasothon" nameth="ยโสธร" HASC="TH.YS" ISO="35" FIPS="TH72" REG="NE" />
</provinces>
'  



CREATE TABLE #Provinces
  (
    [NameUs] [nvarchar](200) NULL,
    [NameTh] [nvarchar](200) NULL,
    [HASC] [nvarchar](200) NULL,
    [ISO] [nvarchar](200) NULL,
    [FIPS] [nvarchar](200) NULL,
    [REG] [nvarchar](200) NULL
  )

INSERT INTO #Provinces ([NameUs], [NameTh],[HASC],[ISO],[FIPS],[REG])
SELECT  
    nref.value('@NameUs', 'nvarchar(200)'), 
    nref.value('@nameth', 'nvarchar(200)'), 
    nref.value('@HASC', 'nvarchar(200)'), 
    nref.value('@ISO', 'nvarchar(200)'), 
    nref.value('@FIPS', 'nvarchar(200)'), 
    nref.value('@REG', 'nvarchar(200)')
    
FROM  @provinces.nodes('//provinces/Province') AS R(nref)

--SELECT * FROM #Provinces


DECLARE @UsLanguageId int; SET @UsLanguageId = (SELECT TOP 1 [Id] FROM Language WHERE LanguageCulture = 'en-US')
DECLARE @ThLanguageId int; SET @ThLanguageId = (SELECT TOP 1 [Id] FROM Language WHERE LanguageCulture = 'th-TH')
DECLARE @ThailandId int;  SET @ThailandId = (SELECT TOP 1 [Id] FROM Country WHERE ThreeLetterIsoCode = 'THA')
DECLARE cur_region CURSOR FOR
SELECT [NameUs], [NameTh],[HASC],[ISO],[FIPS],[REG]
FROM #Provinces

DECLARE @NameUs nvarchar(200);
DECLARE @NameTh nvarchar(200);
DECLARE @HASC nvarchar(200);
DECLARE @ISO nvarchar(200);
DECLARE @FIPS nvarchar(200);
DECLARE @REG nvarchar(200);


OPEN cur_region
FETCH NEXT FROM cur_region INTO @NameUs,@NameTh,@HASC,@ISO,@FIPS,@REG
WHILE @@FETCH_STATUS = 0
BEGIN
  DECLARE @StateProvinceId int;
  
  SET @StateProvinceId = (SELECT [Id] FROM StateProvince WHERE CountryId = @ThailandId AND Abbreviation =  @HASC)
  
  IF (@StateProvinceId>0)
  BEGIN
    UPDATE [StateProvince] SET [Name] = @NameUs  WHERE Id = @StateProvinceId;
  END
  ELSE
  BEGIN
    INSERT INTO [StateProvince]
           ([CountryId]
           ,[Name]
           ,[Abbreviation]
           ,[Published]
           ,[DisplayOrder])
     VALUES
           (
           @ThailandId
           ,@NameUs
           ,@HASC
           ,1
           ,1);

    SET @StateProvinceId = SCOPE_IDENTITY();

  END
  
  
  IF (@StateProvinceId>0 AND @ThLanguageId>0)
  BEGIN
    DECLARE @LocalizedPropertyId int;
    SET @LocalizedPropertyId = (SELECT Id FROM LocalizedProperty WHERE LanguageId = @ThLanguageId AND LocaleKeyGroup = 'StateProvince' AND LocaleKey = 'Name' AND EntityId = @StateProvinceId)
  
    IF (@LocalizedPropertyId>0)
    BEGIN
      UPDATE LocalizedProperty SET LocaleValue = @NameTh WHERE Id = @LocalizedPropertyId;
    END
    ELSE
    BEGIN
      INSERT INTO LocalizedProperty 
      (
      EntityId,
      LanguageId,
      LocaleKeyGroup,
      LocaleKey,
      LocaleValue
      )
      VALUES
      (
      @StateProvinceId,
      @ThLanguageId,
      'StateProvince',
      'Name',
      @NameTh
      );
    END
    
  
  END

  --fetch next language identifier
  FETCH NEXT FROM cur_region INTO @NameUs,@NameTh,@HASC,@ISO,@FIPS,@REG
END
CLOSE cur_region
DEALLOCATE cur_region

DROP TABLE #Provinces
GO

0
10 Jahre weitere
Using Google translate to get the Thai spelling from the romanized name was not a good idea, there are several mistakes in your table. I wonder why none of your Thai friends spotted them, as two are quite striking - the order of the syllables was mixed up.

Bueng Kan: บึงกาฬ
Nong Bua Lam Phu: หนองบัวลำภู
Sa Kaeo: สระแก้ว

The recommended transcription for Phangnga is Phang Nga.

If you want to add the districts (Amphoe) as well, you better start with a spreadsheet I have compiled (https://dl.dropboxusercontent.com/u/473159/TH%20Province.ods) or use the XML files from http://code.google.com/p/tambon

Cheers,
   Andy (http://tambon.blogspot.com)
1
This topic was automatically closed 365 days after the last reply. New replies are no longer allowed.