4

Assuming you have the latitude and longitude of your user, and you want to find nearby... let's say restaurants, how do you query your relational DB? For something like "restaurants within a 25 mile radius"?

Would you just query for restaurants in a square with a long and lat greater than/less than the user's loc +/- 25 miles (converted to degrees I guess), and then filter out the "corners" in whatever language your using? Or can/should you do it directly in the SQL?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
mpen
  • 272,448
  • 266
  • 850
  • 1,236
  • 4
    This sort of stuff is handled via spatial data. Lat & longitude aren't ideal because the earth isn't flat. PostgreSQL has a highly regarded/used spatial component, both of which are free. – OMG Ponies Jun 23 '10 at 01:59

5 Answers5

4

Take a look at the spatial features available in oracle for instance - here is the wikipedia entry http://en.wikipedia.org/wiki/Spatial_query, and a link with examples from noaa https://www.ngdc.noaa.gov/wiki/index.php?title=Sample_Oracle_Spatial_Queries

Jubal
  • 8,357
  • 5
  • 29
  • 30
  • 1
    +1 MySQL, SQL Server, and PostgreSQL also have spatial components. This means you "do it directly" in the SQL, just using special query functions that work out (for example) the distance between 2 points. Here's some air SQL for restaurants within 25 miles `select r.restaurant_name from restaurants r where sdo_within_distance(r.shape, (select shape from users where user_name = "Fred"), "distance=25 unit=miles" ) = "TRUE"; ` – MarkJ Jun 23 '10 at 09:00
3

Found a really good guide here: http://code.google.com/apis/maps/articles/phpsqlsearch.html

Much simpler than many of the other explanations...

Haroldo
  • 36,607
  • 46
  • 127
  • 169
2

Use a SQL implementation of the Haversine formula

Community
  • 1
  • 1
LesterDove
  • 3,014
  • 1
  • 23
  • 24
  • 1
    No, don't! Use a database with spatial features and let it do the work for you. MySQL, SQL Server, Oracle or PostgreSQL to name four. http://stackoverflow.com/questions/3098395/how-to-find-nearby-results/3098402#3098402 – MarkJ Jun 23 '10 at 09:02
2

Your "circle" will already be quite deformed if you are treating lat/long as a rectilinear grid any significant distance from the equator (like, say, in New York, or London, or Moscow). Trimming the corners off the "square" doesn't improve it much.

Sparr
  • 7,489
  • 31
  • 48
1

I know you specifically mentioned relational databases, but I would reco that you at least look into a system which has a native implementation of spacial querying.

Personally, I've found MongoDB to be pretty elegant: http://www.mongodb.org/display/DOCS/Geospatial+Indexing

Mike G
  • 758
  • 6
  • 18
  • Many relational databases have a native implementation of spatial querying. MySQL, Oracle, SQL Server, PostGIS – MarkJ Jun 23 '10 at 08:55