Subscribe to this thread
Home - General / All posts - Radian - Geocoding
Bob Cardwell3 post(s)
#15-Mar-17 14:33

First of all - Radian Studio! Wow! It is really as fast as advertised. I'm less than pleased with all the GUI changes from Manifold ("Engineers, they just LOVE to change things").

However, what happened to geocoding? About 90% of my work involves locating addresses. It appears that in Radian you have to build your own. Has anyone done this? Am I missing something obvious?

Help!

Dimitri

4,024 post(s)
#15-Mar-17 16:02

Geocoding in Radian is done in SQL (for now... there will be point and click templates added to the Transform dialog later on).

Launch the command window and enter geocode in the filter box to see the functions. Data sources are the various geocoding servers that are supported, which you can useNew Data Source to create. In the Type box enter Web Server: geocodeserver and that will give you a list of geocoding servers you can use, similar to how you can get a list of built-in imageservers. It's a big list, and the legacy geocoding database (TIGER/Line) used in Release 8 can also be used via other types.

Suppose we create a geocoding server data source called GoogleG using the google geocoding server. We have a table called adds (short for addresses) and it has a field called address. The address has addresses in the form like

123 main street, newtown, OK, 12345

Add a new field to the table, let's call it location, that is of type float32x2, and with the Set Field values box checked. In the Expression tab enter

GeocodeAddress([GoogleG],[address])

You're telling Radian to populate the location field with the results of the GeocodeAddress function using the Google geocoder applied to the contents of the address field. That function (as you can see from the command window listing) returns a value x 2 , that is, two numbers. That's why we made the location field type float32x2.

Press the add field button and you get a column that provides geocoding for the addresses.

If you want to do this sort of thing as a query , in the command window you can enter SQL like

SELECT GeocodeAddress([GoogleG],[address]) FROM [adds];

The google geocoder, by the way, is a huge deal. It does reverse geocoding. The opencage geocoder uses OSM. Others supported by Radian might require an API key (paid). But the choices are much better and the functionality using things like reverse geocoding will be expanded.

artlembo


2,784 post(s)
#15-Mar-17 16:10

in addition, you can use the VALUES statement if you'd like:

VALUES (GeocodeAddress([geocoder]'16 Bush Lane Ithaca NY')) 

Bob Cardwell3 post(s)
#15-Mar-17 22:23

Dimitri,

I ran a set of 4290 addresses through the Google source using the table modification method. I got 2296 locations (53%). This is a small town in north-central Colorado. I would expect marginal results.

Then, I ran the same set of 4290 addresses through the Google source using the SQL method several times. Earlier runs gave me fewer locations and on the last run, I got no results.

There is something strange about this. Running the same data against the same Geocoding source should produce the same results.

What's going on?

Bob

artlembo


2,784 post(s)
#15-Mar-17 22:41

You may be hitting up against the daily usage limit.

Bob Cardwell3 post(s)
#15-Mar-17 23:11

At this point, that is probable. I've run this at least 6 times today. What is the daily usage limit?

adamw

7,031 post(s)
#16-Mar-17 07:16

Seems to be 2500 free requests per day (Google Maps Geocoding API Usage Limits), they they have premium plans or pay-as-you-go, etc.

There is also a limit of 50 requests per second. I guess it would make sense to perform geocoding against that service in batches of 50 records. You can check the log to see if there were any errors.

adamw

7,031 post(s)
#16-Mar-17 08:03

Regarding performing geocoding in batches of 50 records, it is fairly easy to write a query that would update just one batch.

Ie, with a table of addresses defined like this (I also added a drawing for convenience):

--SQL

CREATE TABLE [addresses] (

  [mfd_id] INT64, INDEX [mfd_id_x] BTREE ([mfd_id]),

  [addr] NVARCHAR,

  [geom] GEOM, INDEX [geom_x] RTREE ([geom])

);

CREATE DRAWING [addresses_d] (

  PROPERTY 'Table' '[addresses]',

  PROPERTY 'FieldGeom' 'geom'

);

This query will geocode only the first 50 records that have not yet been geocoded:

--SQL

