1

I am developing a web page where users can create activities and others find them via a search function. When you create an activity you must specify the exact location where it will take place, assisted by google maps I retrieve the latitude and longitude. Now, when doing a search I want to have the functionality to find all activities close to a specified location(also assisted by google maps).

So I have a set of activities with coordinates, the coordinates of a point I want to find activities nearby, and I want to return activities that are no more than, lets say, 5 km(or miles or whatever you prefer) away from this point.

I am having this idea in my head that this can be solved by calculating max/min latitude and longitude, and use these as parameters in an sql-query where I use a where clause for filtering...The problem I'm facing here is firstly calculating these max/min values, secondly in an circular area(with radius 5km), and not a rectangular

Would appreciate any input here! Thanks!:)

rkg
  • 5,559
  • 8
  • 37
  • 50
Runar Halse
  • 3,528
  • 10
  • 39
  • 59

3 Answers3

2

Coordinates you get are probably not x and y but latitude and longitude; you will need spherical distance unless all your points are within rather small radius, e.g. few hundred miles.

If you have many points, direct exhaustive search becomes too slow, spherical or not. Fortunately, GIS extensions available both for MySQL and for Postgres. Commercial DBs also have spatial extensions. These make searches for nearby objects efficient.

Community
  • 1
  • 1
9000
  • 39,899
  • 9
  • 66
  • 104
1
  • Calculate the boundary latitudes and longitudes.
  • Select everything where the latitude is between your two values for that, and similarly for longitude. If you're not using a spatial index, beware of edge cases on your sphere (a most excellent pun!): crossing 0, 90, or 180 degrees may result in impossible criteria.
  • Either in your SQL server or your app, execute the Haversine formula against your results. You must have the rectangular bounding values to prevent a table scan, but results in the rectangle will include results outside of your circle.

If you actually stop to think about it, your rectangle and your circle are both misshapen... but that's not really relevant anyway.

Also, check out this, which will expand on distance measuring and mention some other ideas: http://www.movable-type.co.uk/scripts/gis-faq-5.1.html

Jeff Ferland
  • 17,832
  • 7
  • 46
  • 76
1

Hope this can help you.

Get nearest places on Google Maps, using MySQL spatial data

However this is MySQl query. You can convert it as your requirement.

Community
  • 1
  • 1
Gaurav
  • 28,447
  • 8
  • 50
  • 80