Australian Suburb Coordinates DB

Australopythecus 0
Viewed 28

I'm working with Matthew Proctor data matthewproctor.com/australian_postcodes to display information on my online leaflet maps as markers.

I'd like to implement the function to show the shape of australians suburbs.

I found the coordinates I need for every suburb here https://raw.githubusercontent.com/tonywr71/GeoJson-Data/master/australian-suburbs.geojson

Leaflet Shape through coordinates

But that file is 10MB and I'd like to import the parsed data from the GEOjson to my mysql DB as a simple varchar with the whole set of coordinates so i.e. an address is from "CANTON BEACH" I can retrieve the coordinates set "[[[151.54941006,-33.27054219],[151.5507917,-33.28289733],[151.5430911,-33.28177744],[151.53813584,-33.26757533],[151.54941006,-33.27054219]]]" from my database to create the map.

suburb name and coordinates

The question is that I can't find the way to parse or retrieve just that data from the GEOjson.

I've tried through jsonformatter.org but the key "XXX_loca_2" changes depending on the state of each suburb... I'm missing the way to parse that "XXX_loca_2" and his "coordinates" so I get a clean list to import matches on my database.

Maybe build an array with the states (nsw, qld, nt, wa, sa, vic, act, tas) then parse XXX_loca_2 and coordinates in a foreach?

Any hand? Thanks ahead.

geojson coordinates leaflet mysql json
1 Answers

First to say out of 18443 from the australian suburb database from Matthew Proctor, a total 2153 suburbs have no coordinates when mixed with this other database.

The code I've used is:

<?php


$suburb_file = file_get_contents('australian-suburbs.geojson');
$json = json_decode($suburb_file);


for ($i=0; $i<16288; $i++) {

echo "UPDATE `yourdatabase` SET `shape` = '";   
$set = array();
    foreach($json->features[$i]->geometry->coordinates[0] as $coordinates)  {    
        $set[] = '['.$coordinates[0].','.$coordinates[1].']';   
    }
echo "[[";  
$allcoord = implode(',', $set);
echo $allcoord; 
echo "]]"; 

echo "' WHERE `yourdatabase`.`locality` = '";

$states = array('nsw_loca_2','vic_loca_2','qld_loca_2','tas_loca_2','sa_local_2','wa_local_2','nt_local_2','act_loca_2'); 

foreach ($states as $key => $value) {
    $haystack = $json->features[$i]->properties->$value;
        if (!empty($haystack)) {    
            $suburb = str_replace("'","''",$json->features[$i]->properties->$value);            
            echo $suburb;                   
            echo "' AND `yourdatabase`.`state` = '";
            $state = explode("_", $value);
            echo strtoupper($state[0]);  
    }}

echo "';<br>";
}
?>
Australopythecus0
Related