0

I've to create a db of a lot of restaurants in italy (>100000), and I'd like to create a "near me" function. I woud like to create a table containing latitude and longitude of all restaurants, and I've thought to use mysql spatial.

Do you have any idea of how can I create a query to find all restaurants around me (my coordinates will be taken by gps), in a 10km radius?

I'm trying to undestand distances, but googling around I haven't found anything interesting.

Do you think that using mysql spatial is a good idea? could I use float instead? in this case, how can I create a query to find all restaurants around me in a 10km radius?

Thanks a lot

satboy78
  • 217
  • 1
  • 5
  • 14

2 Answers2

1

The MBRIntersects or MBRContains function of MySQL spatial seems to be what you'll need to get acquainted with.

See this question's accepted answer as a start.

Asking on gis.stackexchange.com may give better answers regarding MySQL spatial specifically.

Community
  • 1
  • 1
acraig5075
  • 10,588
  • 3
  • 31
  • 50
-2

Yes, you could use the geospatial functionality - but it's rather complicated. It would be a lot simpler to just index entries based on latitude and longitude (and longitude and latitude too!). As for querying the data, I'd recommend you run queries looking for rows matching a 20km X 20km bounding box centred on the current location (which can use the indices) rather than trying to query for records which are actually within 10km - you can discard those outside the radius eksewhere in the query, e.g.

SELECT * FROM (
  SELECT p.id, p.description,
  SQRT((x.current_latitiude-p.latitude)*(x.current_latitiude-p.latitude)
      + (x.current_longitude-p.longitude)*(x.current_longitude-p.longitude)) 
      AS distance
  FROM places p
  WHERE p.latitude BETWEEN (x.current_latitiude - x.max_range) 
     AND (x.current_latitiude + x.max_range) 
  AND p.longitude BETWEEN (x.current_longitiude - x.max_range) 
     AND (x.current_longitiude - x.max_range)
) ilv
WHERE ilv.distance<x.max_range

(You'll need to convert 10km into the units you're using for longitude / latitude and subsitute the value for x.max_range).

symcbean
  • 47,736
  • 6
  • 59
  • 94
  • thanks a lot. I can't understand why I should use 20 x 20 km: could you please explain me? – satboy78 May 03 '12 at 18:45
  • because 10km west + 10km east = 20km span – symcbean May 04 '12 at 08:14
  • 3
    Latitudes and longitudes are angular quantities, not linear. Using them directly in a Pythogoras hypotenuse calculation will give a very strange number. Rather use the formula for an approximate distance discussed [here](http://stackoverflow.com/questions/1006654/fastest-distance-lookup-given-latitude-longitude) and elsewhere assuming the Earth is a sphere with constant radius (which it's not). – acraig5075 May 04 '12 at 10:58