-
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 docstring for getCrossovers() is below:
""" Get crossover values from the OPS.
Input:
location: (string)
lyr_name: (string or list of strings)
point_path_id: (integer or list of integers)
OR
frame: (string or list of strings)
Output:
status: (integer) 0:error 1:success 2:warning
data:
source_point_path_id: (list of integer/s) point path ids of the source points
cross_point_path_id: (list of integer/s) point path ids of the crossovers
source_elev: (list of float/s) aircraft elevation of the source points
cross_elev: (list of float/s) aircraft elevation of the crossovers
layer_id: (list of integer/s) layer ids of the crossovers
season_name: (list of string/s) season names of the crossovers
frame_name: (list of string/s) frame names of the crossovers
segment_id: (list of integer/s) segment ids of the crossovers
twtt: (list of float/s) two-way travel time of the crossovers
angle: (list of float/s) acute angle (degrees) of the crossovers path
abs_error: (list of float/s) absolute difference (meters) between the source and crossover layer points
"""The following query is used to retrieve crossover information and calculate the error for the surface layer:
WITH cx1 AS (SELECT cx.id, cx.angle, cx.geom,cx.point_path_1_id,pp1.geom AS pp1_geom, pp1.frame_id,
pp1.segment_id,pp1.season_id,lp.layer_id, lp.twtt,lp.quality FROM {app}_crossovers cx JOIN {app}_point_paths pp1
ON cx.point_path_1_id=pp1.id JOIN {app}_point_paths pp2 ON cx.point_path_2_id=pp2.id LEFT JOIN
{app}_layer_points lp ON cx.point_path_1_id=lp.point_path_id AND lp.layer_id=1 WHERE pp1.frame_id IN %s OR
pp2.frame_id IN %s), cx2 AS (SELECT cx.id, cx.point_path_2_id, pp2.geom AS pp2_geom,
pp2.frame_id,pp2.segment_id,pp2.season_id, lp.layer_id, lp.twtt,lp.quality FROM {app}_crossovers cx JOIN
{app}_point_paths pp2 ON cx.point_path_2_id=pp2.id JOIN {app}_point_paths pp1 ON cx.point_path_1_id=pp1.id LEFT
JOIN {app}_layer_points lp ON cx.point_path_2_id=lp.point_path_id AND lp.layer_id=1 WHERE pp2.frame_id IN %s OR
pp1.frame_id IN %s) SELECT point_path_1_id, point_path_2_id, ST_Z(pp1_geom), ST_Z(pp2_geom),
COALESCE(cx1.layer_id,cx2.layer_id), frm1.name, frm2.name, cx1.segment_id,cx2.segment_id,
cx1.twtt,cx2.twtt,cx1.angle, ABS((ST_Z(pp1_geom)-(cx1.twtt*299792458.0003452/2)) - (ST_Z(pp2_geom)-
(cx2.twtt*299792458.0003452/2))), s1.name, s2.name,cx1.quality,cx2.quality FROM cx1 FULL OUTER JOIN cx2 ON
cx1.id=cx2.id JOIN {app}_frames frm1 ON cx1.frame_id=frm1.id JOIN {app}_frames frm2 ON cx2.frame_id=frm2.id JOIN
{app}_seasons s1 ON cx1.season_id=s1.id JOIN {app}_seasons s2 ON cx2.season_id=s2.id;
The following query is used to retreive and calculate the error of crossovers for any other specified layers:
WITH cx1 AS (SELECT cx.id, cx.angle, cx.geom,cx.point_path_1_id,pp1.geom AS pp1_geom, pp1.frame_id,
pp1.segment_id,pp1.season_id,lp.layer_id, lp.twtt,lp.quality FROM {app}_crossovers cx JOIN {app}_point_paths pp1
ON cx.point_path_1_id=pp1.id JOIN {app}_point_paths pp2 ON cx.point_path_2_id=pp2.id LEFT JOIN {app}_layer_points
lp ON cx.point_path_1_id=lp.point_path_id AND lp.layer_id=%s WHERE pp1.frame_id IN %s OR pp2.frame_id IN %s), cx2
AS (SELECT cx.id, cx.point_path_2_id, pp2.geom AS pp2_geom, pp2.frame_id,pp2.segment_id,pp2.season_id,
lp.layer_id, lp.twtt,lp.quality FROM {app}_crossovers cx JOIN {app}_point_paths pp2 ON cx.point_path_2_id=pp2.id
JOIN {app}_point_paths pp1 ON cx.point_path_1_id=pp1.id LEFT JOIN {app}_layer_points lp ON
cx.point_path_2_id=lp.point_path_id AND lp.layer_id=%s WHERE pp2.frame_id IN %s OR pp1.frame_id IN %s) SELECT
point_path_1_id, point_path_2_id, ST_Z(pp1_geom), ST_Z(pp2_geom), COALESCE(cx1.layer_id,cx2.layer_id), frm1.name,
frm2.name, cx1.segment_id,cx2.segment_id, cx1.twtt,cx2.twtt,cx1.angle, ABS((((ST_Z(pp1_geom) - (SELECT twtt FROM
{app}_layer_points WHERE layer_id=1 AND point_path_id = cx1.point_path_1_id)*299792458.0003452/2) - ((cx1.twtt -
(SELECT twtt FROM {app}_layer_points WHERE layer_id = 1 AND point_path_id =
cx1.point_path_1_id))*299792458.0003452/2/sqrt(3.15))) - (((ST_Z(pp2_geom) - (SELECT twtt FROM {app}_layer_points
WHERE layer_id=1 AND point_path_id = cx2.point_path_2_id)*299792458.0003452/2) - ((cx2.twtt - (SELECT twtt FROM
{app}_layer_points WHERE layer_id = 1 AND point_path_id =
cx2.point_path_2_id))*299792458.0003452/2/sqrt(3.15)))))), s1.name, s2.name,cx1.quality,cx2.quality FROM cx1 FULL
OUTER JOIN cx2 ON cx1.id=cx2.id JOIN {app}_frames frm1 ON cx1.frame_id=frm1.id JOIN {app}_frames frm2 ON
cx2.frame_id=frm2.id JOIN {app}_seasons s1 ON cx1.season_id=s1.id JOIN {app}_seasons s2 ON cx2.season_id=s2.id;
The getCrossoversReport() view is used to generate a report with crossover information as a csv file that can be downloaded from the server.
The docstring for getCrossoversReport() is shown below:
""" Get crossover error report from the OPS.
Input:
location: (string)
lyr_name: (string or list of strings)
'all' will fetch crossovers for all layers
seasons: (string or list of strings)
OR
frame: (string or list of strings)
Output:
status: (integer) 0:error 1:success 2:warning
data: url to crossover report .csv on the server
"""The query for generating a crossovers report for a given set of seasons is shown below:
WITH cx1 AS (SELECT cx.point_path_1_id,cx.id, cx.angle, cx.geom, pp1.geom AS pp1_geom,pp1.gps_time, frm1.name AS
frm1Name, ss1.name AS ss1Name, lp.layer_id, lp.twtt FROM {app}_crossovers cx JOIN {app}_point_paths pp1 ON
cx.point_path_1_id=pp1.id JOIN {app}_frames frm1 ON pp1.frame_id=frm1.id JOIN {app}_seasons ss1 ON
pp1.season_id=ss1.id JOIN {app}_layer_points lp ON cx.point_path_1_id=lp.point_path_id AND lp.layer_id IN %s
WHERE ss1.name IN %s), cx2 AS (SELECT cx.point_path_2_id, cx.id, pp2.geom AS pp2_geom,pp2.gps_time, frm2.name AS
frm2Name, ss2.name AS ss2Name, lp.layer_id, lp.twtt FROM {app}_crossovers cx JOIN {app}_point_paths pp2 ON
cx.point_path_2_id=pp2.id JOIN {app}_frames frm2 ON pp2.frame_id=frm2.id JOIN {app}_seasons ss2 ON
pp2.season_id=ss2.id JOIN {app}_layer_points lp ON cx.point_path_2_id=lp.point_path_id AND lp.layer_id IN %s
WHERE ss2.name IN %s) SELECT CASE WHEN COALESCE(cx1.layer_id,cx2.layer_id) = 1 THEN (ST_Z(cx1.pp1_geom) -
cx1.twtt*299792458.0003452/2) ELSE (ST_Z(cx1.pp1_geom) - (SELECT twtt FROM {app}_layer_points WHERE layer_id=1
AND point_path_id = cx1.point_path_1_id)*299792458.0003452/2 - (cx1.twtt - (SELECT twtt FROM {app}_layer_points
WHERE layer_id = 1 AND point_path_id = cx1.point_path_1_id))*299792458.0003452/2/sqrt(3.15)) END AS layer_elev_1,
CASE WHEN COALESCE(cx1.layer_id,cx2.layer_id) = 1 THEN (ST_Z(cx2.pp2_geom) - cx2.twtt*299792458.0003452/2) ELSE
(ST_Z(cx2.pp2_geom) - (SELECT twtt FROM {app}_layer_points WHERE layer_id=1 AND point_path_id =
cx2.point_path_2_id)*299792458.0003452/2 - (cx2.twtt - (SELECT twtt FROM {app}_layer_points WHERE layer_id = 1
AND point_path_id = cx2.point_path_2_id))*299792458.0003452/2/sqrt(3.15)) END AS layer_elev_2, cx1.twtt,
cx2.twtt, cx1.angle, lyr.name, cx1.ss1Name,cx2.ss2Name, frm1Name, frm2Name, cx1.gps_time, cx2.gps_time,
cx1.pp1_geom, cx2.pp2_geom, cx1.geom FROM cx1 JOIN cx2 ON cx1.id=cx2.id AND cx1.layer_id=cx2.layer_id JOIN
{app}_layers lyr ON lyr.id=cx1.layer_id;
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.