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)