How to store distance between cities and towns in DB efficiently

Question

I need to be able to display distance to n cities/towns from a particular location chosen by user. Its like clicking on a map and getting all destinations within 100 miles, only that it wont be a map but a link on webpage.

I need to choose a solution that would scale-up from within a state to a country to globally potentially - which means from thousand to hundred thousand locations.

I though of storing CITY1_ID, CITY2_ID & DISTANCE in a Relational DB table, but I doubt if it would scale well for a web application (million of rows).

Could this be done more efficiently using a NoSQL Database or Graph DB ? Or is RDBMS good enough for this problem with proper design?

Added: If I do not store in DB then how will I get something like: Get me all cities within 100 miles of San Jose?

Answer 1

you should store city_id, latitude, longitude one for each city - then calculate the distances based on runtime input.