Hi Davie, Cheers for that!
Bugger, I think our posts had crossed wires :) Let me try and absorb that. Sorry, I've only been using php/mysql for a couple of months and it was hard enough getting as far as I did. This whole Geocode methodology is completely baffling me. Please be patient as this will no doubt take a couple of hours. Thanks again, J HOLD UP! I think the penny has dropped. I think I've got my hardcoded vs. select fields (lat, lon) the wrong way around!! Argh :) Let me get back to you before too long. On Sep 30, 9:52 am, davie strachan <[email protected]> wrote: > The SQL query from the article is > // Search the rows in the markers table > $query = sprintf("SELECT address, name, lat, lng, ( 3959 * > acos( cos( radians('%s') ) * cos( radians( lat ) ) * > cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * > sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < > '%s' ORDER BY distance LIMIT 0 , 20", > mysql_real_escape_string($center_lat), > mysql_real_escape_string($center_lng), > mysql_real_escape_string($center_lat), > mysql_real_escape_string($radius)); > > lat & lng are the fields to search in the database > $centre_lat and $centre_lng are parameters passed to the PHP file by > the function searchUrl in the calling function (centroid) > If you are not passing the centroid and radius your SQL should be > something like > $query = "SELECT address, name, lat, lng, ( 3959 * > acos( cos( radians(-37) ) * cos( radians( lat ) ) * > cos( radians( lng ) - radians(144) ) + sin( radians(-37') ) * > sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < 5 > ORDER BY distance LIMIT 0 , 20"; > > This will list all records from your database with a lat/lng within 5 > miles of your centroid > > Regards Davie > > On Sep 29, 8:04 pm, Rossko <[email protected]> wrote: > > > > > > > > > > SELECT *, ( 6371 * acos( cos( radians(-37) ) * cos( radians( -37.7833 ) ) > > > * cos( radians( 144.9833 ) - radians(144) ) + sin( radians(-37) ) * sin( > > > radians( -37.7833 ) ) ) ) AS distance FROM Geocode_AU HAVING distance < 5 > > > ORDER BY distance > > > It's wrong, you have all fixed numbers in your formula. Whatever the > > value of data in your table the formula will always evaluate to > > exactly the same value. If that is more than 5, no rows will ever be > > selected. > > > As you want to SELECT some of your rows that fit your criteria (by > > radius) and discard other of your rows that don't fit your criteria, > > you need the formula to include or compare data from the rows it is > > examining. > > > The example is > > SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * > > cos( radians( lng ) ... etc > > > where 37 is the "target" longitude for the centre of the search > > and 'lat' will substitute the value from the row of data to be tested, > > from each row in turn. > > etc > > > > Also why I don't understand is why they rounded some of the lattitudes > > > and > > > longitudes??? For example, they use -37 instead of the full -37.7833 and > > > 144 instead of > > > As it says, "Here's the SQL statement that will find the closest 20 > > locations that are within a radius of 25 miles to the 37, -122 > > coordinate" They're not presenting an example SELECT within a radius > > of -37.7833, etc. > > > > Seems odd that Google would post an example that doesn't work!??? > > > You could take it as a clue that your implementation, which isn't the > > same as Google's example, could be wrong? -- You received this message because you are subscribed to the Google Groups "Google Maps API V2" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/google-maps-api?hl=en.
