Subscribe to this thread
Home - General / All posts - Geocoding with Google data source in 9 - real world data experience

921 post(s)
#28-Apr-18 07:00

There is a separate thread dealing with the technical aspects. We're using Google as the geocoding data source because Bing is not available currently.

We have ten tables of between 100 and 1,000 real Australian addresses. One is included in the MXB. They all need to be geocoded.

The query "Geocode Query" is for that purpose, it requires a new geocoding data source - named "google_geo" in the screenshot. Note: Adding a Google geocoding API key (free version) fails - all geoms "NULL"


  • First run of query of 248 records, 184 return a point, 64 return a null
  • Second run of 64 failed records, 51 return a point, 13 again return null
  • Third run of 13 failed records, 10 return a point, 3 again return null
  • Fourth run of 3 failed records, all 3 return a point.

It's necessary to select all the Nulls from each run, copy and paste as a new table, then edit the query to reflect the new table and a new output table. It would be nice if the status bar displayed the number of selected records, currently we have to invoke the Contents pane / Component to get this information.

To get a composite table of all 248 records with a populated Geom, Select / Template / Is not Null / Value: Geom, Replace Selection, Table window copy, give result table from run 3 focus Menu Edit / Paste (Ctrl V). Repeat for other runs. Sure could be done via SQL, probably a little quicker for a quick keyboarder!

All in all, an unsatisfactory experience and not one that I could charge a normal commercial rate for. I've included the actual data in case anyone else wishes to have a go. I've performed this exercise three times and the only difference is the actual number of Null results for each run. I don't think this is Manifold issue per-se, Google have obviously done something their end. However, if this is as good as it's going to get using a "free" geocoding data source (I notice Google want $10,000 for an annual fee, couldn't claw a fraction of that back commercially) and Bing remain inaccessible, looks as though we may have to develop a local file based geocoding database.


Geocode with Google real data example.mxb
Google API key screenshot.PNG
New Geocoding data source.PNG


4,941 post(s)
#28-Apr-18 10:45

because Bing is not available currently

Is Bing available if you sign up for a free key? Have you tried any of the other geocoders? For example, you can get a free key for Here or try to use OpenCage.


8,037 post(s)
#28-Apr-18 10:58

If you add a geom field to the original table, eg:


ALTER TABLE [Address_Source_table] (ADD [Geom] GEOM);

...then you can rewrite the query to just geocode the geoms that are NULLs:



SELECT [mfd_id][Full_Address][Geom]

  FROM [Address_Source_table]


SET [Geom] = GeomMakePoint(GeocodeAddress([google_geo],[Full_Address]));

This way you just run the query repeatedly until all geoms are geocoded.

Given that web geocoders returning NULLs for whatever reason only to work a second later is so common, perhaps we should extend the geocoding functions to (a) use a queue to throttle requests, and (b) try multiple times.


4,941 post(s)
#28-Apr-18 14:13

Some geocoders have a throttle, some documented and some undocumented. For example, I believe that OSM rejects more than two queries a second, or something like that... a surprisingly low number if I remember correctly (could be wrong). So if you write code to ask OSM for a geocode you have to insert a delay.

A throttle would have to be spread out over time. Trying multiple times, likewise, would have to have some sort of time delay option because some geocoders will shut you off if you use them "too much", without actually saying what they consider fair use.

Long term, I think the best way to get around all this if you want free geocoding is to have some means to download OSM data for the area of interest and then do your own local geocoding against that. It's not updated automatically and it is far from perfect, but at least there isn't a black box in the picture that sometimes decides to return NULL, sometimes blocks you, and so on.


921 post(s)
#02-May-18 07:21

Thanks folks, the repeat run on nulls via Google geocoder, using your update query did the trick.

I've just double checked Bing geocoder and it is available again, working but does require an API key.

Manifold User Community Use Agreement Copyright (C) 2007-2017 Manifold Software Limited. All rights reserved.