GeoServer does not display/preview pgRouting Layer

Question

I am developing a routing application using GeoServer, PostgreSQL, HTML and JavaScript. To display the routing layer on the HTML webpage, I followed the instructions from this workshop. GeoServer does not preview the pgRouting layer. The Error message is: "Internal Error: Rendering Process failed". I used the following query when I created the pgRouting SQL view inside geoserver:

    SELECT geom FROM pgr_fromAtoB('roads', %x1%, %y1%, %x2%, %y2%
  ) ORDER BY seq) AS route

This query uses the function pgr_fromAtoB. I Created this function in PostgreSQL as follows:

    IN edges_subset varchar,
    IN x1 double precision,
    IN y1 double precision,
    IN x2 double precision,
    IN y2 double precision,
    OUT seq INTEGER,
    OUT cost FLOAT,
    OUT name TEXT,
    OUT geom geometry,
    OUT heading FLOAT
)
RETURNS SETOF record AS
$BODY$

WITH
dijkstra AS (
    SELECT * FROM pgr_dijkstra(
        'SELECT gid as id, source, target, length AS cost FROM ' || $1,
        -- source
        (SELECT id FROM roads_vertices_pgr
            ORDER BY the_geom <-> ST_SetSRID(ST_Point(x1,y1),4326) LIMIT 1),
        -- target
        (SELECT id FROM roads_vertices_pgr
            ORDER BY the_geom <-> ST_SetSRID(ST_Point(x2,y2),4326) LIMIT 1),
        false) -- undirected
    ),
    with_geom AS (
        SELECT dijkstra.seq, dijkstra.cost, roads.name,
        CASE
            WHEN dijkstra.node = roads.source THEN geom
            ELSE ST_Reverse(geom)
        END AS route_geom
        FROM dijkstra JOIN roads
        ON (edge = gid) ORDER BY seq
    )
    SELECT *,
    ST_azimuth(ST_StartPoint(route_geom), ST_EndPoint(route_geom))
    FROM with_geom;
$BODY$
LANGUAGE 'sql';

I cannot figure out if the error is within the function I created or somewhere else. I ran the dijkstra algorithm on the roads shapefile inside PostgreSQL and it works fine. There is no projection or geometry issue either. Please, Can anyone suggest a solution to this??

Answer 1

I finally resolved the issue. The problem was in two places. The first problem was that I did not create a node network for the roads shapefile in PostgreSQL. Furthermore, I needed to create a network topology specifically after creating the node network for my roads. It would go as:

1. Create node network for Roads. Result roads_noded table created.
2. Create network topology for the noded table, not for the original roads layer. Result roads_noded_vertices_pgr table created
3. Add distance, type and name columns. And this completes the SQL portion.

In GeoServer, I made the mistake of creating a SQL view for my layer and publishing it afterwards. It actually has to be done oppositely in fact. First publishing the layer ensures that the projection of the layer and GeoServer match automatically. And then the coordinates created using "Compute from Native Bounds" match the coordinates of the layer. Hence the layer can be successfully viewed.