What is the best way to store delivery zone data?

Question

PostgreSQL

It is necessary to store data about the places of delivery. The first thing that comes to mind is to create a table of cities. In another city_shipment - indicate such a possibility of delivery:

city_shipment_id | seller_id | city_id

In this case, you will have to indicate in the table all the surrounding settlements, towns, villages. For example around Moscow.

The second thought is to store the shipping geometry as a type polygon. In this case, the first question is solved. But there is a difficulty for such a request: delivery in Moscow and to the neighboring city of Khimki.

How to organize it?
I think if you work with geometry (delivery zones), several polygons (zones). Then if Moscow asks, then you need to take the point of its center of coordinates. And check if this point is in the polygons (zones).

Answer 1

You need to store not geometry, but a graph of delivery possibilities between cities . Since in the end it’s not proximity that matters to you, but the cost / time of delivery .... of course, our planet is not a flat pancake, but a sphere, and on a large scale, the minimum distance between cities is no longer the sum of the distances between intermediate ones, but in practice this rarely happens, but if yes, then this can be taken into account by adding the corresponding branches in the graph.
You also need to understand the difference - delivery between cities and delivery within the city are different bases. Within the same city there may be different warehouses, and here the delivery path between the warehouses of neighboring cities may vary from the choice of warehouse.
Those. in the end, you should not have cities, but warehouses / logistics centers. Some warehouses can work only with intercity transportation, some have a restriction on the maximum or even minimum weight / dimensions of parcels, somewhere there is a restriction on a lawyer / physicist, etc. All this needs to be taken into account and stored somehow ... and most importantly, to build such a map.
ps no one delivers the goods immediately to the client, first the parcel is delivered to the local warehouse and from there by courier or pickup to the client.

Answer 2

If you have the opportunity to create such a table and hang it with indexes, this is good and correct.
Because when working with geometry, you run the risk of flying into interesting stories (especially at the border of areas), when the algorithm will confuse points between areas, depending on the errors in calculating the coordinates.