Kyoto, 03/26/03Kyoto, 03/26/03Martin Pfeifle, Database Group, University of MunichMartin Pfeifle, Database Group, University of Munich

Spatial Query ProcessingSpatial Query Processingfor High Resolutionsfor High Resolutions

Hans-Peter Kriegel, Martin Pfeifle,Marco Pötke, Thomas Seidl

8th International DASFAA-Conference 26 - 28 March, 2003, Kyoto, Japan

Database GroupInstitute for Computer ScienceUniversity of Munich, Germany

Kyoto, 03/26/03Kyoto, 03/26/03Martin Pfeifle, Database Group, University of MunichMartin Pfeifle, Database Group, University of Munich

Outline of the TalkOutline of the Talk

1.) Introduction1.) Introduction

2.) RI-Tree2.) RI-Tree

3.) HRI-Approach3.) HRI-Approach

4.) Experimental Evaluation 4.) Experimental Evaluation

5.) Conclusions5.) Conclusions

Kyoto, 03/26/03Kyoto, 03/26/03Martin Pfeifle, Database Group, University of MunichMartin Pfeifle, Database Group, University of Munich

1.) Introduction1.) Introduction

2.) RI-Tree2.) RI-Tree

3.) HRI-Approach3.) HRI-Approach

4.) Experimental Evaluation 4.) Experimental Evaluation

5.) Conclusions5.) Conclusions

Spatial DatabasesSpatial Databases

Voxelized ObjectsVoxelized Objects

High ResolutionsHigh Resolutions

Outline of the TalkOutline of the Talk

Kyoto, 03/26/03Kyoto, 03/26/03Martin Pfeifle, Database Group, University of MunichMartin Pfeifle, Database Group, University of Munich

Spatial Database Management SystemsSpatial Database Management Systems

collision queriescollision queries

System Requirements:System Requirements:

EffectivityEffectivity

EfficiencyEfficiency

ScalabilityScalability

Concurrency ControlConcurrency Control

RecoveryRecovery

complexcomplex

box queriesbox queries

spatialspatialobjectsobjects

Spatial DatabaseSpatial DatabaseManagement SystemsManagement Systems

(based on extensible ORDBMS)(based on extensible ORDBMS)

Kyoto, 03/26/03Kyoto, 03/26/03Martin Pfeifle, Database Group, University of MunichMartin Pfeifle, Database Group, University of Munich

Voxelized Spatial ObjectsVoxelized Spatial Objects

triangulated objects

voxel sequence

1.) linearization of the data space – grid-approximation – space filling curve

interval sequence

2.) interval sequence

– bottom-up or top-down – size-bound or error-bound

Kyoto, 03/26/03Kyoto, 03/26/03Martin Pfeifle, Database Group, University of MunichMartin Pfeifle, Database Group, University of Munich

Query Processing for High ResolutionsQuery Processing for High Resolutions

Filter - Step

Candidate Set forApplication Specific

Refinement Step

Spatial - DB

RefinementStep

ORDBMS

CAD

Result

e.g. RI-Tree

High Resolution

HRI-Approach

Kyoto, 03/26/03Kyoto, 03/26/03Martin Pfeifle, Database Group, University of MunichMartin Pfeifle, Database Group, University of Munich

1.) Introduction1.) Introduction

2.) RI-Tree2.) RI-Tree

3.) HRI-Approach3.) HRI-Approach

4.) Experimental Evaluation 4.) Experimental Evaluation

5.) Conclusions5.) Conclusions

Relational Interval TreeRelational Interval Tree [VLDB 00][VLDB 00]

Extensible IndexingExtensible Indexing

Outline of the TalkOutline of the Talk

Kyoto, 03/26/03Kyoto, 03/26/03Martin Pfeifle, Database Group, University of MunichMartin Pfeifle, Database Group, University of Munich

Relational Interval Tree (RI-Tree)Relational Interval Tree (RI-Tree)

3a 15a12c

5c 15a

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

15

8

1 3 5 7 13119

2 6 10 14

4 12

AB

C D

7b1b

13d 13d

Foundation: Interval Tree [Edelsbrunner 1980]

Kyoto, 03/26/03Kyoto, 03/26/03Martin Pfeifle, Database Group, University of MunichMartin Pfeifle, Database Group, University of Munich

D

Foundation: Interval Tree [Edelsbrunner 1980]

1. Idea: Virtualization of the Primary Structure

3a 15a12c

5c 15a

15

8

1 3 5 7 13119

2 6 10 14

4 12

7b1b

13d 13d

151 3 5 7 13119

2 6 10 14

4 12

