Geocoding in ArcGIS with STATA using JSON

[John MacDonald]

For many STATA users there is the desire to get geocoded data without paying for a geocoding service. Below is some code that worked well using JSON from the open ArcGis geocoder. The program is slow but it does a good job matching.

************************************************** *****
* LOAD DATA
************************************************** *****
import delimited "~dataset", clear

************************************************** *****
* 1. Split full address
************************************************** *****
split full_address, parse(",") gen(part)

drop address new_address city state full_address
rename part1 address
rename part2 city
rename part3 state

foreach v in address city state {
replace `v' = trim(`v')
}

************************************************** *****
* 2. Address cleanup
************************************************** *****
replace address = subinstr(address, "00 ", "100 ", 1) if substr(address,1,3) == "00 "
replace address = subinstr(address, "00", "50", .)

************************************************** *****
* 3. Rebuild single-line address
************************************************** *****
gen full_address = trim(address + ", " + city + ", " + state)

* URL-safe encoding (manual)
gen safe_address = full_address
replace safe_address = subinstr(safe_address, " ", "%20", .)
replace safe_address = subinstr(safe_address, ",", "%2C", .)

keep safe_address


************************************************** *****
* 4. Build ArcGIS URL
************************************************** *****
gen url = "https://geocode.arcgis.com/arcgis/rest/services/World/GeocodeServer/findAddressCandidates?f=json&singleLine=" ///
+ safe_address + ///
"&outFields=Match_addr,Addr_type,location"

************************************************** *****
* 5. Output variables
************************************************** *****
gen lat = .
gen lon = .
* Use strL for match_addr to ensure long addresses are not truncated
gen strL match_addr = ""

tempfile j

************************************************** *****
* 6. GEOCODING LOOP — with FAIL-SAFE SKIPS (using jsonio)
************************************************** *****
forvalues i = 1/`=_N' {

di "Geocoding row `i' of `=_N'"

*--------------------------------------------------
* 6.1 Try fetching JSON (saves content to tempfile 'j')
*--------------------------------------------------
local myurl = url in `i'

capture noisily copy "`myurl'" "`j'", replace

* If copy failed → skip row
if _rc {
di as error " → SKIPPED (URL fetch failed)"
continue
}

*--------------------------------------------------
* 6.2 Import JSON using jsonio rv (row-value mode)
*--------------------------------------------------
preserve
capture noisily jsonio rv, filenm("`j'") nourl obid(1) stubname(geo)

if _rc {
restore
di as error " → SKIPPED (JSON parse failed)"
continue
}

*--------------------------------------------------
* 6.3 Extract results from parsed JSON
*--------------------------------------------------

* The variables will be named geo1, geo2, geo3, etc.
* We need to find which ones contain our coordinates and address
* Look for variables containing location.x, location.y, and address

local lon_result = .
local lat_result = .
local addr_result = ""

* Loop through all geo variables to find the right ones
quietly ds geo*
foreach var in `r(varlist)' {
local varlabel : variable label `var'

* Check if this is longitude (location.x)
if strpos("`varlabel'", "location") > 0 & strpos("`varlabel'", "x") > 0 {
local lon_result = `var'[1]
}

* Check if this is latitude (location.y)
if strpos("`varlabel'", "location") > 0 & strpos("`varlabel'", "y") > 0 {
local lat_result = `var'[1]
}

* Check if this is address
if strpos("`varlabel'", "address") > 0 & strpos("`varlabel'", "location") == 0 {
local addr_result = `var'[1]
}
}

restore

* Check if we found the coordinates
if missing(`lon_result') | missing(`lat_result') {
di as error " → SKIPPED (Coordinates not found or missing)"
continue
}

*--------------------------------------------------
* 6.4 Save results to dataset
*--------------------------------------------------

* Replace variables using the results
quietly {
replace lon = `lon_result' in `i'
replace lat = `lat_result' in `i'
replace match_addr = "`addr_result'" in `i'
}

di as text " → SUCCESS! Matched Address: `addr_result'"

}

save "filename.dta"

[Fahad Mirza]

would be nice to know what the sample dataset (with a few observations) is like for this to work