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)

Community Page
Last updated: 2019-04-23T14:35:15.344Z