This is a MySQL version of Sri Lankan Provinces => Districts => Cities, and related data. Each city has longitude, latitude, and postal code (postcode) other than its name.
If you discover wrong translations or any other issues, please use the issue tracker to mention or send a pull request with the changes.
There are three SQL files,
- provinces.sql (Names of nine provinces)
- districts.sql (All districts related to each province)
- cities.sql (All cities related to each district)
- July 18, 2016 - Changed the structure of cities table ability to add sub-city names.
- April 25, 2022 - Completed translations in all three languages.
- Provinces - 9
- Districts - 25
- Cities - 2154
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 |
|---|---|---|---|---|---|---|---|---|---|---|
| 338 | 5 | Colombo 8 | කොළඹ 8 | கொழும்பு 8 | Borella | බොරැල්ල | பொறளை | 00800 | 6.914722 | 79.877778 |
| 376 | 5 | Colombo 6 | කොළඹ 6 | கொழும்பு 6 | Wellawatta | වැල්ලවත්ත | வெள்ளவத்தை | 00600 | 6.874657 | 79.860483 |
To prevent unnecessary errors from occurring, start to import or execute provinces.sql, then districts.sql, and lastly cities.sql
Advantages of latitude and longitude
- Integrate with Google Maps or any map-related service to show the exact place of the city on the map.
- Find locations within a certain radius of a given latitude/longitude.
Find nearby locations using the Haversine formula
Here's the SQL statement that will find the closest locations 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, ordering 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 distanceThe above assumes that you have distance in kilometers. If distance is in miles then replace 6371 with 3959.
References:
- https://en.wikipedia.org/wiki/Haversine_formula
- https://stackoverflow.com/questions/574691/mysql-great-circle-distance-haversine-formula
- This free database does not guarantee the complete list of cities in Sri Lanka.
- Feel free to contribute to the project.
Provinces
සිංහල தமிழ 100% 100%
Districts
සිංහල தமிழ 100% 100%
Cities
සිංහල தமிழ 100% 100%