8

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

1 2h – 1

root = 2h–1

Relational Interval Tree (RI-Tree)Relational Interval Tree (RI-Tree)

Kyoto, 03/26/03Kyoto, 03/26/03Martin Pfeifle, Database Group, University of MunichMartin Pfeifle, Database Group, University of Munich

Foundation: Interval Tree [Edelsbrunner 1980]

1. Idea: Virtualization of the Primary Structure

2. Idea: Managing of the Secondary Structure by 2 B+-treesnode lower

488

13

135

13

id

bacd

node upper

488

13

7121513

id

bcad

lowerIndex upperIndex

Relational Interval Tree (RI-Tree)Relational Interval Tree (RI-Tree)

root = 2h–1

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

8

151 3 5 7 13119

2 6 10 14

4 123a 15a12c

5c 15a7b1b

13d 13d

AB

C D

Kyoto, 03/26/03Kyoto, 03/26/03Martin Pfeifle, Database Group, University of MunichMartin Pfeifle, Database Group, University of Munich

1. Procedural Step

2. Declarative Step

RI-Tree: Intersection QueryRI-Tree: Intersection Query

Kyoto, 03/26/03Kyoto, 03/26/03Martin Pfeifle, Database Group, University of MunichMartin Pfeifle, Database Group, University of Munich

1. Procedural Step

16 = root

24 = fork

25

28

26

20

22

23

2. Declarative Step

– posting one single

SQL-statement

– number of I/O-accesses:

O(h·logbn + r/b)

arithmetic traversal

of the primary structure

collecting the visited nodes

in transient tables

number of I/0-accesses:

0

select id from upperIndex i, :leftNodes left

where i.node = left.node and i.upper >= :Q.lower

union all

select id from lowerIndex i, :rightNodes right

where i.node = right.node and i.lower <= :Q.upper

union all

select id from upperIndex i

where i.node between :Q.lower and :Q.upper

16 20

28 26

22-25

upper = 2522 = lowerquery Q

RI-Tree: Intersection QueryRI-Tree: Intersection Query

Kyoto, 03/26/03Kyoto, 03/26/03Martin Pfeifle, Database Group, University of MunichMartin Pfeifle, Database Group, University of Munich

User-defined Index Structure[VLDB 00] [SSTD 01]

Extensible Indexing FrameworkObject-relational interface for indexmaintenance and querying functions.

Relational ImplementationMapping to built-in indexes (B+-trees);SQL-based query processing

Physical ImplementationBlock-Manager, Caches, Locking, Logging, …

User-defined Cost Model[SSDBM 02]

Extensible Optimization FrameworkObject-relational interface for selectivity estimation and cost prediction functions.

Relational ImplementationMapping to built-in statistics facilities;SQL-based evaluation of cost model

Declarative EmbeddingObject-relational DML and DDL

RI-Tree: Integration into an ORDBMSRI-Tree: Integration into an ORDBMS

Kyoto, 03/26/03Kyoto, 03/26/03Martin Pfeifle, Database Group, University of MunichMartin Pfeifle, Database Group, University of Munich

Outline of the TalkOutline of the Talk

1.) Introduction1.) Introduction

2.) RI-Tree2.) RI-Tree

3.) HRI-Approach3.) HRI-Approach

4.) Experimental Evaluation 4.) Experimental Evaluation

5.) Conclusions5.) Conclusions

Grey IntervalsGrey Intervals

Storage of Grey Storage of Grey Intervals in an Intervals in an ORDBMS ORDBMS

Intersection QueriesIntersection Queries based on based on Grey IntervalsGrey Intervals

Kyoto, 03/26/03Kyoto, 03/26/03Martin Pfeifle, Database Group, University of MunichMartin Pfeifle, Database Group, University of Munich

Grey IntervalsGrey Intervals

A voxelized “real-world” object

Black object interval sequence (obtained from encoding voxels via a space filling curve)

0 8 24 32 40 48 16

Grey object interval sequence (obtained from grouping black intervals together)

Ogrey = ( id,  (2, 5), (7, 7),  (12, 12), (15, 15),(18, 18),  (22, 29),  (36, 36), (39, 39),(42, 42), (46, 47))(2, 5), (7, 7)

Grey Interval

Kyoto, 03/26/03Kyoto, 03/26/03Martin Pfeifle, Database Group, University of MunichMartin Pfeifle, Database Group, University of Munich

Ln

L

