Tech Off Thread

9 posts

Databases - Storing locations and finding nearby locations

Back to Forum: Tech Off
  • ZippyV

    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?

  • JChung2006

    You're on the right track.

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

    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.

  • figuerres

    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...

  • ZippyV

    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.

  • ZippyV

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

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

  • TommyCarlier

    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

  • ZippyV

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

  • yizhe

    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

Comments closed

Comments have been closed since this content was published more than 30 days ago, but if you'd like to continue the conversation, please create a new thread in our Forums, or Contact Us and let us know.