2.3.1 Parsing JSON and Accessing GIS Data via REST | GEOG 489: Advanced Python Programming for GIS

Python includes several built in methods for handling JSON (or json). As you remember from Lesson 1, json is widely used to transfer data from one language to another or from one data source to another. This section will demonstrate two of the most frequent operations that you may perform with json data, which is loading it into a Python Dictionary and dumping it to a json string. The official documentation can be found here.

Loading json means to convert a formatted string into a json object. This is useful for converting string representations of dictionaries that come from various sources, or from API’s results. It is important to note that there is also json.load(…) which takes an file or bytes like object as an input parameter whereas json.loads(…) takes a json string. The s at the end of loads denotes that the method is for a string input, whereas the load method without the s is for objects such as files. The json.dump() and json.dumps() follows the same convention, except it outputs to a file writer type object or a json string.

A simple json loads example:

#json
import json

# JSON string:Multi-line string
x = '{"City": "Cheyenne", "State": "Wyoming", "population": "Very Little", "Industries":["Mining", "Restaurants", "Rodeos"]}'

# parse x:
y = json.loads(x)

print(type(y))
print(y)

To write the python dictionary back to a json string, you would use the .dumps() method.

And a simple json dumps example:

# Creating a dictionary
wyo_hi_dict = {1:'Welcome', 2:'to', 3:'Cheyenne', 4:'Wyoming'}

# Converts input dictionary into
# string and stores it in json_string
json_string = json.dumps(wyo_hi_dict)

#print
print('Equivalent json string of input dictionary:', json_string)

Equivalent json string of input dictionary: '{"1": "Welcome", "2": "to", "3": "Cheyenne", "4": "Wyoming"}'

You will notice that the JSON dumps wraps the dictionary into single quotes, making it a string. This can be easily overlooked while troubleshooting. The dump process also converts the keys and values to strings. The de-serialization process converts the value to its datatype, but it doesn't always get it right, so sometimes we are left with adding custom casting.

Accessing the properties of the json object when loaded from json.loads() is the same as accessing them via Python dictionary.

# parse json_string back to a dictionary:
json_string = json.loads(json_string)

print(json_string["1"])

Now that we know some methods for requesting data from the web and parsing JSON data, let’s look at a REST service from esri and how we can use it to generate an url for our code. Using the query UI endpoint for the service at 2023_BLM_AZ_Fire_Restrictions_view, there are four parameters that we need to fill out for it to return a result. These four parameters we will work with are the Where, Out Fields, Return Geometry, and Return format. If you need a more specific result, you can enter more parameters as needed.

The base url for the query will start out as:

https://services3.arcgis.com/T4QMspbfLg3qTGWY/ArcGIS/rest/services/2023_BLM_AZ_Fire_Restrictions_view/FeatureServer/0/query?

As described in the last section, this ? is starting of the passing of the parameters, which are separated by & sign. Note that the url and the parameters we are passing does not contain spaces, which would break the url structure and wouldn't resolve.

The complete request’s parameters are added to the URL when the request is submitted:

?where=1=1&objectIds=&time=&geometry=&geometryType=esriGeometryEnvelope&inSR=&spatialRel=esriSpatialRelIntersects&resultType=none&distance=0.0&units=esriSRUnit_Meter&relationParam=&returnGeodetic=false&outFields=*&returnGeometry=true&featureEncoding=esriDefault&multipatchOption=xyFootprint&maxAllowableOffset=&geometryPrecision=&outSR=&defaultSR=&datumTransformation=&applyVCSProjection=false&returnIdsOnly=false&returnUniqueIdsOnly=false&returnCountOnly=false&returnExtentOnly=false&returnQueryGeometry=false&returnDistinctValues=false&cacheHint=false&orderByFields=&groupByFieldsForStatistics=&outStatistics=&having=&resultOffset=&resultRecordCount=&returnZ=false&returnM=false&returnExceededLimitFeatures=true&quantizationParameters=&sqlFormat=none&f=json&token=

Building our query that will return all features and all fields in json format will look like:

https://services3.arcgis.com/T4QMspbfLg3qTGWY/ArcGIS/rest/services/2023_BLM_AZ_Fire_Restrictions_view/FeatureServer/0/query?where=1=1&outFields=*&returnGeometry=true&f=json

