[SOLVED] Argument in pgr_createTopology() function as query

Argument in pgr_createTopology() function as query

I am using pgrouting 2.1.0.

I have spatial and non-spatial data in different table(in different schema). Here I am creating a view which contain geom(filled), id(filled) source(blank) and target(blank).

I have tried pgr_createTopology() function but on view it is running as it requires table. Also tried to write query in pgr_createtopology() but not succeeded. Can any one suggest other option.

I can not merge spatial and non-spatial data.

Solution

First of all update your pg_routing version as pgr_createTopology() won't run on view for your version. I have used 2.6.0. Next you have to create editable view of your spatial and non-spatial data. This can be done by applying rules to your view. You might be wanting to update source and target columns by running pgr_createTopology() So create a rule like following for your view. Hope that works.

CREATE OR REPLACE RULE update_vv1 AS
    ON UPDATE TO view_schema_name.view_table_name
    DO INSTEAD
    ( 
    UPDATE schema_non_spatial_table.non_spatial_table_name SET source = new.source,   target = new.target
  WHERE non_spatail_table_name.id = old.id;
);