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 attributes accuracy and featurelimit are valid only in GET_FEATURES requests.
• A map configuration file with a SPATIALQUERY containing a full ArcSDE name for a field (TABLE.FIELD) works only in an ArcIMS service. The file cannot be read locally in ArcIMS Author, ArcExplorer 4, 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 in the relational database management system (RDBMS).
• When using subfields, the number of fields that can be listed is limited to 254.

Notes:

• Without SPATIALFILTER, SPATIALQUERY works exactly the same as QUERY when querying database attributes.
  
  
• 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 4 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_CNTRY='CA' (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_INFO" />

  
  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 full ArcSDE table name must be used. In the above example, the two joined tables are DB.SCHOOLS and DB.SCHOOL_INFO.
  
  
• 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. 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'}" />

  
  

Attribute Descriptions for SPATIALQUERY:

Examples for SPATIALQUERY: