In many web projects it is useful to have a database of French cities, at least to verify that the name of the city indicated by a user is correct. This article offers a database of 36,700 cities in France, with associated data such as area, number of inhabitants and geographic coordinates (latitude and longitude) of these cities.
Usefulness of such a database of cities in France
Such a database can be useful for websites or mobile applications. Here is a non-exhaustive list of possible applications:
- Associate a city with the users of a site, then group them by proximity or create an interactive map of these users
- Have standardized city names (avoid spelling mistakes). It is very convenient for online sales sites. It is also useful for improving ergonomics by suggesting the correct spelling.
- Create a directory on which registrants can be searched according to their city
- Create a city search form with an auto-suggestion of cities that start with the same letters. Thanks to the column which allows to know the population, it is possible to suggest a popular city in priority.
- In a form, pre-fill the name of the city if the postal code is indicated
- Thanks to geolization, this database can help indicate nearby towns
The database proposed in this article contains the following information in particular:
- Department: department number.
- Slug: unique identifier in lowercase, without accent and without spaces. Can be used to make the URLs of a website.
- Last name : name in upper case and without accent.
- Simple name: name in lowercase, without accent and with dashes replaced by spaces. Can be used to search when it is not known if the city name has a hyphen or spaces (eg “Saint-Étienne” has a hyphen as a separator, while “Le Havre” has a space as a separator)
- Real name : correct name with accents
- Soundex name: soundex of the name of the city (allows you to find cities that sound almost the same) [ajouté le 31/10/2013]
- Metaphone name: metaphone of the name of the city (same use as the soundex) [ajouté le 31/10/2013]
- Postal code : city zip code. If the city has more than one postcode, they are all listed and separated by a hyphen [ajouté le 31/10/2013]
- Municipality number: number of the town in the department. Combined with the department number, it creates the INSEE code under 5 characters.
- Common code (or INSEE code): unique identifier under 5 characters
- Borough : city district
- Canton: Township of the city
- Population in 2010: number of inhabitants at the 2010 census
- Population in 1999: number of inhabitants in the 1999 census
- Population in 2012 (approximate): value expressed in hundred
- Density in 2010: number of inhabitants per square kilometer rounded to the whole. Calculated from the number of inhabitants and the area of the city [corrigé le 02/07/2014]
- Surface / area: city area in square kilometers [corrigé le 02/07/2014]
- Longitude / latitude in degrees: geolocation of the city center. Used to locate the city on a map (example: Google Maps map) [ajouté le 31/10/2013, corrigé le 07/11/2013]
- Longitude / latitude in GRD: geolocation expressed in GRD
- Longitude / latitude in DMS (Degree Minute Second): geolocation expressed in Degree Minute Second
- Minimum / maximum altitude: minimum and maximum height of the city from the water level
In the French cities table there is a column containing the postal codes of these cities. When several postal codes are associated with the same city, they are separated by a hyphen, but this can affect performance in some cases. Knowing that a city can have several postal codes and that a postal code can be associated with several cities, SQL.sh offers a correspondence table for these cities.
In some cases this table can improve performance by 60! In a specific case, an SQL query which took 3.14s to execute was increased to 52ms thanks to the correspondence table.
The premium cities table also contains 6 additional columns:
- National ranking of the city according to the number of inhabitants
- National ranking of the city according to the area of the city
- National ranking of the city according to population density
- Classification of the city’s department according to the number of inhabitants
- Classification of the city’s department according to the size of the city
- Classification of the city’s department according to population density
Premium table + correspondence table with postal code (SQL, CSV and XML)
To know : the correspondence table is based neither more nor less on the “postal code” column of the table of French cities and contains a small error rate (with more than 36,000 French cities, you have to be forgiving, thank you for your understanding)