Custom table prefixes and schemas

[kachkaev]

At the moment it is not possible to define custom prefixes to the tables, which can be useful for having multiple routing datasets in one database (different versions or different locations). It will be really good if these prefixes are introduced as arguments to functions doing import, update topology, etc. The same applies to schema name, but it seems that its customisation is already introduced in 2.0.

                     List of relations
 Schema |        Name         |   Type   |  Owner
--------+---------------------+----------+----------
 public | geography_columns   | view     | postgres
 public | geometry_columns    | table    | postgres
 public | spatial_ref_sys     | table    | postgres
 public | vertices_tmp        | table    | postgres
 public | vertices_tmp_id_seq | sequence | postgres
 public | ways                | table    | postgres
(6 rows)

     ↓

                     List of relations
  Schema  |             Name              |   Type   |  Owner
----------+-------------------------------+----------+----------
 myschema | dataset1__vertices_tmp        | table    | postgres
 myschema | dataset1__vertices_tmp_id_seq | sequence | postgres
 myschema | dataset1__ways                | table    | postgres
 myschema | dataset2__vertices_tmp        | table    | postgres
 myschema | dataset2__vertices_tmp_id_seq | sequence | postgres
 myschema | dataset2__ways                | table    | postgres
 public   | geography_columns             | view     | postgres
 public   | geometry_columns              | table    | postgres
 public   | spatial_ref_sys               | table    | postgres
(9 rows)

[dkastl]

Yes, schema support should be available in 2.0. Why do you think schema is not sufficient and you want to have table name prefixes as well?

[kachkaev]

E.g. I can have different schemas for different types of data (routing graphs, timetables, levels of air pollution, etc.) and also multiple datasets in each schema (London, New York, Berlin, etc.) Thus, I’ll probably want my road network for London to go to routing.london__ways.

How difficult is it to alter the code to support the prefixes? Will the routing functions work if I rename the tables manually after they are generated?

[dkastl]

As far as I remember there is no other function than pgr_createTopology, that creates a table (which is the table vertices for this function).

In general you can name your tables however you like. pgRouting should not assume certain table names. Some wrapper functions did in the old release. That's why we dropped most of them.

Looking at your table names you probably used osm2pgrouting to import the data. The development branch of osm2pgrouting supports prefix as parameter: https://github.com/pgrouting/osm2pgrouting/tree/develop

[woodbri]

Currently some of the pgr_analyze* functions assume that vertices_tmp exists and adds columns to that table then populated those.

I think there is some merit to this request, but I also think we will wait for a 2.x release to add this functionality otherwise, 2.0 will never get done and released :(

I'm pushing this to the 2.1 milestone.

[cvvergara]

modified pgr_analizegraph, pgr_isColumnInTable and pgr_isColumnIndexed that handles schemas

https://gist.github.com/cvvergara/6246202

[dkastl]

@cvvergara Thanks a lot! If there are just small changes, then it would make sense that you "fork" the pgRouting repository and modify the function and then send a pull request, because then the commit history is preserved.

@kachkaev You may want to try the modified function. It's good if it is tested by a few people to make sure it works as expected also for different data for example.

[cvvergara]

https://github.com/cvvergara/pgrouting/tree/develop

I created a pull request for the schema handling problem. Issue 178 Can you give it a try and report any problems?

It has a lot of NOTICES to inform the status of the process, to help pin point any problem you encounter.

[woodbri]

I have created a branch in pgrouting cvvergara-issue178 where I merged vivky's pull request and fixed a few minor problems. It might be easier for most users to:

git pull
git checkout cvvergara-issue178

And work this that code to test her changes.

[woodbri]

Changes for this have been integrated into v2.0.0-rc2 of the develop branch. Closing.