Crossovers

This page details how crossovers are identified and retrieved using with the OPS system.

views.py

createPath()

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.

Non Self-Intersecting Crossovers

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.

Self-Intersecting Crossovers

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)

getCrossovers()

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 above query calculates the error for the surface layer with the following sql:

 ABS((ST_Z(pp1_geom)-(cx1.twtt*299792458.0003452/2)) - (ST_Z(pp2_geom)-(cx2.twtt*299792458.0003452/2)))

This calculation boils down to finding the absolute difference between the elevation of the the two layer points at the crossover point. To find the elevation of a surface layer point, the following formula can be applied: [Elevation of Aircraft] - [surface twtt] * [speed of light] / 2

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 above query calculates the error for internal layers with the following sql:

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

The previous calculation, like the one for surface crossovers, finds the absolute difference between the elevations of the two layer points of an internal layer at a crossover point. To find the elevation of an internal layer point, the following formula can be applied:

([Elevation of Aircraft] - [surface twtt]*[speed of light] / 2) - (([layer twtt] - [surface twtt]) * [speed of light] / 2 / squareRoot([electrical resistivity of ice])

getCrossoversReport()

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

Note: the crossovers returned are currently ONLY from those seasons/frames specified in the request. If only one frame or only the frames from one segment are specified, only self-intersecting crossovers will be returned. If only one season is specified, crossovers and errors only from within that season will be reported.

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;

Geoserver

arctic/antarctic_app_crossover_errors Layers

There are 8 layers in Geoserver that have SQL views defined for crossover errors: (arctic,antarctic)_(rds,kuband,snow,accum)_crossover_errors.

The following SQL is used by GeoServer to query the database for crossovers:

WITH cx1 AS (SELECT pp1.location_id,cx.point_path_1_id,cx.id, cx.geom, lp.layer_id, lp.twtt, pp1.geom AS pp1_geom 
FROM {app}_crossovers cx JOIN {app}_point_paths pp1 ON cx.point_path_1_id=pp1.id JOIN {app}_segments seg ON 
pp1.segment_id=seg.id JOIN {app}_seasons ss1 ON pp1.season_id=ss1.id  LEFT JOIN {app}_layer_points lp ON 
cx.point_path_1_id=lp.point_path_id AND lp.layer_id = %layer_id%  WHERE ss1.name IN (%season_name%) AND 
ss1.season_group_id IN (%season_group_ids%) AND seg.name > %start_seg% AND seg.name < %stop_seg%), cx2 AS (SELECT 
cx.point_path_2_id, cx.id, lp.layer_id, lp.twtt, pp2.geom AS pp2_geom FROM {app}_crossovers cx JOIN 
{app}_point_paths pp2 ON cx.point_path_2_id=pp2.id JOIN {app}_segments seg ON pp2.segment_id=seg.id JOIN 
{app}_seasons ss2 ON pp2.season_id=ss2.id LEFT JOIN {app}_layer_points lp ON cx.point_path_2_id=lp.point_path_id 
AND lp.layer_id=%layer_id% WHERE ss2.name IN (%season_name%) AND ss2.season_group_id IN (%season_group_ids%) AND 
seg.name > %start_seg% AND seg.name < %stop_seg%) SELECT cx1.geom, CASE WHEN COALESCE(cx1.layer_id,cx2.layer_id) 
= 1 THEN ABS((ST_Z(cx1.pp1_geom) - cx1.twtt*299792458.0003452/2) - (ST_Z(cx2.pp2_geom) - 
cx2.twtt*299792458.0003452/2)) ELSE ABS((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)) - 
(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 error FROM cx1 JOIN cx2 ON 
cx1.id=cx2.id AND (cx1.layer_id=cx2.layer_id OR (cx1.layer_id IS NULL OR cx2.layer_id IS NULL)) WHERE 
cx1.location_id = %location_id%

Note: The crossover errors layers seem to be difficult for Geoserver to render over large areas. For now, crossovers are disabled in the Geoportal. It may be necessary to limit the rendering of crossovers to small geographic subsets (only appear when zoomed-in; this can be done through custom modification to the crossover_errors SLD.).

arctic/antarctic_crossover_errors Styled Layer Descriptors (SLD)

There are two .sld files that define the crossover_errors layers' style: arctic_crossover_errors and antarctic_crossover_errors.

The SLD files specify the color scheme for crossover errors based on the error (hollow points for NULL values, green for low error values, red for high error values.
The SLD files also specify the size of each crossover point based on zoom level (small when zoomed out, large when zoomed further in).
The SLD may need to be edited to display crossovers only when zoomed in to prevent geoserver from querying the database for many thousands of crossovers at once.

Provisions

Crossover errors views USED TO BE created for each of the apps (rds,accum,kuband,snow) in provisions. These views were phased out as it became apparent that they were extremely inefficient. This section of the wiki remains for reference only. The crossover errors views should only be re-implemented if it becomes necessary for some unforeseen reason in the future. The one major advantage to such a view is the ease with which it can be queried. This makes writing functions that use crossover errors very simple. The tradeoff is speed.

The crossover errors views were as follows: rds_crossover_errors, snow_crossover_errors, kuband_crossover_errors, and accum_crossover_errors. These could be referenced like any other table in the database.

The crossover errors view was created for each of the applications with the following code in provisions:

# CREATE DATABASE VIEWS FOR CROSSOVER ERRORS (CURRENTLY ONLY USED FOR GEOSERVER CROSSOVER ERRORS LAYER. SHOULD EVENTUALLY BE REMOVED/REPLACED!)
viewstr='psql -U postgres -d '$dbName' -c "CREATE VIEW app_crossover_errors AS WITH cx1 AS (SELECT cx.id, 
cx.angle, cx.geom,cx.point_path_1_id,cx.point_path_2_id, lp.layer_id, lp.twtt FROM app_crossovers cx LEFT JOIN 
app_layer_points lp ON cx.point_path_1_id=lp.point_path_id), cx2 AS (SELECT cx.id, cx.angle, 
cx.geom,cx.point_path_1_id,cx.point_path_2_id, lp.layer_id, lp.twtt FROM app_crossovers cx LEFT JOIN 
app_layer_points lp ON cx.point_path_2_id=lp.point_path_id) SELECT COALESCE(cx1.id,cx2.id) cross_id, 
COALESCE(cx1.angle,cx2.angle) angle, COALESCE(cx1.geom,cx2.geom) geom, COALESCE(cx1.layer_id,cx2.layer_id) 
layer_id, cx1.twtt twtt_1, cx2.twtt twtt_2,pp1.id point_path_1_id, pp2.id point_path_2_id, pp1.location_id, 
pp1.gps_time gps_time_1, pp2.gps_time gps_time_2, pp1.heading heading_1, pp2.heading heading_2, pp1.roll roll_1, 
pp2.roll roll_2, pp1.pitch pitch_1, pp2.pitch pitch_2, pp1.geom point_path_1_geom, pp2.geom point_path_2_geom, 
(SELECT name FROM app_frames WHERE id=pp1.frame_id) frame_1_name, (SELECT name FROM app_frames WHERE 
id=pp2.frame_id) frame_2_name, pp1.segment_id segment_1_id, pp2.segment_id segment_2_id, (SELECT name FROM 
app_seasons WHERE id=pp1.season_id) season_1_name, (SELECT name FROM app_seasons WHERE id=pp2.season_id) 
season_2_name, CASE WHEN COALESCE(cx1.layer_id,cx2.layer_id) IS NULL THEN NULL WHEN 
COALESCE(cx1.layer_id,cx2.layer_id) = 1 THEN (ST_Z(pp1.geom) - cx1.twtt*299792458.0003452/2) ELSE (ST_Z(pp1.geom) 
- (SELECT twtt FROM app_layer_points WHERE layer_id=1 AND point_path_id = pp1.id)*299792458.0003452/2 - (cx1.twtt 
- (SELECT twtt FROM app_layer_points WHERE layer_id = 1 AND point_path_id = 
pp1.id))*299792458.0003452/2/sqrt(3.15)) END AS layer_elev_1, CASE WHEN COALESCE(cx1.layer_id,cx2.layer_id) IS 
NULL THEN NULL WHEN COALESCE(cx1.layer_id,cx2.layer_id) = 1 THEN (ST_Z(pp2.geom) - cx2.twtt*299792458.0003452/2)
 ELSE (ST_Z(pp2.geom) - (SELECT twtt FROM app_layer_points WHERE layer_id=1 AND point_path_id = 
pp2.id)*299792458.0003452/2 - (cx2.twtt - (SELECT twtt FROM app_layer_points WHERE layer_id = 1 AND point_path_id 
= pp2.id))*299792458.0003452/2/sqrt(3.15)) END AS layer_elev_2 FROM cx1 FULL OUTER JOIN cx2 ON (cx1.id=cx2.id AND
 cx1.layer_id=cx2.layer_id) JOIN app_point_paths pp1 ON pp1.id=COALESCE(cx1.point_path_1_id,cx2.point_path_1_id) 
JOIN app_point_paths pp2 ON pp2.id=COALESCE(cx1.point_path_2_id,cx2.point_path_2_id) WHERE (cx1.layer_id IS NOT 
NULL OR cx2.layer_id IS NOT NULL);"'

eval ${viewstr//app/rds}
eval ${viewstr//app/snow}
eval ${viewstr//app/accum}
eval ${viewstr//app/kuband}

Note that the above code is no longer in provisions. If crossover errors views are ever re-implemented, this code should be put back into provisions after the installation of postgreSQL. Alternatively, this code can be directly entered into a terminal of an already built version of OPS.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly