Benchmarking 2011/Cadcorp notes

Data
The "UNION ALL" views used by, for example, the "osm_roads" table are not usable by GeognoSIS because:


 * No spatial index can be detected (this restriction could be relaxed, because PostGIS should work OK, albeit potentially slowly)
 * No primary key/oid/unique id column can be detected

The views were converted to tables, and indexed, as follows:

create table osm_railways_gen1_cadcorp as (select * from osm_railways_gen1); alter table osm_railways_gen1_cadcorp add column cadcorp_id serial; create index osm_railways_gen1_cadcorp_geometry_sidx on osm_railways_gen1_cadcorp using gist(geometry); insert into geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type") SELECT '', 'public', 'osm_railways_gen1_cadcorp', 'geometry', ST_CoordDim(geometry), ST_SRID(geometry), GeometryType(geometry) FROM public.osm_railways_gen1_cadcorp LIMIT 1; create unique index osm_railways_gen1_cadcorp_unqidx on osm_railways_gen1_cadcorp(cadcorp_id);

This was done for the following Imposm tables:


 * osm_railways_gen1
 * osm_roads_gen0
 * osm_roads_gen1
 * osm_roads

Giving the following additional tables:


 * osm_railways_gen1_cadcorp
 * osm_roads_gen0_cadcorp
 * osm_roads_gen1_cadcorp
 * osm_roads_cadcorp

Styling
In order to mimic the layered styling the "osm_roads_gen1_cadcorp" and "osm_roads_cadcorp" tables have additional columns added, as follows:

alter table osm_roads_gen1_cadcorp add column cadcorp_simplefc int4; alter table osm_roads_cadcorp add column cadcorp_simplefc int4; alter table osm_roads_cadcorp add column cadcorp_complexfc int4;

mapserver2geognosis
The MapServer MAP file was copied, edited to fix the "PROJ_LIB" setting to match the machine configuration (a freshly downloaded MS4W), then parsed via MapScript, in Python 2.6.

The output of the Python program is:


 * A JSON file (approximately) describing the MapServer MAP file LAYER/STYLE/CLASS hierarchy
 * A SQL file for adding styling codes to the "osm_roads_cadcorp" and "osm_roads_gen1_cadcorp" tables (see below)
 * A JSON file containing styling lookup tables to match the styling codes

The first JSON file is then re-parsed and the contents used to create the GeognoSIS equivalent of a MapServer MAP file.

Style lookup codes
The following SQL is used to set styling codes on the "osm_roads_cadcorp" and "osm_roads_gen1_cadcorp" tables:

update osm_roads_cadcorp set cadcorp_simplefc=5 where type='trunk_link'; update osm_roads_cadcorp set cadcorp_simplefc=4 where type='motorway_link'; update osm_roads_cadcorp set cadcorp_simplefc=9 where type='track'; update osm_roads_gen1_cadcorp set cadcorp_simplefc=2 where type='trunk'; update osm_roads_cadcorp set cadcorp_simplefc=10 where type='pedestrian'; update osm_roads_cadcorp set cadcorp_simplefc=7 where type='tertiary'; update osm_roads_gen1_cadcorp set cadcorp_simplefc=3 where type='primary'; update osm_roads_gen1_cadcorp set cadcorp_simplefc=1 where type='motorway'; update osm_roads_cadcorp set cadcorp_simplefc=8 where type in ('unclassified','residential','service','road','living_street'); update osm_roads_cadcorp set cadcorp_simplefc=6 where type='secondary'; update osm_roads_cadcorp set cadcorp_complexfc=23 where type='secondary'; update osm_roads_cadcorp set cadcorp_complexfc=21 where type='trunk_link'; update osm_roads_cadcorp set cadcorp_complexfc=24 where type='tertiary'; update osm_roads_cadcorp set cadcorp_complexfc=20 where type='trunk'; update osm_roads_cadcorp set cadcorp_complexfc=22 where type='primary'; update osm_roads_cadcorp set cadcorp_complexfc=28 where type='footway'; update osm_roads_cadcorp set cadcorp_complexfc=25 where type in ('unclassified','residential','service','road','living_street'); update osm_roads_cadcorp set cadcorp_complexfc=19 where type='motorway_link'; update osm_roads_cadcorp set cadcorp_complexfc=26 where type='track'; update osm_roads_cadcorp set cadcorp_complexfc=27 where type='pedestrian'; update osm_roads_cadcorp set cadcorp_complexfc=18 where type='motorway'; update osm_roads_cadcorp set cadcorp_complexfc=8 where bridge=1 and type in ('classified','residential','service','road','living_street'); update osm_roads_cadcorp set cadcorp_complexfc=9 where bridge=1 and type='pedestrian'; update osm_roads_cadcorp set cadcorp_complexfc=6 where bridge=1 and type in ('secondary','secondary_link'); update osm_roads_cadcorp set cadcorp_complexfc=3 where bridge=1 and type='trunk'; update osm_roads_cadcorp set cadcorp_complexfc=5 where bridge=1 and type in ('primary','primary_link'); update osm_roads_cadcorp set cadcorp_complexfc=1 where bridge=1 and type='motorway'; update osm_roads_cadcorp set cadcorp_complexfc=7 where bridge=1 and type in ('tertiary','tertiary_link'); update osm_roads_cadcorp set cadcorp_complexfc=4 where bridge=1 and type='trunk_link'; update osm_roads_cadcorp set cadcorp_complexfc=2 where bridge=1 and type='motorway_link'; update osm_roads_cadcorp set cadcorp_complexfc=10 where type='motorway' and tunnel=1; update osm_roads_cadcorp set cadcorp_complexfc=16 where type in ('tertiary','tertiary_link') and tunnel=1; update osm_roads_cadcorp set cadcorp_complexfc=12 where type='trunk' and tunnel=1; update osm_roads_cadcorp set cadcorp_complexfc=11 where type='motorway_link' and tunnel=1; update osm_roads_cadcorp set cadcorp_complexfc=14 where type in ('primary','primary_link') and tunnel=1; update osm_roads_cadcorp set cadcorp_complexfc=17 where type in ('unclassified','residential','service','road','living_street') and tunnel=1; update osm_roads_cadcorp set cadcorp_complexfc=15 where type in ('secondary','secondary_link') and tunnel=1; update osm_roads_cadcorp set cadcorp_complexfc=13 where type='trunk_link' and tunnel=1;