Description:

Queries a dataset in one of three ways:

1. A tabular query based on the value of attributes. For example, an attribute query asks for all cities in Canada where the population is greater than 500,000.
2. A spatial query based on features selected on a map. For example, a rectangle might be dragged over the eastern United States to select a group of cities.
3. A combination of a tabular and spatial query. For example, a group of cities might be selected, but only cities with a population greater than 500,000 are displayed.

SPATIALQUERY is also used to join DBF files to shapefiles and to relate tables in ArcSDE.

Restrictions:

• The where attribute is required when the jointable attribute for ArcSDE tables is used. Otherwise, this attribute is optional.
• The attribute accuracy is valid only in GET_FEATURES requests.
• When using an ArcMap Image Service, the attribute featurelimit is valid only in GET_FEATURES requests. For Image and Feature Services, featurelimit can also be used in a map configuration file.
• A map configuration file with a SPATIALQUERY containing a fully qualified ArcSDE name or a joined table for a field (TABLE.FIELD) works only in an ArcIMS service. The file cannot be read locally in ArcIMS Author, ArcExplorer 9, or the ArcIMS Java Viewers.
• When joining shapefiles, shapefile layers can only be joined to other DBF files located in the same directory as the shapefile. A joined DBF file cannot be another shapefile DBF file that is currently being used in an ArcIMS service.
• DBF jointable names are limited to 10 characters.
• When joining tables in ArcSDE, ArcSDE layers can only be joined to other tables within the same schema in the relational database management system (RDBMS).
• When using subfields, the number of fields that can be listed is limited to 254.
• When using shapefiles, field values in the DBF cannot contain a double quote. Double quotes are not processed correctly in the 'where' statement.
  
  

  <SPATIALQUERY where="myfield = '&quot;myvalue&quot;'" />

  
  This restriction is not in place for ArcSDE layers. Field values can include a double quote.

Notes:

• Without SPATIALFILTER, SPATIALQUERY works exactly the same as QUERY when querying database attributes.
  
  
• Using featurelimit. In a map configuration file for Image and Feature Services, the featurelimit attribute can be used to limit the number of features returned for a layer, regardless of how many features are requested. In this example, the number of returned features can never exceed 100.
  
  

  <SPATIALQUERY featurelimit="100" />

  
  This is useful if a layer has many features and you don't want a user requesting all features with a GET_FEATURES or GET_EXTRACT request. A large number of features will be slow and can potentially bring down an ArcIMS Spatial Server. In addition, large responses can hang a client viewer. Note that for ArcMap Image Services, a feature limit cannot be set in the map document.
  
  A feature limit can be set in several places in addition to SPATIALQUERY:
  • In the Spatial Server configuration (.cfg) files
  • Inside GET_FEATURES
  
  A global feature limit can be set in the Spatial Server configuration files limiting the number of requested features from all layers in all services. By default, the feature limit is 2000 features for queries made to Image and ArcMap Image Services. No upper limit is set for Feature Services. For ArcMap Image Services, this is the only place on the server to limit the number of requested features. For more information on setting this feature limit, see ArcIMS Help.
  
  The attribute featurelimit is also available in GET_FEATURES. In many cases, you want to limit the number of features to something manageable. For example, you many want to return only 10 records at a time and allow for users to page for the next or previous 10 records.
  
  The hierarchy for all the feature limit settings are as follows:
  1. The feature limit in the Spatial Server configuration files is the maximum that can ever be returned in one request.
  2. SPATIALQUERY featurelimit in a map configuration file overrides the value in #1 if it is smaller.
  3. GET_FEATURES featurelimit overrides the values in #1 and #2 if it is smaller.
  4. In a GET_FEATURES request, SPATIALQUERY featurelimit overrides GET_FEATURES featurelimit. In both elements, the feature limit must be smaller than #1 or #2, or it is ignored.
  
  
• Empty where statements. A user can send an empty where statement (where="") or a statement that is always true (where="1=1") in a GET_FEATURES request. In both cases, all features are returned. The best way to restrict the number of features is to restrict the feature limit as described in the previous note.
  
  
• Queries defined in a map configuration file or generated in an ArcMap document always filter the data and cannot be changed through a request. Any requests made to a filtered layer in a map configuration file can select only features within the filtered subset. For example, assume a map configuration file has a world cities layer that has been filtered to display only cities with a population greater than 1,000,000. All requests to this layer, whether tabular or spatial, include only cities with a population greater than 1,000,000. Other cities in the database are ignored.
  
  
• Using subfields in a map configuration file (Image and Feature Server). The subfields attribute can be used to limit the number of available fields in a shapefile or ArcSDE layer along with any joined tables. In a SERVICEINFO response, only the fields listed in subfields are included. In a FEATURES response, only the subfields listed are available for querying. The following rules apply:
  • You should always include the #SHAPE# and #ID# fields in the list of subfields in the map configuration file. If #SHAPE# and #ID# are not included, a map will display, but queries will not work properly.
  • If you plan to use CHARTSYMBOL, CHARTVALUE, SIMPLELABELRENDERER, VALUEMAPRENDERER, VALUEMAPLABELRENDERER, or TRUETYPEMARKERSYMBOL, you must include any fields used with these elements in the subfields list.
  • If you are using ArcSDE layers, the fully qualified name must be used for all fields in the ArcSDE layer and any joined tables: ARCSDENAME.TABLE.NAME. Note that the map configuration file cannot be read locally in ArcIMS Author or ArcExplorer 9 when using fully qualified names.
  
  
