(Last updated on April 18th, 2021 at 11:43 am)
Here’s a database of Australian suburbs – including geographic coordinates.
This was originally developed for Kevin Fleming, who was gracious enough to release it in the hope that it will be useful to others 🙂
The tarball contains two SQL files – one for the `suburbs` table, and another for `states`. They were generated via phpMyAdmin 2.9.1 from MySQL 5.0.51. You’ll probably want to start using them together with something like:
select postcode, lat, lng, states.name as state
from suburbs, states
where suburbs.name = 'suburbname' and state.id = state;
The usual / expected disclaimers apply: if it breaks, both pieces are yours… I / we make no claims as to the accuracy of this stuff (although the application we built it for relies on it)… Blah blah blah…
I’d imagine that New Zealand stuff will come – we’ve made a small start on it – and will post that here if / when it happens 🙂
But wait! There’s more! Perhaps not a steak knife, but here’s a function to find the distance between two sets of co-ordinates (taking into account the curvature of the earth):
// Return the distance between two sets of geo coordinates:
function distance($lat1, $lng1, $lat2, $lng2) {
$pi80 = M_PI / 180;
$lat1 *= $pi80;
$lng1 *= $pi80;
$lat2 *= $pi80;
$lng2 *= $pi80;
$r = 6372.797;
$dlat = $lat2 - $lat1;
$dlng = $lng2 - $lng1;
$a = sin($dlat / 2) * sin($dlat / 2) + cos($lat1)
* cos($lat2) * sin($dlng / 2) * sin($dlng / 2);
$c = 2 * atan2(sqrt($a), sqrt(1 - $a));
$km = $r * $c;
return $km;
}