def segmentized_line_with_geographic_points(cls, trip_id):
''' Returns (lat, lon) of geographic coordinate of points every 50m along route.
Mainly intended for use with testing.'''
s = select([
func.ST_DumpPoints(func.ST_Transform(func.ST_Segmentize(Trip.geom_path, 50),
settings.TARGET_DATUM))\
.label('dp')
]).where(Trip.trip_id == trip_id)
inner_select = s.correlate(None).alias()
s = select([
func.ST_Y(inner_select.columns.dp.geom),
func.ST_X(inner_select.columns.dp.geom),
])
return list(engine.execute(s))
def getIntersectingPoints(wktStr):
"""
Takes an EWKT string of a Strava Activity Stream's latlngs and returns a list of float points which reside within
the privacy areas.
@param wktStr: String. EWKT representation of Strava Activity Stream latlngs
@return: List of strings. Points are returned as WGS 1984 coordinate strings in the format lon,lat
"""
# geometricProj = 32610
collectionExtract = 3
# Open session
session = Session()
# Get coordinates from within privacy zones
try:
# Create a labled common table expression to query privacy zones geometries collected into a single multi-polygon
privacy_cte = session.query(
sqlfunc.ST_CollectionExtract(sqlfunc.ST_Collect(
AOI.geom), collectionExtract).label("ctelab")).filter(
AOI.privacy == "Yes").cte()
# points_cte = session.query(sqlfunc.ST_DumpPoints(sqlfunc.st_geomfromewkt(wktStr)))
# Take provided EWKT string and convert to GeoAlchemy geometry
# lineString = sqlfunc.ST_GeomFromEWKT(wktStr)
# application.logger.debug(f"Geoalchemy Geom is: \n{dir(lineString)}")
# Get a list of points from the linestring which fall inside the privacy zone
# ST_DumpPoints provides a point geometry per iterative loop which is converted to a text representation using As_Text
pointQuery = session.query(
sqlfunc.ST_AsText(
sqlfunc.ST_DumpPoints(
sqlfunc.ST_Intersection(sqlfunc.ST_GeomFromEWKT(wktStr),
privacy_cte.c.ctelab)).geom))
# pointQuery = session.query(sqlfunc.ST_AsText(
# sqlfunc.ST_DumpPoints(sqlfunc.ST_Intersection(sqlfunc.ST_GeomFromEWKT(wktStr),
# privacy_cte.c.ctelab)).geom)).filter(privacy_cte.c.ctelab.
# ST_Intersects(sqlfunc.ST_GeomFromEWKT(wktStr)))
coordinateList = []
for i in pointQuery:
# application.logger.debug(f"Point query response is: {i}")
# strip out the WKT parts of the coordinates, only want list of [lon,lat]
coordinateList.append(formatPointResponse(i))
finally:
session.close()
return coordinateList
def dump_points_to_projected_line(cls, line):
'''Wraps SpatialQueries.points_to_projected_line, dumps all points.'''
return func.ST_DumpPoints(cls.points_to_projected_line(line))
def get_near_flights(flight, filter=None):
"""
WITH src AS
(SELECT ST_Buffer(ST_Simplify(locations, 0.005), 0.015) AS src_loc_buf,
start_time AS src_start,
end_time AS src_end
FROM flight_paths
WHERE flight_id = 8503)
SELECT (dst_points).geom AS dst_point,
dst_times[(dst_points).path[1]] AS dst_time,
dst_points_fid AS dst_flight_id
FROM
(SELECT ST_dumppoints(locations) as dst_points,
timestamps AS dst_times,
src_loc_buf,
flight_id AS dst_points_fid,
src_start,
src_end
FROM flight_paths, src
WHERE flight_id != 8503 AND
end_time >= src_start AND
start_time <= src_end AND
locations && src_loc_buf AND
_ST_Intersects(ST_Simplify(locations, 0.005), src_loc_buf)) AS foo
WHERE _ST_Contains(src_loc_buf, (dst_points).geom);
"""
cte = db.session.query(FlightPathChunks.locations.ST_Simplify(0.005).ST_Buffer(0.015).label('src_loc_buf'),
FlightPathChunks.start_time.label('src_start'),
FlightPathChunks.end_time.label('src_end')) \
.filter(FlightPathChunks.flight == flight) \
.cte('src')
subq = db.session.query(func.ST_DumpPoints(FlightPathChunks.locations).label('dst_points'),
FlightPathChunks.timestamps.label('dst_times'),
cte.c.src_loc_buf,
FlightPathChunks.flight_id.label('dst_points_fid'),
cte.c.src_start,
cte.c.src_end) \
.filter(and_(FlightPathChunks.flight != flight,
FlightPathChunks.end_time >= cte.c.src_start,
FlightPathChunks.start_time <= cte.c.src_end,
FlightPathChunks.locations.intersects(cte.c.src_loc_buf),
_ST_Intersects(FlightPathChunks.locations.ST_Simplify(0.005), cte.c.src_loc_buf))) \
.subquery()
dst_times = literal_column('dst_times[(dst_points).path[1]]')
q = db.session.query(subq.c.dst_points.geom.label('dst_location'),
dst_times.label('dst_time'),
subq.c.dst_points_fid.label('dst_point_fid')) \
.filter(_ST_Contains(subq.c.src_loc_buf, subq.c.dst_points.geom)) \
.order_by(subq.c.dst_points_fid, dst_times) \
.all()
src_trace = to_shape(flight.locations).coords
max_distance = 1000
other_flights = dict()
for point in q:
dst_time = point.dst_time
dst_loc = to_shape(point.dst_location).coords
# we might have got a destination point earier than source takeoff
# or later than source landing. Check this case and disregard early.
if dst_time < flight.takeoff_time or dst_time > flight.landing_time:
continue
# find point closest to given time
closest = bisect_left(flight.timestamps,
dst_time,
hi=len(flight.timestamps) - 1)
if closest == 0:
src_point = src_trace[0]
else:
# interpolate flight trace between two fixes
dx = (dst_time - flight.timestamps[closest - 1]).total_seconds() / \
(flight.timestamps[closest] - flight.timestamps[closest - 1]).total_seconds()
src_point_prev = src_trace[closest - 1]
src_point_next = src_trace[closest]
src_point = [
src_point_prev[0] +
(src_point_next[0] - src_point_prev[0]) * dx,
src_point_prev[1] +
(src_point_next[1] - src_point_prev[1]) * dx
]
point_distance = Location(
latitude=dst_loc[0][1],
longitude=dst_loc[0][0]).geographic_distance(
Location(latitude=src_point[1], longitude=src_point[0]))
if point_distance > max_distance:
continue
if point.dst_point_fid not in other_flights:
other_flights[point.dst_point_fid] = []
other_flights[point.dst_point_fid].append(
dict(times=list(), points=list()))
elif len(other_flights[point.dst_point_fid][-1]['times']) and \
(dst_time - other_flights[point.dst_point_fid][-1]['times'][-1]).total_seconds() > 600:
other_flights[point.dst_point_fid].append(
dict(times=list(), points=list()))
other_flights[point.dst_point_fid][-1]['times'].append(dst_time)
other_flights[point.dst_point_fid][-1]['points'].append(
Location(latitude=dst_loc[0][1], longitude=dst_loc[0][0]))
return other_flights
def get_points_query_from_gridpoints(self, gridpoints):
return select([
func.ST_DumpPoints(
func.ST_Collect(array(gridpoints)))
.label('point')]) \
.alias('point_query')