Slightly off topic from Wappler but…
Is there an easy way to geocode all address in a database so that I can use the google maps “dynamic markers” without get the query limit. Addresses in the database is less than 500.
Thanks,
Tom
I can give you a custom php routine which i use to do it as a one off if it’s any use
1 Like
That would be great. Can you email it to me at altimages@gmail.com.
Thanks,
Tom
Hi @Hyperbytes you don’t happen to have a custom classic .asp routine do you?
Regards,
Mim
I will just post it here @tomkomin then everyone can see it.
It’s pretty basic and simply uses html meta-refresh to send a query every second until they are all done but it works
It echos messages about what is has done as it processes so you can see it’s progress
Just copy and paste it into a blank page and make the appropriate modifications
<html><head>
<?php
$connection = new PDO('mysql:host=yourdatabasehostname;dbname=yourdatabasename;charset=utf8', 'yourusername', 'yourpassword');
?>
<meta charset="utf-8">
<meta http-equiv="refresh" content="1">
<title>Geocoder</title>
</head>
<body>
<?php
/*
database fields used are as follows, datatype i use shown:
lat - latitude Type:Decimal 10.6
lng - longitude Type:Decimal 10.6
FullAddress - Full address of property to geocode
isgeo - database field used to record if address is geocoded, 0 = no, 1 = yes, 2 = geocoding failed (Type:smallint)
geoaddress - stored the address returned by google when an address is successfully geocoded (optional)
ID - unique id of the record
*/
$STH = $connection -> prepare( "SELECT count(*) as datacount FROM <yourtablename> where isgeo = 0" );
$STH -> execute();
$result = $STH -> fetch();
echo "hits:".$result["datacount"]."<br/>";
$qry="SELECT * FROM <yourtablename> where isgeo = 0 limit 1";
foreach($connection->query($qry) as $row) {
// get latlang
$address=$row["FullAddress"];
$id=$row["ID"];
$address_enc=str_replace(" ","+",$address);
echo $address;
$url ="https://maps.googleapis.com/maps/api/geocode/xml?address=".$address_enc."+UK&sensor=false&key=YourGoogleAPIKeyHere";
echo $url;
$xml = simplexml_load_file($url);
print"<br/><pre>";
print_r($xml);
echo $xml->Status; // US
$status = $xml->status;
if ($status=="OK") {
$Lat = $xml->result->geometry->location->lat;
$Lng = $xml->result->geometry->location->lng;
$geoaddress = $xml->result->formatted_address;
$LatLng = "$Lat,$Lng";
$isgeo = 1;
$statement = $connection->prepare("update <yourtablename> set lat = :lat, lng = :lng, isgeo = :isgeo, geoaddress = :geoaddress where ID = :id");
$statement->execute(array(
"lat" => $Lat,
"lng" => $Lng,
"isgeo"=> $isgeo,
"geoaddress" => $geoaddress,
"id" => $id
));
echo "<br/>";
echo $row["ID"];
echo " - ";
echo $row["Full Address"];
echo " - ";
echo $row["lat"];
echo " - ";
echo $row["lng"];
echo " - ";
echo $row["isgeo"];
echo " - ";
echo $geoaddress;
print"<br/><pre>";
print_r($xml);
echo $xml->Status; // US
}
if ($status=="ZERO_RESULTS") {
$statement = $connection->prepare("update <yourtablename> set lat = :lat, lng = :lng, isgeo = :isgeo, geoaddress = :geoaddress where ID = :id");
$statement->execute(array(
"lat" => 0,
"lng" => 0,
"isgeo" => 2,
"geoaddress" => "",
"id" => $id
));
echo $xml->status;
}
}
?>
</body></html>
4 Likes
Sorry @mimuk, no ASP equivalent, havn’t used ASP for a long time.
Could you not enable PHP on your server and run the PHP script, most servers will offer PHP
2 Likes
Thanks. I’ll try it out.
Tom
Hi,
sorry trying to work out how your code works, I have a table with users data in it, including their address and I have the lat and long fields empty, I am trying to create markers and info windows for each user and retrieve lat and long via geocoding.
Trying to work out if your script does that?
Thanks
Michael
The code starts with a table containing 4 relevant fields,
FullAddress is the address to geocode
isgeo is set to 0 in all fields initially and is used to identify which records have been successfully geocoded (or geocode has failed) 1= geocoded, 2 - geocode failed
The code steps through a table , 1 record per second (set in meta-refresh)
It takes the value of the field FullAddress and gets the lat/ lng given by google for that address and writes that to the database fields lat and lng They key field in the table in this case is named ID.
it also captures the address returned by google which would be the google assigned equivalent of the address you sent to google and writes it to the field (that is not a necessary feature, i just wanted to do that)
It will cycle through the table until all records are marked as geocoded (=1) or failed (=2) and it echos what it is doing as it does so, you can safely take out all the “echo” statements if you want
The update is performed here:
$statement = $connection->prepare(“update set lat = :lat, lng = :lng, isgeo = :isgeo, geoaddress = :geoaddress where ID = :id”);
$statement->execute(array(
“lat” => $Lat,
“lng” => $Lng,
“isgeo”=> $isgeo,
“geoaddress” => $geoaddress,
“id” => $id
));
You will have to change the field references to match your table
This routine does not deal with markers or info windows, that is done within wappler
thankyou, thats well explained
I’m about to implement some Google Maps into a project and found this thread. I don’t want to mass-update the data but I do want to get the co-ordinates from Google Maps when an address is entered. Should I do that within Wappler or does it require hand-coding? Ideally I’d like to show a map with the marker on it (taken from the address entered) but allow the person to drag the marker if it’s not quite accurate enough.
There doesn’t seem to be any documentation for the Google Maps module.
Cheers.
Have you read this,it should cover everything you need to know to use the Google API
P.S. drag is not available “out of the box” with wappler
Thanks Brian. I hadn’t found that thread so will work through it. It’s a shame there isn’t the drag feature built in.
You could always submit a feature request, i think a ondrop/ dragend listener within the maps plugin would be a great enhancement, you are not the first to ask this
2 Likes