Using Lookup Tables with Alchemy

A lookup table is a simple data structure that provides a one-to-one mapping between a key and a value.

When writing Alchemy scripts you will find that a lookup table provides a valuable means of moving information between tables - similar to a table join in SQL.

Lookup tables are created using the CREATE LOOKUP statement. It has the following form:

CREATE LOOKUP name_of_lookup
  ON value_column_name
  FROM filename_of_table
  [WHERE filter_expression]
  INDEX BY key_column_name;

Lookup tables are queried using the lookup function. The lookup function will return NULL if the key (and thus, the corresponding value) could not be found in the specified lookup table:

$value = lookup ( "name_of_lookup" , key )

You can save lookups for later use with the SAVE LOOKUP statement. This statement has the following syntax:

SAVE LOOKUP name_of_lookup AS file_name;

You can load lookups that you have previously saved with the LOAD LOOKUP statement. This statement has the following syntax:

LOAD LOOKUP name_of_lookup FROM file_name;

Suppose that we are performing a street import and require each street to have 4 attributes:

• a unique ID number
• a street name
• the name of the suburb in which the street is located
• the post code of the street

Suppose we are supplied with two shapefiles:

• the first shapefile contains 3 attributes: a unique ID for every street, the street name and the name of the appropriate suburb for each street
• the second shapefile contains 2 attributes: the suburb name and the post code of the suburb

Because the data is spanned across two shapefiles we must somehow merge the data (however an IMPORT Statement statement may only import data from one shapefile).

To do this we will create a lookup table from the second shapefile, linking the suburb name (the 'key') to the corresponding post code (the 'value'). This could be done as follows:

CREATE LOOKUP postCodeFromSuburb
  ON %postcode
  FROM "data\shapefile2"
  INDEX BY %suburb;

Now we may perform the import with all 4 attributes. Note how the post code is retrieved from the lookup table (and thus the second shapefile):

IMPORT streets [
  ID = %street_id,
  NAME = %street_name,
  L_REGION 1 = %suburb;
  LPOSTCODE = lookup("postCodeFromSuburb", %suburb)
] FROM "data\shapefile1";

The following is a complex example that creates a lookup table using link_id as a key to find the corresponding array of z-levels for the link (one z-level for every point in the link).

The shapefile has the following columns:

• LINKID - a unique identifier for each street. Each street consists of two or more points

• POINT_NUM - an integer identifier for each point along a street, numbered consecutively from 1

• ZLEVEL - the z-level for the point. This is a relative measure of the vertical height of a point

Note the use of the COALESCE statement to test whether an array of z-levels already exists for the link: if the array of z-levels exists for the link then lookup will return a reference to the array, otherwise lookup will return a reference to the supplied empty array.

The array.set_at function will take the array returned by the COALESCE statement and set the element at index point_num - 1 to the corresponding z-level.

The code to create the lookup table is:

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

The lookup table may then be used during an IMPORT as follows:

IMPORT streets [
  ...
  ZLEVEL = lookup("zlevels", %link_id),
  ...
FROM "data\streets";