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

[Lucas Ferreira Mation <lucasmation@gmail.com>]

Joe,

you cracked it.  I'll now test Mapquest API, for response time, any
unmentioned query limits, and if the data is accurate for Brazil.
Tks a lot.
Regards
Lucas



On Tue, Mar 25, 2014 at 9:18 PM, Joe Canner <jcanner1@jhmi.edu> wrote:
>
> 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惴  trying to use the
> API from Mapquest. I惴 having trouble parsing the json data I get from
> the API, to extract the (lat,lon) coordinates.
> I don愒 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/

*
*   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/