def crimes_api(fileext=None):
if len(request.args) == 0:
return genMarkdownResult('doc/crimes.md')
queryType = request.args.get('query')
year = request.args.get('year')
crimeCls = request.args.get('class', '')
# Remove later if we return spatial data
if fileext != 'json':
return make_response('Error: Must use file extension .json (for now)', 400)
if queryType and fileext == 'json':
if queryType == 'perNeighborhoodPerYear':
if not year:
return make_response('Error: must supply year parameter', 400)
startRange = year + '-01-01'
try:
endRange = int(year)
except ValueError:
return make_response('Error: year must be a number between 2004->2014', 400)
endRange = str(endRange+1) + '-01-01'
sql = "SELECT a.neighborhood as name, count(*) as count FROM crime3 a JOIN portland_neighborhoods b ON a.neighborhood=b.name " \
"WHERE a.report_datetime >= %s and a.report_datetime < %s GROUP BY a.neighborhood"
if crimeCls == 'violent':
sql = sql.replace('GROUP BY', "AND a.violent='t' GROUP BY")
elif crimeCls == 'nonviolent':
sql = sql.replace('GROUP BY', "AND a.violent='f' GROUP BY")
else:
# Just for returning to the caller
crimeCls = 'all'
db, cur = getDbConn()
cur.execute(sql, (startRange, endRange))
return jsonify({'query': 'perNeighborhoodPerYear', 'class': crimeCls, 'year': year,
'rows': [(row[0], row[1]) for row in cur]})
return make_response('Error: unknown query option', 400)
def permits_api(fileext=None):
if len(request.args) == 0:
return genMarkdownResult('doc/permits.md')
# Required argument. One of 'residential'. Will eventually support 'commercial' as well
permitType = request.args.get('type')
if not permitType:
return make_response('Error: Must supply permitType arg', 400)
# Read optional params
bounds = request.args.get('bounds')
startDate = request.args.get('startdate')
endDate = request.args.get('enddate')
nbrhood = request.args.get('neighborhood')
queryType = request.args.get('query')
# Return JSON formatted table without spatial.
if queryType and fileext == 'json':
if queryType == 'perNeighborhoodPerYear':
# Permits by neighborhood by year
db, cur = getDbConn()
#sql = "SELECT COUNT(*) AS num, year(issuedate) AS date_issued, nbrhood FROM permits2 " \
# "WHERE YEAR(issuedate) > 0 GROUP BY(YEAR(issuedate)), nbrhood"
# Filter out additions & alterations
sql = "SELECT COUNT(*) AS num, year(issuedate) AS date_issued, nbrhood FROM permits2 " \
"WHERE YEAR(issuedate) > 0 AND new_class NOT IN ('ALTERATION', 'ADDITION') " \
"GROUP BY(YEAR(issuedate)), nbrhood"
cur.execute(sql)
return jsonify({'query': 'perNeighborhoodPerYear',
'rows': [(row[0], row[1], row[2]) for row in cur]})
# Spatial Queries
if fileext != 'geojson':
return make_response('Error: Unknown file type extension', 400)
if bounds:
try:
# bounds is x1, y1, x2, y2
ext = [float(pt) for pt in bounds.split(',')]
except ValueError:
return make_response('Error: Bounds values must be floats', 400)
else:
# Use the default BBX of the entire city
ext = consts.PDX_BOUNDING_BOX
# POLYGON(x1 y1, x1 y2, x2 y2, x2 y1, x1 y1)
mbrPolyWKT = "'POLYGON((%f %f, %f %f, %f %f, %f %f, %f %f))'" % (ext[0], ext[1], ext[0], \
ext[3], ext[2], ext[3], ext[2], ext[1], ext[0], ext[1])
# Setup base SQL with spatial bounding box query
sql = "SELECT AsText(shape), id, address, issuedate, nbrhood, sqft, value, units " \
"FROM permits2 WHERE MbrContains(PolygonFromText(%s), shape)"
qParams = []
# Always filter out additions & alterations
sql += " AND new_class NOT IN ('ALTERATION', 'ADDITION')"
# Optional start date & end date append to WHERE clause. ToDo: validate start & end date
if startDate:
sql += " AND issuedate >= %%s"
qParams = [startDate]
if endDate:
sql += " AND issuedate <= %%s"
qParams.append(endDate)
# Comma delimited list of neighborhoods. Build SQL IN expression
if nbrhood:
nbrs = nbrhood.split(',')
sql += " AND nbrhood IN (" + "%%s," * len(nbrs)
sql = sql[:-1] + ')'
qParams.extend(nbrs)
# Get a database connection & execute query
db, cur = getDbConn()
cur.execute(sql % (mbrPolyWKT,), qParams)
# Convert result to GeoJSON using default CRS. If this profiles to be slow
# are a great opportunities for caching the GeoJSON
d = []
for row in cur:
d.append(geoJSONBuildPoint(*wktUnpackPoint(row[0]), prop_dict={
'id': row[1],
'address': row[2],
'issuedate': row[3].isoformat() if row[3] else None,
'nbrhood': row[4],
'sqft': row[5],
'value': row[6],
'units': row[7]
}
))
return jsonify(geoJSONBuildFeatureCollection(d))
def neighborhoods_api(fileext=None):
if len(request.args) == 0:
return genMarkdownResult('doc/neighborhoods.md')
nbrhoods = request.args.get('name')
bounds = request.args.get('bounds')
option = request.args.get('option')
queryType = request.args.get('query')
city = request.args.get('city', '')
if fileext not in ('json', 'geojson'):
return make_response('Error: missing or invalid file extension', 400)
if queryType and fileext == 'json':
if queryType == 'list':
# Return just neighborhood list. Filter by city
sql = "SELECT distinct(name) FROM neighborhoods ORDER BY name"
# If city is Portland, just use the view 'portland_neighborhoods'
if city == 'portland':
sql = "SELECT name FROM portland_neighborhoods ORDER BY name"
db, cur = getDbConn()
cur.execute(sql)
return jsonify({'query': 'list',
'rows': [row[0] for row in cur]})
elif queryType == 'listWithBBX':
# Return neighborhood list w/ BBX of each shape. Filter by city
sql = "SELECT name, astext(St_envelope(shape)), area FROM urbandev_stg.neighborhoods "
if city == 'portland':
sql += "WHERE in_portland='t'"
sql += " ORDER BY name"
db, cur = getDbConn()
cur.execute(sql)
result = []
nbrs = OrderedDict()
for row in cur:
# Change WKT Polygon to Rect
ply = row[1].decode("utf8")
rectP1 = ply[9:-2].split(',')
p1 = rectP1[0].split(' ')
p2 = rectP1[2].split(' ')
# Array of 2 arrays [y1, x1], [y2, x2]
#rectP2 = ((float(p1[1]), float(p1[0])), (float(p2[1]), float(p2[0])))
rectP2 = ((float(p1[0]), float(p1[1])), (float(p2[0]), float(p2[1])))
# There are multiple records for multi-part neighborhood polygons & we
# want a unique list so for now, we just pick the polygon with the
# greatest area.
# ToDo: fix by calculating the combined BBX of all the polygons instead
# Add name & rect if not already in dict. (May be able to use setdefault()?)
if row[0] not in nbrs:
nbrs[row[0]] = (row[2], rectP2)
else:
# If already in dict, update record if new area is greater
if nbrs[row[0]][0] < row[2]:
nbrs[row[0]] = (row[2], rectP2)
return jsonify({
'query': 'listWithBBX',
'rows': [(k, v[1]) for k, v in nbrs.items()]
})
if fileext == 'geojson':
# Return neighborhood polygons as GeoJSON features
if bounds:
try:
# bounds is x1, y1, x2, y2
ext = [float(pt) for pt in bounds.split(',')]
except ValueError:
return make_response('Error: Bounds values must be floats', 400)
else:
# Use the default BBX of the entire city
ext = consts.PDX_BOUNDING_BOX
# POLYGON(x1 y1, x1 y2, x2 y2, x2 y1, x1 y1)
mbrPolyWKT = "'POLYGON((%f %f, %f %f, %f %f, %f %f, %f %f))'" % (ext[0], ext[1], ext[0], \
ext[3], ext[2], ext[3], ext[2], ext[1], ext[0], ext[1])
# Base sql
sql = "SELECT id, name, AsText(shape) FROM neighborhoods "
if option == 'intersects':
# Grab any neighborhood polygon whose bbx intersects the viewport
sql += "WHERE MbrContains(PolygonFromText({0}), shape) " \
"OR MbrIntersects(PolygonFromText({0}), shape)".format(mbrPolyWKT)
elif option == 'contains':
# Grab only neighborhood polygons whose bbx is completely within the viewport
sql += "WHERE MbrContains(PolygonFromText({0}), shape)".format(mbrPolyWKT)
qParams = []
if nbrhoods:
nbrs = nbrhoods.split(',')
if 'WHERE' in sql:
sql += " AND name IN (" + "%s," * len(nbrs)
else:
sql += " WHERE name IN (" + "%s," * len(nbrs)
sql = sql[:-1] + ')'
qParams.extend(nbrs)
# Get a database connection & execute query
db, cur = getDbConn()
cur.execute(sql, qParams)
d = []
for row in cur:
# Convert neighborhood Polygons & MultiPolygons to GeoJson. If this profiles to be slow
# are a great opportunities for caching the GeoJSON
shp = shapely.wkt.loads(row[2].decode("ascii"))
gj = geojson.Feature(geometry=shp, properties={'id': row[0], 'name': row[1]})
d.append(gj)
return jsonify(geoJSONBuildFeatureCollection(d))