2log)1(2

+ -

bit-oriented bit-oriented approachapproach

offset-oriented offset-oriented approachapproach

Storage of a Grey Object Interval SequenceStorage of a Grey Object Interval Sequence

1 0 1 0 1 1 O (L)

......

1 0 1 0 1 1

......

w1

w1w2w3w4 O (n * log L)

w2 w3 w4

table schema GreyIntervals (id, intervalsequence)

BLOB aggregated information H(Igrey), D(Igrey), G(Igrey)

Kyoto, 03/26/03Kyoto, 03/26/03Martin Pfeifle, Database Group, University of MunichMartin Pfeifle, Database Group, University of Munich

Multi-Step Query ProcessingMulti-Step Query Processingfor Intersection Queriesfor Intersection Queries

A1 Q1

A3 Q2

A2 Q1

B3 Q1

B2 Q1

C1 Q1

C2 Q1

C2 Q2

C3 Q2

D1 Q1

A1 Q1

A3 Q2

C1 Q1

DB

Query Q

A1 Q1

A3 Q2A2 Q1

+B3 Q1

C1 Q1C2 Q1

B2 Q1

B2 Q1

?

result set

Interval Index

1. filter step

FASTGREYTEST

2. filter step

BLOBTEST

3. filter step

D1 Q1

ACB

Q1 Q2

A1 A2 A3 AB1 B2 B3 B

C1 C2 C3 C4 C

D1 D2 D

E1 E...

B3 Q1B3 Q1 ++

Kyoto, 03/26/03Kyoto, 03/26/03Martin Pfeifle, Database Group, University of MunichMartin Pfeifle, Database Group, University of Munich

2. filter step: 2. filter step: FAST-GREY-TESTFAST-GREY-TEST

black interval + grey intervalblack interval covers the starting or end point of the grey interval

grey interval + grey intervalshare the same starting or end point

grey interval + grey intervalnumber of white cells is smaller than the intersecting area

... ...black interval + black interval

Lgrey interval + other intervalgrey interval has only two black cells and the other interval is completelyincluded in this interval

grey interval + grey intervalgrey intervals have only two black cells and the intervals have no common starting or end point

inte

rsec

tion

noin

ters

ectio

n

long black interval + grey intervalmaximum gap of the grey interval smaller than the length of the black interval

intersection test based on the aggregated information of the grey intervals.

Kyoto, 03/26/03Kyoto, 03/26/03Martin Pfeifle, Database Group, University of MunichMartin Pfeifle, Database Group, University of Munich

3. filter step: 3. filter step: BLOB-TESTBLOB-TEST intersection test based on the examination of the black interval sequence

... ...

I2

I1

runtime analysisruntime analysis

bit-oriented approach

finding the starting point A of the interlacing area

testing the L voxels

LoLA

O (1)

O (L)

Kyoto, 03/26/03Kyoto, 03/26/03Martin Pfeifle, Database Group, University of MunichMartin Pfeifle, Database Group, University of Munich

3. filter step: 3. filter step: BLOB-TESTBLOB-TEST

... ...L

I2

I1

w1 w2 w3 w4 w5 w6 w7 w8

A

w6w4 w7 n1 = 8 nL1 = 1

nL2 = 3

runtime analysisruntime analysis

offset-oriented approach

finding the starting point of the interlacing area

testing the nL1 resp. nL2 intervals

O (log n1)

O (nL1+nL2)

intersection test based on the examination of the black interval sequence

bit-oriented approach

finding the starting point A of the interlacing area

testing the L voxels

O (1)

O (L)

Kyoto, 03/26/03Kyoto, 03/26/03Martin Pfeifle, Database Group, University of MunichMartin Pfeifle, Database Group, University of Munich

SQL-Statement SQL-Statement

SELECT candidates.id FROM

( SELECT db.id AS id,

table (AggInfos(db.intervalsequence, q.intervalsequence)) AS ctable

FROM GreyIntervals db, :GreyQueryIntervals q

WHERE intersects (hull(db.intervalsequence), hull(q.intervalsequence))

GROUP BY db.id

) candidates

WHERE EXISTS

( SELECT 1

FROM GreyIntervals db, :GreyQueryIntervals q, candidates.ctable ctable

WHERE db.rowid = ctable.dbrowid AND q.rowid = ctable.qrowid AND

blobintersection (db.intervalsequence, q.intervalsequence)                 

)

intersects (hull(db.intervalsequence), hull(q.intervalsequence))

table (AggInfos(db.intervalsequence, q.intervalsequence))

blobintersection (db.intervalsequence, q.intervalsequence)

Kyoto, 03/26/03Kyoto, 03/26/03Martin Pfeifle, Database Group, University of MunichMartin Pfeifle, Database Group, University of Munich

Outline of the TalkOutline of the Talk

1.) Introduction1.) Introduction

