[Gfoss] rasterlite: prove

[Marco Curreli]

Ciao a tutti,

ho fatto alcune prove con rasterlite.

Ho provato a combinare assieme due vestizioni della stessa mappa, in
formato geotiff, nella stesa piramide, in modo da visualizzare mappe con
diversi dettagli a scale diverse. Per farlo ho creato un file sql con un
serie di istruzioni, sfruttando le mie poche conoscenze del linguaggio
sql. Funziona a meraviglia.

Riporto il codice sql:

BEGIN ;
	CREATE VIEW IF NOT EXISTS base250_rm AS
	SELECT base250_rasters.id, raster, source_name, tile_id, width, height, pixel_x_size, pixel_y_size, geometry
	FROM base250_rasters
	JOIN base250_metadata ON (base250_rasters.id = base250_metadata.id) AND (base250_metadata.pixel_x_size < 160) ;

	CREATE VIEW IF NOT EXISTS base500_rm AS
	SELECT base500_rasters.id, raster, source_name, tile_id, width, height, pixel_x_size, pixel_y_size, geometry
	FROM base500_rasters
	JOIN base500_metadata ON (base500_metadata.id = base500_rasters.id) AND (base500_metadata.pixel_x_size >= 160) ;
COMMIT ;

ATTACH DATABASE  "/home/marco/quantumnik/base10m/base.sqlite" AS "base" ;

BEGIN ;
	CREATE TABLE base.base_metadata (
	id INTEGER NOT NULL PRIMARY KEY,
	source_name TEXT NOT NULL,
	tile_id INTEGER NOT NULL,
	width INTEGER NOT NULL,
	height INTEGER NOT NULL,
	pixel_x_size DOUBLE NOT NULL,
	pixel_y_size DOUBLE NOT NULL, "geometry" POLYGON) ;

	INSERT INTO base.base_metadata 
	SELECT * FROM base250_metadata
	WHERE ROWID = 0 ;

	CREATE TABLE base.base_rasters (
	id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	raster BLOB NOT NULL) ;
COMMIT ;

BEGIN ;
	INSERT INTO base.base_metadata 
		( "source_name", "tile_id", "width", "height", "pixel_x_size", "pixel_y_size", "geometry")
	SELECT "source_name", "tile_id", "width", "height", "pixel_x_size", "pixel_y_size", "geometry"
	FROM base250_rm
	ORDER BY ROWID	;

	INSERT INTO base.base_rasters (raster) SELECT raster FROM base250_rm ;
COMMIT ;

BEGIN ;
	INSERT INTO base.base_metadata 
		( "source_name", "tile_id", "width", "height", "pixel_x_size", "pixel_y_size", "geometry")
	SELECT "source_name", "tile_id", "width", "height", "pixel_x_size", "pixel_y_size", "geometry"
	FROM base500_rm
	ORDER BY ROWID	;

	INSERT INTO base.base_rasters (raster) SELECT raster FROM base500_rm ;
COMMIT ;

BEGIN ;
	INSERT INTO base.geometry_columns
		("f_table_name", "f_geometry_column", "type", "coord_dimension", "srid", "spatial_index_enabled")
	SELECT "f_table_name" AS base_metadata, "f_geometry_column", "type", "coord_dimension", "srid", "spatial_index_enabled"
	FROM geometry_columns
	WHERE ROWID = 1
	ORDER BY ROWID ;

       	CREATE TABLE base.idx_base_metadata_geometry AS
	SELECT * FROM idx_base250_metadata_geometry
	ORDER BY ROWID ;

	CREATE TABLE base.idx_base_metadata_geometry_node AS
	SELECT * FROM idx_base250_metadata_geometry_node
	ORDER BY ROWID ;

	CREATE TABLE base.idx_base_metadata_geometry_parent AS
	SELECT * FROM idx_base250_metadata_geometry_parent
	ORDER BY ROWID ;

	CREATE TABLE base.idx_base_metadata_geometry_rowid AS
	SELECT * FROM idx_base250_metadata_geometry_rowid
	ORDER BY ROWID ;
COMMIT ;