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