In the previous section, we created a table. In this section, you will grab data from an open data site and put it on your table so that you can query it in the next section.

Most cities have open data websites and portals. The City of Albuquerque has several ArcServer endpoints with spatial data. The following code will use the requests Python library to grab public art data and then use psycopg2 to send it to the PostgreSQL database, pythonspatial:

import requests

url='http://coagisweb.cabq.gov/arcgis/rest/services/public/PublicArt/MapServer/0/query'

params={"where":"1=1","outFields":"*","outSR":"4326","f":"json"}

r=requests.get(url,params=params)

data=r.json()

data["features"][0]

The code which we mentioned earlier imports requests, then, using the URL to the ArcServer endpoint, it grabs the results of a query asking for all of the data (where:1=1) and all of the fields (outFields:*) in World Geodetic System (WGS) 84 (outSR:4326), and returns it as a JSON (f:json).

The results are loaded into the data variable. Each record is in the array features (data["features"][n]). A single record, data["features"][0], is shown as follows:

{'attributes': {'ADDRESS': '4440 Osuna NE',
 'ARTIST': 'David Anderson',
 'ART_CODE': '101',
 'IMAGE_URL': 'http://www.flickr.com/photos/abqpublicart/6831137393/',
 'JPG_URL': 'http://farm8.staticflickr.com/7153/6831137393_fa38634fd7_m.jpg',
 'LOCATION': 'Osuna Median bet.Jefferson/ W.Frontage Rd',
 'OBJECTID': 951737,
 'TITLE': 'Almond Blossom/Astronomy',
 'TYPE': 'public sculpture',
 'X': -106.5918383,
 'Y': 35.1555,
 'YEAR': '1986'},
 'geometry': {'x': -106.59183830022498, 'y': 35.155500000061544}}

With the data, you will iterate through the array of features, inserting the ART_CODE as code and creating a well-known text (WKT) representation of each point.

The following code shows you how to insert the data:

for a in data["features"]:
    code=a["attributes"]["ART_CODE"]
    wkt="POINT("+str(a["geometry"]["x"])+" "+str(a["geometry"]        ["y"])+")"
    if a["geometry"]["x"]=='NaN':
        pass
    else:
        cursor.execute("INSERT INTO art_pieces (code, location)             VALUES ({},
        ST_GeomFromText('{}'))".format(code, wkt))
connection.commit()

The preceding code iterates through each feature. It assigns ART_CODE to code, then constructs the WKT (Point(-106.5918 35.1555)), and assigns it to wkt. The code uses ART_CODE to show how to load other properties into the database.

Data is almost never clean and perfect. This data is no exception. So that it doesn't crash when the x coordinate is missing, I have added an if, else statement to skip over missing data. This concept is known as error handling, and it is a best practice when constructing requests. The else statement is where the data gets inserted. Using cursor.execute(), you can construct the SQL query.

The query inserts art_pieces into the database along with the code and location fields with values. The first value, for code, is a placeholder {}. The second value, for location, is geometry which we stored as WKT. Because of this, it is inserted using the ST_GeomFromText() function and a placeholder {}.

The format() method is where you pass the variables to fill the placeholders—code, wkt. The following code shows what the query will look like when the placeholders are filled in:

INSERT INTO art_pieces (code, location) VALUES (101, ST_GeomFromText('Point(-106.5918 35.1555)'))

In the previously mentioned code, you created the WKT as a concatenated string. This can be accomplished in a cleaner and more Pythonic fashion by using the Shapely library.