Skip to content

Latest commit

 

History

History
101 lines (62 loc) · 3.27 KB

README.md

File metadata and controls

101 lines (62 loc) · 3.27 KB
Raw

Sri Lanka Provinces Districts Cities Database

About

This is a MySQL version of Sri Lankan Provinces => Districts => Cities related data.

There are three SQL files,

  1. provinces.sql (Names of nine provinces)
  2. districts.sql (All districts related to each province)
  3. cities.sql (All cities related to each district)

Updates

  • July 18 2016 - Changed the structure of cities table ability to add sub city names.

Statistics

  • Provinces - 9
  • Districts - 25
  • Cities - 1846

Sample tables structure with data

Provinces

id name_en name_si name_ta
1 Western බස්නාහිර மேல
2 Central මධ්‍යම மத்தி

Districts

id province_id name_en name_si name_ta
1 6 Ampara අම්පාර அம்பாறை
2 8 Anuradhapura අනුරාධපුරය அனுராதபுரம்

Cities

id district_id name_en name_si name_ta sub_name_en sub_name_si sub_name_ta postcode latitude longitude
1 1 Akkaraipattu අක්කරපත්තුව NULL NULL NULL NULL 32400 7.2167 81.85
2 1 Ambagahawatta අඹගහවත්ත NULL NULL NULL NULL 90326 7.4 81.3

Install

In order to prevent unnecessary error occurring, start to import or execute provinces.sql, then districts.sql, lastly cities.sql

MySQL Usage

Advantages of latitude and longitude

  • Integrate with google map or any map related service to show exact place of the city in the map.
  • Find locations are within a certain radius distance of a given latitude/longitude.

Here's the SQL statement that will find the closest locations that are within a radius of 25 kilometers to the 7.358849, 81.280133 coordinate. It calculates the distance based on the latitude/longitude of that row and the target latitude/longitude, and then asks for only rows where the distance value is less than 25, orders the whole query by distance.

SELECT id, name_en, name_si, name_ta, (6371 * ACOS(COS(RADIANS(7.358849)) * COS(RADIANS(latitude)) * COS(RADIANS(longitude) - RADIANS(81.280133)) + SIN(RADIANS(7.358849)) * SIN(RADIANS(latitude)))) AS distance
FROM cities
HAVING distance < 25
ORDER BY distance

Note

  • This free database dose not guarantee for the complete list of cities in Sri Lanka.
  • Feel free to contribute to the project.
  • TODO - Translate names into සිංහල and தமிழ் for multilingual purposes.

Translation in progress

Provinces

සිංහල தமிழ
100% 100%

Districts

සිංහල தமிழ
100% 100%

Cities

සිංහල தமிழ
48% 0.6%