def getDist(coordinate1, coordinate2): """ Get the distance between the newest incoming point and the most recent previously recorded point, distance is reported as meters by default by ST_DistanceSphere. Parameters ---------- coordinate:1 String WKT representation of the most recently recorded GPS point coordinates. coordinate2: String WKT representation of the incoming GPS point coordinates. Returns ------- dist : Float Distance in meters between the newest and most recent recorded points. """ session = Session() # Geoalchemy ORM expression res = session.query( sqlfunc.ST_DistanceSphere(sqlfunc.ST_GeomFromText(coordinate1), sqlfunc.ST_GeomFromText(coordinate2))) # coordinate1 = coordinate1 # res = db.session.query(sqlfunc.ST_DistanceSphere(coordinate1,coordinate2)) dist = None for i in res: # Make sure the data comes through as a float, not a ORM object dist = float(i[0]) # Round off number, don't need high precision dist = round(dist, 1) session.close() return dist
def show_user_feed(): b_levels = boulder_levels s_levels = sport_levels is_following = [follow.user_being_followed_id for follow in Follows.query.filter( Follows.user_following_id == current_user.id).all()] is_project = [project.route_id for project in Project.query.filter(Project.user_id == current_user.id).all()] is_send = [send.route_id for send in Send.query.filter(Send.user_id == current_user.id).all()] followed_users = User.query.filter(User.id.in_(is_following)).limit(5).all() recent_projects = Project.query.filter(or_(Project.user_id.in_(is_following), Project.user_id == current_user.id)).order_by(Project.projected_on.desc()).limit(20).all() recent_sends = Send.query.filter(or_(Send.user_id.in_(is_following), Send.user_id == current_user.id)).order_by(Send.sent_on.desc()).limit(20).all() geo = func.ST_GeomFromText('POINT({} {})'.format(current_user.lon, current_user.lat)) nearby_users = User.query.filter(and_(func.ST_DistanceSphere(User.geo, geo) < (50*1609.344), (User.id != current_user.id), (User.id.notin_(is_following)))).order_by(func.ST_DistanceSphere(User.geo, geo)).limit(5).all() nearby_routes = Route.get_routes_within_radius_count_for_feed(current_user.lat, current_user.lon, 500, 5) feed = generate_feed_from_users_and_routes(nearby_users, nearby_routes) is_p = [project.id for project in Project.query.filter(Project.user_id == current_user.id).all()] is_liked = [like.project_id for like in Likes.query.filter( Likes.user_id == current_user.id).all()] kudos = [kudo.send_id for kudo in Kudos.query.filter( Kudos.user_id == current_user.id).all()] return render_template( 'user_feed.html', boulder_levels=b_levels, sport_levels=s_levels, feed=feed, followed_users=followed_users, is_following=is_following, is_project=is_project, is_send=is_send, recent_projects=recent_projects, recent_sends=recent_sends, is_liked=is_liked, kudos=kudos)
def getGeom(): if request.method == 'GET': left = request.args.get('left') top = request.args.get('top') right = request.args.get('right') bottom = request.args.get('bottom') layerName = request.args.get('layerName') or '' if request.method == 'POST': left = request.form.get('left') top = request.form.get('top') right = request.form.get('right') bottom = request.form.get('bottom') layerName = request.form.get('layerName') or '' tablename = 'g_cloudlayer_meta_' + layerName cls = get_table_by_name(tablename) tBoundsText = 'POLYGON( ( %s %s ,%s %s ,%s %s ,%s %s ,%s %s ) )' % ( left, top, right, top, right, bottom, left, bottom, left, top) if cls is None: return 'cant find table:%s [%s]' % (tablename, tBoundsText) tmptext = func.ST_GeomFromText(tBoundsText, 0) geodata = db.session.query(func.ST_AsGeoJSON(cls.geomdata)).filter( cls.geomdata.ST_Intersects(tmptext)).all() #.limit(2) return return_feature_collection(geodata)
def get_places_near_point(self, position, distance): query = self.session.query(TestPlace) query = query.filter( func.ST_DistanceSphere( func.ST_GeomFromText('POINT({} {} 4326)'.format( position[0], position[1])), cast(TestPlace.coord, Geometry), ) <= distance) query = query.order_by( func.ST_DistanceSphere( func.ST_GeomFromText('POINT({} {} 4326)'.format( position[0], position[1])), cast(TestPlace.coord, Geometry), )) places = query.limit(100) return [place for place in places]
def get_routes_within_radius_count_for_feed(lat, lon, radius, count): """Return all routes within a given radius (in meters) of this point.""" geo = func.ST_GeomFromText('POINT({} {})'.format(lon, lat)) return Route.query.filter( func.ST_DistanceSphere(Route.geo, geo) < radius * 1609.344).order_by(func.ST_DistanceSphere(Route.geo, geo)).limit(count).all()
async def create_fake_observation(): lat = uniform(BOTTOM, TOP) lng = uniform(LEFT, RIGHT) which_school = choice(range(0, 4)) if which_school == 0: s = 'Utah State' elif which_school == 1: s = 'Virginia Tech' elif which_school == 2: s = 'Rutgers' else: s = 'Virginia' school_coroutine = await session.query(School) filtered_schools = school_coroutine.filter(School.school == s) school = filtered_schools.all()[0] if not quiet: print("inserting %s seen at (lat: %02f, long: %02f)" % (s, lat, lng)) observation_geom = func.ST_GeomFromText('POINT({} {})'.format( lng, lat)) return Observation(school_id=school.id, observed_lat=lat, observed_long=lng, observation_geom=observation_geom)
def query_builder(search_parameter): if search_parameter.service_category == 'AutoService': classTask = BusinessOwnerTask ownerType = AutoServiceBusinessOwner else: classTask = BusinessOwnerTaskCarWash ownerType = CarWashBusinessOwner query = db.session.query(classTask, ownerType, ServicesDefinition) \ .join(ServicesDefinition, classTask.service_definition_id == ServicesDefinition.id) \ .join(ownerType, ownerType.id == classTask.business_owner_id) \ .filter(ServicesDefinition.service_grade == search_parameter.service_grade) \ .filter( ServicesDefinition.service_category == search_parameter.service_category) # TODO search_parameter.service_category # TODO add activation to search # search_parameter.service_grade # .filter(cast(BusinessOwners.flags['activation'], String) == cast('true', String)) # return query if len(search_parameter.name) > 0: query = query.filter(User.name == search_parameter.name) if len(search_parameter.service_types) > 0: query = query.filter( ServicesDefinition.service_type_id.in_( search_parameter.service_types)) if len(search_parameter.region) > 0: polygon = CarWashBusinessOwner.polygon_maker( search_parameter.region) query = query.filter( func.ST_Contains( func.ST_GeomFromText(polygon, Keys.SRID_VALUE), BusinessOwner.geom)) return query
def get_census_geoid(self, lat, lon, year): geoid = {} if (year == "All"): for year_value in s.Socio_Econ_Data_Years.values(): census_obj_name = "CensusBlockGrps" + year_value census_obj = globals()[census_obj_name] session = Session() # given this lat lon, find the census tract that contains it. query = session.query(census_obj.geoid). \ filter(func.ST_Contains(census_obj.geom, func.ST_GeomFromText("POINT(" + str(lon) + " " + str(lat) + ")", 4269))) result = session.execute(query) for query_return_values in result: tmp_geoid = query_return_values[0] if (len(tmp_geoid) > 14): geoid[year_value] = tmp_geoid[-14:] else: geoid[year_value] = tmp_geoid session.close() else: census_obj_name = "CensusBlockGrps" + year census_obj = globals()[census_obj_name] session = Session() # given this lat lon, find the census tract that contains it. query = session.query(census_obj.geoid). \ filter(func.ST_Contains(census_obj.geom, func.ST_GeomFromText("POINT(" + str(lon) + " " + str(lat) + ")", 4269))) result = session.execute(query) for query_return_values in result: tmp_geoid = query_return_values[0] if (len(tmp_geoid) > 14): geoid[year] = tmp_geoid[-14:] else: geoid[year] = tmp_geoid session.close() return geoid
def within_distance_func(cls, position, distance): """ Creates the geoalchemy function that determines if a point is in range < distance from the position item :param position: the position to check distance with :param distance: the maximum distance in meters :return: function to apply to query """ point = func.ST_GeomFromText('POINT({0} {1})'.format(*position), srid=Position.SRS_WGS_84) return func.ST_DWithin(cast(Position.location, Geography(srid=Position.SRS_WGS_84)), point, distance)
def geodesic_calc_with_postgis(): # TODO: Remove these cludges after pyproj upgarade from sqlalchemy import func from ...models import DBSession fun = dict(length=func.ST_Length, area=func.ST_Area)[prop] query = fun(func.geography(func.ST_GeomFromText(geom.wkt, srid))) return DBSession.query(query).scalar()
def find_in_zone(lat, lon): result = Region.query.filter( Region.geog.ST_Covers( func.ST_GeomFromText(func.Concat('POINT(', lon, ' ', lat, ')'), 4326))).first() if result == None: return None else: return result.zone_id
def find_ooaza(db: Session, coordinate: Coordinate): ooaza = (db.query( OoazaPolygon.prefecture_name, OoazaPolygon.city_name, OoazaPolygon.ooaza, OoazaPolygon.polygon, ).filter( func.ST_Contains(OoazaPolygon.polygon, func.ST_GeomFromText(coordinate.to_wkt()))).first()) return ooaza
def create_well_bgt_geometry_table(): """Store well information with bgt information. creates records for each bgt item close to a well_id. so each well should have around ~8 items nearby. """ conn = engine.connect() db_model = models.WellBGT bgt_items = [] bgt_bak_items = [] for key, bgts in WELL_BGT_MAP.items(): point = WELL_POINT_MAP[key] for bgt_id, geom in bgts: bgt_geo = geom new = { 'well_id': key, 'geometrie': point, } if geom.startswith('MULTIPOINT'): new['bgt_bak'] = bgt_geo bgt_bak_items.append(new) else: new['bgt'] = bgt_geo bgt_items.append(new) if not bgt_items: raise ValueError("nothing matched..") insert_stmt = (db_model.__table__.insert().values( bgt=func.ST_GeomFromText(bindparam('bgt'), 28992))) conn.execute(insert_stmt, bgt_items) if not bgt_bak_items: raise ValueError("nothing matched..") insert_stmt = (db_model.__table__.insert().values(bgt_bak=func.ST_Dump( func.ST_GeomFromText(bindparam('bgt_bak'), 28992)).geom)) conn.execute(insert_stmt, bgt_bak_items)
def fesomMeshQuery(dbcon, fesominfo, geoWKT=None): """queries the geoslurp database for a valid vertices of a FESOM grid""" #retrieve/reflect the table tbl=dbcon.getTable(fesominfo["mesh"]["vertTable"],'fesom') qry=select([tbl.c.topo, tbl.c.nodeid,literal_column('geom::geometry').label('geom')]) if geoWKT: qry=qry.where(func.ST_within(literal_column('geom::geometry'),func.ST_GeomFromText(geoWKT,4326))) return dbcon.dbeng.execute(qry)
def get_stadium_groups_by_points(points: List[PointDC]): return db.session.query(StadiumGroup) \ .filter(func.ST_Contains(func.ST_MakePolygon(func.ST_GeomFromText( f"LINESTRING(" f"{points[0].lat} {points[0].long}, " f"{points[1].lat} {points[1].long}, " f"{points[2].lat} {points[2].long}, " f"{points[3].lat} {points[3].long}, " f"{points[0].lat} {points[0].long} " f")" )), func.ST_MakePoint(StadiumGroup.lat, StadiumGroup.long))).all()
def _envelope(bounding_box, geom=True): envelope = """POLYGON(( %f %f, %f %f, %f %f, %f %f, %f %f ))""" % ( bounding_box.west, bounding_box.south, bounding_box.west, bounding_box.north, bounding_box.east, bounding_box.north, bounding_box.east, bounding_box.south, bounding_box.west, bounding_box.south) if geom: return func.ST_GeomFromText(envelope, 4326) else: return envelope return
def closest2Fesom(dbcon, fesominfo, geoWKT=None, samplePoints=[]): """returns vertices of a FESOM grid that have the smallest distance to sample points""" #retrieve/reflect the table tbl=dbcon.getTable(fesominfo["mesh"]["vertTable"],'fesom') qry=select([tbl.c.topo, tbl.c.nodeid,literal_column('geom::geometry').label('geom')]) if geoWKT: qry=qry.where(func.ST_within(literal_column('geom::geometry'), func.ST_GeomFromText(geoWKT,4326))) if len(samplePoints)!=0: pp=[] for p in samplePoints: # print(p) qry1=qry.order_by(func.ST_Distance(literal_column('geom::geometry'), func.ST_GeomFromText(p.wkt,4326))) qry1=qry1.limit(1) pp.append(dbcon.dbeng.execute(qry1).first()._row) return pp
def process_formdata(self, valuelist): super(GeoJSONField, self).process_formdata(valuelist) if str(self.data) is '': self.data = None if self.data is not None: web_shape = self.session.scalar( func.ST_AsText( func.ST_Transform( func.ST_GeomFromText( shape(self.data).wkt, self.web_srid), self.transform_srid))) self.data = 'SRID=' + str(self.srid) + ';' + str(web_shape)
def get_places_in_rect(self, rect, sort_close_to=None): query = self.session.query(TestPlace) query = query.filter( func.ST_Contains( func.ST_MakeEnvelope(rect[0], rect[1], rect[2], rect[3], 4326), cast(TestPlace.coord, Geometry))) if sort_close_to is not None: query = query.order_by( func.ST_DistanceSphere( func.ST_GeomFromText('POINT({} {} 4326)'.format( sort_close_to[0], sort_close_to[1])), cast(TestPlace.coord, Geometry), )) places = query.limit(100) return [place for place in places]
def append_query_trips(trips, is_rectangle, vertices): if is_rectangle: return trips \ .filter(func.ST_Contains( func.ST_MakeBox2D(func.ST_Point(vertices[0], vertices[1]), func.ST_POINT(vertices[2], vertices[3])), Trip.dropoff)) \ .filter(func.ST_Contains( func.ST_MakeBox2D(func.ST_Point(vertices[0], vertices[1]), func.ST_POINT(vertices[2], vertices[3])), Trip.pickup)); else: # We are building the LINESTRING() query object dynamically because we do not know how many vertices there are polygon = 'LINESTRING(' for x in range(len(vertices)): if x % 2 == 0: if x != 0: polygon += ', ' polygon += vertices[x] + ' ' else: polygon += vertices[x] polygon += ', ' + vertices[0] + ' ' + vertices[1] + ')' return trips.filter(func.ST_Contains(func.ST_MakePolygon(func.ST_GeomFromText(polygon)), Trip.pickup)) \ .filter(func.ST_Contains(func.ST_MakePolygon(func.ST_GeomFromText(polygon)), Trip.dropoff));
def get_all_places(self, city, position=None): query = self.session.query(TestPlace) if city: req_city_id = self.get_city_by_name(city) query = query.filter_by(city_id=req_city_id) if position is not None: query = query.order_by( func.ST_DistanceSphere( func.ST_GeomFromText('POINT({} {} 4326)'.format( position[0], position[1])), cast(TestPlace.coord, Geometry), )) places = query.limit(1000) return [place for place in places]
def fetch_polygons_intersecting(bounding_box: BoundingBox): """Fetch all the polygons intersecting the given rectangular bounding box Reference http://geoalchemy-2.readthedocs.io/en/0.3/orm_tutorial.html#spatial-query """ envelope = """POLYGON(( %f %f, %f %f, %f %f, %f %f, %f %f ))""" % ( bounding_box.west, bounding_box.south, bounding_box.west, bounding_box.north, bounding_box.east, bounding_box.north, bounding_box.east, bounding_box.south, bounding_box.west, bounding_box.south) envelope_geom = func.ST_GeomFromText(envelope, 4326) query = session.query(SidewalkPolygon).filter( func.ST_Intersects(func.ST_Transform(SidewalkPolygon.geom, 4326), envelope_geom)) return query
def radsQuery(dbcon, sattable, polyWKT, tspan=None, cycle=None): """queries the geoslurp database for segments of altimetry tracks within a specified geometry and/or timespan and or cycle""" #retrieve/reflect the table tbl = dbcon.getTable(sattable, 'altim') qry = select([ tbl.c.tstart, tbl.c.tend, tbl.c.uri, literal_column('geom::geometry').label('geom') ]) # qry=select([tbl.c.uri,tbl.c.geom]) if tspan: qry = qry.where(and_(tbl.c.tstart > tspan[0], tbl.c.tend < tspan[1])) if cycle: qry = qry.where(tbl.c.cycle == cycle) #add geospatial constraint # ogrpoly=lonlat2ogr(polygon) qry = qry.where( func.ST_intersects(tbl.c.geom, func.ST_GeomFromText(polyWKT, 4326))) qry = qry.order_by(asc(tbl.c.tstart)) # print(qry) return dbcon.dbeng.execute(qry) # def queryMonthlyRads(dbcon, sattable, polyWKT,tstart,tend): # """Query the database for lists of monthly Argo profiles within a certain polygon and time span""" # out = {} # for entry in radsQuery(dbcon, sattable, polyWKT=polyWKT,tspan=[tstart,tend],cycle=None): # epoch=(entry.tlocation.year,entry.tlocation.month) # #pnt=shpextract(entry) # tmpdict = {"uri": entry.uri} # if not epoch in out: # out[epoch] = [tmpdict] # else: # out[epoch].append(tmpdict) # return out
def search_by_sightings(self, position_query: str) -> Dict[Bird, float]: matches = dict() match = re.match('position:([0-9.]+),([0-9.]+);r=([0-9.]+)', position_query) if match: lat = match.group(1) lon = match.group(2) radius = float(match.group(3)) result = self.session.query(Bird, func.count(Sighting.bird)) \ .join(Sighting.bird) \ .join(Sighting.position) \ .filter(func.ST_DistanceSphere(func.ST_GeomFromText(func.ST_AsText(Position.point), 4326), func.ST_GeomFromText(f'POINT({lon} {lat})', 4326)) < 1000 * radius) \ .group_by(Bird) \ .all() total = sum([count for _, count in result]) for bird, count in result: score = count / total matches[bird] = score return matches
def test_postgis_transformation(self): from sqlalchemy import func v = self.session.query(func.ST_AsText( func.ST_Transform( func.ST_GeomFromText( ('POLYGON((' '743238 2967416,' '743238 2967450,' '743265 2967450,' '743265.625 2967416,' '743238 2967416))'), 2249), 4326))).one()[0] self.assertEqual(v, ( 'POLYGON((' '-71.1776848522251 42.3902896512902,' '-71.1776843766326 42.3903829478009,' '-71.1775844305465 42.3903826677917,' '-71.1775825927231 42.3902893647987,' '-71.1776848522251 42.3902896512902))' ))
def psmslQuery(dbcon, psmsltable, polyWKT, tspan=None): """queries the geoslurp database for tide gauge series""" #retrieve/reflect the table tbl = dbcon.getTable(psmsltable, 'oceanobs') qry = select([tbl]) if tspan: qry = qry.where(and_(tbl.c.tstart > tspan[0], tbl.c.tend < tspan[1])) #add geospatial constraint # ogrpoly=lonlat2ogr(polygon) qry = qry.where( func.ST_within(literal_column('geom::geometry'), func.ST_GeomFromText(polyWKT, 4326))) qry = qry.order_by(asc(tbl.c.tstart)) # print(qry) return dbcon.dbeng.execute(qry)
def test_query(self): conn = self.conn # Define geometries to insert values = [{ "ewkt": "SRID=4326;LINESTRING(0 0, 1 0)" }, { "ewkt": "SRID=4326;LINESTRING(0 0, 0 1)" }] # Define the query to compute distance (without spheroid) distance = func.ST_Length(func.ST_GeomFromText(bindparam("ewkt")), False) i = table.insert() i = i.values(geom=bindparam("ewkt"), distance=distance) # Execute the query with values as parameters conn.execute(i, values) # Check the result q = select([table]) res = conn.execute(q).fetchall() # Check results assert len(res) == 2 r1 = res[0] assert r1[0] == 1 assert r1[1].srid == 4326 assert to_shape(r1[1]).wkt == "LINESTRING (0 0, 1 0)" assert round(r1[2]) == 111195 r2 = res[1] assert r2[0] == 2 assert r2[1].srid == 4326 assert to_shape(r2[1]).wkt == "LINESTRING (0 0, 0 1)" assert round(r2[2]) == 111195
async def insert_fake_observations(session, num_fake_observations=10000, quiet=False): """Insert a fake observation record. Meant to be used with integration testing. """ if not quiet: print("Inserting %s records into the observations data table" % num_fake_observations) for (lat, lng) in locations[:num_fake_observations]: observation_geom = func.ST_GeomFromText('POINT({} {})'.format( lng, lat)) if not quiet: print('POINT({} {})'.format(lng, lat)) _ = await session.add( Observation(school_id=usu_school_id, observed_lat=lat, observed_long=lng, observation_geom=observation_geom))
def testmap(request): maxx, maxy, minx, miny = request.GET.get('bbox').split(',') #- define viewport for query viewport = 'POLYGON(( \ {maxx} {maxy}, \ {maxx} {miny}, \ {minx} {miny}, \ {minx} {maxy}, \ {maxx} {maxy}))'.format( \ maxx=maxx, maxy=maxy, minx=minx, miny=miny) viewport = select([func.ST_GeomFromText(viewport, 4326)]).label("viewport") tracks_in_viewport = DBSession.query(Track).filter(or_( func.ST_Overlaps(viewport, Track.bbox), func.ST_Contains(viewport, Track.bbox) )).all() features = convert_to_geojson_featurecollection(tracks_in_viewport) response = Response(json.dumps(dict(type='FeatureCollection', features=features))) response.content_type = 'application/json' return(response)
def argoQuery(dbcon, geoWKT=None, tspan=None, withinDmeter=None, tsort=None): tbl = dbcon.getTable('argo2', 'oceanobs') #first create a subquery to quickly discard argo profiles subqry = select([tbl]) if tspan: subqry = subqry.where( func.overlaps(tbl.c.tstart, tbl.c.tend, tspan[0], tspan[1])) # Apply initial geospatial constraints if geoWKT: if withinDmeter: #only base initial constraints ont he bounding box subqry = subqry.where( func.ST_DWithin( literal_column('ST_Envelope(geom::geometry)::geography'), func.ST_GeogFromText(geoWKT), withinDmeter)) else: subqry = subqry.where( func.ST_Intersects(literal_column('geom::geometry'), func.ST_GeomFromText(geoWKT, 4326))) #we need to assign an alias to this subquery in order to work with it subqry = subqry.alias("ar") #expand the arrays and points int he subquery qry = select([ subqry.c.wmoid, subqry.c.uri, subqry.c.datacenter, func.unnest(subqry.c.mode).label('mode'), func.unnest(subqry.c.ascend).label('ascend'), func.unnest(subqry.c.tlocation).label('tlocation'), func.unnest(subqry.c.cycle).label('cycle'), func.unnest(subqry.c.iprof).label('iprof'), ST_Dump(literal_column("ar.geom::geometry")).geom.label('geom') ]) #additional spatial constraints finalqry = qry qry = qry.alias("arex") if tspan: finalqry = select([qry]).where( between(qry.c.tlocation, tspan[0], tspan[1])) if geoWKT: if withinDmeter: #only base initial constraints ont he bounding box finalqry = finalqry.where( func.ST_DWithin(qry.c.geom, func.ST_GeogFromText(geoWKT), withinDmeter)) else: finalqry = finalqry.where( func.ST_Within(literal_column("arex.geom"), func.ST_GeomFromText(geoWKT, 4326))) if tsort: finalqry = finalqry.order_by(qry.c.tlocation) return dbcon.dbeng.execute(finalqry)