2.) RI-Tree2.) RI-Tree

3.) HRI-Approach3.) HRI-Approach

4.) Experimental Evaluation4.) Experimental Evaluation

5.) Conclusions5.) Conclusions

Kyoto, 03/26/03Kyoto, 03/26/03Martin Pfeifle, Database Group, University of MunichMartin Pfeifle, Database Group, University of Munich

Experimental EvaluationExperimental Evaluation

Examination of the HRI approach based on different MAXGAP Parameters: 10 100 1,000 10,000 100,000 1,000,000 Comparison between the HRI approach and the spatial variant of the RI-tree [SSTD 01]

CAR PLANE

approx. 200 parts approx. 10.000 partsapprox. 7 million intervals approx. 9 million intervalsresolution: 33 bit (0 .. 8.589.934.591) resolution: 42 bit (0 .. 4.398.046.511.103)

Kyoto, 03/26/03Kyoto, 03/26/03Martin Pfeifle, Database Group, University of MunichMartin Pfeifle, Database Group, University of Munich

ExperimentsExperimentsC

AR

1

10

100

1000

10000

100000

1000000

10000000

Interval-Lengthnu

mb

er o

f in

terv

als

M=0 (RI-Tree)

M=10^1

M=10^2

M=10^3

M=10^4

M=10^5

M=10^6

number of interval decreases with increasing MAXGAP - parameter

average interval length increases with increasing MAXGAP - parameter

- Interval Distribution- Interval Distribution

Kyoto, 03/26/03Kyoto, 03/26/03Martin Pfeifle, Database Group, University of MunichMartin Pfeifle, Database Group, University of Munich

Experiments Experiments – Secondary Storage– Secondary Storage

0

10

20

30

40

50

0 10 100 1000 10000 100000 1000000MAXGAP

CAR

PLANE

PLANE

0

20

40

60

80

100

120

140

160

0 1000 1000000MAXGAP

nu

mb

er

of

blo

ck

s

[x1

00

0]

indexes

table

With the HRI method we can improve the storage requirement by an order of magnitude.

(RI-Tree)

Kyoto, 03/26/03Kyoto, 03/26/03Martin Pfeifle, Database Group, University of MunichMartin Pfeifle, Database Group, University of Munich

0.0

0.2

0.4

0.6

0.8

10 100 1000 10000 100000 1000000Maxgap

time [

sec]

3.FilterStep

2.FilterStep

1.FilterStep

Experiments Experiments – Runtime for collision queries– Runtime for collision queriesP

LA

NE

CA

R

01020304050

10 100 1000 10000 100000 1000000Maxgap

time [

sec]

3.FilterStep

2.FilterStep

1.FilterStep

Kyoto, 03/26/03Kyoto, 03/26/03Martin Pfeifle, Database Group, University of MunichMartin Pfeifle, Database Group, University of Munich

0.0

0.2

0.4

0.6

0.8

10 100 1000 10000 100000 1000000Maxgap

time [

sec]

3.FilterStep

2.FilterStep

1.FilterStep

Experiments Experiments – Runtime for collision queries– Runtime for collision queriesP

LA

NE

CA

R

01020304050

10 100 1000 10000 100000 1000000Maxgap

time [

sec]

3.FilterStep

2.FilterStep

1.FilterStep

RI-tree

RI-tree

huge part (PLANE)

RI-tree: 316.5 s HRI: 2.2 s (Maxgap=10,000)

Kyoto, 03/26/03Kyoto, 03/26/03Martin Pfeifle, Database Group, University of MunichMartin Pfeifle, Database Group, University of Munich

Outline of the TalkOutline of the Talk

1.) Introduction1.) Introduction

2.) RI-Tree2.) RI-Tree

3.) HRI-Approach3.) HRI-Approach

4.) Experimental Evaluation4.) Experimental Evaluation

5.) Conclusions5.) Conclusions

Kyoto, 03/26/03Kyoto, 03/26/03Martin Pfeifle, Database Group, University of MunichMartin Pfeifle, Database Group, University of Munich

What are the advantages of the What are the advantages of the HRIHRI approach? approach?

good secondary storage utilization

ConclusionsConclusions

What is the What is the HRIHRI approach? approach?

the HRI approach is a multi-step index structure

suitable for spatial query processing for high resolutions

small main memory footprint

improved query response time behaviour

Kyoto, 03/26/03Kyoto, 03/26/03Martin Pfeifle, Database Group, University of MunichMartin Pfeifle, Database Group, University of Munich

Any questions?

? ?

?

??

?

?