1

In my database I currently have all countries/states and cities, each city has its own latitude and longitude which I managed to get using reverse geocodingvia google.

When a particular user comes on to the site and says "Ok show me everyone that's in Australia, within the state of Victoria, and lives in the city of Fitzroy" within a radius of 20km I have the latitude and longitude for Fitzroy but I'm unsure on how to go about getting other users who aren't currently situated in Fitzroy but do fall in to the radius of 20km.

I did come across this thread which to me looks very promising Calculating Distance between two Latitude and Longitude, difference being I will be filtering a lot of records which could potentially be 10k if not more depending on the radius and location. I'm looking to implement a solution which not only gives me the desired results but also takes into consideration performance as well. Whether that be done in the MVC controller or in a stored procedure.

I'm wondering has anyone done something similar? Can they share their knowledge with me. I am new to this type of searching so any information/links would be beneficial.

Please note I'm not expecting you to write code for me, I'm more than capable of doing that myself.

Community
  • 1
  • 1
Code Ratchet
  • 5,141
  • 11
  • 67
  • 123

2 Answers2

1

I think, the possible solution is to calculate a range of latitude and longitude for the required city and radius. In that case, you will be able to get all users in this range using simple WHERE sql query.

For instance, you have to find all users within a radious of N km around the city with latitude = X and longitude = Y. My idea is that you should calculate min and max values of latitude and longitude which satisfy to this condition.

One degree of latitude is equal to 111.3 km. So, min and max values of latitude may be calculated this way:

minX = X - N/111.3

maxX = X + N/111.3

One degree of longitude equals to 111.3 * cos(X), so:

mixY = Y - N/(111.3 * cos(X))

maxY = Y + N/(111.3 * cos(X))

After the calculation of min and max values of latitude and longitude you will be able to compose a query to your database. It will be something like:

"SELECT FROM ... WHERE (latitude <= maxX & latitude >= minX) & (longitude <= maxY & longitude >= minY)"

floyd
  • 632
  • 6
  • 22
0

I'm assuming since you're using MVC that you're using an SQL Server database. If this is the case, then you could try making use of the Spatial data support that is offered within both this and Entity Framework, rather than trying to do all the work yourself.

Try this blog post showing the basics: http://weblog.west-wind.com/posts/2012/Jun/21/Basic-Spatial-Data-with-SQL-Server-and-Entity-Framework-50

Richard
  • 27,531
  • 8
  • 67
  • 116