With the tables in place, we need to grab the data and populate them. The following code will grab the area commands and insert them into our table:

url='http://coagisweb.cabq.gov/arcgis/rest/services/public/adminboundaries/MapServer/8/query'
params={"where":"1=1","outFields":"*","outSR":"4326","f":"json"}
r=requests.get(url,params=params)
data=r.json()

for acmd in data['features']:
    polys=[]
 
    for ring in acmd['geometry']['rings']:
        polys.append(Polygon(ring))
    p=MultiPolygon(polys)
    name=acmd['attributes']['Area_Command']
    
    cursor.execute("INSERT INTO areacommand (name, geom) VALUES ('{}',
    ST_GeomFromText('{}'))".format(name, p.wkt))
 
 connection.commit()

The previous code uses requests to query the URL passing parameters. The parameters just grab all the data (1=1), and grab all the fields (*) in reference 4326 and as json. The results are loaded in the variable data using the json() method. 

The next block of code is the for loop that will insert the data. The service returns json, and the data we need is stored in the features array. For each area command (acmd) in the features array (data['features']), we will grab the name and geometry.

The geometry is comprised of multiple rings—in this case, because our data is comprised of polygons. We need to loop through the rings. To do so, the code has another for loop that iterates through each ring, creates a polygon, and adds it to polys[]. When all the rings are collected as polygons, the code creates a single MultiPolygon with the name of the area command and inserts it into the table using cursor.execute().

The SQL is the basic insert command but uses a parameterized query and ST_GeometryFromText(). Do not get distracted by those additions. Build the query by using the base query as follows:

INSERT INTO table (field, field) VALUES (value,value)

To pass the values, the code uses .format(). It passes the string name and uses Shapely to convert the coordinates to WKT (p.wkt).

You will need to do the same thing for the beats table:

url='http://coagisweb.cabq.gov/arcgis/rest/services/public/adminboundaries/MapServer/9/query'
params={"where":"1=1","outFields":"*","outSR":"4326","f":"json"}
r=requests.get(url,params=params)
data=r.json()

for acmd in data['features']:
    polys=[]
    for ring in acmd['geometry']['rings']:
        polys.append(Polygon(ring))
    p=MultiPolygon(polys)
 
    beat = acmd['attributes']['BEAT']
    agency = acmd['attributes']['AGENCY']
    areacomm = acmd['attributes']['AREA_COMMA']

    cursor.execute("INSERT INTO beats (beat, agency,areacomm,geom) VALUES ('{}','{}','{}',
    ST_GeomFromText('{}'))".format(beat,agency,areacomm,p.wkt))
 
connection.commit()

The previous code is the same as the code for area commands, only passing additional fields using multiple placeholders ('{}').

Lastly, we need to add the incidents:

url='http://coagisweb.cabq.gov/arcgis/rest/services/public/APD_Incidents/MapServer/0/query'
params={"where":"1=1","outFields":"*","outSR":"4326","f":"json"}
r=requests.get(url,params=params)
data=r.json()

for a in data["features"]:
    address=a["attributes"]["CV_BLOCK_ADD"]
    crimetype=a["attributes"]["CVINC_TYPE"]
    if a['attributes']['date'] is None:
        pass
    else:
        date = datetime.datetime.fromtimestamp(a['attributes']['date'] / 1e3).date()
    try:
        p=Point(float(a["geometry"]["x"]),float(a["geometry"]["y"]))
        cursor.execute("INSERT INTO incidents (address,crimetype,date, geom) VALUES
        ('{}','{}','{}', ST_GeomFromText('{}'))".format(address,crimetype,str(date), p.wkt))
 
   except KeyError:
        pass
connection.commit()

The previous code grabs the data using requests. It then iterates through the features. This code block has some error checking because there are features with blank dates and some with no coordinates. The code passes if there is no date and uses a try, with the catch block accepting a KeyError, which will catch the missing coordinates.

Now that the data is loaded into the tables, we can start to query the data and present it in maps and charts.