-
Notifications
You must be signed in to change notification settings - Fork 2
Crossovers
This page details how crossovers are identified and retrieved using with the OPS system.
The createPath() view is used to insert new flight-lines into the database. As part of this process, self and non self-intersecting crossovers are found.
After createPath() has inserted the current segment and its point paths, non self-intersecting crossovers for the segment are found. To do this, a series of two queries are utilized to find the points of intersection and the nearest two point paths of the intersecting segments.
The first query finds and returns the points of intersection, the nearest point path on the current segment, and the angle formed by the vector defined by the point of intersection and the next point path along the line:
WITH pts AS (SELECT row_number() over (ORDER BY gps_time) AS rn, id,geom FROM {app}_point_paths WHERE
segment_id = {seg} ORDER BY gps_time), line AS (SELECT
ST_MakeLine(ST_GeomFromText('POINTZ('||ST_X(pts.geom)||' '||ST_Y(pts.geom)||' '||pts.rn||')',4326)) AS ln FROM
pts), i_pts AS (SELECT (ST_Dump(ST_Intersection(ST_Transform(line.ln,{proj}),ST_Transform(o.geom,
{proj})))).geom AS i_pt FROM line, {app}_segments AS o WHERE o.id != {seg}) SELECT
ST_Transform(ST_Force_2D(i_pt),4326) AS i, pts1.id, CASE WHEN ST_Equals(i_pt,pts1.geom) THEN
degrees(ST_Azimuth(i_pt,ST_Transform(pts2.geom,{proj}))) ELSE degrees(ST_Azimuth(i_pt,ST_Transform(pts1.geom,
{proj}))) END FROM i_pts, pts AS pts1, pts AS pts2 WHERE pts1.rn = ST_Z(i_pt)::int AND pts2.rn = (SELECT rn
FROM pts WHERE rn != ST_Z(i_pts.i_pt)::int ORDER BY ABS(ST_Z(i_pts.i_pt)::int - rn) ASC LIMIT 1) ORDER BY i;
The above query first creates a temporary view, known as a common table expression (CTE), called 'pts' which gets the point paths from the current segment ordered by gps_time. A column called 'rn' is generated that contains each row's position (1-n) using the row_number() function:
WITH pts AS (SELECT row_number() over (ORDER BY gps_time) AS rn, id,geom FROM {app}_point_paths WHERE
segment_id = {seg} ORDER BY gps_time), Next, a CTE called 'line' is created. This creates a linestring from the results of the 'pts' CTE. The Z value of each vertex is the row number of the ordered point paths:
line AS (SELECT ST_MakeLine(ST_GeomFromText('POINTZ('||ST_X(pts.geom)||' '||ST_Y(pts.geom)||'
'||pts.rn||')',4326)) AS ln FROM pts),After this, a CTE is created called 'i_pts'. This CTE finds the points of intersection between the line generated by the 'line' CTE and all other segments in the database. The result of this next part are points of intersection with an interpolated Z value from the row numbers generated in the pts CTE:
i_pts AS (SELECT (ST_Dump(ST_Intersection(ST_Transform(line.ln,{proj}),ST_Transform(o.geom,{proj})))).geom AS
i_pt FROM line, {app}_segments AS o WHERE o.id != {seg})Finally, the points of intersection are selected, the nearest point path on the current segment is selected, and the angle formed by the vector defined by the intersection point and the next point path along the line is found and returned (See ST_Azimuth() for more information). The nearest point path is determined by the rounded Z value of the intersection point (this corresponds to the row number of the nearest point path):
SELECT ST_Transform(ST_Force_2D(i_pt),4326) AS i, pts1.id, CASE WHEN ST_Equals(i_pt,pts1.geom) THEN
degrees(ST_Azimuth(i_pt,ST_Transform(pts2.geom,{proj}))) ELSE degrees(ST_Azimuth(i_pt,ST_Transform(pts1.geom,
{proj}))) END
FROM i_pts, pts AS pts1, pts AS pts2 WHERE pts1.rn = ST_Z(i_pt)::int AND pts2.rn = (SELECT rn FROM
pts
WHERE rn != ST_Z(i_pts.i_pt)::int ORDER BY ABS(ST_Z(i_pts.i_pt)::int - rn) ASC LIMIT 1) ORDER BY i;
The second query used to find non self-intersecting crossovers is similar to the one above:
WITH pts AS (SELECT row_number() over (ORDER BY gps_time) AS rn, geom, id, segment_id FROM {app}_point_paths
WHERE segment_id IN (SELECT s2.id FROM {app}_segments AS s1, {app}_segments AS s2 WHERE s1.id = {seg} AND s2.id
!= {seg} AND ST_Intersects(s1.geom,s2.geom)) ORDER BY gps_time), line AS (SELECT
ST_MakeLine(ST_GeomFromText('POINTZ('||ST_X(pts.geom)||' '||ST_Y(pts.geom)||' '||pts.rn||')',4326)) AS ln FROM
pts GROUP BY pts.segment_id), i_pts AS (SELECT (ST_Dump(ST_Intersection(ST_Transform(line.ln,
{proj}),ST_Transform(o.geom,{proj})))).geom AS i_pt FROM line, {app}_segments AS o WHERE o.id = {seg}) SELECT
pts1.id, CASE WHEN ST_Equals(i_pt,pts1.geom) THEN degrees(ST_Azimuth(i_pt,ST_Transform(pts2.geom,{proj}))) ELSE
degrees(ST_Azimuth(i_pt,ST_Transform(pts1.geom,{proj}))) END FROM i_pts, pts AS pts1, pts AS pts2 WHERE pts1.rn =
ST_Z(i_pt)::int AND pts2.rn = (SELECT rn FROM pts WHERE rn != ST_Z(i_pts.i_pt)::int ORDER BY
ABS(ST_Z(i_pts.i_pt)::int - rn) ASC LIMIT 1) ORDER BY ST_Transform(ST_Force_2D(i_pt),4326);
The major difference is that the above query creates linestrings from all of the segments except the current segment with row_number as Z values. In this way, the nearest point paths to the other segments can be found:
WITH pts AS (SELECT row_number() over (ORDER BY gps_time) AS rn, geom, id, segment_id FROM {app}_point_paths
WHERE segment_id IN (SELECT s2.id FROM {app}_segments AS s1, {app}_segments AS s2 WHERE s1.id = {seg} AND s2.id
!= {seg} AND ST_Intersects(s1.geom,s2.geom)) ORDER BY gps_time),
line AS (SELECT ST_MakeLine(ST_GeomFromText('POINTZ('||ST_X(pts.geom)||' '||ST_Y(pts.geom)||'
'||pts.rn||')',4326)) AS ln FROM pts GROUP BY pts.segment_id)
After the above two queries are run and the results retrieved, the data is extracted and the angle of intersection is determined base on the angles returned above:
for idx in range(len(cross_info1)):
cross_pts.append(cross_info1[idx][0])
point_path_1_id.append(cross_info1[idx][1])
point_path_2_id.append(cross_info2[idx][0])
#Determine the angle of intersection
angle1 = cross_info1[idx][2]
angle2 = cross_info2[idx][1]
angle = math.fabs(angle1-angle2)
#Only record the acute angle.
if angle > 90:
angle = math.fabs(180 - angle)
if angle > 90:
angle = math.fabs(180 - angle)
cross_angles.append(angle)Next, self-intersecting crossovers are found.
Only one query to the database is used for determining self-intersecting crossovers. Note that there is no pre-existing function like ST_Intersection() to find points of self-intersection.
WITH pts AS (SELECT id, geom FROM {app}_point_paths WHERE segment_id = {seg} ORDER BY gps_time) SELECT
ST_UnaryUnion(ST_Transform(ST_MakeLine(ST_GeomFromText('POINTZ('||ST_X(pts.geom)||' '||ST_Y(pts.geom)||'
'||pts.id||')',4326)),{proj})) FROM pts;
The above query gets the point paths from the segment, creates a linestring from them with the id of each point path as the Z value of the vertices, and then uses the ST_UnaryUnion() function to break up the linestring at points of intersection. The ST_UnaryUnion() operation on linestrings with self-intersection results in a multilinestring where the last point of every linestring except the last linestring is a crossover (also, the first point of every linestring except the first linestring is a crossover).
If a multilinestring result is fetched from the database (indicating self-intersection), the point path ids for all of the points in a segment are retrieved:
pt_ids = models.point_paths.objects.filter(segment_id=segmentsObj.pk).values_list('id',flat=True)Next, all of the linestrings in the mulilinestring are looped through. The point path before and after the intersection point is found and the distance between each of these points and the intersection point calculated. The point path closest to the intersection point and the point path directly after the intersection point are kept (could be the same point):
self_crosses = {}; nxt_pts = {}
#Loop through all linestrings created by ST_UnaryUnion
for idx in range(len(lines) - 1):
#Set the value of idx2:
idx2 = idx + 1
#Keep track of intersection point w/ dictionary:
i_point = lines[idx][-1]
if i_point not in self_crosses.keys():
self_crosses[i_point] = []
nxt_pts[i_point] = []
#Fall back/forward to point w/ z in pt_ids.
#For the point before the crossover:
coord_idx = -1 #Include the crossover pt in the search (could be on top of point_path geom)
while lines[idx][coord_idx][2] not in pt_ids:
coord_idx -= 1
if math.fabs(coord_idx) > len(lines[idx].coords):
coord_idx = -1
idx -= 1
pt_1 = lines[idx][coord_idx]
#For the point after the crossover:
coord_idx = 1 #Start after the crossover point.
while lines[idx2][coord_idx][2] not in pt_ids:
coord_idx += 1
if coord_idx >= len(lines[idx2].coords):
coord_idx = 0
idx2 += 1
pt_2 = lines[idx2][coord_idx]
#select pt closest to current intersection point
if LineString(pt_1,i_point).length <= LineString(i_point,pt_2).length:
self_crosses[i_point].append(pt_1[2])
else:
self_crosses[i_point].append(pt_2[2])
nxt_pts[i_point].append(pt_2)Note that each crossover is found twice, once for each part of the line that crosses itself (crossover from one direction and then the other).
Next, the angle of intersection is determined:
#Find the angle of self-intersection
change_x1 = i_pt[0] - nxt_pts[i_pt][0][0]
change_y1 = i_pt[1] - nxt_pts[i_pt][0][1]
change_x2 = i_pt[0] - nxt_pts[i_pt][1][0]
change_y2 = i_pt[1] - nxt_pts[i_pt][1][1]
angle1 = math.degrees(math.atan2(change_y1,change_x1))
angle2 = math.degrees(math.atan2(change_y2,change_x2))
angle = math.fabs(angle1-angle2)
#Get the acute angle:
if angle > 90:
angle = math.fabs(180 - angle)
if angle > 90:
angle = math.fabs(180-angle)
cross_angles.append(angle)Finally, any self and non-self-intersecting crossovers are inserted into the database:
if len(point_path_1_id) > 0:
logging.info('Crossovers for segment %s of season %s are being inserted.',inSegment,inSeason)
crossovers = []
#If so, package for bulk insert.
for i in range(len(point_path_1_id)):
crossovers.append(models.crossovers(point_path_1_id=point_path_1_id[i], point_path_2_id=point_path_2_id[i], angle=cross_angles[i], geom=cross_pts[i]))
#Bulk insert found crossovers into the database.
_ = models.crossovers.objects.bulk_create(crossovers)The getCrossovers() view is used to retrieve crossover data from the database.
The getCrossoversReport() view is used to generate a report with crossover information as a csv file that can be downloaded from the server.
There are 8 layers in Geoserver that have SQL views defined for crossover errors: (arctic,antarctic)_(rds,kuband,snow,accum)_crossover_errors.
There are two .sld files that define the crossover_errors layers' style (one for each workspace: arctic and antarctic).
Crossover errors views are created for each of the apps (rds,accum,kuband,snow) in provisions. This view should be used sparingly and should eventually be removed. Its only use is for the geoserver crossover errors layers. Functions like getCrossovers() now use more efficient/faster queries.
- The crossover errors views are as follows: rds_crossover_errors, snow_crossover_errors, kuband_crossover_errors, and accum_crossover_errors. These can be referenced like any other table in the database.