Difference between revisions of "Benchmarking 2011/QGIS notes"

From OSGeo
Jump to navigation Jump to search
(QGIS notes)
 
(QGIS tables)
Line 1: Line 1:
 
== Data ==
 
== Data ==
  
Same problem with missing/non-unique keys as [[Benchmarking_2011/Cadcorp_notes | Cadcorp GeognoSIS]].
+
QGIS also requires tables with unique keys like [[Benchmarking_2011/Cadcorp_notes | Cadcorp GeognoSIS]].
 +
Additionaly, QGIS doesn't support "order by" clauses for DB layers.
  
 
The views were converted to tables, and indexed, as follows:
 
The views were converted to tables, and indexed, as follows:
  
 
<pre>
 
<pre>
create table osm_railways_gen1_cadcorp as (select * from osm_railways_gen1);
+
CREATE TABLE osm_roads_ordered AS (SELECT * FROM osm_roads ORDER BY z_order ASC, st_length(geometry) ASC);
alter table osm_railways_gen1_cadcorp add column cadcorp_id serial;
+
ALTER TABLE osm_roads_ordered ADD COLUMN unique_id serial;
create index osm_railways_gen1_cadcorp_geometry_sidx on osm_railways_gen1_cadcorp using gist(geometry);
+
CREATE INDEX osm_roads_ordered_geometry_sidx ON osm_roads_ordered 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;
+
INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
create unique index osm_railways_gen1_cadcorp_unqidx on osm_railways_gen1_cadcorp(cadcorp_id);
+
SELECT '', 'public', 'osm_roads_ordered', 'geometry', ST_CoordDim(geometry), ST_SRID(geometry), GeometryType(geometry)
</pre>
+
FROM public.osm_roads_ordered LIMIT 1;
 +
CREATE UNIQUE INDEX osm_roads_ordered_unqidx ON osm_roads_ordered(unique_id);
  
This was done for the following [[Benchmarking_2011/Imposm|Imposm]] tables:
+
CREATE TABLE osm_roads_gen0_ordered AS (SELECT * FROM osm_roads_gen0 ORDER BY z_order ASC, st_length(geometry) ASC);
 +
ALTER TABLE osm_roads_gen0_ordered ADD COLUMN unique_id serial;
 +
CREATE INDEX osm_roads_gen0_ordered_geometry_sidx ON osm_roads_gen0_ordered 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_roads_gen0_ordered', 'geometry', ST_CoordDim(geometry), ST_SRID(geometry), GeometryType(geometry)
 +
FROM public.osm_roads_gen0_ordered LIMIT 1;
 +
CREATE UNIQUE INDEX osm_roads_gen0_ordered_unqidx ON osm_roads_gen0_ordered(unique_id);
  
* osm_railways_gen1
+
CREATE TABLE osm_roads_gen1_ordered AS (SELECT * FROM osm_roads_gen1 ORDER BY z_order ASC, st_length(geometry) ASC);
* osm_roads_gen0
+
ALTER TABLE osm_roads_gen1_ordered ADD COLUMN unique_id serial;
* osm_roads_gen1
+
CREATE INDEX osm_roads_gen1_ordered_geometry_sidx ON osm_roads_gen1_ordered USING gist(geometry);
* osm_roads
+
INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
 
+
SELECT '', 'public', 'osm_roads_gen1_ordered', 'geometry', ST_CoordDim(geometry), ST_SRID(geometry), GeometryType(geometry)
Giving the following additional tables:
+
FROM public.osm_roads_gen1_ordered LIMIT 1;
 +
CREATE UNIQUE INDEX osm_roads_gen1_ordered_unqidx ON osm_roads_gen1_ordered(unique_id);
 +
</pre>
  
* osm_railways_gen1_cadcorp
 
* osm_roads_gen0_cadcorp
 
* osm_roads_gen1_cadcorp
 
* osm_roads_cadcorp
 
  
 
=== Raster ===
 
=== Raster ===

Revision as of 03:55, 8 September 2011

Data

QGIS also requires tables with unique keys like Cadcorp GeognoSIS. Additionaly, QGIS doesn't support "order by" clauses for DB layers.

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

CREATE TABLE osm_roads_ordered AS (SELECT * FROM osm_roads ORDER BY z_order ASC, st_length(geometry) ASC);
ALTER TABLE osm_roads_ordered ADD COLUMN unique_id serial;
CREATE INDEX osm_roads_ordered_geometry_sidx ON osm_roads_ordered 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_roads_ordered', 'geometry', ST_CoordDim(geometry), ST_SRID(geometry), GeometryType(geometry)
FROM public.osm_roads_ordered LIMIT 1;
CREATE UNIQUE INDEX osm_roads_ordered_unqidx ON osm_roads_ordered(unique_id);

CREATE TABLE osm_roads_gen0_ordered AS (SELECT * FROM osm_roads_gen0 ORDER BY z_order ASC, st_length(geometry) ASC);
ALTER TABLE osm_roads_gen0_ordered ADD COLUMN unique_id serial;
CREATE INDEX osm_roads_gen0_ordered_geometry_sidx ON osm_roads_gen0_ordered 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_roads_gen0_ordered', 'geometry', ST_CoordDim(geometry), ST_SRID(geometry), GeometryType(geometry)
FROM public.osm_roads_gen0_ordered LIMIT 1;
CREATE UNIQUE INDEX osm_roads_gen0_ordered_unqidx ON osm_roads_gen0_ordered(unique_id);

CREATE TABLE osm_roads_gen1_ordered AS (SELECT * FROM osm_roads_gen1 ORDER BY z_order ASC, st_length(geometry) ASC);
ALTER TABLE osm_roads_gen1_ordered ADD COLUMN unique_id serial;
CREATE INDEX osm_roads_gen1_ordered_geometry_sidx ON osm_roads_gen1_ordered 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_roads_gen1_ordered', 'geometry', ST_CoordDim(geometry), ST_SRID(geometry), GeometryType(geometry)
FROM public.osm_roads_gen1_ordered LIMIT 1;
CREATE UNIQUE INDEX osm_roads_gen1_ordered_unqidx ON osm_roads_gen1_ordered(unique_id);


Raster

Create GDAL VRT from tile index:

/opt/qgisms/bin/gdalbuildvrt /benchmarking/wms/2011/data/raster/DEM/bil_16int/dem_bil16int.vrt /benchmarking/wms/2011/data/raster/DEM/bil_16int/dem_bil16int.shp