We will need to create three tables to hold the crime data. We need a table for:

1. Area commands
2. Beats
3. Incidents

To create the tables, we need to import the required libraries:

import psycopg2
import requests
from shapely.geometry import Point,Polygon,MultiPolygon, mapping
import datetime

The precious code imports psycopg2 for connecting to PostGIS, requests to make the call to the service so you can grab the data, Point, Polygon, and MultiPolygon from shapely.geometry to make converting the GeoJSON to objects easier, and datetime because the incidents have a date field.

In Chapter 3, Introduction to Geospatial Databases, you created a database named pythonspatial with a user called postgres. We will create the tables in that database. To populate the tables, we will copy some of the fields from the service. The layer page of the service has a list of fields at the bottom.

Each of the fields has a type and length for the incidents layer as follows:

• OBJECTID (type: esriFieldTypeOID, alias: Object_ID)
• Shape (type: esriFieldTypeGeometry, alias: Geometry)
• CV_BLOCK_ADD (type: esriFieldTypeString, alias: Location, length: 72)
• CVINC_TYPE (type: esriFieldTypeString, alias: Description, length: 255)
• date (type: esriFieldTypeDate, alias: Date, length: 8)

Create the tables using the following code:

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

cursor.execute("CREATE TABLE areacommand (id SERIAL PRIMARY KEY, name VARCHAR(20), geom GEOMETRY)")

cursor.execute("CREATE TABLE beats (id SERIAL PRIMARY KEY, beat VARCHAR(6), agency VARCHAR(3), areacomm VARCHAR(15),geom GEOMETRY)")

cursor.execute("CREATE TABLE incidents (id SERIAL PRIMARY KEY, address VARCHAR(72), crimetype VARCHAR(255), date DATE,geom GEOMETRY)")

connection.commit()

The previous code starts by creating the connection and getting the cursor. It then creates the areacommand table, with a field for the name and a GEOMETRY field. In the ArcServer service, the area command field has a length of 20, so the code created a field called name as a VARCHAR(20). The next two lines create the tables for beats and incidents, and lastly, the code commits, making the changes permanent.