Spatial Extenders index usage

Hi,

I am having trouble getting DB2 to select an index I have created for a
spatial location.

Table definition

create table gps_paf_address
( gnaf_address gnaf_address not null
,GNF_PID varchar(015) not null

,location st_point not null

) in data4k1 index in index4k1 long in long4k1
;


spatial index

CREATE INDEX "GPS "."GPS_PAF_ADDR_S2" ON "GPS
"."GPS_PAF_ADDRESS"
("LOCATION" )
EXTEND USING "DB2GSE "."SPATIAL_INDEX" (0.0001, 0,
0) ;


Tried this no success

select
cast(db2gse.st_x(location)as char(15)) as GNF_LONG

, cast(db2gse.st_y(location)as char(15)) as GNF_LAT
, db2gse.st_distance(db2gse.st_point( 'POINT(
-33.71492167 151.1091933 )' ,1),location )
from gps.gps_paf_address addr
where
db2gse.st_distance(db2gse.st_point('POINT( -33.71492167 151.1091933
)' ,1),location ) < 0.002 selectivity 0.0000001


Tried this no success

select
cast(db2gse.st_x(location)as char(15)) as GNF_LONG

, cast(db2gse.st_y(location)as char(15)) as GNF_LAT
, db2gse.st_distance(db2gse.st_point( 'POINT(
-33.71492167 151.1091933 )' ,1),location )
from gps.gps_paf_address addr
where
db2gse.ST_Contains(
db2gse.ST_Polygon('polygon(( -33.714 151.10
, -33.714 151.12
, -33.715 151.12
, -33.715 151.10
, -33.714 151.10
))',1),
location
) = 1
selectivity 0.0000001


I have performed a Runstats and the grid size has the following stats

[/home/db2inst1] : TATISTICS FOR COLUMN gps.gps_paf_address(location) \
<[color=blue]
> USING GRID SIZES (0.0001) SHOW HISTOGRAM WITH 10 BUCKETS"[/color]


Number of Rows: 3895220
Number of non-empty Geometries: 3895220
Number of empty Geometries: 0
Number of null values: 0

Extent covered by data:
Minimum X: -37.375902
Maximum X: -28.165696
Minimum Y: 141.011252
Maximum Y: 153.636718



Grid Level 1
------------

Grid Size : 0.0001
Number of Geometries : 3895220
Number of Index Entries : 3895220

Number of occupied Grid Cells : 2326459
Index Entry/Geometry ratio : 1.000000
Geometry/Grid Cell ratio : 1.674313
Maximum number of Geometries per Grid Cell: 2473
Minimum number of Geometries per Grid Cell: 1

Index Entries : 1 2 3 4 10
--------------- ------ ------ ------ ------ ------
Absolute : 3895220 0 0 0 0
Percentage (%): 100.00 0.00 0.00 0.00 0.00



Is there some other action I need to perform or what have I done wrong.
DB2 V8 fixpak 10

re: Spatial Extenders index usage

Found the problem, appears to be a bug in DB2. The optimiser seems to
be sensitive to columns in the answer set. I left out some columns
from the select not to confuse matters. These appear to have been
important. One was a transform function against a structured data
type. When I removed this reference it operated successfully. Not
sure why the columns returned (and function operating on columns)
should stop the optimiser using the index.




[color=blue]
> Hi,
>
> I am having trouble getting DB2 to select an index I have created for a
> spatial location.
>
> Table definition
>
> create table gps_paf_address
> ( gnaf_address gnaf_address not null
> ,GNF_PID varchar(015) not null
>
> ,location st_point not null
>
> ) in data4k1 index in index4k1 long in long4k1
> ;
>
>
> spatial index
>
> CREATE INDEX "GPS "."GPS_PAF_ADDR_S2" ON "GPS
> "."GPS_PAF_ADDRESS"
> ("LOCATION" )
> EXTEND USING "DB2GSE "."SPATIAL_INDEX" (0.0001, 0,
> 0) ;
>
>
> Tried this no success
>
> select
> cast(db2gse.st_x(location)as char(15)) as GNF_LONG
>
> , cast(db2gse.st_y(location)as char(15)) as GNF_LAT
> , db2gse.st_distance(db2gse.st_point( 'POINT(
> -33.71492167 151.1091933 )' ,1),location )
> from gps.gps_paf_address addr
> where
> db2gse.st_distance(db2gse.st_point('POINT( -33.71492167 151.1091933
> )' ,1),location ) < 0.002 selectivity 0.0000001
>
>
> Tried this no success
>
> select
> cast(db2gse.st_x(location)as char(15)) as GNF_LONG
>
> , cast(db2gse.st_y(location)as char(15)) as GNF_LAT
> , db2gse.st_distance(db2gse.st_point( 'POINT(
> -33.71492167 151.1091933 )' ,1),location )
> from gps.gps_paf_address addr
> where
> db2gse.ST_Contains(
> db2gse.ST_Polygon('polygon(( -33.714 151.10
> , -33.714 151.12
> , -33.715 151.12
> , -33.715 151.10
> , -33.714 151.10
> ))',1),
> location
> ) = 1
> selectivity 0.0000001
>
>
> I have performed a Runstats and the grid size has the following stats
>
> [/home/db2inst1] : TATISTICS FOR COLUMN gps.gps_paf_address(location) \
> <[color=green]
> > USING GRID SIZES (0.0001) SHOW HISTOGRAM WITH 10 BUCKETS"[/color]
>
>
> Number of Rows: 3895220
> Number of non-empty Geometries: 3895220
> Number of empty Geometries: 0
> Number of null values: 0
>
> Extent covered by data:
> Minimum X: -37.375902
> Maximum X: -28.165696
> Minimum Y: 141.011252
> Maximum Y: 153.636718
>
>
>
> Grid Level 1
> ------------
>
> Grid Size : 0.0001
> Number of Geometries : 3895220
> Number of Index Entries : 3895220
>
> Number of occupied Grid Cells : 2326459
> Index Entry/Geometry ratio : 1.000000
> Geometry/Grid Cell ratio : 1.674313
> Maximum number of Geometries per Grid Cell: 2473
> Minimum number of Geometries per Grid Cell: 1
>
> Index Entries : 1 2 3 4 10
> --------------- ------ ------ ------ ------ ------
> Absolute : 3895220 0 0 0 0
> Percentage (%): 100.00 0.00 0.00 0.00 0.00
>
>
>
> Is there some other action I need to perform or what have I done wrong.
> DB2 V8 fixpak 10[/color]

re: Spatial Extenders index usage

peter.prib@salmat.com.au wrote:
[color=blue]
> Found the problem, appears to be a bug in DB2. The optimiser seems to
> be sensitive to columns in the answer set. I left out some columns
> from the select not to confuse matters. These appear to have been
> important. One was a transform function against a structured data
> type. When I removed this reference it operated successfully. Not
> sure why the columns returned (and function operating on columns)
> should stop the optimiser using the index.[/color]

You may want to have a look here if you're interested in spatial extender
performance tuning:

http://tinyurl.com/aqztc

As for the question why the index was not used originally with the
additional columns, it would be good to have a look at the query plan. I
imagine that the plan is quite different...

--
Knut Stolze
DB2 Information Integration Development
IBM Germany