Javascript – Display in range addresses within x miles of a geo location with google maps

google mapsjavascriptMySQLPHP

In my database I have a list of places and for each I have a street name and number, postcode, city and county. Some of them have a latitude and longitude location.

And I have the geo location of the city centre for example. I would like to display only the places that are within X miles of the city centre on a google map.

Incase this would need a geo location for each of my places to work, I could perhaps set up a script to use google maps api to use geocoding to get a geo location for all my places and update the database with the lat/lng. Then I would have a database full of lat and long locations to work from.

Once all the places have a lat/lng then maybe mysql can return the within range addresses?

Best Answer

This is not hard once you have lat / long data, and if somebody gives you the great circle distance formula in mySQL format.

@maggie gave a good reference. How to efficiently find the closest locations nearby a given location

Indexing strategy: Keep in mind that one minute of latitude (1/60 degree) is one nautical mile, or 1.1515 statute miles (approximately) all over the world. So index your latitude column and do your search like this. (If you're in the part of the world that uses km, you can convert; sorry for the Old-British-Empire-Centric answer, but they did define the nautical mile.)

WHERE mylat BETWEEN column.lat-(myradius*1.1515) AND column.lat+(myradius*1.1515)
  AND (the big distance formula) <= myradius

This will give you both decent data base indexing AND reasonably accurate distance circles.

One extra refinement: You can index longitude too. The trouble is that ground distance isn't directly related to longitude. At the equator it is one nautical mile per minute, but it gets smaller, and at the poles there are singularities. So, you can add another term to your WHERE. It gives correct results but isn't as selective as latitude indexing. But it still helps the indexing lookup, especially if you have lots of rows to sift through. So you get:

WHERE mylat BETWEEN column.lat-(myradius*1.1515) AND column.lat+(myradius*1.1515)
  AND mylon BETWEEN column.lon-(myradius*1.1515) AND column.lon+(myradius*1.1515)
  AND (the big distance formula) < myradius

Related Solutions

Mysql – Fastest Way to Find Distance Between Two Lat/Long Points

Create your points using Point values of Geometry data types in MyISAM table. As of Mysql 5.7.5, InnoDB tables now also support SPATIAL indices.
Create a SPATIAL index on these points

Use MBRContains() to find the values:

  SELECT  *
  FROM    table
  WHERE   MBRContains(LineFromText(CONCAT(
          '('
          , @lon + 10 / ( 111.1 / cos(RADIANS(@lat)))
          , ' '
          , @lat + 10 / 111.1
          , ','
          , @lon - 10 / ( 111.1 / cos(RADIANS(@lat)))
          , ' '
          , @lat - 10 / 111.1 
          , ')' )
          ,mypoint)

, or, in MySQL 5.1 and above:

    SELECT  *
    FROM    table
    WHERE   MBRContains
                    (
                    LineString
                            (
                            Point (
                                    @lon + 10 / ( 111.1 / COS(RADIANS(@lat))),
                                    @lat + 10 / 111.1
                                  ),
                            Point (
                                    @lon - 10 / ( 111.1 / COS(RADIANS(@lat))),
                                    @lat - 10 / 111.1
                                  ) 
                            ),
                    mypoint
                    )

This will select all points approximately within the box (@lat +/- 10 km, @lon +/- 10km).

This actually is not a box, but a spherical rectangle: latitude and longitude bound segment of the sphere. This may differ from a plain rectangle on the Franz Joseph Land, but quite close to it on most inhabited places.

Apply additional filtering to select everything inside the circle (not the square)
Possibly apply additional fine filtering to account for the big circle distance (for large distances)

Html – How to link to Google Maps with a particular longitude and latitude

The best way is to use q parameter so that it displays the map with the point marked. eg.:

https://maps.google.com/?q=<lat>,<lng>

Best Answer

Related Solutions

Mysql – Fastest Way to Find Distance Between Two Lat/Long Points

Html – How to link to Google Maps with a particular longitude and latitude

Related Topic