Skip to main content

Calgary Queries

Queries allow you to select any number of fields. All fields selected in a query must have an Index built for them.

Queries can be any mixture of ANDs and ORs, and the given field can specify any number of values or a begin/end range. In this case, it's an OR within the field.  There is no limit to the depth of ANDs and ORs.

XML Query Example

<And>
	<Or>
		<Field name="ZIP" type="list">80303,80304</Field>
		<Field name="ZIP" type="list>
			<value>80303</value>
			<value>80304</value>
		</Field>
	</Or>
	<Field name="Income" type="range" begin="50000" end="750000"/>
	<SpatialField name="SpatialObj" type="radius" lat="40" long="-105" radius="10" units="Miles" />
	<SpatialField name="SpatialObj" type="binaryIn" spatialInputNum="0" />
	<Not>
		<Field name="Gender" value="M"/>
	</Not>
</And>

Query Types

  • And & Or: These groups can contain any number of Field or SpatialField elements or additional And or Or elements.

  • Not: Can contain exactly one And, Or, Field, or SpatialField element. The selection is reversed from what is inside, in other words, if inside you have Gender="M", it returns all genders except M (including NULL and otherwise invalid values). If there is more than 1 child element, it generates an error.

  • All:  Special tag that selects all records.

  • Field: The Field element has a required attribute name which is the field name for this query.

Attribute Types

For all index types except spatialObject the attribute type can have these values:

  • value (Default): A single exact value is searched for. If no type is specified, it is presumed to be value. The value attribute or the value of the element contains the value.

  • delayedValueNum: Used in place of value if you're setting a value later with CalgarySetDelayedValue (found in the Calgary API). You can use it to speed up repeated queries.

  • list: The value attribute or the value of the element contains a comma-separated list of values to query. If escaping is needed, you can place each element of the list within <value>...</value> tags. Value tags are only valid if you use a list. If not, use the value attribute or the value of the parent element. If you use the value tags, you must not set the other methods.

  • range: There are 2 additional attributes, begin and end, that contain the endpoints. By default, it finds value>=begin AND value<end. Optionally, you can specify includeBegin="True/False" includeEnd="True/False" to change this behavior.

If you leave the begin or end attribute out or set it to an empty string, that becomes unbounded.

String Attributes

The single exception to this is string-type indexes. If the begin is empty, and includeBegin is false, then the empty string in the index is not part of the result.

  • begin: This is valid only for string indexes or Full Text (Contains) indexes. Strings that contain the query value are found, even if they are longer.

  • delayedValueBegin:  Used in place of begin if you are setting a value later with CalgarySetDelayedValue (found in the Calgary API). You can use it to speed up repeated queries.

  • contains: This is valid only for Full Text (Contains) indexes. Strings that contain the query value are found.

  • end: This is valid only for Full Text (Contains) indexes. Strings that end with the query value are found, even if they are longer.

  • delayedValueEnd: Used in place of end if you are setting a value later with CalgarySetDelayedValue (found in the Calgary API).  You can use it to speed up repeated queries.

  • isNull:  This is valid for any index type and queries the NULL fields in that index. All index types support querying for NULL. It is the only <Field... /> query type that you can use a spatial field with. For the purpose of spatial indexes, records are considered NULL if they don't have a valid point, in other words, type None, and a point (0,0) are both considered NULL.

  • listBegin: This is the intersection of list and begin. You can specify a list of things to be queried in begin mode.

SpatialField Values

SpatialField: The SpatialField Element indexes type has these values:

  • radius: These attributes are required:

    • Latitude

    • Longitude

    • radius: Either a single number or a range for donuts. For example, "3-5".

    • units: Miles, Kilometers, or Minutes

    • dtDataSet: Dataset name (or country code) for the Drivetime Engine. Omit for default.

  • binaryIn: The API allows an arbitrary number of spatial objects to be supplied. Use the spatialInputNum="n" attribute to specify which one. If not specified, it defaults to 0. You can specify up to 100 separate binary objects.

  • file: The attribute dataSource="File or DW2 Connect String" specifies a file or table to take polygons from. The 1st SpatialObj found in the table or file is the one the query uses. If there are multiple records, they are OR'd together. Added in r16831.

  • Allocate: The spatial object is taken from an Allocate workspace. Specify the dataset via the attribute: dataSet="Software\Alteryx\Portfolio\6.30\Alteryx_US_AGS_08A". Specify the workspace as the text value of the SpatialField element. Added in r16831. If there are multiple spatial objects, they are OR'd together.

In this section: