CREATE INDEX Statement

Print this page
Learn more about Verizon Connect GeoBase.
Get information about the latest release

The CREATE INDEX statement creates an index on a specified column. This index may be used by the Geocoder to resolve an address.

Syntax

There are two forms for the CREATE INDEX statement, depending on the type of index you want to create:

The most common index type is a BTREE index. This type of index can be applied to any column type, and indexes exact column values.
Text columns can use a BK (Burkhard-Keller) Tree index instead. This type of index can only be applied to text columns, and takes into account Levenshtein edit distance (the number of insertion, deletion, and replacement operations required to convert the search string to the indexed value), thereby allowing fuzzy matches to the index.

Alchemy

Copy

CREATE INDEX index_name ON table USING BTREE(col_name);

Alchemy

Copy

CREATE INDEX index_name ON table USING BKTREE value (col_name);

Nomenclature

index_name	The desired name of this index
table	The source table containing column col_name
value	Number of errors to allow when finding a match in the index (BKTREE indexes only)
col_name	The column within table on which to build the index

Example

Alchemy

Copy

SELECT %forest_nam AS name, %forest_size AS size INTO forest FROM "land2";
CREATE INDEX f_size ON forest USING BTree(size);

Example

The following example SELECTs a number of lakes and then builds an index on the lake name. The index is built using a BK (Burkhard-Keller) Tree with a Levenshtein distance of 2.

Alchemy

Copy

SELECT %NAME AS name INTO lakes FROM "waterbody";
CREATE INDEX lake_name ON lakes USING BKTREE 2 (name);