ST_SETUP_GEOCODING procedure

Use this stored procedure to associate a column that is to be geocoded with a geocoder and to set up the corresponding geocoding parameters.

Information about the set up is available through the DB2GSE.ST_GEOCODING and DB2GSE.ST_GEOCODING_PARAMETERS catalog views.

This stored procedure does not invoke geocoding. It provides a way for you to specify parameter settings for the column that is to be geocoded. With these settings, the subsequent invocation of either batch geocoding or autogeocoding can be done with a much simpler interface. Parameter settings that are specified in this setup step override any of the default parameter values for the geocoder that were specified when the geocoder was registered. You can also override these parameter settings by running the ST_RUN_GEOCODING procedure in batch mode.

This step is a prerequisite for autogeocoding. You cannot enable autogeocoding without first setting up the geocoding parameters. This step is not a prerequisite for batch geocoding. You can run geocoding in batch mode with or without performing the setup step. However, if the setup step is done prior to batch geocoding, parameter values are taken from the setup time if they are not specified at run time.

Authorization

The user ID under which this stored procedure is invoked must hold one of the following authorities or privileges:

DATAACCESS authority on the database that contains the table on which the specified geocoder is to operate
CONTROL privilege on this table
UPDATE privilege on this table

Syntax

Parameter descriptions

table_schema

Names the schema to which the table or view that is specified in the table_name parameter belongs. Although you must specify a value for this parameter, the value can be null. If this parameter is null, the value in the CURRENT SCHEMA special register is used as the schema name for the table or view.

The table_schema value is converted to uppercase unless you enclose it in double quotation marks.

The data type of this parameter is VARCHAR(128) or, if you enclose the value in double quotation marks, VARCHAR(130).

table_name

Specifies the unqualified name of the table or view that contains the column into which the geocoded data is to be inserted or updated. If a view name is specified, the view must be updatable. You must specify a non-null value for this parameter.

The table_name value is converted to uppercase unless you enclose it in double quotation marks.

The data type of this parameter is VARCHAR(128) or, if you enclose the value in double quotation marks, VARCHAR(130).

column_name

Names the column into which the geocoded data is to be inserted or updated. You must specify a non-null value for this parameter.

The column_name value is converted to uppercase unless you enclose it in double quotation marks.

The data type of this parameter is VARCHAR(128) or, if you enclose the value in double quotation marks, VARCHAR(130).

geocoder_name

Names the geocoder that is to perform the geocoding. You must specify a non-null value for this parameter.

The geocoder_name value is converted to uppercase unless you enclose it in double quotation marks.

The data type of this parameter is VARCHAR(128) or, if you enclose the value in double quotation marks, VARCHAR(130).

parameter_values

Specifies the list of geocoding parameter values for the geocoder function. Although you must specify a value for this parameter, the value can be null. If the entire parameter_values parameter is null, the values that are used are taken from the default parameter values at the time the geocoder was registered.

If you specify parameter values, specify them in the order that the function defined them, and separate them with a comma. For example:


parameter1-value,parameter2-value,...

Each parameter value is an SQL expression and can be a column name, a string, a numeric value, or null. Follow these guidelines:

If a parameter value is a geocoding column name, ensure that the column is in the same table or view where the geocoded column resides.
If a parameter value is a string, enclose it in single quotation marks.
If a parameter value is a number, do not enclose it in single quotation marks.
If the parameter value is specified as a null value, cast it to the correct type. For example, instead of specifying just NULL, specify:
```
CAST(NULL AS INTEGER)
```

If any parameter value is not specified (that is, if you specify two consecutive commas (...,,...)), this parameter must be specified either when geocoding is set up or when geocoding is run in batch mode with the parameter_values parameter of the respective stored procedures.

The data type of this parameter is VARCHAR(32K).

autogeocoding_columns

Specifies the list of column names on which the trigger is to be created. Although you must specify a value for this parameter, the value can be null. If this parameter is null and autogeocoding is enabled, an update of any column in the table causes the trigger to be activated.

If you specify a value for the autogeocoding_columns parameter, specify column names in any order, and separate column names with a comma. The column name must exist in the same table where the geocoded column resides.

This parameter setting applies only to subsequent autogeocoding.

The data type of this parameter is VARCHAR(32K).

where_clause

Specifies the body of the WHERE clause, which defines a restriction on the set of records that are to be geocoded. Although you must specify a value for this parameter, the value can be null. If this parameter is null, no restrictions are defined in the WHERE clause.

The clause can reference any column in the table or view that the geocoder is to operate on. Do not specify the keyword WHERE.

This parameter setting applies only to subsequent batch-mode geocoding.

The data type of this parameter is VARCHAR(32K).

commit_scope

Specifies that a COMMIT is to be performed for every n records that are geocoded. Although you must specify a value for this parameter, the value can be null. If this parameter is null, a COMMIT is performed after all records are geocoded.

This parameter setting applies only to subsequent batch-mode geocoding.

The data type of this parameter is INTEGER.

Output parameters

msg_code: Specifies the message code that is returned from the stored procedure. The value of this output parameter identifies the error, success, or warning condition that was encountered during the processing of the procedure. If this parameter value is for a success or warning condition, the procedure finished its task. If the parameter value is for an error condition, no changes to the database were performed.
The data type of this output parameter is INTEGER.
msg_text: Specifies the actual message text, associated with the message code, that is returned from the stored procedure. The message text can include additional information about the success, warning, or error condition, such as where an error was encountered.
The data type of this output parameter is VARCHAR(1024).

Example

This example shows how to use the Db2® command line processor to invoke the ST_SETUP_GEOCODING stored procedure. This example uses a Db2 CALL command to set up a geocoding process for the geocoded column named LOCATION in the table named CUSTOMER. This CALL command specifies the geocoder_name parameter value as SAMPLEGC:


call DB2GSE.ST_SETUP_GEOCODING(NULL, 'CUSTOMERS', 'LOCATION',
    'SAMPLEGC','ADDRESS,CITY,STATE,ZIP,1,100,80,,,,"$HOME/sqllib/
     gse/refdata/ky.edg","$HOME/sqllib/samples/extenders/spatial/EDGESample.loc"',
     'ADDRESS,CITY,STATE,ZIP',NULL,10,?,?)

The two question marks at the end of this CALL command represent the output parameters, msg_code and msg_text. The values for these output parameters are displayed after the stored procedure runs