I have a PostGIS query which involves interating over a cursor and inserting the results from each iteration into a temp table. Once the loop has finished the the results of the temp table are returned. FME is reading the geometry field as an attribute not as the geometry. I've now run out of ideas - I've tried renaming the geometry field, explicitly casting the output as Geometry type, adding an index on the geometry field on the temp table. Nothing works. This is the query (it's for find how long it takes to get from several points (in the `nearest_nodes` parameter) to all other points on a network within a set time ( the `maxDrivetime` parameter), by multiple modes of transport (the `Vehicle` parameter):
```psql
-- Setup
CREATE TEMP TABLE query_output (
from_v int,
node_geom geometry(Point,27700),
agg_cost float,
transport_mode varchar(10)
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
CREATE INDEX node_idx
ON query_output USING gist
(node_geom);
DO $$
DECLARE t RECORD;
cost_field varchar(100);
reverse_cost_field varchar(100);
BEGIN
-- Loop through all selected transport modes
FOR t IN SELECT transport_mode
FROM UNNEST(string_to_array('$(Vehicle)', ' ')) AS transport_mode
LOOP
-- Select cost (time taken to travel along node for given transport mode
SELECT CASE t.transport_mode WHEN 'Car' THEN 'cost'
WHEN 'MRP' THEN 'cost_time_mrp'
WHEN 'LRP' THEN 'cost_time_lrp'
WHEN 'Bicycle' THEN 'cost_time_cycle'
WHEN 'Foot' THEN 'cost_time_walk' END
INTO cost_field;
-- As above but for reverse direction
SELECT CASE t.transport_mode WHEN 'Car' THEN 'reverse_cost'
WHEN 'MRP' THEN 'rcost_time_mrp'
WHEN 'LRP' THEN 'rcost_time_lrp'
WHEN 'Bicycle' THEN 'rcost_time_cycle'
WHEN 'Foot' THEN 'rcost_time_walk' END
INTO reverse_cost_field;
-- ******** MAIN QUERY **********
-- run routing for transport mode for all supplied stations
INSERT INTO query_output
SELECT a.from_v
, c.the_geom as node_geom
, a.agg_cost/60 AS agg_cost
, t.transport_mode AS transport_mode
FROM pgr_drivingDistance(
'SELECT id::int4,
source::int4,
target::int4,
' || cost_field || '::float8 as cost,
' || reverse_cost_field || '::float8 as reverse_cost
FROM edge_table'
, Array[@Value(nearest_node)]
, ($(maxDrivetime)+1)*60
) AS a
LEFT JOIN roadlinkmod_vertices_pgr2 AS c
ON (a.node = c.id)
WHERE a.agg_cost != 0;
END LOOP;
END
$$;
-- Return result
select from_v
, node_geom
, agg_cost
, transport_mode
from query_output
order by from_v, agg_cost;
```
Oddly if I pick one vehicle and run just the section marked MAIN QUERY it works fine and the points are displayed in the inspector e.g.:
```psql
SELECT a.from_v
, c.the_geom as node_geom
, a.agg_cost/60 AS agg_cost
, t.transport_mode AS transport_mode
FROM pgr_drivingDistance(
'SELECT id::int4,
source::int4,
target::int4,
cost::float8 as cost,
reverse_cost::float8 as reverse_cost
FROM edge_table'
, Array[@Value(nearest_node)]
, ($(maxDrivetime)+1)*60
) AS a
LEFT JOIN roadlinkmod_vertices_pgr2 AS c
ON (a.node = c.id)
WHERE a.agg_cost != 0;
```
This makes me think it is something to do with the setup of the table `query_output`