SPATIALQUERY

Used in

CONFIG  REQUEST  

Servers

Image  Query  Feature  Extract  

Parent elements

BUFFER  GET_FEATURES  LAYER  LAYERDEF 

Syntax

<SPATIALQUERY

Attribute Description Table

When using Image, Extract, Query, or Feature Server:      accuracy="Distance between points" [0]      featurelimit="integer" [All that meet criteria]      joinexpression="string"      jointables="string"      searchorder="optimize | spatialfirst | attributefirst" [optimize]      subfields="#ALL# | #ID# | #SHAPE# | Other fields in database" [#ALL#]      where="string"      When using Query Server:      order_by="string" >      When parent element is BUFFER:      <FEATURECOORDSYS... />      <FILTERCOORDSYS... />      <SPATIALFILTER... />      When parent element is GET_FEATURES:      <BUFFER... />      <FEATURECOORDSYS... />      <FILTERCOORDSYS... />      <SPATIALFILTER... />      When parent element is LAYER and LAYERDEF in GET_IMAGE:      <BUFFER... />      <SPATIALFILTER... />      When parent element is LAYER in CONFIG:      <FILTERCOORDSYS... />      <SPATIALFILTER... /> </SPATIALQUERY >

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.
• The order_by attribute is valid only with ArcSDE layers. It is valid only with Image Services.
• 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-Java Edition, 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.
  
  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 Services. No upper limit is set for Feature Services. 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 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-Java Edition 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 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. DISTINCT is not valid. ORDER BY can be used on ArcSDE layers by using the attribute order_by.
  
  
• When using LIKE in a where clause, the wildcard for a single character is "?" for shapefiles and "_" for ArcSDE layers.
• 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'">

  
  An example for an ArcSDE layer:
  

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

  
  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='013'" 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 (the master DBF) 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 (shapefile DBF) column which will be used for joining], From=[external DBF table column which will be joined], Type=[exact or scan]".
  
  

  To	Refers to the master DBF table (shapefile DBF) 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 external DBF table that is joined to the master DBF (shapefile 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'}" />

  
  

Attribute Descriptions for SPATIALQUERY

Attribute	Usage
accuracy	Used in GET_FEATURES requests only. Points are generalized within a feature based on the distance specified and the resolution of the image. Units are the same as the service. A value of 0 for accuracy returns all points of a feature, whereas higher values return a feature with fewer points thus making the feature more generalized. Note that given a non-zero value used for accuracy, and depending on the geometry layout of features in a layer, polylines or polygons may become self-intersecting. When this happens, the geomtery is invalid, the feature is ignored, and no result is returned for the spatial query. Accuracy cannot be used on a point layer; only polygon and polyline layers are valid. It is recommended to use accuracy="0" during any BUFFER operation.
featurelimit	Maximum number of features to be extracted that meet criteria. See Notes section for more information.
joinexpression	Provides the join expression for queries on shapefiles and DBF files. Not required when a jointable is done on ArcSDE. String must form expression: "To=[master table (shapefile DBF) column which will be used for joining], From=[external DBF table column which will be joined], Type=[exact or scan]".
jointables	List of joined table names separated by blank spaces; for ArcSDE, table name is full name including database name (e.g., DATA.STATE); for shapefiles, use the DBF filename without the extension (e.g., STATES).
searchorder	Used with ArcSDE layers only. Determines whether the attribute or spatial part of an ArcSDE query is processed first. "Spatialfirst" processes the spatial part of the query before the attribute part. "Attributefirst" processes the attribute part of the query first. If "optimize" is used, ArcSDE will make the judgment whether to pick "spatialfirst" or "attributefirst".
subfields	List of fields available for querying or extracting. Multiple fields can be included in the subfields list. Fields must be separated by blank space. If subfields is not used, all fields are returned. If subfields is used, only listed fields are returned. The subfields #SHAPE# or #ALL# must be included if geometry is to be returned in a FEATURES response. In addition, the GET_FEATURES geometry attribute must be set to true. The subfields list can include fields from the layer table or a joined table. For shapefiles with no joined tables, the field can be referenced using the short format. field="AREA" For shapefiles with joined tables, the name of the joined table must be included along with the field. field="JOINEDTABLE.AREA" For ArcSDE layers with or without joined tables, the field must be referenced using the full long format. field="ARCSDENAME.TABLE.AREA" Performance is generally better if the field names are all upper case.
where	Defines 'where' part of SQL expression. Required when jointables attribute for ArcSDE tables is used. See Notes section for information on querying dates.
order_by	Defines the 'order by' part of an SQL expression. You must include "ORDER BY" as part of the attribute value. order_by="ORDER BY DB.COUNTRIES.NAME" This attribute is valid only with GET_FEATURES requests.

Back to top 

Examples for SPATIALQUERY

Example 1: Setting a spatial query in a GET_IMAGE request. Note in this example that the DATASET fromlayer is "Countries". This refers to the LAYER id in the map configuration file, not the LAYER name.

<?xml version="1.0" encoding="UTF-8"?> <ARCXML version="1.1"> <REQUEST>   <GET_IMAGE>     <PROPERTIES>       <ENVELOPE minx="-180" miny="-90" maxx="180" maxy="90" />       <IMAGESIZE width="643" height="502" />     </PROPERTIES>     <LAYER type="featureclass" name="select layer" visible="true" id="300">       <DATASET fromlayer="Countries" />       <SPATIALQUERY>         <SPATIALFILTER relation="area_intersection">           <ENVELOPE maxy="30" maxx="30" miny="0" minx="0" />         </SPATIALFILTER>       </SPATIALQUERY>       <SIMPLERENDERER>         <SIMPLEPOLYGONSYMBOL fillcolor="255,255,255" filltype="cross" />       </SIMPLERENDERER>     </LAYER>   </GET_IMAGE> </REQUEST> </ARCXML>

Example 2: Setting a spatial query in a GET_FEATURES request.

<?xml version="1.0" encoding="UTF-8"?> <ARCXML version="1.1">     <REQUEST>       <GET_FEATURES outputmode="xml" geometry="false">         <LAYER id="Countries" />            <SPATIALQUERY >              <SPATIALFILTER relation="area_intersection">                 <POLYGON>                 <RING>                    <POINT x="-87.73640582356195" y="41.84726275" />                    <POINT x="-87.73640582356195" y="41.884308250000004" />                    <POINT x="-87.68764017643805" y="41.884308250000004" />                    <POINT x="-87.68764017643805" y="41.84726275" />                    <POINT x="-87.73640582356195" y="41.84726275" />                 </RING>                 </POLYGON>              </SPATIALFILTER>            </SPATIALQUERY>       </GET_FEATURES>     </REQUEST> </ARCXML>

Example 3: Joining DBF files in CONFIG.

<?xml version="1.0" encoding="UTF-8"?> <ARCXML version="1.1">   <CONFIG>     <ENVIRONMENT>       <LOCALE country="US" language="en" variant="" />       <UIFONT color="0,0,0" name="Arial" size="12" style="regular" />       <SCREEN dpi="96" />     </ENVIRONMENT>     <MAP>       <PROPERTIES>         <ENVELOPE minx="-178.215027" miny="18.924782" maxx="-66.969849" maxy="71.406647" name="Initial_Extent" />         <MAPUNITS units="decimal_degrees" />         <FILTERCOORDSYS id="4326" />         <FEATURECOORDSYS id="4326"/>       </PROPERTIES>       <WORKSPACES>          <SHAPEWORKSPACE name="shp_ws-0" directory="<path to USA ESRIDATA>" />       </WORKSPACES>       <LAYER type="featureclass" name="counties" visible="true" id="0">         <DATASET name="COUNTIES" type="polygon" workspace="shp_ws-0" />           <SPATIALQUERY where="counties.STATE_NAME=&apos;Nevada&apos;" jointables="countyinfo" joinexpression="To=[counties.FIPS],From=[countyinfo.FIPS],Type=[scan]" >               <SPATIALFILTER relation="area_intersection">                   <ENVELOPE minx="-126" miny="31" maxx="-108" maxy="46" />             </SPATIALFILTER>           </SPATIALQUERY>         <SIMPLERENDERER>           <SIMPLEPOLYGONSYMBOL fillcolor="27,127,27" filltype="solid" />         </SIMPLERENDERER>       </LAYER>     </MAP>   </CONFIG> </ARCXML>

Example 4: Joining ArcSDE tables in CONFIG.

<?xml version="1.0" encoding="UTF-8"?> <ARCXML version="1.1">   <CONFIG>     <ENVIRONMENT>       <LOCALE country="US" language="en" variant="" />       <UIFONT color="0,0,0" name="Arial" size="12" style="regular" />       <SCREEN dpi="96" />     </ENVIRONMENT>     <MAP>       <PROPERTIES>       <ENVELOPE minx="-175.2" miny="-90.0" maxx="179.2" maxy="83.6" name="Initial_Extent" />       <MAPUNITS units="decimal_degrees" />       <FILTERCOORDSYS id="4326" />       <FEATURECOORDSYS id="4326"/>       </PROPERTIES>       <WORKSPACES>       <SDEWORKSPACE name="sde_ws-4" server="sierra" instance="esri_sde" database="" user="world_data" encrypted="true" password="LXEMUR" />       </WORKSPACES>       <LAYER type="featureclass" name="WORLD.CITY" visible="true" id="0">       <DATASET name="WORLD.CITY" type="point" workspace="sde_ws-4" />         <SPATIALQUERY where="WORLD.CITY.FIPS_CNTRY = WORLD.COUNTRYP.FIPS_CNTRY and WORLD.COUNTRYP.FIPS_CNTRY=&apos;CA&apos; " jointables="WORLD.COUNTRYP" >             <SPATIALFILTER relation="area_intersection">            <ENVELOPE maxy="30" maxx="30" miny="0" minx="0" />           </SPATIALFILTER>         </SPATIALQUERY>       <SIMPLERENDERER>         <SIMPLEMARKERSYMBOL color="27,227,27" width="8" />       </SIMPLERENDERER>       </LAYER>     </MAP>   </CONFIG> </ARCXML>

Example 5: Using both an attribute query and a spatial filter in CONFIG.

<?xml version="1.0" encoding="UTF-8"?> <ARCXML version="1.1">   <CONFIG>     <ENVIRONMENT>       <LOCALE country="US" language="en" variant="" />       <UIFONT color="0,0,0" name="Arial" size="12" style="regular" />       <SCREEN dpi="96" />     </ENVIRONMENT>     <MAP dynamic="true">       <PROPERTIES>         <ENVELOPE minx="-180" miny="-90" maxx="180" maxy="90" name="Initial_Extent" />       <MAPUNITS units="decimal_degrees" />       <FILTERCOORDSYS id="4326" />       <FEATURECOORDSYS id="4326"/>       </PROPERTIES>       <WORKSPACES>         <SHAPEWORKSPACE name="shp_ws-0" directory="<path to WORLD ESRIDATA>"/>       </WORKSPACES>       <LAYER type="featureclass" name="CITIES" visible="true" id="2">       <DATASET name="CITIES" type="point" workspace="shp_ws-0" />       <SPATIALQUERY where="POPULATION &gt; 2000000">         <SPATIALFILTER relation="area_intersection">           <ENVELOPE maxy="30" maxx="30" miny="0" minx="0" />         </SPATIALFILTER>       </SPATIALQUERY>       <SIMPLERENDERER>         <SIMPLEMARKERSYMBOL type="square" width="5" />       </SIMPLERENDERER>       </LAYER>     </MAP>   </CONFIG> </ARCXML>

Example 6: Using a date query in a GET_FEATURES request.

<?xml version="1.0" encoding="UTF-8" ?> <ARCXML version="1.1">   <REQUEST>     <GET_FEATURES featurelimit="25" beginrecord="0" outputmode="xml" geometry="false" envelope="true" compact="true">       <LAYER id="0" />       <SPATIALQUERY subfields="#ALL#" where="MYDATE = {ts '2000-01-07'}" />     </GET_FEATURES>   </REQUEST> </ARCXML>

Back to top