def _truncate_last_segment(self, segment, start_point, exact_len, current_length):
# Function is called when last segment is too long.
# Previous length is current_length - current_segment_length
# We have to take only (exact_len - previous_len) first meters of current_segment_length
# Detect "correct" orientation of segment
query = self.session.query(func.st_equals(func.ST_StartPoint(segment.geom), start_point))
previous_length = current_length - segment.length
take_only = exact_len - previous_length
truncate_point = float(take_only) / float(segment.length)
# Correct SRID: 32632 (UTM WGS84 32 North)
if not query.first()[0]:
shorter_line = self.session.query(func.st_asewkb(func.st_Line_Substring(segment.geom, 0, truncate_point)),
func.ST_Length(
cast(func.st_Line_Substring(segment.geom, 0, truncate_point),
Geography))).first()
else:
shorter_line = self.session.query(func.st_asewkb(func.st_Line_Substring(segment.geom,
1 - truncate_point, 1)),
func.ST_Length(
cast(func.st_Line_Substring(segment.geom, 1 - truncate_point, 1),
Geography))).first()
segment_labels = []
for label in segment.labels:
segment_labels.append(model.Label(label=label.label, label_rate=label.label_rate))
shorter_segment = model.Segment(name=segment.name,
geom=WKBElement(shorter_line[0]),
length=shorter_line[1],
labels=segment_labels)
return shorter_segment
def find_test_point_within_distance(cls, point, line):
'''Returns a test point within settings.ALERT_DISTANCE of given point
along the given line.
Args:
cls (SpatialQueries): Class object
point (Geometry): PostGIS point Geometry object
line (Geometry): PostGIS line Geometry object
Returns:
Geometry: A new PostGIS point Geometry object
'''
ratio_location_query = func.ST_LineLocatePoint(line, point)
ratio = list(session.execute(ratio_location_query))[0][0]
line_length = func.ST_Length(line)
total_length = list(session.execute(line_length))[0][0]
distance_on_line = ratio * total_length
# can move point back as far as the beginning of the line
# or within the set ALERT_DISTANCE
allowable_subtraction = min(distance_on_line, settings.ALERT_DISTANCE)
fraction = random.random()
new_distance = distance_on_line - (fraction * allowable_subtraction)
new_point = list(session.execute(
func.ST_Line_Interpolate_Point(
line, new_distance/total_length)))[0][0]
return new_point
def _find_subpaths(self, last_point, current_segment, current_length, analyzed_segments):
if current_length > self.exact_len:
shorter_segment = self._truncate_last_segment(current_segment, last_point, self.exact_len, current_length)
return [([shorter_segment], self.exact_len)]
intersection_segments_rows = self.session.query(model.Segment,
func.ST_Length(cast(model.Segment.geom, Geography)))\
.filter(model.Segment.geom.ST_Intersects(last_point)) \
.all()
# The path ends when point intersects with only one line
if len(intersection_segments_rows) == 1:
return [([current_segment], current_length)]
all_subpaths = []
for row in intersection_segments_rows:
# Set length for each segment
row[0].length = row[1]
if row[0].name != current_segment.name and not self._segment_in_list(row[0], analyzed_segments):
segment_last_point = self._get_opposite_bound(row[0], last_point)
analyzed_segments.append(row[0])
my_subpaths = self._find_subpaths(segment_last_point, row[0], current_length + row[1],
copy.copy(analyzed_segments))
for subpath_tuple in my_subpaths:
subpath_tuple[0].insert(0, current_segment)
all_subpaths.append(subpath_tuple)
return all_subpaths
def query(self, source, target, core, column, pkey):
# ST_Buffer is not yet implemented so BigQueryCore won't work
# (groups.google.com/d/msg/bq-gis-feedback/Yq4Ku6u2A80/ceVXU01RCgAJ)
if isinstance(core, BigQueryCore):
raise ValueError(
"The LengthOf feature is currently incompatible with \
BigQueryCore because ST_Buffer is not yet implemented")
# Get all lines-of-interests (LOIs) of fclass `on`
lois = select(
[source.c[self.source_id], source.c.WKT],
source.c[self.source_column] == self.source_filter,
).cte("lois")
# Create a buffer `within` a distance/radius around each centroid.
# The point has to be converted to EPSG:3857 so that meters can be
# used instead of decimal degrees for EPSG:4326.
buff = select([
target,
func.ST_Buffer(core.ST_GeoFromText(target.c[column]),
self.within).label("__buffer__"),
]).cte("buff")
# Clip the LOIs with the buffers then calculate the length of all
# LOIs inside each buffer.
clip = select(
[
buff,
func.ST_Intersection(
core.ST_GeoFromText(lois.c.WKT),
func.ST_Transform(buff.c["__buffer__"], 4326),
).label("__geom__"),
func.ST_Length(
func.ST_Intersection(
func.ST_Transform(core.ST_GeoFromText(lois.c.WKT),
3857),
buff.c["__buffer__"],
)).label("__len__"),
],
func.ST_Intersects(
core.ST_GeoFromText(lois.c.WKT),
func.ST_Transform(buff.c["__buffer__"], 4326),
),
).cte("clip")
# Sum the length of all LOIs inside each buffer
sum_length = (select([
clip.c[pkey],
func.sum(clip.c["__len__"]).label(self.feature_name),
]).select_from(clip).group_by(clip.c[pkey]).cte("sum_length"))
# Join the sum of the length of all LOIs inside each buffer
query = select(
[
col for col in sum_length.columns
if col.key not in ("__len__", "__geom__", "__buffer__")
],
sum_length.c[pkey] == buff.c[pkey],
)
return query
def _split_start_line(self, point, segment):
# Find segment's point nearest to point
nearest_point = self.session.query(func.st_closestpoint(segment.geom, point)).first()
# Locate nearest point on segment. Function returns the position on segment as rate
located_point = self.session.query(func.st_line_locate_point(segment.geom, nearest_point)).first()
# Split segment. First half starts from 0 and ends at located_point
first_half = self.session.query(func.st_asewkb(func.st_Line_Substring(segment.geom, 0, located_point)),
func.ST_Distance_Sphere(func.st_Line_Substring(segment.geom, 0, located_point),
nearest_point),
func.ST_Length(cast(func.st_Line_Substring(segment.geom, 0, located_point),
Geography))).first()
# Split segment. Second half starts from located_point and ends at 1
second_half = self.session.query(func.st_asewkb(func.st_Line_Substring(segment.geom, located_point, 1)),
func.ST_Distance_Sphere(func.st_Line_Substring(segment.geom, located_point, 1),
nearest_point),
func.ST_Length(cast(func.st_Line_Substring(segment.geom, located_point, 1),
Geography))).first()
# Create temporary segments (do not add these segments to session), with their own labels
first_segment_labels = []
second_segment_labels = []
for label in segment.labels:
first_segment_labels.append(model.Label(label=label.label, label_rate=label.label_rate))
second_segment_labels.append(model.Label(label=label.label, label_rate=label.label_rate))
first_segment = model.Segment(name=segment.name,
geom=WKBElement(first_half[0]),
length=first_half[2],
labels=first_segment_labels)
second_segment = model.Segment(name=segment.name,
geom=WKBElement(second_half[0]),
length=second_half[2],
labels=second_segment_labels)
# If located point is the same as start point, return only second half
if located_point[0] == 0.0:
return [second_segment]
# If located point is the same as end point, return only first half
if located_point[0] == 1.0:
return [first_segment]
# Else, return two splitted segments
return [first_segment, second_segment]
def find_line_substring(path, start, end):
'''Returns a substring from start to end distances of a given path.
Args:
path (Geometry): PostGIS Geometry object
start (int): Distance from start vertex of line in meters
end (int): Distance from start vertex of line in meters
Returns:
Returns a subpath of given line from start to end
'''
length = func.ST_Length(path)
start_fraction = start / length
end_fraction = end / length
return func.ST_LineSubstring(path, start_fraction, end_fraction)
def _find_nearest_lines(self):
query = self.session.query(model.Segment,
func.ST_Distance_Sphere(model.Segment.geom,
self.start_point_text_geom).label('distance'),
func.ST_Length(cast(model.Segment.geom, Geography))
).order_by('distance')
segments = []
for row in query: # row format [0]model.Segment, [1]distance (meters), [2]length (meters)
if row[1] > self.start_threshold:
break
else:
lines = self._split_start_line(self.start_point_text_geom, row[0])
segments += lines
return segments
def test_query(self):
conn = self.conn
# Define geometries to insert
values = [{
"ewkt": "SRID=4326;LINESTRING(0 0, 1 0)"
}, {
"ewkt": "SRID=4326;LINESTRING(0 0, 0 1)"
}]
# Define the query to compute distance (without spheroid)
distance = func.ST_Length(func.ST_GeomFromText(bindparam("ewkt")),
False)
i = table.insert()
i = i.values(geom=bindparam("ewkt"), distance=distance)
# Execute the query with values as parameters
conn.execute(i, values)
# Check the result
q = select([table])
res = conn.execute(q).fetchall()
# Check results
assert len(res) == 2
r1 = res[0]
assert r1[0] == 1
assert r1[1].srid == 4326
assert to_shape(r1[1]).wkt == "LINESTRING (0 0, 1 0)"
assert round(r1[2]) == 111195
r2 = res[1]
assert r2[0] == 2
assert r2[1].srid == 4326
assert to_shape(r2[1]).wkt == "LINESTRING (0 0, 0 1)"
assert round(r2[2]) == 111195
def find_test_point_outside_distance(cls, point, line):
'''Returns a test point outside of settings.ALERT_DISTANCE of given point
along the given line.
Args:
cls (SpatialQueries): Class object
point (Geometry): PostGIS point Geometry object
line (Geometry): PostGIS line Geometry object
Returns:
Geometry: A new PostGIS point Geometry object on success.
If the point given is within settings.ALERT_DISTANCE of the start
vertex of the given line, this function returns None, because
a point outside of the requested range before the given point
isn't possible.
'''
ratio_location_query = func.ST_LineLocatePoint(line, point)
ratio = list(session.execute(ratio_location_query))[0][0]
line_length = func.ST_Length(line)
total_length = list(session.execute(line_length))[0][0]
distance_on_line = ratio * total_length
if distance_on_line <= settings.ALERT_DISTANCE:
# can't get a point that is outside tolerance from here
return None
# find a point anywhere from beginning of line
# to threshold of event
fraction = 1
while fraction == 1:
fraction = random.random()
threshold_distance = distance_on_line - settings.ALERT_DISTANCE
new_distance = fraction * threshold_distance
new_point = list(session.execute(
func.ST_Line_Interpolate_Point(
line, new_distance/total_length)))[0][0]
return new_point
