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;