ST_REFRESH_HASHFILTER procedure

Authorization

The user ID under which this stored procedure is invoked must have the necessary privileges to execute the routine and to successfully execute the SELECT statement from which the data is to be exported.

The stored procedure, which runs as a process that is owned by the Db2® instance owner, must have the necessary privileges on the server machine to create or write to the shape files.

Syntax

Parameter descriptions

schema_name

The name of the schema to which the input table belongs. If a null value is specified, the schema specified in the CURRENT SCHEMA special register is used. This name is case sensitive. The data type for this value is VARCHAR(128) or, if enclosed in double quotation marks, VARCHAR(130).

table_name

The name of the input table. This name is case sensitive. The data type for this value is VARCHAR(128) or, if enclosed in double quotation marks, VARCHAR(130).

This table must have a primary or unique key (ERROR CODE GOES HERE).

spatial_column

The name of the spatial column in the input table that contains the geometries that are to be used to calculate the geohash values for the geohash-filter table. This name is case sensitive. The data type for this value is VARCHAR(128) or, if enclosed in double quotation marks, VARCHAR(130).

The specified geometries must use SRS 4326 (GSE3001N).

filter_table_schema_name

The name of the schema to which the generated geohash-filter table is to belong. If a null value is specified, the schema specified in the CURRENT SCHEMA special register is used. This name is case sensitive. The data type for this value is VARCHAR(128) or, if enclosed in double quotation marks, VARCHAR(130)..

filter_table_name

The name of the geohash-filter table to be generated. This name is case sensitive. The data type for this value is VARCHAR(128) or, if enclosed in double quotation marks, VARCHAR(130).

cover_level

The cover level to be used to compute the geohash values of geometries to populate the geohash-filter-table.

If the type of the input spatial column is ST_POINT, the specified cover level is ignored and all levels are populated. Otherwise, the cover level must be specified (SQL0171N).

Examples

Example 1

Prepare the input table:

CREATE TABLE "gsespt2"."geotable2"(polygon_ID integer not null primary key, geo SYSIBM.ST_POINT) organize by COLUMN;
INSERT INTO "gsespt2"."geotable2"(polygon_ID,geo) VALUES (1001,ST_POINT(1,1));
INSERT INTO "gsespt2"."geotable2"(polygon_ID,geo) VALUES (1002, ST_POINT(2,2));

Create the geohash-filter table:

call st_create_hashfilter('gsespt2','geotable2','geo','fschema','ftable2','m');

Display the contents of the geohash-filter table:

select * from "fschema"."ftable2" order by polygon_ID;

Result:

POLYGON_ID  GH_P  GH_S                  GH_M                  GH_L                  GH_H
1001        -     -4611230339577151488  -4611230820613488640  -4611263805962321920  -4611686018427387904
1002        -     -4609863165587488768  -4609865227171790848  -4609926799822946304  -4611686018427387904

Example 2

Prepare the input table:

CREATE TABLE GSESPT.GEOTABLE1(polygon_ID integer not null primary key, geo ST_GEOMETRY) ;
INSERT INTO GSESPT.GEOTABLE1(polygon_ID,geo) VALUES (1001,ST_POINT(1,1));
INSERT INTO GSESPT.GEOTABLE1(polygon_ID,geo) VALUES (1002,ST_POINT(2,2));
INSERT INTO GSESPT.GEOTABLE1(polygon_ID,geo) VALUES (1003, ST_LINESTRING('linestring(0 0 , 0 0.0011)'));
INSERT INTO GSESPT.GEOTABLE1(polygon_ID,geo) VALUES (1005, ST_LINESTRING('linestring empty'));
INSERT INTO GSESPT.GEOTABLE1(polygon_ID,geo) VALUES (1006, NULL);
INSERT INTO GSESPT.GEOTABLE1(polygon_ID,geo) VALUES (1007, ST_POINT(45,90));
INSERT INTO GSESPT.GEOTABLE1(polygon_ID,geo) VALUES (1008, ST_POINT(135,90));
INSERT INTO GSESPT.GEOTABLE1(polygon_ID,geo) VALUES (1009, ST_POINT(180,0));
INSERT INTO GSESPT.GEOTABLE1(polygon_ID,geo) VALUES (1010,ST_POINT(-180,0));
INSERT INTO GSESPT.GEOTABLE1(polygon_ID,geo) VALUES (1011, ST_POINT(0,90));
INSERT INTO GSESPT.GEOTABLE1(polygon_ID,geo) VALUES (1012, st_linestring('linestring(0 90 ,1 -180)'));
INSERT INTO GSESPT.GEOTABLE1(polygon_ID,geo) VALUES (1013, st_linestring('linestring(0 90 ,0 -180)'));

Create the geohash-filter table:

call sysproc.st_create_hashfilter('GSESPT','GEOTABLE1','geo',,'ftable1','h')

Display the contents of the geohash-filter table:

select polygon_ID,GH_L from "ftable1" order by polygon_ID

Result:

POLYGON_ID GH_L
1001       -4611263805962321920
1002       -4609926799822946304
1003       -4611686018427387904
1005       -
1006       -
1007       -8646911284551352320
1008       -6341068275337658368
1009       4611686018427387904
1010       4611686018427387904
1011       -9223372036854775808
1012       -9223372036854775808
1012       -9223231299366420480
1013       -9223372036854775808