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