How to: Flip Geometries

Problem:

After making a query and getting a path, when the application needs the geometries, sometimes there is a need to flip some geometries.

For example, for visualization, arrows are to be displayed based on the geometry and the edge used has the opposite direction of the original LINESTRING geometry.

Example:

This query retrieves the original geometry, if this is what the application requires, then there is no need to flip geometries.

CREATE VIEW route_from2To3_1 AS
WITH
dijkstra AS (SELECT * FROM pgr_dijkstra('
    SELECT id,
        source, target,
        cost, reverse_cost
        FROM edge_table',
    2, 3, true))
SELECT seq, the_geom AS route_geom FROM dijkstra
   JOIN edge_table ON(edge = id);

When adding arrows to the lines there is a mismatch on some edges:

This query flips the geometries that have the arrows in the "wrong" direction

CREATE VIEW route_from2To3_2 AS
WITH
dijkstra AS (SELECT * FROM pgr_dijkstra('
    SELECT id, source, target, cost, reverse_cost
        FROM edge_table',
    2, 3, true))
SELECT seq,
    CASE
    WHEN dijkstra.node = edge_table.source
      THEN edge_table.the_geom
      ELSE ST_Reverse(edge_table.the_geom)
    END AS route_geom
FROM dijkstra
   JOIN edge_table ON(edge = id);

Within the same query you can add more information. Maybe for displaying purposes.

The following query gets the id, renames it as "name" and the column "name" is used as a display value.

CREATE VIEW route_from2To3_3 AS
WITH
dijkstra AS (SELECT * FROM pgr_dijkstra('
    SELECT id, source, target, cost, reverse_cost
        FROM edge_table',
    2, 3, true))
SELECT seq, id AS name, -- additional information
    CASE
    WHEN dijkstra.node = edge_table.source
      THEN edge_table.the_geom
      ELSE ST_Reverse(edge_table.the_geom)
    END AS route_geom
FROM dijkstra
   JOIN edge_table ON(edge = id);