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))
def index(): return genMarkdownResult("doc/services.md")
def census_api(): return genMarkdownResult('doc/census.md')
def taxlots_api(): return genMarkdownResult('doc/taxlots.md')