ST_REGISTER_GEOCODER procedure

Use this stored procedure to register a geocoder.

Prerequisites: Before registering a geocoder:
  • Ensure that the function that implements the geocoder is already created. Each geocoder function can be registered as a geocoder with a uniquely identified geocoder name.
  • Obtain information from the geocoder vendor, such as:
    • The SQL statement that creates the function
    • The parameter values used to call the ST_CREATE_SRS procedure so that geometric data can be supported
    • Information for registering the geocoder, such as:
      • A description of the geocoder
      • Descriptions of the parameters for the geocoder
      • The default values of the geocoder parameters

The geocoder function's return type must match the data type of the geocoded column. The geocoding parameters can be either a column name (called a geocoding column) which contains data that the geocoder needs. For example, the geocoder parameters can identify addresses or a value of particular meaning to the geocoder, such as the minimum match score. If the geocoding parameter is a column name, the column must be in the same table or view as the geocoded column.

The geocoder function's return type serves as the data type for the geocoded column. The return type can be any Db2® data type, user-defined type, or structured type. If a user-defined type or structured type is returned, the geocoder function is responsible for returning a valid value of the respective data type. If the geocoder function returns values of a spatial type, that is ST_Geometry or one of its subtypes, the geocoder function is responsible for constructing a valid geometry. The geometry must be represented using an existing spatial reference system. The geometry is valid if you invoke the ST_IsValid spatial function on the geometry and a value of 1 is returned. The returned data from the geocoder function is updated in or is inserted into the geocoded column, depending on which operation (INSERT or UPDATE) caused the generation of the geocoded value.

To find out whether a geocoder is already registered, examine the DB2GSE.ST_GEOCODERS catalog view.

Authorization

The user ID under which this stored procedure is invoked must hold DBADM authority on the database that contains the geocoder that this stored procedure registers.

Syntax

Read syntax diagramSkip visual syntax diagramDB2GSE.ST_REGISTER_GEOCODER( geocoder_name,function_schemanull,function_namenull,specific_namenull,default_parameter_valuesnull,parameter_descriptionsnull,vendornull,descriptionnull, msg_code , msg_text )

Parameter descriptions

geocoder_name
Uniquely identifies the geocoder. 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).

function_schema
Names the schema for the function that implements this geocoder. 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 function.

The function_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).

function_name
Specifies the unqualified name of the function that implements this geocoder. The function must already be created and listed in SYSCAT.ROUTINES.

For this parameter, you can specify null if the specific_name parameter is specified. If the specific_name parameter is not specified, the function_name value, together with the implicitly or explicitly defined function_schema value, must uniquely identify the function. If the function_name parameter is not specified, Db2 Spatial Extender retrieves the function_name value from the SYSCAT.ROUTINES catalog view.

The function_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).

specific_name
Identifies the specific name of the function that implements the geocoder. The function must already be created and listed in SYSCAT.ROUTINES.

For this parameter, you can specify null if the function_name parameter is specified and the combination of function_schema and function_name uniquely identifies the geocoder function. If the geocoder function name is overloaded, the specific_name parameter cannot be null. (A function name is overloaded if it has the same name, but not the same parameters or parameter data types, as one or more other functions.)

The specific_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).

default_parameter_values
Specifies the list of default geocoding parameter values for the geocoder function. Although you must specify a value for this parameter, the value can be null. If the entire default_parameter_values parameter is null, all parameter default values are null.
If you specify any parameter values, specify them in the order that the function defined them, and separate them with a comma. For example: 

default_parm1_value,default_parm2_value,...
Each parameter value is an SQL expression. Follow these guidelines:
  • If a 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 null, cast it to the correct type. For example, instead of specifying just NULL, specify: 
    
CAST(NULL AS INTEGER)
  • If the geocoding parameter is to be a geocoding column, do not specify the default parameter value.

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).

parameter_descriptions
Specifies the list of geocoding parameter descriptions for the geocoder function. Although you must specify a value for this parameter, the value can be null.
If the entire parameter_descriptions parameter is null, all parameter descriptions are null. Each parameter description that you specify explains the meaning and usage of the parameter, and can be up to 256 characters long. The descriptions for the parameters must be separated by commas and must appear in the order of the parameters as defined by the function. If a comma shall be used within the description of a parameter, enclose the string in single or double quotation marks. For example: 

description,'description2, which contains a comma',description3

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

vendor
Names the vendor who implemented the geocoder. Although you must specify a value for this parameter, the value can be null. If this parameter is null, no information about the vendor who implemented the geocoder is recorded.

The data type of this parameter is VARCHAR(128).

description
Describes the geocoder by explaining its application. Although you must specify a value for this parameter, the value can be null. If this parameter is null, no description information about the geocoder is recorded.

The data type of this parameter is VARCHAR(256).

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 assumes that you want to create a geocoder that takes latitude and longitude as input and geocodes into ST_Point spatial data. To do this, you first create a function named lat_long_gc_func. Then you register a geocoder named SAMPLEGC, which uses the function lat_long_gc_func.

Here is an example of the SQL statement that creates the function lat_long_gc_func that returns ST_Point: 

CREATE FUNCTION lat_long_gc_func(latitude double,
			longitude double, srId integer)
			RETURNS DB2GSE.ST_Point
			LANGUAGE SQL
			RETURN DB2GSE.ST_Point(latitude, longitude, srId)
After the function is created, you can register it as a geocoder. This example shows how to use the Db2 command line processor CALL command to invoke the ST_REGISTER_GEOCODER stored procedure to register a geocoder named SAMPLEGC with function lat_long_gc_func: 

call DB2GSE.ST_REGISTER_GEOCODER ('SAMPLEGC',NULL,'LAT_LONG_GC_FUNC',',,1'
                                   ,NULL,'My Company','Latitude/Longitude to 
                                   ST_Point Geocoder'?,?)
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.