How to get places with a certain radius

Question

I have a database of activities , each activities could be held on 3 days , each day contains a postal code .

So the database looks like that (+ alot of other fields)

In another database i have a Geo Location info (postal code , lat , long)

Now users can enter there postal code and a radius and activities in that radius will appear.

Question :

1 - What is the best way to accomplish that ?

Solution in mind

Make a view of all possible postal codes from the activities and join it on the Geo table to get their Lat/Lng

then when a user search for a postal code , get the Lat/Lng and do the mathematical equation to get all postal codes near that point .

But i don't think in term of performance this is a good way since i will have to apply the query on 3000+ activities

Codes found for distance

• Finding locations nearby with MySQL (Haversine Formula)

  SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;

What do you guys think ?

Answer 1

You would need a GIS to do that (and use spatial index), but mysql is not capable of it - mysql GIS functionality can handle just rectangles. PostreSQL is capable of GIS.

Easiest would really be the math expression. It would be best if you could use some Projected coordinates (lat & lon are sphere coordinates). Convert whole database in this projected coordination system and than just use simple expression (without need to use trigonometric functions):

(activity_x - postal_code_x)^2 + (activity_y - postal_code_y)^2 < distance^2

note that the Earth is a sphere, which means this will only work exactly for smaller distances (say < 1000 km). But anyway I think you don't need exact circle...

Answer 2

I was wrong in term of performance , the query took less than 0,5 second to calculate the distance on the 3000+ activities .