Back to RasterLite2 Tutorials index
Tutorial: building and testing the Planet Earth sample - full SQL
We've already seen on the previous tutorial how to create and populate the earth.sqlite DB using the command shell and rl2tool.In this second example we'll perform yet again the same identical same task, but this time we'll use an alternative approach fully based on executing SQL statements.
So you'll start this tutorial by opening a command shell and then launching the standard sqlite3 CLI front-end tool.
$ export "SPATIALITE_SECURITY=relaxed"
$ sqlite3 earth2.sqlite
SQLite version 3.8.6 2014-08-15 11:46:33
Enter ".help" for usage hints.
sqlite> .null NULL
sqlite> SELECT load_extension('mod_rasterlite2');
NULL
sqlite> SELECT load_extension('mod_spatialite');
NULL
sqlite> SELECT InitSpatialMetadata(1);
1
sqlite> SELECT CreateRasterCoveragesTable();
1
sqlite> SELECT CreateStylingTables();
1
sqlite>
- the first export directive simply sets an environment variable for the current shell session.
And remember: explicitly setting SPATIALITE_SECURITY=relaxed is always strictly required in order to enable any SQL function directly importing or exporting some external file from the DB-file to the local file-system (both directions).
Please note: the export directive is strictly intended for POSIX systems (e.g. Linux, Unix, Mac Os X and many others); if you are using instead some other non-POSIX system (e.g. Windows) you'll be probably required to use a different syntax; please check the appropriate platform specific documentation. - SELECT load_extension(): this SQL statement will load a dynamic extension to SQLite:
- Please note: both RasterLite2 and SpatiaLite are required: RasterLite2 always depends on SpatiaLite.
- Please note well: the load order is absolutely relevant; you are always expected to load RasterLite2 first and SpatiaLite second.
A short rationale: SQLite nicely supports different implementations corresponding to the same function name;- when directly using the C-API any SQL function declared by a dynamic loadable module will always override a previously declared SQL function of the same name.
- anyway the SQL function load_extension works exactly in the opposite way: any SQL function declared by a dynamic module will never override an already declared SQL function of the same name.
- so loading first LibrasterLite2 is always strictly required, because there are few SQL functions sharing the same name and presenting just a minimal default implementation in SpatiaLite whilst a more specialized implementation is made available by RasterLite2.
- SELECT InitSpatialMetadata(1): this is the standard way to fully initialize a SpatiaLite own DB-file.
Please note: any RasterLite2 DB-file is always expected to be a full fledged SpatiaLite DB-file. - SELECT CreateRasterCoveragesTable(): this will create all tables required by RasterLite2.
- so you've now created and correctly initialized a brand new DB-file supporting both SpatiaLite and RasterLite2; and you are now ready to import raster data from the external datasources.
sqlite> SELECT RL2_CreateCoverage('TrueMarble', 'UINT8', 'RGB', 3, 'JPEG', 80, 512, 512, 4326, 0.0166666666666667);
1
sqlite>
- SELECT RL2_CreateCoverage(): this SQL statement will create a new Raster Coverage named TrueMarble.
- this Coverage is intended to support RGB pixels of the most common type ('UINT8', 'RGB', 3).
- an hi-quality JPEG compression is requested ('JPEG', 80).
- each Tile of this Coverages must by 512 x 512 pixels.
- the intended Reference System is EPSG=4326 (aka WGS 84).
- each pixel at the base level resolution exactly covers 0.0166666666666667 on both directions (square pixel).
sqlite> SELECT RL2_LoadRaster('TrueMarble', 'TrueMarble.2km.21600x10800.tif');
------------------
Importing: TrueMarble.2km.21600x10800.tif
Image Size (pixels): 21600 x 10800
SRID: 4326
LowerLeft Corner: X=-180.00 Y=-90.00
UpperRight Corner: X=180.00 Y=90.00
Pixel resolution: X=0.01666666666666666 Y=0.01666666666666666
>> Image successfully imported in: 1 mins 03 secs
1
sqlite>
- SELECT RL2_LoadRaster(): this SQL statement will populate the TrueMarble Coverage by importing raster data from the external GeoTIFF file.
- Please note: no Pyramid Levels have been built since now.
- you are now ready to complete this tutorial: you have just to create and populate the two other Coverages NaturalEarth and Etopo1.
sqlite> SELECT RL2_CreateCoverage('NaturalEarth', 'UINT8', 'RGB', 3, 'JPEG', 80, 512, 512, 4326, 0.0166666666666667);
1
sqlite> SELECT RL2_LoadRaster('NaturalEarth', 'NE1_HR_LC_SR_W_DR.tif');
------------------
Importing: NE1_HR_LC_SR_W_DR.tif
Image Size (pixels): 21600 x 10800
SRID: 4326
LowerLeft Corner: X=-180.00 Y=-90.00
UpperRight Corner: X=180.00 Y=90.00
Pixel resolution: X=0.01666666666667 Y=0.01666666666667
>> Image successfully imported in: 2 mins 09 secs
1
sqlite> SELECT RL2_CreateCoverage('Etopo1', 'UINT8', 'RGB', 3, 'JPEG', 80, 512, 512, 4326, 0.0166666666666667);
1
sqlite> SELECT RL2_LoadRaster('Etopo1', 'color_etopo1_ice_full.tif');------------------
Importing: color_etopo1_ice_full.tif
Image Size (pixels): 21600 x 10800
SRID: 4326
LowerLeft Corner: X=-180.01 Y=-89.99
UpperRight Corner: X=179.99 Y=90.01
Pixel resolution: X=0.01666666666667 Y=0.01666666666667
>> Image successfully imported in: 2 mins 09 secs
1
sqlite> SELECT RL2_Pyramidize('TrueMarble');
1
sqlite> SELECT RL2_Pyramidize('NaturalEarth');
1
sqlite> SELECT RL2_Pyramidize('Etopo1');
1
sqlite> SELECT RL2_SetCoverageInfos('truemarble', 'TrueMarble 2km', 'worldwide synthetic collage of Landsat cloud-free scenes');
1
sqlite> SELECT RL2_SetCoverageInfos('naturalearth', 'Natural Earth 1', 'worldwide land cover (Shaded Relief, Water and Drainages)');
1
sqlite> SELECT RL2_SetCoverageInfos('etopo1', 'Etopo1', 'NOAA Global Relief Model');
1
sqlite> .quit
$
Completing the final steps of this tutorial isn't at all difficult; you are just expected to repeat the same steps we've already commented in the previous paragraph, just adjusting the Coverage's name and the GeoTIFF pathname as appropriate.
|
Back to RasterLite2 Tutorials index