-
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 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).
- More to come!