Databases
StackOverflow2013
Postsdbo
POHow to determine n locations inside a circle efficiently?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to determine n locations inside a circle efficiently?
    text
    copied!<p>Given this the scenario:</p> <ol> <li>We have the order of 1,000,000 points around the world, specified by longitude and latitude;</li> <li>We have a circle c based on a point pc (specified by longitude and latitude) and a radius rc </li> <li>We want to efficiently determine which of the points are in the circle</li> </ol> <p>I'm developing in C# and the locations stored in SQL server 2008.</p> <p>So as I see it I have these 3 options:</p> <ol> <li><p>Store the locations as longitude latitude floats and perform the calculations in C#.</p></li> <li><p>Store the locations as geographical data types and perform the calculations in SQL server 2008 like this:</p> <pre><code>CREATE TABLE UserLocations [UserId] [bigint] NOT NULL, [CurrentLocation] [geography] NOT NULL ALTER PROCEDURE sp_GetCurrentUsersInRange @userPoint geography, @RangeInMeters int AS BEGIN select UserId from UserLocations where @userPoint.STDistance(CurrentLocation) &lt;= @RangeInMeters and UserId &lt;&gt; @userId END </code></pre> <p>Disadvantages: problems using geographical data with LinqToSQL and LinqToEntities.</p> <p>Advantages: using dbms processing power over large data, and usage of the SQL Server spatial index.</p></li> </ol> <p>3.Using some web service such as google's geolocation and calculation services. So far I didn't find such web service.</p> <p><strong>Which is the most efficient in your opinion?</strong> Please justify your answer.</p> <p>Thank you</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload