def get_clustered_locations(location_column,
threshold_radius = 1000, filter = None):
'''
SELECT ST_AsText(
ST_Centroid(
(ST_Dump(
ST_Union(
ST_Buffer(
takeoff_location_wkt::geography, 1000
)::geometry
)
)
).geom)
) FROM flights WHERE pilot_id=31;
'''
# Cast the takeoff_location_wkt column to Geography
geography = cast(location_column.RAW, 'geography')
# Add a metric buffer zone around the locations
buffer = cast(func.ST_Buffer(geography, threshold_radius), 'geometry')
# Join the locations into one MultiPolygon
union = func.ST_Union(buffer)
# Split the MultiPolygon into separate polygons
dump = extract_field(func.ST_Dump(union), 'geom')
# Calculate center points of each polygon
locations = func.ST_Centroid(dump)
# Convert the result into WKT
locations = func.ST_AsText(locations)
query = DBSession.query(locations.label('location'))
if filter is not None:
query = query.filter(filter)
return [Location.from_wkt(row.location) for row in query]
def distance(self, location):
loc1 = cast(self.location_wkt.wkt, 'geography')
loc2 = func.ST_GeographyFromText(location.to_wkt())
return DBSession.scalar(func.ST_Distance(loc1, loc2))
