CODEWITHSUNDEEP
mysqlsql
user1627114
user1627114

Reputation: 91

Google Maps geocoder to find nearby places from MySQL database

I have a website where I store locations in a MySQL database and GeoCode using the address provided by the database.

Now I want to add a feature which will find the closest places to the specified point e.g. the location they are standing now or typing. I'm coding the geocoder and google maps in JavaScript and jQuery and I get the results using PHP to fetch from the database.

I read about the viewport but I can't figure out how it would work in my situation where I need to show my own database locations and not Google's.

Can anybody help? And it would be awesome if you have some kind of example.

Upvotes: 4

Views: 3287

Answers (3)

Marcelo
Marcelo

Reputation: 9407

radius search:

select *,
    acos(cos(centerLat * (PI()/180)) *
     cos(centerLon * (PI()/180)) *
     cos(lat * (PI()/180)) *
     cos(lon * (PI()/180))
     +
     cos(centerLat * (PI()/180)) *
     sin(centerLon * (PI()/180)) *
     cos(lat * (PI()/180)) *
     sin(lon * (PI()/180))
     +
     sin(centerLat * (PI()/180)) *
     sin(lat * (PI()/180))
    ) * 3959 as Dist
from TABLE_NAME
having Dist < radius
order by Dist

3959 is the Earth radius in Miles. Replace this value with radius in KM, or any unit, to get results on the same unit. centerLat and centerLon are the center of the search (your input), while lat and lon are fields in the table.

Upvotes: 4

geocodezip
geocodezip

Reputation: 161334

Sounds like the classic store locator. See this article in the Google Maps API v3 documentation for an example using PHP and MySQL.

Upvotes: 0

Tina CG Hoehr
Tina CG Hoehr

Reputation: 6779

Here's what I would do:

  • Since you use a database anyway, convert and save the human-readable address to a LatLng (once) every time you add a place to your DB

  • Query user for his/her location

  • geocode the user location to a LatLng

  • use a SQL query to find places matching desired distance. (see here https://developers.google.com/maps/articles/phpsqlsearch_v3)

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;

  • display SQL results back to Javascript map

Upvotes: 2

Related Questions