CREATE LOOKUP Statement

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

The CREATE LOOKUP statement creates a lookup table based on a unique key. The lookup function may then be used to retrieve values from the table, using a specified key.

Tip
To use the lookup table, refer to the lookup function

Syntax

Alchemy

Copy

CREATE LOOKUP ident ON value FROM filename [WHERE expr] INDEX BY id;

Nomenclature

ident	The identifier given to this newly created lookup table
value	The value within filename to index
filename	The path to the shapefile containing the table
expr	When the WHERE statement is used, expr is one or more logical expressions used to filter the selection rows in value
id	A unique identifier used as the key in the lookup table

Example

This simple example creates a lookup on post (ZIP) codes using city as a key.

Alchemy

Copy

CREATE LOOKUP cityFromPostcode
  ON %city
  FROM "data\NewZealand"
  INDEX BY %postcode;

This would allow us to find a city given a post (ZIP) code:

Alchemy

Copy

$myPostCode = lookup("cityFromPostcode", "8052");

The following is a more complex example that creates a lookup table using the link_id as a key to find the z-levels for a link. The z-levels are returned in an array, where each element is the z-level for the corresponding point in the link. See the Tutorial: Using Lookup Tables with Alchemy for a detailed explanation.

Alchemy

Copy

CREATE LOOKUP zlevels
  ON array.set_at(COALESCE(LOOKUP("zlevels", %link_id), 
    array.new(0)), %point_num - 1, %z_level)
FROM "data\zlevels"
WHERE %z_level != 0
INDEX BY %link_id;