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.


  • 1
    If you're working with SQL Server 2k8+ you can use the Geography data type. See an example here: SQL Server Geography datatype nearest point on line. See if your platform supports something similar. – Marian Jun 16 '12 at 12:25
  • 1
    nice, yes. I agree with Marian use the Geography data type so you don't have to write program 1 from my answer... But your number 2. "cities between" part of the question you will still have to define (on a sphere, or the planet) a region of some sort given two points and then determine if any other city location points are within that region. – fa1c0n3r Jun 16 '12 at 13:03
  • Linking to a blog like that is not ideal. Some day the link will rot, and then we'll have no idea what you had in mind. Better would be to summarize the goal, and provide the link for additional detail. – Jon of All Trades Jun 22 '12 at 14:50

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:


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.

  • 2
    Floyd's algorithm will calculate all-pairs shortest paths and store them in a matrix, which could be persisted in the structure you describe. However it's O(N^2) for space. In most cases it's probably better to store an adjacency list and use Dijkstra to calculate the path on the fly unless you want really fast path calculations. – ConcernedOfTunbridgeWells Apr 16 '12 at 14:56
  • Well thats a nice thing mate, but how to create matrix like cities in SQL :( – laranz Apr 17 '12 at 6:21
  • 1
    this might be useful for ya: File of all US 5-digit ZIP codes. Includes zip, city, state, latitude, longitude, and county: geocoder.ca/?freedata=1 – Neil McGuigan Jun 16 '12 at 19:48

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.