0

I know this has been talked to death in some forums and I've found a couple different ways to do it. By way of example, this is a stackoverflow post discussing it and this is an msdn post talking about it. Both seem interesting yet seem to accomplish it for the original authors in different ways. They seem happy with the answers provided but I'm not getting satisfactory results (read: not consistently sorted correctly)

The question is twofold:

  1. Is it more efficient to sort these in C# post data retrieval or in SQL by calculating a distance from point of origin and using order by (or other method)? I could end up needing to sort 3000+ locations by distance from point of origin (as denoted by lat and long)

  2. What would be the most "correct" way to sort these points according to a lat/long of origin? By "correct" I don't by method but by actual distance from point of origin. Of course location of sorting comes into play because of question 1.

The technologies used are EF, SQL stored proc, C#.

Community
  • 1
  • 1
user4593252
  • 3,496
  • 6
  • 29
  • 55
  • 1
    What do you mean by SQL? If you mean SQLServer then [STDistance](http://msdn.microsoft.com/en-gb/library/bb933952.aspx) is worth looking up. You will need a geometry column, which can have a spatial index on it for faster processing, and use ORDER BY to sort by distance. This is better than writing lots of code in your application layer. Also, if the calculation is done by the database it limits the amount of data returned. Geometry columns can also be used by Entity Framework. – Andy Nichols Oct 28 '14 at 15:10

1 Answers1

0

Although there's probably a more efficient way to do this, reading the two posts carefully and through some trial and error....

  1. The formula for determining the distance as put forth in the two posts referenced in my questions are essentially the same.

  2. It is important to have the list in approximate order first so run the ordering or sorting according to the formula(s) given.

  3. After the first sort, sort it again but take into account the "fudge" as noted in Teasel's answer whether it be in sql or code (in my case, C#).

    • It's also important to note that this will always always always be only an approximation and never exact but you can get really close.
user4593252
  • 3,496
  • 6
  • 29
  • 55