Wiki-Leo at 11:15, 24 February 2015

2015-02-24T11:15:29Z

Jmckenna at 13:56, 24 August 2011

2011-08-24T13:56:30Z

Wiki-Mpd: Created page with "== 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 ..."

2011-08-10T09:16:28Z

Created page with "== 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 ..."

New page

== 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:

<pre>
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);
</pre>

This was done for the following [[Benchmarking_2011/Imposm|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:

<pre>
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;
</pre>

== 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:

<pre>
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;
</pre>

← Older revision		Revision as of 11:15, 24 February 2015
Line 97:		Line 97:
	</pre>		</pre>

−	[[Category:FOSS4G2011~~]] [[Category:FOSS4G~~]]	+	[[Category:FOSS4G2011]]

← Older revision		Revision as of 13:56, 24 August 2011
Line 96:		Line 96:
	update osm_roads_cadcorp set cadcorp_complexfc=13 where type='trunk_link' and tunnel=1;		update osm_roads_cadcorp set cadcorp_complexfc=13 where type='trunk_link' and tunnel=1;
	</pre>		</pre>
		+
		+	[[Category:FOSS4G2011]] [[Category:FOSS4G]]

Benchmarking 2011/Cadcorp notes - Revision history

Wiki-Leo at 11:15, 24 February 2015

Jmckenna at 13:56, 24 August 2011

Wiki-Mpd: Created page with "== 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 ..."