With this url string, we can use the requests package to retrieve data from services and save them locally. There are a few ways of converting the returned data, such as using pandas to read the url, converting the json to a spatially enabled dataframe and then to a featureclass using built-in methods, converting the JSON result to a dictionary and using an insert/update cursor, or creating a new featureclass using the arcpy method JSONToFeatures_conversion(…) method. JSONToFeatures_conversion comes with a caveat that is noted in the Summary section of the documentation:

Converts feature collections in an Esri JSON formatted file (.json) or a GeoJSON formatted file (.geojson) to a feature class.

This means that the method is expecting a file as the input and trying to pass the response json will result in an error. If you do not need to manipulate the data before outputting to a featureclass, this method might be the simplest to implement with the least amount of packages. If you need to work on the data, such as convert the datetimes for a field, converting the JSON to a dataframe or dictionary would be the preferred process.

Below is an example of the process.

#json example
import arcpy
import requests
import json

arcpy.env.overwriteOutput = True

url = "https://services3.arcgis.com/T4QMspbfLg3qTGWY/ArcGIS/rest/services/2023_BLM_AZ_Fire_Restrictions_view/FeatureServer/0/query?where=1=1&returnGeometry=true&outFields=*&f=json"

# send the request to the url and store the reply as response
response = requests.get(url)

# Get result as json
jsonCode = response.json()

jsonFile = r"C:\GEOG489\Lesson 2\response.json"

# write the response json to a file
with open(jsonFile, "w") as outfile:
    json.dump(jsonCode, outfile)

# JSONToFeatures requires a file as input
arcpy.JSONToFeatures_conversion(jsonFile, r'C:\GEOG489\Lesson 2\Geog489.gdb\az_fire_districts', 'POLYGON')

# Clean up
if os.path.exists(jsonFile):
    os.remove(jsonFile)

You can also separate the parameters into a dictionary and let requests do the url formatting, making for a cleaner code and this method seems to also format the returned date fields, if there are any:

#params
params = {'where': '1=1', 'outFields': '*', 'f': 'pjson', 'returnGeometry': True}
response = requests.get(url, params)

As we said earlier about searching for packages that perform an ETL process, we saved a hidden gem for last. Compared to the previous methods of retrieving data from a service that we went over, the few lines of code this process requires is welcoming from a managerial and readability standpoint.

A hidden capability of arcpy’s conversion FeatureclassToFeatureclass (deprecated in favor of ExportFeatures) is that it can take a service endpoint as an input and make short work of this conversion to a Featureclass. However, as promising as it seems, some services do not transform. Since it is only a few lines of code, it is worth giving it a try and saving some time.

#FeatureClassToFeatureClass
url = 'https://services3.arcgis.com/T4QMspbfLg3qTGWY/ArcGIS/rest/services/2023_BLM_AZ_Fire_Restrictions_view/FeatureServer/0'

out_location = r"C:\GEOG489\TestingExportData\output.gdb"
out_featureclass = "Fire_Tax_District"

# Run FeatureClassToFeatureClass
arcpy.conversion.FeatureClassToFeatureClass(url, out_location, out_featureclass)

Formatting a SQL string to be used in an URL request string may take some trial and error and I suggest using the REST end points UI to help you write the SQL statement. Once complete, you can copy the formatted string to your code. To add a definition query using the arcpy method, you can set the where_clause parameter of the method.

## Adding Expression
import arcpy
import os
arcpy.env.overwriteOutput = True

url = 'https://services3.arcgis.com/T4QMspbfLg3qTGWY/ArcGIS/rest/services/2023_BLM_AZ_Fire_Restrictions_view/FeatureServer/0'

out_location = r"C:\GEOG489\TestingExportData\output.gdb"
out_featureclass = "Fire_Tax_District"

delimitedField = arcpy.AddFieldDelimiters(arcpy.env.workspace, "ORDER_NUM")
expression = delimitedField + " IS NOT NULL"

# Run FeatureClassToFeatureClass
arcpy.conversion.FeatureClassToFeatureClass(url, out_location, out_featureclass, expression)

ExportFeatures contains different parameters so it looks a little different, but works the same:

#ExportFeatures
arcpy.conversion.ExportFeatures(url, os.path.join(out_location, out_featureclass), where_clause=expression)