Nearby coordinates

Question

I will create an app that should post "coordinates" and one other function is to go through the database and find nearby coordinates. Let's say that I had 1.000.000 entries in my database, what algorithm to use? How can i compare the coordinates wich are 100m away from the given coordinates? A lot of apps doing that but it seems difficult.

Update: By Coordinates I mean lon-lat. I don't know why you considered this as not "real" question but I need a starting point that's why I asked this "general" question

Thanks

This question is way too broad for Stack Overflow. It is also much too vague and lacks in detail. — Oded, Jun 12 '12 at 18:33
By coordinate do you mean a 2D point? Give an example of what a "coordinate" is in your question. And what @Oded said. — Ryan, Jun 12 '12 at 18:33
get data from the database, use a search algorithm on the data (not for spatial databases) — UmNyobe, Jun 12 '12 at 18:34
Seems like a "general practices" broad kind of question, not specific enough for StackOverflow — Anna Billstrom, Jun 12 '12 at 18:36
This question has been asked before (and voted up, not down) a few times here. it was phrased a bit differently, but it's still the same basic question: "How to map out nearby coordinates using geocodes?". (look at the "related questions" column to the right.) I can see closing it as a duplicate, but it's really not ***that*** bad a question. — David, Jun 12 '12 at 18:41

score 1 · Answer 1 · answered Jun 12 '12 at 18:38

I've used something called the Great Circle Distance to do this in the past. It treats the Earth as a perfect sphere (which it is not) and uses two sets of lat longs to determine the distance between two points on that sphere. Since the Earth is not a perfect sphere these distances are not perfectly accurate. If you are dealing with small distances and a small difference between calculated and actual is ok this would probably be fine for you. Here is a function that calculates the GCD:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE FUNCTION dbo.GreatCircleDistance
(
    @Latitude1 float = NULL,
    @Longitude1 float = NULL,
    @Latitude2 float = NULL,
    @Longitude2 float = NULL
)
RETURNS float
AS
BEGIN
    IF @Latitude1 IS NULL RETURN 0.0
    IF @Longitude1 IS NULL RETURN 0.0
    IF @Latitude2 IS NULL RETURN 0.0
    IF @Longitude2 IS NULL RETURN 0.0

    DECLARE @sin1 float
        ,@sin2 float
        ,@sind float
        ,@cos1 float
        ,@cos2 float
        ,@cosd float

    SELECT @sin1 = SIN(RADIANS(@Latitude1))
        ,@sin2 = SIN(RADIANS(@Latitude2))
        ,@sinD = SIN(RADIANS(@Longitude2 - @Longitude1))
        ,@cos1 = COS(RADIANS(@Latitude1))
        ,@cos2 = COS(RADIANS(@Latitude2))
        ,@cosD = COS(RADIANS(@Longitude2 - @Longitude1))

    RETURN ATN2     (SQRT(SQUARE(@cos2 * @sinD) + SQUARE(@cos1 * @sin2 - @sin1 * @cos2 * @cosD))
                ,@sin1 * @sin2 + @cos1 * @cos2 * @cosD
            ) * 3959.871
END
GO

Stolen from here.

This can be very slow when searching the database for neighbors. For mssql i recommend the built in spacial tools — Andrey, Jun 12 '12 at 18:40
That may be. When I did this in the past it was a one time only deal, so there weren't any performance considerations. I'd have to see a slow query using this to recommend a solution for speeding it up. — Abe Miessler, Jun 12 '12 at 18:42

score 0 · Answer 2 · answered Jun 12 '12 at 18:44

0

Latitude, longitude in degrees-minutes-seconds or decimal degrees? If decimal then dist^2 ~= lat^2 + lon^2. Don't forget to check for quadrant. You may want to sort you search list into a 2d search tree if doing multiple lookups.

answered Jun 12 '12 at 18:44

starbolin

840
5
13

Nearby coordinates

2 Answers2