Creating a View Table with Parameters

You can create a view table with a MISQL query that includes one or more parameters. Using parameters lets an application or service generate different views of the map or reports based on input. As an example, generating reports for sales territories where the SQL statement generating the view table uses a parameter for territory ID. An application can call the view table for a specific territory to generate a report.

You can also specify a geometry as a MISQL parameter. The geometry must be valid and in well-known text format, such as POINT (-75 45). Supported geometry types are:

  • POINT
  • LINE
  • POLYGON
  • MULTIPOLYGON

Unless the geometry parameter is part of a function that already specifies a coordinate system, like MISQL FromWKT, you must specify a geometry coordinate system.

Before creating a view table:
  • You must be an administrator (admin) or Spatial administrator (spatial-admin).
  • Have a named table in Spectrum Spatial™ Manager.

To create a view table with parameters:

  1. In Spectrum Spatial™ Manager under Data > Repository, click Create and select View Table.
  2. On the Create View Table screen, click the Advanced tab.
  3. In the MISQL box, enter your SQL statement with parameters.
    Note: When using multiple parameters within the same SQL statement, each parameter must have a unique name and be in the WHERE clause. Parameters must begin with the at (@) sign, such as @param or @population.

    The following example queries the Country, Capital, and Pop_Fem (population of females) columns in the sample table called WorldTable. This statement filters the Pop_fem column using a parameter called param.

    SELECT Country, Capital, Pop_Fem FROM "/Samples/NamedTables/WorldTable" WHERE Pop_Fem < @param

    The following example queries countries that include a token in the name (Country column).

    SELECT Country, Capital, Pop_Fem FROM "/Samples/NamedTables/WorldTable" WHERE Country LIKE ('%' + @param + '%')
  4. Click Validate to test the statement.

    When the statement includes a parameter, the Bound Parameters table displays. The Name field shows the parameter name and the Type field shows the value type, such as Double or String.

  5. In the Default Value field for each parameter, set the default value.
  6. In the Description field optionally add a comment that describes each parameter.
  7. In the New View Table Name field, type a name for this view table.
  8. In the Repository Folder field, enter the location of the named table in the repository. A slash (/) at the beginning saves it at the root level in the repository.
    To select a folder from the repository, click Select a Folder.
  9. Click Create.

A detailed screen displays for your View Table where you can add a description for the table and keywords. You can also apply settings for the View Table under the Columns, Sample Rows, and Permissions tabs.

The table is ready to query.