FUNCTION lastid(n INT32) INT64 AS (

  SELECT Max([mfd_id]FROM ( -- highest ID

    SELECT * FROM [addresses] WHERE [geom] IS NULL -- not yet geocoded

    ORDER BY [mfd_id] FETCH n -- in the first n records

  )

END;

UPDATE (

  SELECT * FROM [addresses] WHERE [mfd_id] <= lastid(50) -- 50 records

SET [geom] = GeomMakePoint(GeocodeAddress([googleg][addr]));

Hope this helps.

Dimitri

4,024 post(s)
#16-Mar-17 08:19

What is the daily usage limit?

That's a very good question and one that's worth asking for all web sources of info and not just the google geocoder.

It's certainly great that Radian provides access to so many different web sources of data, like zillions of WMS and other web servers, various image servers and, like we discuss in this thread, so many geocoding servers.

But, providing access to a zillion different servers means providing access under a zillion different terms of service, and those terms often change. Basically, you have to do some research on Internet on all of these different servers to learn what they allow for free, what limitations they place on free access, what their plans are for paid services and so on.

If you report back what you find on this forum for a particular source you've researched that will help other users, like adamw's pointer hopefully will help you. If we report on our experiences that may help discover any hidden rules, like servers which disconnect you if they think you've used their free service "too much" without what that means being documented anywhere.

But everybody should understand that since web servers change all the time so what is true today for any given geocoding or other web server might be different tomorrow.

mlinth
421 post(s)
#16-Mar-17 15:15

Hi,

I'm having trouble getting the Google geocoder to work.

I followed Dimitri's guide above to creating a geocoding server data source, including my API Key.

Now if I try to do either

VALUES (GeocodeAddress([GoogleG]'16 Bush Lane Ithaca NY')) 

or

SELECT GeocodeAddress([GoogleG],[address]FROM [adds];

I get null values back, and checking the log I see:

2017-03-16 16:07:55     Web request: (root)::[GoogleG] (GeocodeAddress) (0.126 sec, 172 B)

2017-03-16 16:07:55 *** (root)::[GoogleG] REQUEST_DENIED

I can type the Google geocoder with an address and my APIKey into a browser window, and I get the expected results back. Also, checking my credentials in Google's app I don't see any failed requests, even after attempting to run the query in Radian several times. Using 64bit version 9.0.159.0.

Any ideas what I am doing wrong? Is there a way I can see the actual request Radian is making?

Thanks

Martin

Dimitri

4,024 post(s)
#16-Mar-17 16:20

Is there a special URL you must use for that API key?

mlinth
421 post(s)
#16-Mar-17 16:33

Not as far as I'm aware. I placed no restrictions on it when I requested today, and the Google project api dashboard page tells me the key is unrestricted. I'm also nowhere near the daily limit.

The URL from the browser is:

https://maps.googleapis.com/maps/api/geocode/json?address=16+Bush+Lane+Ithaca+NY&key=[my key]

which returns a results json object.

Here's the Radian source url from the data source properties:

http:\/\/maps.googleapis.com\/maps\/api\/geocode\/json?

Apart from the escaping, then only difference I see is that one is https, the other http. (I just edited the Radian property to https for humour, but I get the same request refused error).

The full source properties is:

"Source""http:\/\/maps.googleapis.com\/maps\/api\/geocode\/json?""SourceApiKey""[my key]""SourceCache""true""SourceSubtype""google" }

I checked the key values; they're identical.

adamw

7,031 post(s)
#17-Mar-17 06:38

Does it work if you remove the API key or try a different - for example, a new free one - API key? (It works without the API key here.)

We will consider adding an option to log exact URLs for all issued web requests temporarily for debugging issues like that.

mlinth
421 post(s)
#17-Mar-17 09:49

Hi Adam, Dimitri,

I just created a new data source without the API key and it works fine! I'm sure I tried it without an API key before - user error probably.

Thanks both for getting back so quickly. Radian is extremely impressive and your quick and helpful responses here are much appreciated.

M

apo9 post(s)
#31-Mar-17 14:22

Hi all,

able to reproduce and geocode without any problem using some servers I just tried to reversecode locations using the following code but failed

VALUES (GeocodeLocationMatches([GgeoC],VectorMakeX2(6.635622,46.524019))) ;

Any idea what is wrong in this query? because I'm facing a "Invalide object reference" message

best

a.

adamw

7,031 post(s)
#31-Mar-17 14:36

GeocodeLocationMatches returns a table.

Try (untested):

--SQL

EXECUTE CALL GeocodeLocationMatches([GgeoC], VectorMakeX2(6.635622, 46.524019));

The result table may contain multiple records. If so, they will appear in the order used by the server - most servers return matches from best to worst. The format of the matches depends on the server - usually JSON or XML.

apo9 post(s)
#31-Mar-17 14:45

great this is the way to handle the table result.

works great

thank's

a.

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