Our crime database has a polygon area—area commands and beats—as well as incident points. To build a crime dashboard, we want to be able to map incidents within a specific area command or beat. We can do that by using JOIN and ST_Intersects. The following code shows you how:

cursor.execute("SELECT ST_AsGeoJSON(i.geom) FROM incidents i JOIN areacommand acmd ON ST_Intersects(acmd.geom, i.geom) WHERE acmd.name like'FOOTHILLS' and date >= NOW() - interval '10 day';")

crime=cursor.fetchall()
for x in crime:
    layer=json.loads(x[0])
    layergeojson=GeoJSON(data=layer)
    map.add_layer(layergeojson)

The previous code selects the geometry from incidents as GeoJSON (ST_AsGeoJSON(i.geom) from incidents), where the incident ST_Intersects the polygon area command, specifically where the name of the area command is FOOTHILLS. The code is joining the incident and area command tables where the intersection is true. The code limits the results by selecting only the last 10 days of crimes. 

The code then iterates through the results and maps them as in the previous examples. You should see the screenshot as follows:

The preceding screenshot overlays the incidents on the Foothills area command. Notice all the incidents are within the polygon. 

You can do the same thing for specific beats by changing the SQL query. The following code will map specific beats:

cursor.execute("SELECT ST_AsGeoJSON(geom)from beats where beats.beat in ('336','523','117','226','638','636')")

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

The previous code uses an array of the beats.beat field. In Python, the array is [], but in the SQL statement, use parentheses. The results are the specified beats. Then, the code maps them.

Using the same specified beats, we can select the incidents using a join on ST_Intersects() with the beats, and mapping the incidents as shown in the code: 

cursor.execute("SELECT ST_AsGeoJSON(i.geom) FROM incidents i JOIN beats b ON ST_Intersects(b.geom, i.geom) WHERE b.beat in ('336','523','117','226','638','636') and date >= NOW() - interval '10 day';")

crime=cursor.fetchall()
for x in crime:
    layer=json.loads(x[0])
    layergeojson=GeoJSON(data=layer)
    map.add_layer(layergeojson)

The previous code passes the beats array and filters again by the last 10 days. It then maps the incidents, as shown in the following screenshot: