Introduction
At last the long promised method of mass geocoding the wappler way no scripts involved.
As this is basically a reply to a post from @mimuk i will base this tutorial around his data table structures.
Sorry about general quality, having to do this on laptop as main machine dead so few rough edges due to lack of software and using a singe screen!
Hope i have not missed any stages
We will be using the google API but the principal will be the same should you wish to use another such as photon.
Considerations
One if the issues we need to workaround is to control the rate of API calls to within the allowable rate for google. To do this we will use a script which only geocodes one address and use the scheduler to “refresh” this until all are geocoded.
Methodology
So the basic function will be:
Query the database for an entry in need to geocodiing (in this case isgeocoded = Null)
Concatenate the address fields into a full address and set in a variable
call the API script
If successful set the geo info otherwise set as geo failed
Repeat until all done
Data Structures
So the data structures we will be using are:
Address:
AddressGeoCode
geoaddressid is a foreign key to primary key addressid
The server Action
Firstly we create a server action (i have called get_non_geocoded) The server action
This will have 3 stages
Database Connection
Firstly create a database connection
The query
Then create a database query, For maximum efficiency this should be a paged query with limit set to 1 so only 1 record is retrieved however for this tutorial i am going to use a standard query as the record count will server for a countdown
So query our table
we join these tables with an INNER join on addressid = geoaddressid and filter the results on isgeocoded == Null
and filter in isgeocoded is null
This will return all records in need of geocoding
Detecting if no records found
lastly we set a variable to indicate if records have been found. We will use this as an exit condition in app connect
The condition we will use is Query1,length
NOTE length is NOT available in the data picker to will need to be typed in manually
Obviously if you have named you query differently substitute your query name for Query1
make sure Output is checked
Save your server action
The API call
Now we will go to the API call which we will do in app connect
Create a page and add app connect and bootstrap, I have named mine massgeo.php
APP connect data connection
Let us first create a database connection to the new server action which i call conn_getnotgeo
Setting the address to geocode
next we need to build the full address from the returned data
Define a variable via Data =>Variable
I have left this as the default name var1
click the data picker and add the required address fields together using the magic wand
and use Operation + to add the fields
In my case i have only use the address1 line, zip and added the country manually as this is enough to uniquely identify an address in UK, you can add as many fields as required
Creating the API Call
Now we create a google API call in app connect
so go to Data=> API Data Source and add it
Your call will be to: https://maps.googleapis.com/maps/api/geocode/json
set your google key and the address as the variable you have just created. Sensor is an optional parameter in this case.
detecting the no data returned condition
Now create another variable. I will call mine varSwitch. it’s value is unimportant at this stage so set to an empty string.
Now add the browser component as we will need this soon
Now we need to go back to our server connection and add a dynamic event on success.
we are going to add two stages.
Firstly we will use browser,goto to leave the routine if no records are returned
If records are returned we will run the google API call
the first part requires some explanation but firstly create a new page, i call mine “done.php” i simply add a message to this saying “Done”
Now back in your massgeo.php page
We need to detect when no records are returned and conditionally run a browser,GoTo
There is no direct way to do this but there is a hack to do this
When defining a variable we can actually assign an action in place of a value
So in the set value stage we will use the returned variable hasreccords and type:
conn_getnogeo.data.hasrecords?0:browser1.goto(‘done.php’
Basically this statement is interpreted as:
if hasrecords is true then set the variable to 0 otherwise goto “done.php” (yes, a bit bizarre but it works)
We then add a load of the API call
So now if not data is returned the routine will stop and .goto “done.php” otherwise the API call will be made
The lat/lng update query
So now we need another server action to store the lat/lng returned
Create a server action, mine is called setgeo
Add 4 $_GET parameters as below
Next make your server connection
detecting geocode failure
we will be setting isgeocdoed to 1 if the geocode is successful and we will be setting it to 0 if the goecoding has failed
The api response returns “OK” of the goecodeing is successful so we use this to set if the goecode is successful
So go to core actions=> Set Value and call your variable geostatus
we now set this value according to the status returned from the google API so if status ids OK we set it to 1 otherwise 0
Set value geostatus = {{$_GET.status==‘OK’?1:0}}
the update query
Now we add a database update query i will leave as the default name update1
and we update the addressGeoCode table with the lat/ lng returned and the status as if this was a successful geocode using variable geostatus
and we will adda condition to set the unique record id which will be sent from app connect
Save the server action
Information messages (optional)
No we go back to outrpage massgeo.php
Firstly add the Notifications component which we will use for info/ diagnostic messages
Now go back to out API call in app connect and select Dynamic Events => API Connect => Success
Here i have firstly added some information messages using Notifications
In 4 messages i echo the API status, the lat, the lng and the value of hasrecords
Latitude
(api1.data.results[0].geometry.location.lat)
Longitude
(api1.data.results[0].geometry.location.lng)
and hasrecords
(conn_getnogeo.data.hasrecords)
calling update after geocoding via API
lastly we call the update query
Note we set the goecodeid field from the database connection settings
more diagnostic messages (optional)
Now i am going to add an info message on screen, i am going to add the value of var1.
So add a container, row and column
Now add a Content=> Paragraph and set it’s contents to var1.value. Add margins as wanted.
At this stage we should have a fully working geocode routine but at this stage it will only geocode 1 entry
Repeating via the action scheduler
Lastly we add the Action Scheduler
For demo purposes i will set the scheduler rate to every 5 seconds
On each tick of the scheduler we will simply re-calll the massgeo.php page
and that should be it so let us see a video of it in action on 6 records with a 1 second interval set in the action scheduler
Video of this in action
massgeocode.mp4 (1.1 MB)
