srilanka cities Download - srilanka cities Source code download

srilanka cities

Other source code

Download

Sri Lanka Provinces Districts Cities Database

About the

This is a mysql version of sri lankan provinces => Districts => Cities related data.

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)

Updates

July 18 2016 - Changed The Structure Of Cities Table Ability to Add Sub City Names.

###Statitiss

Provences -
Districts -
Cities -

###Sammple Tables Structure with data

Provinces

id	Name_En	Name_si	Name_Ta
1	Westernn	සස්නාිර	மேல
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	90322	7.4	81.3

Install

In order to prevent Unnecessary error ocurring, start to import or execute provinces.sql, then works.sql, lastly cities.sql

Mysql Usage

Advantages of Latitude and Longitude

Integrate with Google Map or any map related service to show exact places of the city in the map.
Find locations are within a certificate 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 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 is 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 multilangual purposes.