City Distance Map Database Design

Question

Well I am just a db beginner, I want to design something like this

1. I want to keep simple as possible, well I will enter all values[Cities, Distance etc.,] manually, because my cities are limited.
2. And also I want to Query like 'oneCity' Between 'AnotherCity', will yield the cities between them.

Any Advice, solution, concepts, technique or tutorials are appreciated. I googled a lot, but no solutions are appropriate :(

And also I don't know this is the right place to post, if this is a wrong place please ignore my mistake.

Thanks.

Answer 1

I've never tried this but here's some ideas I have:

You'll need to keep track of all cities, so you will need a City table. You also need to know the distance between two cities that are directly connected to each other, so you will need a cities_distances table like this:

cities_distances
----------------
  From_City_ID
  To_City_ID
  Distance

This will let you keep track of how much distance it is from one city to another. Note that this is directional - The distance from A to B might not be the same as from B to A if the highway between them has a lane for each direction, and one lane has a detour around some natural feature (like a small lake, or difficult parts of terrain). Within a city, this is also important because you'll have one-way streets.

If you don't care about the direction of the distance between the two cities, you can just rename those two columsn CityA_ID and CityB_ID.

As for writing a query that gets the distance between any two cities, it might be easier to do that in code using Dijkstra's algorithm (because this is really just a graphing problem) than in query, but it might be possible using recursive queries. Or, you could generate a pre-calculated table that stores all this information and just look the data up in that.

Answer 2

you can keep a table which has the latitude and longitude of each city.

City Name, Latitude, Longitude.

1. write a program which calculates the distance between the two cities based on coordinates.

2. And then write a program that defines an area on a sphere based on 2 or more points, and determines if a another point is within that area or "between" those two points.