def random_point_from_district(d_name: str):
district_cat_id = 163
sql = sqlalchemy.select([
func.ST_SetSRID(func.ST_GeneratePoints(table.model.c.geom, 1), 4326)
]).where(
and_(table.model.c.name == d_name,
table.model.c.category_id == district_cat_id))
result = connection.execute(sql).fetchone()
if result is not None:
return result[0]
else:
return print('None')
def find_distance_to_nearby_prop(geom, cat_id, buffer_size=2000):
sql = sqlalchemy.select([
func.ST_Distance(func.ST_Transform(geom, 3857),
func.ST_Transform(table.model.c.geom, 3857))
]).where(
and_(
func.ST_Distance(func.ST_Transform(geom, 3857),
func.ST_Transform(table.model.c.geom, 3857)) <
buffer_size, table.model.c.category_id == cat_id))
result = connection.execute(sql).fetchall()
return result
def get_by_buffer(input_geom, buffer_size=100000):
sql = table.model.select().where(
func.ST_Intersects(
func.ST_setsrid(func.ST_Buffer(
input_geom,
buffer_size,
), 4326), table.model.c.geom
# func.ST_setsrid(table.model.c.geom,4326)
))
# new_geom = func.ST_GeomFromText(geom)
results = connection.execute(sql).fetchall()
return results
def get_by_buffer(input_geom, cat_id=None, buffer_size=500):
sql = table.model.select().where(
and_(
func.ST_Intersects(
func.ST_setsrid(
func.ST_Buffer(func.ST_GeomFromText(input_geom),
buffer_size, "quad_segs=8"), 4326),
func.ST_setsrid(table.model.c.geom, 4326)),
table.model.c.category_id == cat_id))
# new_geom = func.ST_GeomFromText(geom)
if cat_id is not None:
sql = table.model.select().where(
func.ST_Intersects(
func.ST_setsrid(
func.ST_Buffer(func.ST_GeomFromText(input_geom),
buffer_size, "quad_segs=8"), 4326),
func.ST_setsrid(table.model.c.geom, 4326)))
results = connection.execute(sql).fetchall()
return results
def get_by_disctrict(district):
sql = table.model.select().where(table.model.c.district == district)
results = connection.execute(sql).fetchall()
return results
def get_by_category_id_and_name(input_id, input_name):
sql = table.model.select().where(
and_(self.model.c.category_id == input_id,
self.model.c.name == input_name))
results = connection.execute(sql).fetchall()
return results
def get_by_category_id(input_id):
sql = table.model.select().where(self.model.c.category_id == input_id)
results = connection.execute(sql).fetchall()
return results
def get_distance_from(cat_id):
sql = table.model.select().where(self.model.c.category_id == input_id)
#create ex .( hostpitle )buffer
results = connection.execute(sql).fetchall()
return results