• In a GET_FEATURES request, one of the options is to return the geometry of selected features by setting the geometry attribute to true. In addition, the #SHAPE# field (or #ALL#) must be included in the SPATIALFILTER subfields list.
  
  

  <GET_FEATURES outputmode="XML" geometry="true"> <LAYER id="4" /> <SPATIALQUERY subfields="#ALL#" where="NAME='Los Angeles'" /> </GET_FEATURES>

  
  
• When used in GET_FEATURES, queries and finds can be made case insensitive by including the UPPER parameter around the field name. Note that the value must have all uppercase letters.
  
  An example for a shapefile:
  

  <SPATIALQUERY subfields="#ALL#" where="UPPER(NAME)="TORONTO" >

  
  An example for an ArcSDE layer:
  

  <SPATIALQUERY subfields="#ALL#" where="UPPER(MYSDE.US_STATES.STATE_NAME)="FLORIDA" >

  
  
• When creating a where clause, two fields from the same attribute table can be used for comparison. For example, a query can be made to find states with a female population greater than the male population.
  
  

  <SPATIALQUERY where="COUNTRY.STATE.FEMALES &gt; COUNTRY.STATE.MALES" />

  
  
• Some symbols must be "escaped" inside a where expression:
        ampersand (&) is escaped to &
        double quotes (") are escaped to "
        single quotes (') are escaped to '
        greater than (>) is escaped to >
        less than (<) is escaped to &lt;
  
  
• The following operators work in a where clause: =, >, >=, <, <=, <>, LIKE, BETWEEN, IN, NOT IN. The following are not valid: ORDER BY and DISTINCT.
  
  
• By default query and find operations are case sensitive. In many cases, a case insensitive query is needed because what a user types in is unpredictable. Most data sources support UPPER within a query. For a case insensitive query, use UPPER around the field name and convert the comparison text to all upper case letters.
  
  

  <SPATIALQUERY subfields="#ALL#" where="UPPER(NAME) = 'CANADA'">

  
  UPPER does not work with all data sources, including Personal Geodatabases and ArcSDE for Coverages.
  
  
• Joining ArcSDE tables. When joining ArcSDE tables, all valid joins between the RDBMS and ArcSDE are also valid in ArcIMS. To join ArcSDE tables, a 'where' clause is used for defining which tables are joined and for any additional filters using SQL syntax. Multiple joins are also permitted.
  
  In the following example, the 'where' statement includes setting up two relates (in bold type) and filtering the selection to FIPS_CNTY='013' (in italic type).
  
  

  <SPATIALQUERY where="DB.CITY.CITY_FIPS = DB.SCHOOLS.CITY_FIPS and DB.SCHOOLS.SCHOOL_ID = DB.SCHOOL_STATS.SCHOOL_ID and DB.CITY.CNTY_FIPS=&apos;013&apos;" jointables="DB.SCHOOLS DB.SCHOOL_STATS" />

  
  In this statement, cities (DB.CITY) are first joined to schools (DB.SCHOOLS) using the column CITY_FIPS as the common join item. Next, the schools (DB.SCHOOLS) are joined to a table with statistics on the schools (DB.SCHOOL_STATS) using SCHOOL_ID as the join item. A further filter is placed on this query by limiting the results to schools located within a specified county, in this example, where CNTY_FIPS='013'.
  
  The list of joined tables must be listed under the jointables attribute. If more than one table is joined, table names are separated by a space. When naming tables, the fully qualified ArcSDE table name must be used. In the above example, the two joined tables are DB.SCHOOLS and DB.SCHOOL_STATS.
  
  
• Joining DBF files. When joining DBF tables, joins can be made between the shapefile DBF table and one or more external DBF tables that reside in the same directory as the shapefile. One restriction is that a joined DBF file cannot be another shapefile DBF file that is currently being used in an ArcIMS service.
  
  To join DBF files, the joinexpression attribute is used. This attribute uses the syntax: "To=[master table column which will be used for joining], From=[defines a join table column which will be joined], Type=[exact or scan]".
  
  

  To	Refers to the master DBF table and defines the field that is used for joining. When referring to this table, the DBF table name must be used as a prefix to the field name. The entire expression is surrounded by square brackets, e.g., joinexpression="To=[mastertable.fieldname]".
  From	Refers to the DBF table that is joined to the master DBF table and the field that is used for joining, e.g., joinexpression="From=[jointable.fieldname]".
  Type=[exact]	Defines an exact match relation that permits only a single match between the master and join tables. Both one-to-one and many-to-one relations are exact match relations. In a one-to-one relation, there is only one record in the master that matches a single record in the join table. In a many-to-one relation, there are one or more records in the master that match a single join record. If there are multiple join records that match a single master record, then a composite record is only generated for the first join record.
  Type=[scan]	In a scan relation, if there are multiple join records for a master record, there is one composite record in the extended data file for each of the matching join records. Both one-to-many and many-to-many relations are scan relations. In a one-to-many relation, each record in the master can have multiple matching join records. A many-to-many relation is the same as one-to-many, except that different master records can match the same join record.

  
  
  To, From, and Type parameters are case sensitive (first letter is capitalized), and they are separated by a comma, ",". Any number of joined tables can be defined in the joinexpression attribute. Joined tables are separated by a semicolon, ";" such as the following:
  
  

  joinexpression="To=[A.ID],From=[B.ID],Type=[scan];To=[B.NAME],From=[C.NAME],Type=[exact]"

  
  All joined DBF tables must be listed under jointables. If multiple tables are joined, table names are separated by a space. When naming tables, the name of the DBF file without the extension is used. An example DBF query expression is:
  
  

  <SPATIALQUERY joinexpression="To=[counties.CNTY_FIPS],From=[countyinfo.FIPS],Type=[scan]; To=[countyinfo.FIPS],From=[state_roads.FIPS],Type=[scan]" jointables="countyinfo state_roads" where="counties. NAME=&apos;Washoe&apos;" />

  
  In this example, a county DBF file (counties.dbf) is first joined to a DBF file containing county information (countyinfo.dbf). They are joined on the field FIPS (counties.CNTY_FIPS and countyinfo.FIPS). Next, countyinfo.dbf is joined to state_roads.dbf, once again using FIPS as the join item. The jointables are listed as countyinfo and state_roads, separated by a space. The query is filtered to only include one county, in this case Washoe.
  
  
• When joining DBF files to a shapefile, the DBF file cannot be read-only. An indication of this problem is the FIELD information from the joined tables shows up in SERVICEINFO but the data is not drawn on the map.
  
  
• Querying with dates - Image Services. The syntax for querying dates is the same regardless of the locale. A date query uses the following syntax:
  
  {ts 'YYYY-MM-DD hh:mi:ss'}
  
  where
  

  YYYY	Year	Required	Use four digits for the year.
  MM	Month (01-12)	Required	Use two digits for the month. March is 03.
  DD	Day (01-31)	Required	Use two digits for the day. The fourth is 04.
  hh	Hour (00-23)	Optional	Use a 24-hour clock. 8 a.m. is 08, and 8 p.m. is 20.
  mi	Minutes (00-59)	Optional	Use two digits for the minutes. If minutes is used, hours must also be included.
  ss	Seconds (00-59)	Optional	Use two digits for the seconds. If seconds is used, hours and minutes must also be included.

  
  The year, month, and day are each separated by a dash (-). The hour, minutes, and seconds are each separated by a colon (:). The date is enclosed in single quotes (') inside curly brackets ({}). Before the date, ts (for time stamp) must be included.
  
  For 8:03:23 a.m. January 4, 2000, the query on a DBF file looks like:
  

  <SPATIALQUERY where="MYDATE = {ts '2000-01-04 08:03:32'}" />

  
  For 9:18 p.m. March 8, 2002, the query on an ArcSDE layer looks like:
  

  <SPATIALQUERY where="ARCSDE.TABLE.MYDATE = {ts '2002-03-08 21:18:00'}" />

  
  
• Querying with dates - ArcMap Image Services. A date query uses the same syntax as you would use in ArcMap. The format differs depending on the data type that the map is referencing:
  
  Dates in coverages, shapefiles and ArcSDE geodatabases comply with SQL standards. The following example searches for January 31, 2003:

  <SPATIALQUERY where="DATE_ = date '2003-01-31' " >

  
  Dates in personal geodatabases are delimited like the following example:

  <SPATIALQUERY where="[DATE_] = #2003-01-31# " >

  
  Native database supported date formats may also be used when working with ArcSDE geodatabases.

Attribute Descriptions for SPATIALQUERY:

Examples for SPATIALQUERY: