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_ADDR ESS"
("LOCATION" )
EXTEND USING "DB2GSE "."SPATIAL_INDE X" (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_dista nce(db2gse.st_p oint( 'POINT(
-33.71492167 151.1091933 )' ,1),location )
from gps.gps_paf_add ress addr
where
db2gse.st_dista nce(db2gse.st_p oint('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_dista nce(db2gse.st_p oint( 'POINT(
-33.71492167 151.1091933 )' ,1),location )
from gps.gps_paf_add ress addr
where
db2gse.ST_Conta ins(
db2gse.ST_Polyg on('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_add ress(location) \
<
USING GRID SIZES (0.0001) SHOW HISTOGRAM WITH 10 BUCKETS"
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