st: RE: RE: insheetjson: trouble parsing json data

[Joe Canner <jcanner1@jhmi.edu>]

Lucas,

Try the following:

insheetjson Y X using  "`url'", table("results") col("locations:1:latLng:lat" "locations:1:latLng:lng") offset(1) replace

You will notice that the data structure (from running -insheetjson- with the -showresponse- option) has square brackets in it.  These denote arrays and the arrays need to be indexed.  Thus, "locations:1" references the first element of the locations array.  Incidentally, while the JSON documentation online gave me a hint, it wasn't until I added the -flatten- option to the -showresponse- option that I learned the correct way to reference arrays in this context.

Now I will have to try this one my data... :)

Regards,
Joe
________________________________________
From: owner-statalist@hsphsun2.harvard.edu [owner-statalist@hsphsun2.harvard.edu] on behalf of Joe Canner [jcanner1@jhmi.edu]
Sent: Tuesday, March 25, 2014 7:46 PM
To: statalist@hsphsun2.harvard.edu
Subject: st: RE: insheetjson: trouble parsing json data

Lucas,

I don't know much about JSON or about the Mapquest API, but I am intrigued so I tried to get your code to work.

The first problem is that -table(results)- should be -table("results")- (just as with the Google API).

That got rid of errors but I get "[]" as the X and Y value instead of the correct long/lat.  I will keep playing around with this, but perhaps you can get further now.

Regards,
Joe Canner
Johns Hopkins University School of Medicine
________________________________________
From: owner-statalist@hsphsun2.harvard.edu [owner-statalist@hsphsun2.harvard.edu] on behalf of Lucas Ferreira Mation [lucasmation@gmail.com]
Sent: Tuesday, March 25, 2014 4:07 PM
To: statalist
Subject: st: insheetjson: trouble parsing json data

I need to o geocode a long list of addresses (>1000k obs.). Because of
googleMaps API limits (2500 queries per day), I´m  trying to use the
API from Mapquest. I´m having trouble parsing the json data I get from
the API, to extract the (lat,lon) coordinates.
I don´t know anything about json. The code bellow shows the structure
of the data, and my attempt of an insheetjson syntax:

*mapquest:
clear
set obs 30
gen str240 Y=""
gen str240 X =""
local url http://www.mapquestapi.com/geocoding/v1/address?&key=Fmjtd|luur2l0rnl%2Cax%3Do5-9a7s06&inFormat=json&json={%22location%22:{%22street%22:%20%22RUA%20BENJAMIN%20CONSTANT%202211%22,%22city%22:%22Blumenau%22,%22state%22:%22SC%22,%22postalCode%22:%2289035-100%22}}
insheetjson using "`url'", showresponse
insheetjson Y X using  "`url'", table(results)
col("locations:latLng:lat" "locations:latLng:lng") offset(1) replace


As a comparisson, I got the equivalent code to parse the googlemaps
json data from inside the geocode3 command, which works:

*googlemaps:
clear
set obs 30
gen str240 g_lat=""
gen str240 g_lon =""
local url http://maps.googleapis.com/maps/api/geocode/json?address=RUA+BENJAMIN+CONSTANT+2211+Blumenau+89035-100&sensor=false
insheetjson using "`url'", showresponse
insheetjson g_lat g_lon using "`url'" , table("results")
col("geometry:location:lat" "geometry:location:lng") limit(1)
offset(1) replace


any suggestions on this or other suggestions to geocode from other
sources is appreciated
regards
Lucas

*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/

*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/

*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/