How to store distance between cities and towns in DB efficiently
architecture, database-design, graph-databases, mysql, nosql
Solution
you should store `city_id, latitude, longitude` one for each city - then calculate the distances based on runtime input.
Problem
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?