def coords(self):
return session.query(func.ST_Y(self.location),
func.ST_X(self.location)).one()
def ra(self):
return func.ST_X(self.radec) + 180.
def get_lat_lon(self):
return session.query(func.ST_Y(self.location),
func.ST_X(self.location)).one()
def generate_tracts():
parser = reqparse.RequestParser()
parser.add_argument('observations',
type=int,
location='args',
required=True,
help='Observations must be a positive integer!')
parser.add_argument('format',
type=str,
location='args',
required=True,
help="Format must be one of: geojson, csv, or tsv.")
parser.add_argument(
'geoid',
type=bool,
location='args',
)
parser.add_argument(
'usps',
type=bool,
location='args',
)
parser.add_argument(
'pop10',
type=bool,
location='args',
)
parser.add_argument(
'customPropertyName',
type=str,
location='args',
)
parser.add_argument(
'customPropertyNumber',
type=int,
location='args',
)
args = parser.parse_args()
if args['customPropertyName'] and args['customPropertyNumber'] > 1:
for i in range(0, args['customPropertyNumber']):
parser.add_argument(
'value-' + str(i),
type=str,
location='args',
)
parser.add_argument(
'weight-' + str(i),
type=float,
location='args',
)
args = parser.parse_args()
# Map the total population to the max value of the sampling range.
max = db.session.query(func.max(func.upper(
TractDistribution.weight))).scalar()
if args['observations'] > 0:
# Create an array of cumulative weights. This is used to map `random.random_sample` values to custom properties.
if args['customPropertyName'] and args['customPropertyNumber'] > 1:
acc = 0.0
valueSelector = []
for j in range(args['customPropertyNumber']):
if args['weight-' + str(j)] > 0:
acc += args['weight-' + str(j)]
valueSelector.append(acc)
for j in range(args['customPropertyNumber']):
if args['weight-' + str(j)] > 0:
valueSelector[j] /= acc
# Create `feature_array`. Its row 0 is a 'header' row.
feature_array = []
row = []
if args['geoid']:
row.append('geoid')
if args['usps']:
row.append('usps')
if args['pop10']:
row.append('pop10')
if args['customPropertyName'] and args['customPropertyNumber'] >= 2:
row.append(args['customPropertyName'])
if args['format'] == 'geojson':
row.append('geometry')
else:
row.append('lon')
row.append('lat')
feature_array.append(row)
for i in range(args["observations"]):
# Sample one tract for each requested feature and generate a random point within the tract. This uses the
# brute force function in `postgis_functions/random_point.sql` to generate a point within the bounding box,
# validate that it's within the multipolygon expressing the boundaries of the tract, and generate another
# if it's not.
#
# Because this can still on occasion strike out, this process is wrapped in the python equivalent of
# `repeat...until`. An Exception here will rollback the PostgreSQL transaction, resample a tract, and
# start over.
while True:
try:
sample = random.randint(0, max)
tract = db.session.query(TractDistribution).filter(
TractDistribution.weight.contains(sample)).one()
feature_geom = db.session.execute(
func.RandomPoint(tract.wkb_geometry)).scalar()
break
except Exception:
print(Exception)
db.session.rollback()
row = []
if args['geoid']:
row.append(tract.geoid)
if args['usps']:
row.append(tract.usps)
if args['pop10']:
row.append(tract.pop10)
if args['customPropertyName'] and args['customPropertyNumber'] >= 2:
random_value = random.random_sample()
for j in range(args['customPropertyNumber']):
if args['value-' +
str(j)] and args['weight-' + str(j)] > 0:
if random_value < valueSelector[j]:
row.append(args['value-' + str(j)])
break
if args['format'] == 'geojson':
row.append(
json.loads(
db.session.scalar(func.ST_AsGeoJSON(feature_geom, 6))))
else:
row.append(db.session.scalar(func.ST_X(feature_geom)))
row.append(db.session.scalar(func.ST_Y(feature_geom)))
feature_array.append(row)
if args['format'] == 'geojson':
output = array_to_geojson(feature_array)
elif args['format'] == 'csv':
output = array_to_delimited_values(feature_array, ',')
else:
output = array_to_delimited_values(feature_array, '\t')
return output
def centerline_query(session, detection):
"""Finds the centerline orientation that most closely agrees with
detection-intersected roadbeds."""
# pylint: disable-msg=E1101
car_polygon = Detection.geom
car_polygon102718 = func.ST_Transform(car_polygon, 102718)
car_filter = func.ST_Intersects(
Roadbed.geom,
car_polygon102718
)
query = session.query(
Roadbed.gid) \
.filter(Detection.id == detection.id) \
.filter(car_filter)
road_gids = query.all()
if len(road_gids) == 0:
return
lat, lon, alt = session.query(
func.ST_Y(Detection.lla),
func.ST_X(Detection.lla),
func.ST_Z(Detection.lla)) \
.filter(Detection.id == detection.id) \
.one()
lla = numpy.array([[lat, lon, alt]])
enu = pygeo.LLAToENU(lla).reshape((3, 3))
roadbeds4326 = func.ST_Transform(Roadbed.geom, 4326)
centerlines4326 = PlanetOsmLine.way
centerline_filter = func.ST_Intersects(roadbeds4326, centerlines4326)
centerline_frac = func.ST_Line_Locate_Point(
centerlines4326, Detection.lla)
centerline_start_frac = func.least(1, centerline_frac + 0.01)
centerline_end_frac = func.greatest(0, centerline_frac - 0.01)
centerline_start = func.ST_Line_Interpolate_Point(centerlines4326,
centerline_start_frac)
centerline_end = func.ST_Line_Interpolate_Point(centerlines4326,
centerline_end_frac)
segments = session.query(
func.ST_Y(centerline_start).label('lats'),
func.ST_X(centerline_start).label('lons'),
func.ST_Y(centerline_end).label('late'),
func.ST_X(centerline_end).label('lone'),
PlanetOsmLine.oneway) \
.filter(Detection.id == detection.id) \
.filter(centerline_filter) \
.filter(Roadbed.gid.in_(road_gids)) \
.filter(PlanetOsmLine.osm_id >= 0) \
.filter(PlanetOsmLine.railway.__eq__(None))
# pylint: enable-msg=E1101
for segment in segments:
segment_start = pygeo.LLAToECEF(numpy.array(
[[segment.lats, segment.lons, alt]],
dtype=numpy.float64
))
segment_end = pygeo.LLAToECEF(numpy.array(
[[segment.late, segment.lone, alt]],
dtype=numpy.float64
))
segment_dir = (segment_end - segment_start)
segment_dir /= numpy.linalg.norm(segment_dir)
segment_rot = enu.T.dot(segment_dir.T)
segment_angle = math.atan2(segment_rot[1], segment_rot[0])
yield segment_angle, segment.oneway
def data_route():
west = request.args.get('w')
east = request.args.get('e')
north = request.args.get('n')
south = request.args.get('s')
month = request.args.get('m')
nb = int(request.args.get('nb', config['max_nb_cities_at_once']))
if nb > config['max_nb_cities_at_once']:
nb = config['max_nb_cities_at_once']
if west is None or east is None or south is None or north is None:
return 'TODO 404'
rectangle = 'POLYGON(({0} {1}, {0} {2}, {3} {2}, {3} {1}, {0} {1}))' \
.format(west, south, north, east)
with session_scope() as session:
# choose N cities
sq = session.query(City) \
.filter(func.ST_Covers(
cast(rectangle, Geometry()),
func.ST_SetSRID(cast(City.location, Geometry()), 0))) \
.order_by(City.priority_index) \
.limit(nb).subquery('city')
# get their data
query = session.query(
sq.c.id,
sq.c.name,
func.ST_Y(cast(sq.c.location, Geometry())),
func.ST_X(cast(sq.c.location, Geometry())),
sq.c.population, MonthlyStat.month,
MonthlyStat.value, Stat.code, sq.c.country, sq.c.source) \
.join(MonthlyStat) \
.join(Stat) \
.filter(Stat.code.in_(['avgHigh', 'avgLow', 'precipitation',
'precipitationDays', 'monthlySunHours',
'rain', 'rainDays']))
if month is not None:
query = query.filter(MonthlyStat.month == month)
def default():
return {'month_stats': defaultdict(dict)}
cities = defaultdict(default)
# print(query)
# format what is returned from the query
for row in query:
id = row[0]
cities[id]['name'] = row[1]
cities[id]['coords'] = (row[2], row[3])
cities[id]['pop'] = row[4]
cities[id]['month_stats'][row[7]][row[5]] = row[6]
cities[id]['country'] = row[8]
cities[id]['source'] = row[9]
# changing rain to precipitation
# TODO something similar with snow?
for _,c in cities.items():
for old, new in [('rain', 'precipitation'),
('rainDays', 'precipitationDays')]:
if old in c['month_stats'] and new not in c['month_stats']:
c['month_stats'][new] = c['month_stats'].pop(old)
# from pprint import pprint
# pprint(cities)
return json.dumps(cities)
def get_osm_url(self, zoom=18):
lat, lon = session.query(func.ST_Y(self.location), func.ST_X(self.location)).one()
params = (zoom, lat, lon)
return 'https://www.openstreetmap.org/#map={}/{}/{}'.format(*params)
def add_priority_index(session, fast_mode=False):
""" decides the order in which the cities should be selected """
cities = session.query(City,
func.ST_Y(cast(City.location, Geometry())),
func.ST_X(cast(City.location, Geometry()))) \
.join(MonthlyStat) \
.order_by(City.region_rank, City.country_rank,
desc(func.count(MonthlyStat.id))) \
.group_by(City.id) \
.yield_per(1000).all()
if fast_mode:
logger.info('doing the fast version of priority index')
for i, city in enumerate(cities):
city[0].priority_index = i
session.commit()
return
def distance_fn(tuple1, tuple2):
_, lat1, lon1 = tuple1
_, lat2, lon2 = tuple2
return lat_lon_fast_distance(lat1, lon1, lat2, lon2)
indices = [0]
indices_left = list(range(1, len(cities)))
# pre-calculate the distances between all the cities
logger.info('pre-calculating the distances between all cities')
lats = numpy.array([c[1] for c in cities])
lons = numpy.array([c[2] for c in cities])
distances = lat_lon_fast_distance(lats.reshape(-1, 1), lons.reshape(-1, 1),
lats.reshape(1, -1), lons.reshape(1, -1))
class CityComp(object):
idx = None
max_dist = None
max_dist_idx = None
def __init__(self, max_dist, max_dist_idx):
self.max_dist = max_dist
self.max_dist_idx = max_dist_idx
def __lt__(self, other):
return self.max_dist < other.max_dist
# each city is compared to all the previous ones (maximum)
timer = Timer(len(indices_left))
# percent of closest cities to choose from
perc_closest_cities = 0.1
# same but max
max_closest_cities = 200
while len(indices_left) > 0:
# let's find the next city amongst the next candidates
# this will be our (heap) list of good candidates, i.e. the ones
# farthest from all the others
good_candidates = []
nb_keep = min(perc_closest_cities * len(indices_left),
max_closest_cities)
nb_keep = max(1, nb_keep) # at least 1!
logger.debug('will keep the farthest %i', nb_keep)
# max_dist = 0.
# max_dist_idx = 0
logger.debug('---------looking for the next one----------')
for no_candidate, i_left in enumerate(indices_left):
# logger.debug('candidate %i, idx %i', no_candidate, i_left)
# find how close is the nearest neighbor for this city
# we are looking for the city with the fartest nearest neighbor
dist_nearest_neighbor = 1e9
# get the distance of our candidate to the closest (already chosen)
# city
too_close = False
for i_chosen in indices:
cur_dist = distances[i_chosen, i_left]
# if we already have enough candidates, and if the current is
# worse than all others, let's skip it
if len(good_candidates) >= nb_keep \
and cur_dist <= good_candidates[0].max_dist:
too_close = True
# logger.debug('too close @%f', cur_dist)
break
dist_nearest_neighbor = min(dist_nearest_neighbor, cur_dist)
# we don't compare the distance of this candidate with all cities
# if it's closer to (already chosen) city than our best candidate
# so far
if too_close:
continue
# dist_nearest_neighbor = numpy.min(distances[indices][:,i_left])
# logger.debug('candidate %i has a city at %f', no_candidate,
# dist_nearest_neighbor)
# if dist_nearest_neighbor > best_candidate.max_dist:
# logger.debug('(new max)')
new_candidate = CityComp(dist_nearest_neighbor, no_candidate)
# logger.debug('trying to add new candidate with dist %f',
# new_candidate.max_dist)
# if we don't have enough anyway
if len(good_candidates) < nb_keep:
heapq.heappush(good_candidates, new_candidate)
else:
# if we have enough, just keep the n best
rejected_cand = heapq.heappushpop(good_candidates,
new_candidate)
# logger.debug('removed candidate %i with dist %f',
# rejected_cand.max_dist_idx,
# rejected_cand.max_dist)
# take the smallest index in our good candidates. this corresponds to
# the best (according to our first ORDER BY) amongst the "far enough"
# candidates
best_candidate = min(good_candidates, key=lambda x: x.max_dist_idx)
logger.debug(
'keeping %s with pop %i',
cities[indices_left[best_candidate.max_dist_idx]][0].name,
cities[indices_left[best_candidate.max_dist_idx]][0].population,
)
# input('press to continue')
indices.append(indices_left.pop(best_candidate.max_dist_idx))
logger.debug('done, best candidate was %i with distance %f',
best_candidate.max_dist_idx, best_candidate.max_dist)
logger.debug('done, chosen: %i, remaining: %i', len(indices),
len(indices_left))
timer.update()
assert len(indices) == len(cities)
for priority_index, i in enumerate(indices):
cities[i][0].priority_index = priority_index
session.commit()
def get_page_data(id, model, area_type):
"""
Returns a Response object for the given user input
"""
# parse args from http request
classifier = request.args.get("classifier")
date = request.args.get("date")
date = datetime.strptime(date, "%m/%d/%Y %H:%M %p")
hour = int(date.strftime("%H"))
hour = (
db.session.query(CrimeHourClass.classification)
.filter(between(hour, CrimeHourClass.lower_bound, CrimeHourClass.upper_bound))
.scalar()
)
month = int(date.strftime("%m"))
day_of_week = int(date.strftime("%w"))
area_type = request.args.get("area-type")
uri = request.args.get("district" if area_type == "d" else "neighborhood")
area = uri.split("/")
area = int(area[len(area) - 1])
# get all general object lists
districts, neighborhoods, classifiers = get_list_objs()
# get the centroid of the geometry for the selected area
g = db.session.query(func.ST_Centroid(model.geom)).filter_by(id=id).scalar()
if g is not None:
# retrieve model from database
crime_model = (
db.session.query(CrimeModel)
.filter_by(area_type=area_type.upper(), classifier=classifier)
.first()
)
# bytes -> python object
crime_model = pickle.loads(crime_model.model)
# do a prediction for this area and datetime using the model
prediction = crime_model.predict([[area, month, day_of_week, hour]])[0]
# parse geojson for map
geojson = (
db.session.query(func.ST_AsGeoJSON(model.geom).label("json"))
.filter_by(id=id)
.scalar()
)
geojson = json.loads(geojson)
# find incidents for map
points = get_incidents(
db.session.query(model.geom).filter_by(id=id).scalar(), prediction
)
# get centroid points for map
g = db.session.query(func.ST_X(g).label("x"), func.ST_Y(g).label("y")).first()
x, y = g.x, g.y
# render response
return render_template(
"index.html",
selected=uri,
area_type=area_type,
prediction=prediction.lower().capitalize(),
calendar=date.strftime("%m/%d/%Y %H:%M %p"),
x=x,
y=y,
geojson=geojson,
districts=districts,
neighborhoods=neighborhoods,
classifiers=classifiers,
selected_classifier=classifier,
points=points,
)
else:
# general 404 response
return make_response(f"<h2>ID {id} not found.", 404)
def geom_to_latlng(self, *args):
geom = args[1].location
query = self.session.query(func.ST_X(geom), func.ST_Y(geom))
return query.first()
def convert_vehicle(nyc3dcars_session, labeler_vehicle):
"""Converts the vehicle from Labeler format to NYC3DCars format."""
photo, lat, lon, alt = nyc3dcars_session.query(
Photo,
func.ST_Y(Photo.lla),
func.ST_X(Photo.lla),
func.ST_Z(Photo.lla)) \
.filter_by(id=labeler_vehicle.revision.annotation.pid) \
.options(joinedload('dataset')) \
.one()
left = labeler_vehicle.x1
right = labeler_vehicle.x2
top = labeler_vehicle.y1
bottom = labeler_vehicle.y2
camera_lla = numpy.array([[lat], [lon], [alt]])
camera_enu = pygeo.LLAToENU(camera_lla.T).reshape((3, 3))
dataset_correction = numpy.array([
[photo.dataset.t1],
[photo.dataset.t2],
[photo.dataset.t3],
])
camera_rotation = numpy.array([
[photo.r11, photo.r12, photo.r13],
[photo.r21, photo.r22, photo.r23],
[photo.r31, photo.r32, photo.r33],
])
camera_up = camera_enu.T.dot(
camera_rotation.T.dot(numpy.array([[0], [1], [0]])))
offset = numpy.array([[-labeler_vehicle.x], [-labeler_vehicle.z], [0]])
camera_offset = camera_up * \
labeler_vehicle.revision.cameraheight / camera_up[2]
total_offset = offset - camera_offset
ecef_camera = pygeo.LLAToECEF(camera_lla.T).T
ecef_camera += dataset_correction
ecef_total_offset = camera_enu.dot(total_offset)
vehicle_ecef = ecef_camera + ecef_total_offset
vehicle_type = labeler_vehicle.type
model = nyc3dcars_session.query(VehicleType) \
.filter_by(label=vehicle_type) \
.one()
vehicle_lla = pygeo.ECEFToLLA(vehicle_ecef.T).T
theta = math.radians(-labeler_vehicle.theta)
mlength = model.length
mwidth = model.width
car_a = -math.sin(theta) * 0.3048 * \
mlength / 2 + math.cos(theta) * 0.3048 * mwidth / 2
car_b = math.cos(theta) * 0.3048 * mlength / \
2 + math.sin(theta) * 0.3048 * mwidth / 2
car_c = math.sin(theta) * 0.3048 * mlength / \
2 + math.cos(theta) * 0.3048 * mwidth / 2
car_d = -math.cos(theta) * 0.3048 * \
mlength / 2 + math.sin(theta) * 0.3048 * mwidth / 2
car_corner_offset1 = camera_enu.dot(numpy.array([[car_a], [car_b], [0]]))
car_corner_offset2 = camera_enu.dot(numpy.array([[car_c], [car_d], [0]]))
car_corner1 = pygeo.ECEFToLLA(
(vehicle_ecef + car_corner_offset1).T).T.flatten()
car_corner2 = pygeo.ECEFToLLA(
(vehicle_ecef - car_corner_offset1).T).T.flatten()
car_corner3 = pygeo.ECEFToLLA(
(vehicle_ecef + car_corner_offset2).T).T.flatten()
car_corner4 = pygeo.ECEFToLLA(
(vehicle_ecef - car_corner_offset2).T).T.flatten()
pg_corner1 = func.ST_SetSRID(
func.ST_MakePoint(car_corner1[1], car_corner1[0], car_corner1[2]),
4326)
pg_corner2 = func.ST_SetSRID(
func.ST_MakePoint(car_corner2[1], car_corner2[0], car_corner2[2]),
4326)
pg_corner3 = func.ST_SetSRID(
func.ST_MakePoint(car_corner3[1], car_corner3[0], car_corner3[2]),
4326)
pg_corner4 = func.ST_SetSRID(
func.ST_MakePoint(car_corner4[1], car_corner4[0], car_corner4[2]),
4326)
collection = func.ST_Collect(pg_corner1, pg_corner2)
collection = func.ST_Collect(collection, pg_corner3)
collection = func.ST_Collect(collection, pg_corner4)
car_polygon = func.ST_ConvexHull(collection)
camera_ecef = pygeo.LLAToECEF(camera_lla.T).T
vehicle_ecef = pygeo.LLAToECEF(vehicle_lla.T).T
diff = camera_ecef - vehicle_ecef
normalized = diff / numpy.linalg.norm(diff)
vehicle_enu = pygeo.LLAToENU(vehicle_lla.T).reshape((3, 3))
rotated = vehicle_enu.T.dot(normalized)
theta = func.acos(rotated[2][0])
view_phi = func.atan2(rotated[1][0], rotated[0][0])
vehicle_phi = math.radians(-labeler_vehicle.theta)
phi = vehicle_phi - view_phi
out = nyc3dcars_session.query(
theta.label('theta'),
phi.label('phi')) \
.one()
out.phi = ((out.phi + math.pi) % (2 * math.pi)) - math.pi
out.theta = ((out.theta + math.pi) % (2 * math.pi)) - math.pi
view_phi = out.phi
view_theta = out.theta
left = labeler_vehicle.x1
right = labeler_vehicle.x2
top = labeler_vehicle.y1
bottom = labeler_vehicle.y2
for bbox_session in labeler_vehicle.bbox_sessions:
if not bbox_session.user.trust:
continue
print((bbox_session.user.username,
labeler_vehicle.revision.annotation.pid))
left = bbox_session.x1
right = bbox_session.x2
top = bbox_session.y1
bottom = bbox_session.y2
break
occlusions = [
occlusion.category for occlusion in labeler_vehicle.occlusionrankings
if occlusion.occlusion_session.user.trust and occlusion.category != 5
]
if len(occlusions) == 0:
return
pg_lla = func.ST_SetSRID(
func.ST_MakePoint(vehicle_lla[1][0], vehicle_lla[0][0],
vehicle_lla[2][0]), 4326)
nyc3dcars_vehicle = Vehicle(
id=labeler_vehicle.id,
pid=photo.id,
x=labeler_vehicle.x,
z=labeler_vehicle.z,
theta=labeler_vehicle.theta,
x1=left,
x2=right,
y1=top,
y2=bottom,
type_id=model.id,
occlusion=min(occlusions),
geom=car_polygon,
lla=pg_lla,
view_theta=view_theta,
view_phi=view_phi,
cropped=labeler_vehicle.cropped,
)
nyc3dcars_session.add(nyc3dcars_vehicle)
def on_get(self, req, resp):
resp.content_type = falcon.MEDIA_JSON
session = Session()
matchedSymptoms = json.loads(req.get_header('symptoms'))
location = 'POINT('+req.get_header("latitude")+' '+req.get_header("longitude")+')'
clinicsForSympts = []
for symptId in matchedSymptoms:
clinicsForSympts.append(
set(
session.query(Clinic.id)
.join(Clinic.doctors)
.join(Doctor.specialities)
.join(Speciality.symptoms)
.filter(Symptom.id == symptId)
.distinct(Clinic.id)
.all()
)
)
matchedClinics = set(clinicsForSympts[0]).intersection(*clinicsForSympts)
closestClinic = session.query(Clinic.id) \
.filter(Clinic.id.in_(matchedClinics)) \
.order_by(asc(func.ST_Distance(Clinic.location, func.ST_GeographyFromText(location)))) \
.first()
if closestClinic is None:
resp.status = falcon.HTTP_404
resp.body = json.dumps({})
return
clinicOutput = session.query(Clinic).filter(Clinic.id == closestClinic.id).first()
matchedSpecs = session.query(Speciality.id) \
.join(Speciality.symptoms) \
.filter(Symptom.id.in_(matchedSymptoms)) \
.all()
doctorsOutput = session.query(Doctor).join(Doctor.clinics).join(Doctor.specialities) \
.filter(Clinic.id == closestClinic.id).filter(Speciality.id.in_(matchedSpecs)).distinct(Doctor.id).all()
specsOutput = session.query(Speciality.name).join(Clinic.doctors).join(Doctor.specialities)\
.filter(Clinic.id == closestClinic.id).filter(Speciality.id.in_(matchedSpecs))
losationStringX = session.query(func.ST_X(clinicOutput.location).label('x')).first()
losationStringY = session.query(func.ST_Y(clinicOutput.location).label('y')).first()
respp = {
'name': clinicOutput.name,
'location': str(losationStringX.x) + ' ' + str(losationStringY.y),
'opening': str(clinicOutput.opening),
'closure': str(clinicOutput.closure),
'doctors': [{
'fname': doctor.first_name,
'sname': doctor.second_name,
'pname': doctor.fathers_name,
'specialities': [
spec.name for spec in specsOutput.filter(Doctor.id == doctor.id).all()
],
'cabinet': doctor.cabinet,
'phone': doctor.phone
} for doctor in doctorsOutput
]
}
resp.body = json.dumps(respp)
resp.status = falcon.HTTP_200
