Transcript PowerPoint-presentatie

Raster data in DBMS
(mainly Oracle GeoRaster)
Theo Tijssen
Delft
University of
Technology
Challenge the future
Overview
• Introduction: why, why not
• Raster support
• How it works
• Experiences
2
Why raster NOT in DBMS ?
•
•
Rasters are (usually) static, do not need updates
Rasters contain simply values, no references
 no need for the advanced multi-user update and transaction
mechanisms of DBMS
•
•
Rasters are large and with DBMS-overhead even larger
Until recently rasters hardly supported by DBMS
(similar to early days of non-‘vector support’)
3
Why store raster in DBMS ?
Similar to reasons for using DBMS in the first place:
• Uniform, shared access to data (SQL)
• Access from different clients/applications
• Store data reliably:
•
•
•
•
Ensures integrity of data
Security, authorization
Availability (replication)
Backup, recovery
• Scalability
But how about performance?
4
Why raster in a geo-DBMS ?
• Use generic DBMS facilities for efficient spatial access:
indexing and clustering
• Generic raster functions available to all users, in same manner
• Raster data (in DBMS) has metadata with a rich structure,
can be used in queries
• Keeps the (generic) operations close to the data,
where the data should be: in a database
• Integrated support of all (spatial) data in one DBMS:
files tend to ‘get lost’, or separated from metadata …
5
Testing raster data support
• Functionality
•
•
•
•
•
•
•
Data type(s)
Geo-referencing
Multi-resolution / level of detail (pyramids)
Available operations
APIs
Input / output formats
Client tools
• Performance
•
•
•
•
Physical storage options, blocking (tiles), compression
Data loading
Indexing
Data retrieval, queries (rectangles, polygons)
6
Raster data support in DBMS
Several initiatives for storing raster data in DBMS, e.g.:
• Oracle
• GeoRaster - production
• PostgreSQL/PostGIS
• PostGIS Raster – in beween Beta and production
7
GeoRaster data model
• Component-based
• Logically layered
• Multi-dimensional
8
Multi-dimensional options
9
GeoRaster coordinate spaces
10
Mapping between cell and model
coordinate systems
• Spatial reference system: GeoRaster SRS
• Temporal reference system: GeoRaster TRS
• Band reference system: GeoRaster BRS
11
GeoRaster datatype (for metadata)
CREATE TYPE sdo_georaster AS OBJECT
(
rasterType
NUMBER,
spatialExtent
SDO_GEOMETRY,
rasterDataTable VARCHAR2(32),
rasterID
NUMBER,
metadata
XMLType
);
rasterType: [d] [b] [t] [gt]
(a bit like sdo_geometry GTYPE):
20001: 2D, 1 layer (or band), GeoRaster type 01
30001: 3D (= voxel), 1 layer, GeoRaster type 01
21001: 2D, more than 1 layer, GeoRaster type 01
30101: 3D, 1 layer, with time ‘dimension’, GeoRaster type 01
Current version: only 2D with 1 or more layers implemented
12
XML metadata (schema)
<xsd:element name="georasterMetadata">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="objectInfo" type="objectDescriptionType"/>
<xsd:element name="rasterInfo" type="rasterDescriptionType"/>
<xsd:element name="spatialReferenceInfo"
type="rasterSpatialReferenceSystemType" minOccurs="0"/>
<xsd:element name="temporalReferenceInfo"
type="rasterTemporalReferenceSystemType" minOccurs="0"/>
<xsd:element name="bandReferenceInfo" type="rasterBandReferenceSystemType"
minOccurs="0"/>
<xsd:element name="layerInfo" type="layerDescriptionType"
maxOccurs="unbounded"/>
<xsd:element name="sourceInfo" type="xsd:string" minOccurs="0"
maxOccurs="unbounded"/>
.....
13
GeoRaster datatype for data (cell values)
CREATE TYPE sdo_raster AS OBJECT
(
rasterID
NUMBER,
pyramidLevel
NUMBER,
bandBlockNumber
NUMBER,
rowBlockNumber
NUMBER,
columnBlockNumber
NUMBER,
blockMBR
SDO_GEOMETRY,
rasterBlock
BLOB
);
14
GeoRaster physical storage
15
Raster data in an Oracle database
16
Layers, bands in Raster Data Table
17
Cell coordinate systems
18
Storage parameters
• blocking, blocksize
• e.g. blocksize = (200, 350, 3)
• cellDepth
• from 1-bit to 64bit_real
• bitmapmask
• can be associated with any layer
• pyramid
• resampling methods: nearest neighbor, bilinear interpolation (4cell),
cubic convolution (16cell), average4, average16
• compression
• JPEG-B, JPEG-F, deflate (zlib) or as plugins: MrSID, Jpeg2000
• quality
• interleaving
• BSQ, BIL, BIP
19
Geo-referencing
Functional fitting model (e.g. using GCP) based on:
20
Create GeoRaster object & data table
create table RDAM_TEST
(
image_id
number primary key,
image_desc varchar2(50),
image
sdo_georaster
);
create table RDAM_TEST_RAST
of sdo_raster
(
primary key (rasterid, pyramidlevel, bandblocknumber,
rowblocknumber, columnblocknumber)
)
tablespace PCTEMP lob(rasterblock) store as securefile (nologging)
;
21
Sample operation: merge images
DECLARE
gr sdo_georaster;
BEGIN
insert into RDAM_TEST (image_id, image_desc, image) values
(
1,
'3D Pilot test image',
sdo_geor.init('RDAM_TEST_RAST')
)
returning image into gr;
sdo_geor.mosaic('RDAM_STD', 'RAST', gr, NULL);
update RDAM_TEST set image = gr where image_id = 1;
commit;
END;
22
Sample operation: compress raster
DECLARE
gr1 sdo_georaster;
gr2 sdo_georaster;
BEGIN
select image into gr1 from RDAM_TEST where image_id = 1;
insert into RDAM_TEST (image_id, image_desc, image) values
(
2,
'3D Pilot test image compressed',
sdo_geor.init('RDAM_TEST_COMPR')
)
returning image into gr2;
sdo_geor.changeformatcopy(gr1, 'compression=deflate', gr2);
update RDAM_TEST set image = gr2 where image_id = 2;
END;
23
Sample operation: add pyramids
DECLARE
gr sdo_georaster;
BEGIN
select image into gr from RDAM_TEST where image_id = 2 for update;
sdo_geor.generatePyramid(gr, 'rLevel=5, resampling=BILINEAR');
update RDAM_TEST set image = gr where image_id = 2;
commit;
END;
24
Test dataset
25
Storage requirements
Test data: set of 12 GeoTIFF images, 24 bit RGB values,
600 mln pixels
original images:
1792 MB
standard GeoRaster:
2061 MB
compressed GeoRaster:
1055 MB (deflate compression)
compressed, 5 pyramid levels: 1541 MB
26
Updating, retrieving raster data
• Updating, e.g:
• SDO_GEOR.changeCellValue
• SDO_GEOR.updateRaster
• Retrieving, e.g:
• SDO_GEOR.getCellValue
• SDO_GEOR.evaluateDouble: interpolated value at location
• SDO_GEOR.getRasterSubset:
subset of raster at specified pyramid level based on
rectangular window or polygon geometry
27
Query process
• Determine required raster blocks based on MBR of query data
and MBRs of raster blocks (using B-tree or R-tree index)
• Retrieve data from required raster blocks
• Rectangle (or point) query:
• remove unwanted cells from data block(s)
• Polygon query:
• determine cells outside polygon and remove
28
Query raster: getRasterSubset
SDO_GEOR.getRasterSubset
(
georaster
IN SDO_GEORASTER,
pyramidLevel
IN NUMBER,
window
IN SDO_GEOMETRY,
layerNumbers
IN VARCHAR2,
rasterBlob
IN OUT NOCOPY BLOB,
storageParam
IN VARCHAR2 DEFAULT NULL,
bgValues
IN SDO_NUMBER_ARRAY DEFAULT NULL,
polygonClip
IN VARCHAR2 DEFAULT NULL
);
29
Sample polygon query
DECLARE
gr
sdo_georaster;
lb
blob;
geom
sdo_geometry;
BEGIN
geom := sdo_geometry(2003, 28992, NULL, sdo_elem_info_array(1,1003,1),
sdo_ordinate_array(93500,436000, 93604,436078, 93651,436065,
93560,436188, 93500,436000));
dbms_lob.createtemporary(lb, TRUE);
select image into gr from RDAM_TEST where image_id = 2;
sdo_geor.getrastersubset(gr, 0 ,geom, NULL, lb, 'compression=NONE', NULL, 'TRUE');
-- Now work on the resulting subset stored in the lb blob.
END;
30
Polygon query
31
APIs, tools, client software
• APIs
• PL/SQL & Java
• Tools
• Java viewer, loader, exporter included
• Client software
• GeoRaster natively supported by e.g. GDAL, FME
32
Additional functionality
In total more than 150 functions
•
•
•
•
Import/export functions
Setting/retrieving metadata
Histograms, colormaps (in metadata)
GeoRaster system data views: e.g. user_sdo_geor_sysdata
(like user_sdo_geom_metadata,
or geometry_columns in postGIS)
• Missing:
• Link to vector data, e.g. no to/from vector conversions
33
GeoRaster conclusions
• Mature, stable support for raster data
• Functionality limited to storing & retrieving of raster data,
no GIS or image processing functions
(should they be included ?)
• No integration with vector data
• Integrated, multi-dimensional support (spatial 3D, time) not yet
implemented
34
PostGIS Raster approach
From the Beta documentation:
“PostGIS Raster implements a minimalistic yet complete raster
data structure, and adopts a simple single-type and single-table
relational schema. This data structure is very similar to the
PostGIS geometry vector type structure, and very different, for
example, from the Oracle Spatial SDO_GEORASTER and
SDO_RASTER raster types. “
35
PostGIS Raster
•Similar to PostGIS vector geometry
• 1 table = 1 raster coverage (possibly non-rectangular)
• 1 row = 1 tile or raster object
•Only 1 new datatype: RASTER
• is self-contained (includes metadata and data)
•High level of vector – raster integration
• many spatial operations can be applied to vector and raster
• lossless vector – raster conversion
•In-DB and Out-DB storage (python loader, import/export by GDAL)
• In-DB: R/W operations, vector/raster operations
• Out-DB: to support R/O applications
36
Flexible
raster
setup
37
PostGIS
raster
type
38
Functions for raster manipulation
and analysis
• Get and set raster properties: upperleft(), setupperleft(), pixelsize(),
setpixelsize(), skew(), setskew(), ..
• Get and set raster band properties: bandpath(), setbandpath(),..
• Get and set pixel values: value(), setvalue(), ..
• Creation: makeemptyraster(), addband(), addrastercolumn(), ..
• Transformation: resample(), ..
• Conversion: toimage(), tojpeg(), totiff(), tokml(), ..
39
Working seamlessly on raster and
vector data
• Seamless raster versions of existing geometry functions:
convexhull(), envelope(), area(), rotate(), scale(), ..
• Raster to vector conversion functions:
dumpaspolygons(), pixelaspolygon(), ..
• Vector to raster conversion functions:
asraster(), toraster(), interpolate(), ..
• Vector-like analysis functions working with rasters:
intersection(), within(), contains(), overlaps(), ..
• Raster-like analysis functions working with vectors:
mapalgebra(), clip(), ..
40