You created a table, added columns for code and location, and populated it using data from another source. Now, you will learn how to query the data and get it out of the database.

While there are spatial SQL queries available to you, you can always just select the data as if it were a non-spatially enabled database so that you can use it as follows:

SELECT * FROM table

The following code shows the generic SELECT all query and the results:

cursor.execute("SELECT * from art_pieces")
data=cursor.fetchall()
data

The result should look as follows:

 [(1, '101', '010100000025FFBFADE0A55AC06A658B6CE7934140'),
 (2, '102', '0101000000CC4E16E181AA5AC0D99F67B3EA8B4140'),
 .......,]

The first number, 1,2,n, is the id (the SERIAL PRIMARY KEY). Next, is the code. The geometry is the last column. The string of what appears to be random numbers and letters is a well-known binary (WKB) in hex.

To convert the WKB, you use shapely. The following code walks you through converting the WKB to a shapely Point, and then printing the WKT:

from shapely.wkb import loads
aPoint=loads(data[0][2],hex=True)
aPoint.wkt

The previous code imports the loads() method from shapely.wkb. You must add the hex parameter and make it equal to True or you will receive an error. To get the geography column of the first record, you can use data[0][2] with [0] as the record and [2] as the column. Now that you have a shapely Point, you can verify it by using type(aPoint), you can print it as a WKT using aPoint.wkt. You should see the result as follows:

POINT (-106.591838300225 35.15550000006154)

If you want PostgreSQL to return the data in WKB without hex, you can do so using ST_AsBinary(). The following code shows you how:

cursor.execute("SELECT id,code,ST_AsBinary(location) from art_pieces")
data=cursor.fetchall()
data[0][2]
from shapely.wkb import loads
pNoHex=loads(bytes(data[0][2]))
pNoHex.wkt

The previous code wraps the location in ST_AsBinary(). To load the result into a shapely Point, you have to use bytes(). Then, you can see the WKT using pNoHex.wkt. You should see the same point as in the previous example.

Binary may come in handy, but you can also query the data and get the geometry back as a WKT:

cursor.execute("SELECT code, ST_AsText(location) from art_pieces")
data = cursor.fetchone()

The previous code uses ST_AsText(geometry column) to return the data as a WKT. You can return a column that contains geometry at any time by using ST_AsText(). Instead of fetchall(), the code uses fetchone() to grab a single record. You should see a single record as follows:

('101', 'POINT(-106.591838300225 35.1555000000615)')

You can load WKT into a shapely Point using loads(), but you need to import it first, just like you did earlier with WKB:

from shapely.wkt import loads
pb=loads(data[1])
pb.coords[:]

The previous code import loads from shapely—but this time using shapely.wkt, not wkb. Otherwise, you load the data the same way as in the previous examples. You can see the coordinates of the shapely Point using pb.coords[:], or you can see them individually using pb.x and pb.y.

The result of pb.coords[:] will be a coordinate pair, which is shown as follows:

[(-106.591838300225, 35.1555000000615)]