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))