Databases - Storing locations and finding nearby locations

When you search on certain websites you can specify "Find [type of company/person] within [x] miles/km from [insert city]"

What kind of information do you store to calculate distances between cities? Is it geographical data like degrees, minutes, seconds,  then calculating the difference between the two coordinates and then translating them to km/miles?

Or am I seeing to difficult?

You're on the right track.

http://en.wikipedia.org/wiki/Geocoding

Ok, I got the file with all the longitudes and latitudes of all cities in Belgium. Next, I had to find some information about calculating latitude and longitudes to kilometers. This is what Wikipedia says:

"As opposed to a degree of latitude, which is always around sixty nautical miles or about 111 km, a degree of longitude varies from 0 to 111 km: it is 111 km times the cosine of the latitude, when the distance is laid out on a circle of constant latitude. More precisely, one degree of longitude = (111.320 + 0.373sin²φ)cosφ km, where φ is latitude)"

That is sick. I wonder how fast my pc is going to be when it has to calculate and find this sort of data.

ZippyV wrote:

Ok, I got the file with all the longitudes and latitudes of all cities in Belgium. Next, I had to find some information about calculating latitude and longitudes to kilometers. This is what Wikipedia says:

"As opposed to a degree of latitude, which is always around sixty nautical miles or about 111 km, a degree of longitude varies from 0 to 111 km: it is 111 km times the cosine of the latitude, when the distance is laid out on a circle of constant latitude. More precisely, one degree of longitude = (111.320 + 0.373sin²φ)cosφ km, where φ is latitude)"

That is sick. I wonder how fast my pc is going to be when it has to calculate and find this sort of data.

in some cases you can skip "the great circle" formula.

if you are only dealing with local locations and if you do not have to be super acurate then you can approximate the variable part and use the range of values to help.

for example what are the smallest lat and long for your area?
what are the highest values ?

if you need all locations within x km of a given point then find the values for  two corners that describe a box.
any values in the range are inside the box and are "in range"

this is not a "perfect fit" but may be a "good approximation" and reduces the amount of processing to mostly a sql select.
where Lat >x1 and lat < x2 and long > y1 and long < y2
or some veriation of that...

It works. Luckily, I didn't have to calculate any longitudes or latitudes. After a couple of days of thinking (math is not my strongest point) this was my resulting query:

CREATE PROCEDURE FindNeighbourCities

(

@mycity nvarchar(50),

@distance tinyint --distance in km

)

AS

DECLARE @mycitylat decimal(9,6), @mycitylong decimal(9,6);

SET ROWCOUNT 1

SET @mycitylat = (SELECT latitude FROM cities WHERE city = @mycity)

SET @mycitylong = (SELECT longitude FROM cities WHERE city = @mycity)

SET ROWCOUNT 0

SELECT city

FROM cities

WHERE SQRT(POWER(latitude - @mycitylat, 2) + POWER(longitude - @mycitylong, 2)) BETWEEN 0 AND @distance / 111.320

the table looks like
Cities:
   - cityid
   - city nvarchar(50)
   - latitude decimal(9,6)
   - longitude decimal(9,6)

The result comes pretty fast too, within a second. The table is about 12000 rows big but there are still to many non-cities in it. Like regions and streets, waterways, mountains.

If you want to know how I came up with the WHERE clause, it's from Pythagoras:

http://mathworld.wolfram.com/PythagoreanTheorem.html

Instead of using SET ROWCOUNT 1, you could also just use the TOP keyword in your query, and you could merge your 2 queries into 1, like this:

SELECT TOP 1
    @mycitylat = latitude
    , @mycitylong = longitude
FROM cities
WHERE city = @mycity

I tried that first but the graphical sql designer in Visual Studio didn't like that.

Check out the next version of SQL with native Spatial support:

http://blogs.msdn.com/isaac/archive/2007/05/16/sql-server-spatial-support-an-introduction.aspx