In Chapter 3, Introduction to Geospatial Databases, you queried the database and got text back. The geometry came back as well-known text (WKT). These are the results we asked for, but I cannot visualize geographic data by reading a list of coordinates. I need to see it on a map. In this section, you will use ipyleaflet and Jupyter to map the results of your queries.

To map the queries in Jupyter, you need to install ipyleaflet. You can do this using pip at your OS's command prompt:

pip install ipyleaflet

Then you may need to enable the extension, depending on your environment. At the command prompt, type:

jupyter nbextension enable --py --sys-prefix ipyleaflet

If you receive an error in your mapping, you may need to enable the widgetsnbextension:

jupyter nbextension enable --py --sys-prefix widgetsnbextension

If you have Jupyter running, you will need to restart it.

With ipyleaflet installed and enabled, you can map your queries:

import psycopg2
from shapely.geometry import Point,Polygon,MultiPolygon
from shapely.wkb import loads
from shapely.wkt import dumps, loads
import datetime
import json
from ipyleaflet import (
    Map, Marker,
    TileLayer, ImageOverlay,
    Polyline, Polygon, Rectangle, Circle, CircleMarker,
    GeoJSON
)

The previous code imports the libraries we need to query and map the data. Let's make the connection and get the cursor, as shown in the following code:

connection = psycopg2.connect(database="pythonspatial",user="postgres", password="postgres")
cursor = connection.cursor()

In Chapter 3, Introduction to Geospatial Databases, the queries all used ST_AsText() to return geometry. Now that we will map the results, it will be easier if we have them returned as GeoJSON. In the following code, you will use ST_AsGeoJSON() to get the geometry:

cursor.execute("SELECT name, ST_AsGeoJSON(geom) from areacommand")
c=cursor.fetchall()
c[0]

The previous query grabs all the records in the areacommand table, with their name and geometry as GeoJSON, then prints the first record (c[0]). The result is as follows:

('FOOTHILLS',
 '{"type":"MultiPolygon","coordinates":[[[[-106.519742762931,35.0505292241227],[-106.519741401085,35.0505292211811],[-106.51973952181,35.0505292175042],[-106.518248463965,35.0505262104449],[-106.518299012166,35.0517336649125],[-106.516932057477,35.0537380198153],....]]]}

Now that you have some GeoJSON, it is time to create a map to display it. To make the leaflet map, use the following code:

center = [35.106196,-106.629515]
zoom = 10
map = Map(center=center, zoom=zoom)
map

The previous code defines the center of the map which, for Albuquerque, I always use the intersections of I-25 and I-40. This intersection splits the city into quadrants. The code then defines the zoom level—the higher the number, the closer the zoom. Lastly, it prints the map. 

You will have a blank basemap with OpenStreetMap tiles. In Jupyter, when you add data to the map, you can scroll back to the original print of the map to see the data; you do not need to reprint the map every time.

The GeoJSON of the area commands is stored in variable c. For every item c[x], the GeoJSON is in position 1 (c[x][1]). The following code will iterate through c and add the GeoJSON to the map:

for x in c:
   layer=json.loads(x[1])
   layergeojson=GeoJSON(data=layer)
   map.add_layer(layergeojson)

The previous code assigns the GeoJSON to a layer using json.loads(). This will make the returned GeoJSON string a dictionary in Python. Next, the code calls the ipyleaflet GeoJSON() method on the layer, and passes it to the variable layergeojson. Finally, add_layer() is called on the map and passes layergeojson. There are other ways to draw maps in Jupyter; for example, you could plot them using Matplotlib, Plotly, or Bokeh. If you come from web mapping, you are probably already familiar with the Leaflet JavaScript library, which will make using ipyleaflet familiar. Also, ipyleaflet loads a basemap and provides interactivity.

If you scroll up to the map, you should see the screenshot as follows:

Changing the SQL query in cursor.execute(), you can map the beats:

cursor.execute("SELECT beat, ST_AsGeoJSON(geom) from beats")
c=cursor.fetchall()
for x in c:
   layer=json.loads(x[1])
   layergeojson=GeoJSON(data=layer)
   map.add_layer(layergeojson)

You should see the beats drawn as follows:

You can do the same for incidents, but we will hold on to that for now, because there are almost 30,000 incidents in the data set and it would overwhelm our map. To map incidents, we will use spatial queries to limit our selection.