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

933 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"

Results:

  • 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.

9.166.5

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

Dimitri

5,119 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.

adamw


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

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

--SQL9

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

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

--SQL9

UPDATE (

SELECT [mfd_id][Full_Address][Geom]

  FROM [Address_Source_table]

  WHERE [Geom] IS NULL

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.

Dimitri

5,119 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.

Graeme

933 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.

Graeme

933 post(s)
#26-Sep-18 03:39

Returned to this previously effective 9 project to geocode new addresses, but today Google geocoding data source fails to populate the geom column with a point. It worked last week. I created a new Bing geocoding data source with an API key and it kind of worked - same list of 29 addresses which Google populated last week, Bing just won't return a result for two, regardless of multiple re-runs. Any one else having Google geocode issues?

Graeme

933 post(s)
#26-Sep-18 04:41

Tracked down the Bing issue with failed points. The addresses were formatted "crn Street 1 & Street 2". Replacing the "&" with a "/" works for Bing.

adamw


8,259 post(s)
#27-Sep-18 09:57

Regarding Bing geocoder choking on & - this apparently is a popular issue with Bing. We have a workaround that side-steps the issue, it will be part of the next cutting edge build.

adamw


8,259 post(s)
#09-Oct-18 12:35

Just in case, 9.0.168.3 includes the workaround. You should be able to geocode strings with & using Bing geocoder without any issues now.

Dimitri

5,119 post(s)
#26-Sep-18 10:37

Any one else having Google geocode issues?

Are you using a paid service or a free service? If you use a free service, there are no guarantees.

Graeme

933 post(s)
#27-Sep-18 00:13

Fair point, yes unpaid.

Dimitri

5,119 post(s)
#27-Sep-18 06:18

Google exists to sell you stuff, and nobody is better than Google at profiling you to better understand how to hook you. They are also very subtle about how they do that, and they will use every technical means that are enabled in the many pages of their terms to which you agreed at some point when you touched some Google product. From a purely computer science perspective, it is impressive how extensive and totally defining those technical means can be.

Normal humans cannot live with Internet without having touched some Google product at some point, so basically all of humanity has agreed to Google surveillance of every last click they've made and every byte they've interchanged. You've agreed to let Google use everything you've done for years - every moment that you've spent online - as feed data for the AI that will extract another few cents out of you for Google. Automatically. It never sleeps, it never gets tired, and it never gives up.

Google will use all that to nudge you into what they want you to do so that Google can make more money. They're a business, not Mother Teresa. If they think you are a candidate to spend a few cents per month on a paid geocoding service, they'll nudge you in that direction by manipulating what the free service does or does not do. It will be a custom manipulation, just for you. They'll know better than you do yourself exactly at which point what sort of nudge will maximize the likelihood of your saying "enough of this... time to sign up for the paid service."

It is an interesting world we have created.

hgmonaro45 post(s)
#08-Oct-18 05:33

I use a JavaScript (found somewhere) that requires a Google free API key but limits you to a certain number of calls per day (can't remember the actual number but I've never hit the limit based on the following...) The script has a delay factor built in and I have found 80ms is reliable (ie. if the address can be found it will, fails are due to addresses that are dodgy for whatever reason). What it does do though is it slows down considerably after about 200 geocodes and although it will keep going, I find it quicker to run in batches of 200.

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