Real estate base. Designing location(country, city, ...)?

Question

MySQL

I design a base for real estate. The property will be sold in different countries. A reasonable question arose, how to store the location of the property in the database, so that later there would be no problems with the search.
Store the Country, city, district in each table, and then refer to the real estate table, and make the street text? I think this option is overkill.
Store coordinates? But how then to implement the search, display of real estate for a certain country, city?
So far, there are only two such ideas, is there a more ideal option, and which one, so that later it does not turn into a problem?
Thanks in advance

Answer 1

Interest Ask.
Have you looked at the options for international services? Airbnb / booking I think is the best fit for your task.
See how they integrate with addresses.
You have two completely different approaches to architecture.
1) Use a database of addresses from all over the world. It is large, each country has different rules for composing the address. District, Subdistrict, etc. Those. still need to be adapted.
+ you may have to use a paid base, and there will be questions.
2) Approach using Google services. It is paid, but it solves the problem of developing and maintaining the database.
For example, when filling in the address, you enter the address in any human way, and Google maps edit and format the address according to the rules that you need.
Pay attention to GIS data types such as POINT.
On the user's side, when searching, a person can start from the place where he is now, and you show him objects in the visibility zone. (and this is the preferred option for a real estate website - card priority).
He can enter the following queries, which Google will correct and the same in English -
Novosibirsk region.
Novosibirsk
Novosibirsk central region
through the API we request the polygon of the zone, and are already looking for it on our website.
Well, by the number of text, you can understand which option is more interesting and easier.

Answer 2

store, and something else, mutually does not interfere.
in the work to make a selection according to the option that is required.
one user wants to select by city, the other wants to select a square on the map with the mouse ...
option 2: store one thing in the table and a link with the second search option in an external source

Answer 3

If you want to make selections by districts, then the option with coordinates will not work, for the selection it is better to have all the components of the address at home.