Advanced Queries

• What is a macro?
• Macros for defining simple SQL variables
• Macros for defining table variables
• Macros returning table variables as output

• Defining a pAOI
• The SSA pAOI
• The Mapunit Key List pAOI
• Geographic Region pAOIs
• Importing a WSS AOI
• Querying the pAOI
• Aoi and AoiMapunit tables
• AoiPart table
• AoiSoilMapUnit {Line, Point, Polygon} tables
• AoiSoiSpotFeature {Line, Point} tables
• AoiSoilThematicMap {plus Legend, Rating} tables
• AoiSld table

• Use Categories and the Catalog
• Interpretation Descriptions
• Rule Data
  • Short Format
  • Long Format
• Running the Interpretation
• Using Interpretation Results
• Ad-Hoc Ratings

• Use Categories and the Catalog
• Report Descriptions
• Report Data
  • Short Format
  • Long Format
• Running the Report
• Using Report Results
• Ad-Hoc Reports

Introduction

Tabular queries can access spatial data stored in the Soil Data Mart (SDM) database. Spatial data is stored in tables such as mupolygon, mupoint and muline, which contain soil map unit polygon, point and line features, respectively. These tables have two spatial data columns, one with the data in WGS84 geographic coordinates (epsg:4326) and one with the data in Web Mercator (WM, epsg:3857).

The spatial column names are formed as follows: <table-name><[geo|proj]>. For example in table "mupolygon", the WGS84 data are held in column "mupolygongeo" and the Web Mercator data are in "mupolygonproj". These tables can be joined with other SDM tables using the "mukey" column.

Queries provide a mechanism for defining both transient or persistent "Areas of Interest", the persistent ones may be re-referenced for later access (see The Persistent Area of Interest (pAOI)). Once a pAOI is defined then analyses (called "interpretations" or "ratings") may be performed on the AOI's soil data and the results accessed via subsequent queries or used to create thematic maps (see Interpretations). "Reports" may also be generated from the AOI's soil data, the results are immediately returned and are not persisted with the pAOI. (see Reports).

Tabular queries can also access the supporting data for a thematic map produced in Web Soil Survey (WSS). When a colored map of soil properties or interpretations is requested in WSS, a class value and color are derived for each mapunit in the area of interest. These results are stored temporarily in a database associated with a specific WSS session, and are assigned a unique Thematic Map ID. The ID is obtained from WSS by using the Info button to display the table of map information. Because these data are not in the SDM database, macros (described below) must be used to retrieve the data, and the data are only available until the WSS session is purged or the AOI cleared or replaced. While a Web Soil Survey AOI is still "alive" its data may be imported into SDA to form a new pAOI, complete with all thematic maps associated with the WSS AOI (see Importing a WSS AOI). The Thematic Map ID can also be used with SDA web services to obtain the map image or features. See the Web Service Help page for more information.

Soil Data Access provides two advanced features for querying spatial data:

• Tabular Functions act as "virtual" tables that can be used in the "FROM" clause of a query.
• Macros are condensed statements that SDA expands into full SQL statements.

Most of the techniques described in this document may be used through the Soil Data Access Query Page, some are limited to use through the "post.rest" web service, described further in the Web Service Help Page. Those techniques that are limited to the "post.rest" web service are so identified.

Tabular Functions

These functions either accept or return spatial data expressed in Well-known Text (WKT) format. Note that the WKT must follow the ISO standard definition (see the WKT Wikipedia page for a simple introduction, for example, remember that the list of vertices that defines a polygon must start and end at the same point). Macros are described below, at Macros.

Here are sample SQL statements using the above new functions. First find the survey area symbol for a rectangular area:

select * from SDA_Get_Areasymbol_from_intersection_with_WktWgs84(
  'polygon((
    -121.77100 37.368402,
    -121.77100 37.373473,
    -121.76000 37.373473,
    -121.76000 37.368402,
    -121.77100 37.368402))')

select * from SDA_Get_Areasymbol_from_intersection_with_WktWm(
  'polygon((
    -13555610.9782664 4490483.16765171,
    -13555610.9782664 4491391.31746713,
    -13554135.9341103 4491391.31746713,
    -13554135.9341103 4490483.16765171,
    -13555610.9782664 4490483.16765171))')
	

For the list of all mapunit keys in the same area,

select * from SDA_Get_Mukey_from_intersection_with_WktWgs84(
  'polygon((
    -121.77100 37.368402,
    -121.77100 37.373473,
    -121.76000 37.373473,
    -121.76000 37.368402,
    -121.77100 37.368402))')

select * from SDA_Get_Mukey_from_intersection_with_WktWm(
  'polygon((
    -13555610.9782664 4490483.16765171,
    -13555610.9782664 4491391.31746713,
    -13554135.9341103 4491391.31746713,
    -13554135.9341103 4490483.16765171,
    -13555610.9782664 4490483.16765171))')
	

Or to get the mupolygonkey values for this area:

select * from SDA_Get_Mupolygonkey_from_intersection_with_WktWgs84(
  'polygon((
    -121.77100 37.368402,
    -121.77100 37.373473,
    -121.76000 37.373473,
    -121.76000 37.368402,
    -121.77100 37.368402))')

select * from SDA_Get_Mupolygonkey_from_intersection_with_WktWm(
  'polygon((
    -13555610.9782664 4490483.16765171,
    -13555610.9782664 4491391.31746713,
    -13554135.9341103 4491391.31746713,
    -13554135.9341103 4490483.16765171,
    -13555610.9782664 4490483.16765171))')
	

Combining with an areasymbol test for 'CA646',

select S.mukey, M.musym
from SDA_Get_Mukey_from_intersection_with_WktWm(
  'polygon((
    -13555610.9782664 4490483.16765171,
    -13555610.9782664 4491391.31746713,
    -13554135.9341103 4491391.31746713,
     13554135.9341103 4490483.16765171,
     13555610.9782664 4490483.16765171))') as S,
legend as L,
mapunit M
where M.mukey = S.mukey
and M.lkey = L.lkey
and L.areasymbol = 'CA646'
	

Macros

• What is a Macro?
• Macros for defining simple SQL variables
• Macros for defining table variables
• Macros returning table variables as output
• Examples

What is a Macro

Macros are shorthand statements that are "expanded" into normal SQL statements. They allow creation of SQL statements that would normally not be allowed or present short sequences of SQL statements that address specific needs.

In all cases the macro name and arguments are surrounded by immediately-adjacent "tilde" characters ("~"). No embedded whitespace is allowed. SQL variable names are indicated by an at-sign ("@") followed by an identifier. The identifier may start with an alphabetic character or underscore and be followed by zero or more alphanumeric characters and/or underscores. The macro names and variable names are case-insensitive.

If your query includes the line:

That line will be translated into the following lines:

(The result of the macro expansion contains the original macro as a comment. The expansion is not displayed on the query page, but if you use macros in a query for queued execution the completion email will show the expanded SQL text.)

Following the above macro statement conventional SQL statements may reference the defined variable "@aoi". For example spatial coordinates may be assigned by use of "Well Known Text", here a polygonal boundary is defined in WGS84 (epsg:4326):

select @aoi = geometry::STPolyFromText(
  'polygon((
    -121.157072910308 46.0181639308995,
    -121.321280753631 45.9248106152548,
    -121.348997869021 45.9168439802811,
    -121.157072910308 46.0181639308995
  ))', 4326)
	

A few of the macros also require an "IdGeom" or "IdGeog" table variable. The table variables are defined by these additional macros, in which you supply a name, starting with an @ sign, for the table variable:

These two macro statements are expanded into:

-- ~DeclareIdGeomTable(@intersectedPolygonGeometries)~
-- begin macro substitution
declare @intersectedPolygonGeometries table (id int, geom geometry);
-- end macro substitution
-- ~DeclareIdGeogTable(@intersectedPolygonGeographies)~
-- begin macro substitution
declare @intersectedPolygonGeographies table (id int, geog geography);
-- end macro substitution
	

These two-column tables are used, for example, to associate a mapunit polygon’s spatial definition with a mukey for joining against other tables in the SDM database. A fully worked example will be presented after the following lists of macros.

Macros for defining simple SQL variables

~DeclareBigint(@varname)~
~DeclareBit(@varname)~
~DeclareInt(@varname)~
~DeclareSmallint(@varname)~
~DeclareTinyint(@varname)~
~DeclareFloat(@varname)~
~DeclareReal(@varname)~
~DeclareDate(@varname)~
~DeclareDatetime(@varname)~
~DeclareDecimal(@varname,p,s)~
~DeclareNumeric(@varname,p,s)~
~DeclareTime(@varname)~
~DeclareChar(@varname,n)~
~DeclareVarchar(@varname,[n|max])~
~DeclareGeometry(@varname)~
~DeclareGeography(@varname)~
	

In the case of the Char and Varchar definitions a number (1 to 8000) must be supplied as the second argument, the Varchar definition also allows a MAX specification. For example, to declare a char(3), varchar(4) and varchar(max), use:

~DeclareChar(@a_char_variable,3)~
~DeclareVarchar(@a_varchar_variable,4)~
~DeclareVarchar(@a_second_varchar_variable,max)~
	

In the definitions for Decimal and Numeric variables a precision and scale are required. Precision (p) is the maximum number of digits that will be stored in the variable, both to the left and right of the decimal. Scale (s) is the number of digits to the right of the decimal. Decimal and Numeric are equivalent types. For example, to store numbers less than 1000 with two decimal places, use:

~DeclareDecimal(@a_decimal_variable,5,2)~
	

Macros for defining table variables

While the above simple variables can hold just one value, a table variable can hold zero or more rows for a fixed set of columns. Each column has a name and a data type. A table variable can be used as a data source in a SQL query or as a parameter to a macro. Some of the table variables correspond to table types defined in the database, which allows them to be passed as a function parameter.

There is a set of macros to define table variables containing an integer Id or Varchar(255) string, and optionally a geometry or geography column:

~DeclareIntTable(@varname)~
-- equivalent to type dbo.IntTable
-- single column: i int

~DeclareVarchar255Table(@varname)~
-- equivalent to type dbo.VarcharTable
-- single column: s varchar(255)

~DeclareIdGeomTable(@varname)~
-- two columns: id int
--              geom geometry

~DeclareIdGeogTable(@varname)~
-- two columns: id int
--              geog geography

~DeclareVarchar255GeomTable(@varname)~
-- two columns: s varchar(255)
--              geom geometry

~DeclareVarchar255GeogTable(@varname)~
-- two columns: s varchar(255)
--              geog geography
	

Macros returning table variables as output

In these macros, the parameter listed as "@outtable" is a table variable that will be created on your behalf by the macro. Note that no space is allowed after a comma in the parameters for a macro.

Mapunit points, lines or polygons (WGS84 or WM) clipped to a filtering boundary (WGS84) are returned by:

~GetClippedMapunits(@filter,[point|line|polygon],[geo|proj],@outtable)~
	

Before this statement, create the @filter with macro DeclareGeometry. For the second parameter specify point, line or polygon for the type of feature to return, and for the third parameter specify the spatial reference as geo for geometry (WGS84) or proj for projected (Web Mercator).

GetClippedMapunits will return a table of mukeys and associated clipped geometry. Note that the geometries may include geometry collections, points, multipoints, lines, multilines, polygons and multipolygons. If you wish to split the composites apart, use the output table of the above macro as the input table of:

~SplitIdGeomTable(@intable,@outtable)~
	

Table variable "@intable" must be created by macro DeclareIdGeomTable.

Another macro converts an IdGeom table into an IdGeog table. This is especially useful for determining areas of the spatial objects in the tables.

~GetGeogFromGeomWgs84(@intable,@outtable)~
	

The table variable "@intable" must be created by macro DeclareIdGeomTable, the macro will create "@outtable" using the equivalent of the DeclareIdGeogTable macro.

The tabular data associated with a Web Soil Survey thematic map includes a legend table and a ratings table. The mapunit geographic features clipped to the Area of Interest (AOI) can also be retrieved with a tabular query. The legend table lists the class names and colors used in the map, and the ratings table lists each unique mapunit key with its rating value and map color. The geography table lists all the mapunits in the Area of Interest. These macros are used to obtain this data, either by using an explicit integer literal (in place of the "thematicMapId") or by use of a variable to hold the thematic map ID value (the "@varname").

~GetMapLegend(thematicMapId,@outtable)~
~GetMapRatings(thematicMapId,@outtable)~
~GetAoiMapUnits(thematicMapId,[point|line|polygon],@outtable)~

~GetMapLegend(@varname,@outtable)~
~GetMapRatings(@varname,@outtable)~
~GetAoiMapUnits(@varname,[point|line|polygon],@outtable)~
	

The macros create the appropriate output tables for you.

Note: currently, the AoiMapUnitProj is null when the AOI is defined as a whole soil survey area. The mapunit geography in that case comes from SSURGO.

Examples

Here’s an example of an area of interest (AOI) specification followed by retrieval of data from other tables in the SDM database and aggregation of area by mukey:

-- Define a triangular AOI in WGS84
~DeclareGeometry(@aoi)~
select @aoi = geometry::STPolyFromText('polygon((
  -121.157072910308 46.0181639308995,-121.321280753631 45.9248106152548,
  -121.348997869021 45.9168439802811,-121.157072910308 46.0181639308995
  ))', 4326)

-- Extract all intersected polygons
~DeclareIdGeomTable(@intersectedPolygonGeometries)~
~GetClippedMapunits(@aoi,polygon,geo,@intersectedPolygonGeometries)~


-- Convert geometries to geographies so we can get areas
~DeclareIdGeogTable(@intersectedPolygonGeographies)~
~GetGeogFromGeomWgs84(@intersectedPolygonGeometries,@intersectedPolygonGeographies)~


-- get aggregated areas and associated mukey, musym, nationalmusym, areasymbol, mucertstat (Map Unit Certification Status)
select id, sum(geog.STArea()) as area
into #aggarea from @intersectedPolygonGeographies
group by id;


-- Return the polygons with joined data
select mukey, area, musym, nationalmusym, areasymbol, mucertstat
from #aggarea A, mapunit M, legend L
where A.id = M.mukey and M.lkey = L.lkey
order by id;


-- Return the aggregated area by mukey
select id as mukey, area from #aggarea
	

The Persistent Area of Interest (pAOI)

When you work in the "query environment" any intermediate results such as sets of map units or spatial extent has a "lifetime" of your current set of SQL statements in the "batch" that expires when the batch is finished. The definition of a persistent "Area of Interest" (pAOI) allows persistence beyond 24 hours, as a side effect the pAOI may be used in the generation of maps or in execution of "interpretations" - the maps and interpretation results may later be retrieved via queries, Web Map Service (WMS), or Web Feature Service (WFS) requests.

Once a pAOI is defined it is immutable. You may, however, add the results of "interpretations" performed against the pAOI, this is discussed further in "Interpretations".

The first two pAOIs described below (one for pAOIs based upon a Soil Survey Area or SSA, one for pAOIs based upon a list of mapunit keys) may be defined either interactively through the Query Page or through the "post.rest" web service described in the Web Service Help Page.

Defining the pAOI

• The SSA pAOI
• The Mapunit Key List pAOI
• Geographic Region pAOIs
• Importing a WSS AOI

The simplest pAOI is one defined to match a Soil Survey Area (SSA). An SSA-based pAOI may be created within a query batch by use of a function. Assume that an SSA-pAOI is to be defined for SSA "CA795",

~DeclareVarchar(@ssa,20)~
select @ssa = 'CA795';

~CreateAoiFromSsa(@ssa,@aoiid,@message)~
select @aoiid [aoiid], @message [message]
	

The identifier for the created pAOI is set into the "@aoiid" variable and any error message is set into "@message" (null if no error occurs).

Jumping ahead (macros and functions for working with AOIs will be addressed later) the set of mapunit keys identified for this pAOI can be retrieved:

select * from SDA_Get_AoiMapunit_By_AoiId(@aoiid);
	

The pAOI may be defined either via the Query Page or via the post.rest Web service. In the case of the post.rest service, you have the option of executing the above SQL statements via the "query" parameter or use, for example, the formulation

service=aoi
request=create
ssa=CA795
	

Almost as simple is a pAOI defined from a list of mapunit keys. The keys are placed into an "IntTable" and that table is used to define the pAOI.

Let’s assume you want to define a pAOI based upon all map units that are classified as "Farmland of statewide importance" in SSA "McKensie County, North Dakota (ND053)". The set of mukeys (85 of them) could be identified with this query:

select  mu.mukey
from legend L
left join mapunit MU on L.lkey = MU.lkey
where L.areatypename = 'Non-MLRA Soil Survey Area'
and L.areasymbol = 'ND053'
and mu.farmlndcl  = 'Farmland of statewide importance'
	

The pAOI would be created by passing a pre-populated "IntTable" of mukey values to the macro "CreateAoiFromMukeyList". We could then use the function "SDA_Get_AoiMapunit_By_AoiId" to confirm that the pAOI has been created with 85 mukeys:

~DeclareIntTable(@mukeyList)~
insert into @mukeyList
select  mu.mukey
from legend L
left join mapunit MU on L.lkey = MU.lkey
where L.areatypename = 'Non-MLRA Soil Survey Area'
and L.areasymbol = 'ND053'
and mu.farmlndcl  = 'Farmland of statewide importance';

~CreateAoiFromMukeyList(@mukeyList,@aoiid,@message)~
select count(*) [MukeyCount] from SDA_Get_AoiMapunit_By_AoiId(@aoiid);
select @aoiid [aoiid], @message [message]
	

The pAOI may be defined either via the Query Page or via the post.rest Web service. In the case of the post.rest service, you have the option of executing the above SQL statements via the "query" parameter or use, for example, the formulation:

service=aoi
request=create
mukeylist=1860695,1860696,1860697,1860698,1860699,1860700,1900535
	

A pAOI may also be defined geographically through the post.rest Web service.

The WKT pAOI

service=aoi
request=create
aoicoords=
  multipolygon(
    ((-85.05 39.00,-85.04 39.00,-85.04 39.01,-85.05 39.01,-85.05 39.00)),
    ((-85.03 39.02,-85.00 39.02,-85.00 39.05,-85.03 39.05,-85.03 39.02),
    (-85.02 39.03,-85.01 39.03,-85.01 39.04,-85.02 39.04,-85.02 39.03)))
	

The Shapefile pAOI

An ESRI-defined Shapefile (actually a set of three or more individual files) can be passed to the post.rest Web service if encoded as Base64 strings and concatenated together in a specific order.

The strings are concatenated (with a comma) and supplied as the value to the "aoicoords" parameter, prefixed by "shapefile". The strings are concatenated in order from the encoded SHP, SHX, PRJ, and optionally DBF files, in one of the two forms shown here:

aoicoords=shapefile,<encoded-SHP>,<encoded-SHX>,<encoded-PRJ>
aoicoords=shapefile,<encoded-SHP>,<encoded-SHX>,<encoded-PRJ>,<encoded-DBF>
	

In the following two examples most of the content of the Base64 strings is replaced by an ellipsis ("…"). In this first case only the contents of the "SHP", "SHX" and "PRJ" files is transmitted to SDA:

service=aoi
request=create
aoicoords=shapefile,AAAnC...mNEE=,AAAnC...AACfc,UFJPS...yXV0=
	

The second example also transmits the DBF file (line breaks added for clarity):

service=aoi
request=create
aoicoords=shapefile,
  AAAnC...mNEE=,
  AAAnC...AACfc,
  UFJPS...yXV0=,
  A3MME...wGg==
	

The addition of the DBF file allows definition of a "multipart" pAOI….

The Multipart pAOI

When a pAOI is defined with an SSA or a list of mapunit keys the entirety of the pAOI is treated as a single entity. It is possible to define sub-regions, "the parts", and have a name associated with each. With this partitioning, soil features such as map unit polygons are then split into the separate partitions.

The multiple parts will be created when using the DBF file when defining the pAOI provided that the DBF file includes a field named "partname" (case-insensitive) and that the field width is less than or equal to 20 characters. The part names are retrievable when querying the pAOI tables (described in "Querying the pAOI") and will be shown when creating maps via the GetMap request method of the SDA Web Map Service.

Considerably more control may be exercised over creation of pAOIs from GeoJSON data…

The GeoJSON pAOI

GeoJSON is a format for encoding geographic data. In addition to the ability to store polygonal and multipolygonal data, it allows association of sets of "properties" with geometries. The polygons are assumed to be defined with coordinates in WGS84. The GeoJSON text is specified via the post.rest Web service as an argument to the "aoicoords" parameter as in this first example (the aoicoords input is broken into multiple lines for clarity):

service=aoi
request=create
aoicoords=
 {'type': 'MultiPolygon',
  'coordinates': [
   [[[-85.30, 39.40],[-85.20, 39.40],[-85.20, 39.45],
   [-85.30, 39.45],[-85.30, 39.40]]],
   [[[-85.20, 39.35],[-85.10, 39.35],[-85.10, 39.39],
   [-85.20, 39.39],[-85.20, 39.35]],
   [[-85.17, 39.37],[-85.15, 39.37],[-85.15, 39.38],
   [-85.17, 39.38],[-85.17, 39.37]]]]
 }
	

The use of GeoJSON parameters allows composition of multipart part names from the parameter’s attribute values as well as filtering by the parameter’s attribute values. In this first GeoJSON sample the "partname" (case-insensitive) property is explicitly named. Use of this GeoJSON sample will yield a multipart pAOI with two named parts, one labeled "Sister" and one labeled "Brother":

{
'type': 'FeatureCollection',
'features': [
{
 'type': 'Feature',
 'geometry': {
   'type': 'Polygon',
    'coordinates':
    [[[-100.0,40.0],[-100.1,40.0],[-100.1,40.1],
    [-100.0,40.1],[-100.0,40.0]]]
  },
  'properties': {'partName': "Sister" }
},
{
 'type': 'Feature',
 'geometry': {
  'type': 'Polygon',
   'coordinates':
   [[[-100.2,39.8],[-100.3,39.8],[-100.3,39.9],
   [-100.2,39.9],[-100.2,39.8]]]
  },
  'properties': {'partName': 'Brother' }
}]}
	

The actual name of the pAOI’s part defaults to the value of the "partname", in the above case "Sister" or "Brother". Both parts are included in the pAOI.

Consider this next GeoJSON sample:

{"type":"FeatureCollection","features":
 [
 {"type":"Feature",
  "properties":{"Id":3549249,"PLU Number":"D","Calculated Acres":0.03},
  "geometry":{"type":"Polygon","coordinates":[...]}},
 {"type":"Feature",
  "properties":{"Id":3263595,"PLU Number":"F","Calculated Acres":0.03},
  "geometry":{"type":"Polygon","coordinates":[...]}},
 {"type":"Feature",
  "properties":{"Id":3187561,"PLU Number":"2","Calculated Acres":33.46},
  "geometry":{"type":"Polygon","coordinates":[...]}},
 {"type":"Feature",
  "properties":{"Id":3176402,"PLU Number":"3","Calculated Acres":17.14},
  "geometry":{"type":"Polygon","coordinates":[...]}},
 {"type":"Feature",
  "properties":{"Id":3038245,"PLU Number":"5A","Calculated Acres":3.35},
  "geometry": {"type":"Polygon","coordinates":[...]}}
]}
	

If used to define a pAOI with:

service=aoi
request=create
aoicoords={"type":"FeatureCollection","features":...
	

a pAOI would be defined that is not "multipart" – there is no property with the name "partname".

One alternative would be to specify a property other than "partname". The property’s name is surrounded by square brackets. If we want to use the "PLU Number", the request parameter "partname" can be used:

service=aoi
request=create
aoicoords= {"type":"FeatureCollection","features":...
partname=[PLU Number]
	

The name of the pAOI part can also be formed by composition from a non-empty set of the property values, composed by means of the Microsoft .NET "String.Format Method". The non-empty list of square-bracketed property names is followed by a semicolon and the composite format string. For example, the above GeoJSON sample could get part names similar to "5A (3038245)" (drawn from the "PLU Number" and "Id" fields with an extra space and parentheses around the ID by means of:

service=aoi
request=create
aoicoords= {"type":"FeatureCollection","features":...
partname=[PLU Number],[Id];{0} ({1})
	

The GeoJSON data may also be filtered. The "filter" is a SQL "WHERE" clause that is applied to each feature’s properties. The property names are surrounded by square brackets. For example, to retain only features with a "Calculated Acres" value greater than 0.03,

service=aoi
request=create
aoicoords= {"type":"FeatureCollection","features":...
filter=[Calculated Acres] > 0.03
	

The filter may be arbitrarily complex. For example feature selection based upon set inclusion may also be combined with the calculation:

service=aoi
request=create
aoicoords= {"type":"FeatureCollection","features":...
filter=[Calculated Acres] > 0.03 and [Id] in (3038245, 3187561, 3549249)
	

The "filter" and "partname" SDA request parameters are independent of one another, they may be used together.

Web Soil Survey (WSS) creates relatively short-lived AOIs for defining areas to be mapped, interpreted and reported upon. A WSS AOI lasts as long as the user is working with it, when the user clears the AOI, creates a new AOI or logs out the AOI normally goes away immediately. While the WSS AOI is in use its data may be copied into SDA.

In order to perform the copy the WSS "AoiID" number is required. With the WSS "Identify" tool, click within the AOI boundary on either the "AOI", "Soil Map" or "Soil Data Explorer" tabs. The AoiID ls listed with other data below the map image. The copied data includes any interpretation evaluations (Soil Suitabilities and Limitations, Proierties and Qualities) that have been performed in association with the WSS AOI. By using the "Identify" tool within the "Soil Data Explorer" when a thematic map is displayed, you may retrieve the "ThematicMapID" as well (useful for map generation in the context of the WSS AOI).

A macro is used to perform the copy. It returns the identification number of the freshly-created SDA persistent AOI. Once the WSS AOI data has been imported any of the previously described functions may be used to access the data. The persistent SDA is an SSA AOI if the WSS Aoi was created from a Soil Survey Area, otherwise it is an SDA Geographic Region AOI.

The macro for importing a WSS AOI and all related interpretation results:

~CreateAoiFromWssAoi(@wAoiId,@pAoiId,@message)~
  -- creates an AOI by cloning one from WSS with WSS "@wAoiId" value,
  -- returns @pAoiId and @message, @message is null of the
  -- pAoi was created. If an error occurs "-1" will be returned
  -- for the pAoiId value.

The use of the macro is straight-forward. Here a valid-at-the-time WSS AoiId is used to import the WSS Aoi.

-- define a variable to hold the AoiID reported by WSS
~DeclareInt(@wAoiId)~
select @wAoiId = 25731;

-- perform the copy
~CreateAoiFromWssAoi(@wAoiId,@pAoiId,@message)~

-- display resultant SDA pAoiId and any error
select @pAoiId as pAoiId,@message as message
	

Supplying a non-existent wAoiId (such as 99999) results in the return of "-1" for the pAoiId and the message "WSS AOI not found for wAoiID value 99999".

Querying the pAOI

The pAOI is persisted in a hierarchical set of tables that are accessed through a series of "Tabular Functions". Not all tables are populated for each type of pAOI, in addition the "thematic" tables are only populated when "Interpretations" are calculated. Following are the tabular functions that access each of the pAoi tables.

Three columns are shown for the results of each function: the name of the returned field, the type of the field, and (optionally) a remark. All require one or two ID values for data retrieval. You (or your application) are responsible for retaining this information.

Aoi and AoiMapunit tables

Creating a pAOI always creates an Aoi record (although not all fields may be populated) and creates one AoiMapunit record for each map unit associated with the pAOI.

Function SDA_Get_Aoi_By_AoiId(@pAoiId) returns:

Function SDA_Get_AoiMapunit_By_AoiId(@pAoiId) returns:

Aoi Part table

This table is populated for all pAOIs created via an "aoicoords" specification, it is unpopulated for SSA and map unit key pAOIs.

Function SDA_Get_AoiPart_By_AoiId(@pAoiId) returns:

Function SDA_Get_AoiPart_By_AoiId_and_AoiPartId(@pAoiId, @AoiPartID) returns the same fields, it is more specific in its search.

AoiSoilMapUnit {Line, Point, Polygon} tables

These tables are only populated for pAOIs created from an "aoicoords" specification. All soil map unit polygons, points and lines are clipped to individual pAOI part boundaries. Since, for feature location purposes, small extents of a map unit may be spatially represented as "points" and "lines" instead of "polygons", a portion of the theoretical "area" of each "point" and "line" is apportioned to each part and the total of these apportioned areas is captured in the AoiSoilMapUnitPolygon record for the overlapping lines and points.

Function SDA_Get_AoiSoilMapunitLine_By_AoiId(@pAoiId) returns:

Function SDA_Get_AoiSoilMapunitLine_By_AoiId_And_AoiPartId(@pAoiId, @AoiPartID) returns the same fields, it is more specific in its search.

Function SDA_Get_AoiSoilMapunitPoint_By_AoiId(@pAoiId) returns:

Function SDA_Get_AoiSoilMapunitPoint_By_AoiId_And_AoiPartId(@pAoiId, @AoiPartID) returns the same fields, it is more specific in its search.

Function SDA_Get_AoiSoilMapunitPolygon_By_AoiId(@pAoiId) returns:

Function SDA_Get_AoiSoilMapunitPolygon_By_AoiId_And_AoiPartId(@pAoiId, @AoiPartID) returns the same fields, it is more specific in its search.

AoiSoiSpotFeature {Line, Point} tables

These tables are only populated for pAOIs created from an "aoicoords" specification.

Function SDA_Get_AoiSoilSpotFeatureLine_By_AoiId(@pAoiId) returns:

Function SDA_Get_AoiSoilSpotFeatureLine_By_AoiId_And_AoiPartId(@pAoiId, @AoiPartID) returns the same fields, it is more specific in its search.

Function SDA_Get_AoiSoilSpotFeaturePoint_By_AoiId(@pAoiId) returns:

Function SDA_Get_AoiSoilSpotFeaturePoint_By_AoiId_And_AoiPartId(@pAoiId, @AoiPartID) returns the same fields, it is more specific in its search.

AoiSoilThematicMap {plus Legend, Rating} tables

When an interpretation is calculated for a pAOI the results are saved into the AoiSoilThematicMap* tables: one record capturing the parameters for the interpretation into the AoiSoilThematicMap table, one record into the AoiSoilThematicMapLegend table for each legend entry, and one into the AoiSoilThematicMapRating table for each map unit key.

Function SDA_Get_AoiSoilThematicMap_By_AoiId(@pAoiId) returns:

Function SDA_Get_AoiSoilThematicMap_By_AoiSoilThematicMapId (@AoiSoilThematicMapId) returns the same fields, it is more specific in its search.

Function SDA_Get_AoiSoilThematicMapLegend_By_AoiId(@pAoiId) returns:

Function SDA_Get_AoiSoilThematicMapLegend_By_AoiSoilThematicMapId (@AoiSoilThematicMapId) returns the same fields, it is more specific in its search.

Function SDA_Get_AoiSoilThematicMapRating_By_AoiId(@pAoiId) returns:

Function SDA_Get_AoiSoilThematicMapRating_By_AoiSoilThematicMapId (@AoiSoilThematicMapId) returns the same fields, it is more specific in its search.

AoiSld table

A "Styled Layer Descriptor" (SLD) is a mechanism for instructing SDA how to render maps. You may define one or more SLD instances (each describing "painting" of one or more layers), these are associated with a persistent AOI. Each SLD must be registered with a pAOI, in doing so you receive an sldId that is used when rendering maps to specify the SLD to use.

The SLD is in the form of an XML document. The easiest way to get started is to request the "default style" of one or more layers from SDA and then edit the XML (using an ordinary text editor such as "Notepad") before registering your masterwork (information on the SLD may be found at SLD - Soil Data Access uses MapServer libraries to provide SLD support for map image creation via the GetMap Web Map Service).

For example, the default SLD for the "mapunitpoly" layer may be retrieved submitting a Web Map Service (WMS) "GetStyles" request, this request returns the response formatted as XML.

    https://sdmdataaccess.sc.egov.usda.gov/spatial/sdm.wms?service=wms&request=GetStyles&layers=mapunitpoly
	

With a pAoiId in-hand, the text of the SLD is registered through the post.rest web service using the following parameters:

 service=aoi
 request=registersld
 aoiid=<the value of the pAoiId>
 sld=<the text of the SLD>
	

Note that an SLD, once registered, may be used promiscuously. For example, if an SLD is registered to pAoiId=12345 and given an sldId of 42, you may also use #42 (without further registration) for drawing maps for any other pAOI (for example, if you also created a pAOI with pAoiId=12479 without registering #42 with #12479). In addition, you are free to use #42 to create maps without specification of a pAoiId.

Once created, the registered information may be retrieved...

Function SDA_Get_AoiSld_By_AoiId(@pAoiId) returns:

Caution: the SLD, as an XML document, contains "tags" (specifiers surrounded by angle brackets) that may confuse a Web Browser. Do not retrieve and then display without proper encoding (for example, replace a left angle bracket, "<" with its encoded form, "&lt;").

Function SDA_Get_AoiSld_By_AoiSldId (@AoiSldId) returns the same fields, it is more specific in its search.

Interpretations

The soils data includes a number of "intrinsic" attributes that may be accessed directly by performing queries. For example, given a set of map unit keys, the "farm land classification" value of each mapunit may be retrieved with a query such as:

There are other "interpreted" attributes that must be derived computationally. A broad set of these attributes (also variously called "interpretations", "properties" or "ratings") are predefined and may be used accessed through Soil Data Access in the context of a pAOI. Once the calculation is performed it is assigned an identification number (the "interpresultid") and the interpretation results are then accessible through queries (see above, AoiSoilThematicMap {plus Legend, Rating} tables, or via the Web Map and Web Feature services ("WMS" and "WFS", see the Web Service Help page for more information).

The "catalog" of the interpreted attributes (also called "ratings" or "rules") mirrors that presented by Web Soil Survey. Once an AOI is defined within WSS and you've switched to the "Soil Data Explorer" tab, almost two hundred attributes will be presented under the "Suitabilities and Limitations for Use" and "Soil Properties and Qualities" tabs. All techniques described in this "Interpretations" discussion must be performed through the "post.rest" web service, see the Web Service Help Page.

Use Categories and the Catalog

A catalog of all interpretations that are applicable to a given pAOI may be requested from the post.rest service by specifying the following parameters:

      service=interpretation
      request=getcatalog
      aoiid=...the pAoiId value...
  

The result is a JSON string, a sample excerpted here in "prettified" form:

      { "tables": [
        {  "tablename":"properties",
         "folders": [ {
             "foldername":"Soil Chemical Properties",
             "attributes": [
              {
               "attributename":"Calcium Carbonate (CaCO3)",
               "attributekey":"98"
              }, ...
             ]
          }, ...
         ]
        }, {
         "tablename":"interpretations",
         "folders": [ {
             "foldername":"Building Site Development",
             "attributes": [
              {
               "attributename":"Corrosion of Concrete",
               "attributekey":"95"
              }, ...
             ]
          }, ...
        ]
      } }
  

There are two important "shortnames" in each interpretation: the "attributename" (with a value such as "Calcium Carbonate (CaCO3)") and the corresponding "attributekey" (with the corresponding value "98"). The "attributename" is for your edification, the "attributekey" is used in the request for the interpretation's input options ("getruledata", described below).

The list provided by "getcatalog" may be filtered by specifying a WSS "Soil Use". The list of these uses may be requested with:

      service=interpretation
      request=getusecategories
  

The response is:

      [
         {
          "usecategoryid": 0,
          "usecategoryname":"All Uses"
         },
         {
          "usecategoryid": 4,
          "usecategoryname":"Cropland"
         },
         {
          "usecategoryid": 6,
          "usecategoryname":"Forestland"
         },
         {
          "usecategoryid": 7,
          "usecategoryname":"Horticulture"
         },
         {
          "usecategoryid": 9,
          "usecategoryname":"Hayland/Pastureland"
         },
         {
          "usecategoryid": 10,
          "usecategoryname":"Rangeland"
         },
         {
          "usecategoryid": 11,
          "usecategoryname":"Recreation"
         },
         {
          "usecategoryid": 16,
          "usecategoryname":"Urban Uses"
         }
      ]
  

If you wish to filter by one of the above categories other than "All Uses", add the category's number to the getcatalog request. For example, for "Urban Uses" (usecategoryid=16), request:

      service=interpretation
      request=getcatalog
      aoiid=...the pAoiId value...
      usecategoryid=16
  

Interpretation Descriptions

You may retrieve the description of an interpretation given its attribute key. The response is returned as a JSON string with embedded return ("\r") and newline ("\n") characters.

For example, the request and response for "Corrosion of Concrete" (attributekey=95) is:

      service=interpretation
      request=getdescription
      attributekey=95

      {
        "description":
              "\"Risk of corrosion\" pertains to potential soil-induced 
               electrochemical or chemical action that corrodes or weakens 
               concrete. The rate of corrosion of concrete is based mainly on 
               the sulfate and sodium content, texture, moisture content, and 
               acidity of the soil. Special site examination and design may be 
               needed if the combination of factors results in a severe hazard 
               of corrosion. The concrete in installations that intersect soil 
               boundaries or soil layers is more susceptible to corrosion than 
               the concrete in installations that are entirely within one kind 
               of soil or within one soil layer.
               \r\n\r\n
               The risk of corrosion is expressed as \"low,\" \"moderate,\" 
               or \"high.\""
      }
  

Rule Data

Once you have a pAOI and an attributekey you may request information about the interpretation's required input data. Typically the "short" format may be used as-is to request that the interpretation be executed (if you know ahead of time that the input data is complete and acceptable for your purposes by default, you can jump ahead and simply request the interpretation, see Running the Interpretation below).

Short Format

The "short" format ("short" is the default for a "getruledata" request) presents the interpretation's default parameters for your immediate use when requesting interpretation execution ("get rating", below). It is a starting point for editing, should you need to change the defaults.

      service=interpretation
      request=getruledata
      aoiid=502
      attributekey=471
         

The response is a collection of "shortname"/"value" pairs, in this instance the prettified response is:

       {
          "ComponentPercentCutoff": "",
          "aoiid": "502",
          "ruleid": "471",
          "AggregationMethod": "Dominant Condition",
          "TiebreakRule": "-1"
       }
    

Note that the aoiid and attributekey (renamed as "ruleid") are duplicated in the returned data.

Here's a more complicated short response for "Flooding Frequency Class", attributekey=12:

       {
          "ComponentPercentCutoff":"",
          "aoiid":"502",
          "ruleid":"12",
          "AggregationMethod":"Dominant Condition",
          "TiebreakRule":"1",
          "BeginningMonth":"January",
          "EndingMonth":"December"
       }
    

Long Format

The long format response to "getruledata", formatted as XML, provides extra information including full names for parameters (besides the "shortname"), attribute type, parameter validation constraints, default values, parameter options and nesting information for trees of options (the XML content maps exactly to the input form presented by WSS, browsing through assorted rating input forms in WSS and requesting the "long" format from SDA will be quite instructive). In order to request the long format, "format=long" must be added to the "getruledata" request. Again for "Soil Taxonomic Classification",

      service=interpretation
      request=getruledata
      aoiid=502
      attributekey=471
      format=long
  

The short format response for "471" is:

       {
          "ComponentPercentCutoff":"",
          "aoiid":"502",
          "ruleid":"471",
          "AggregationMethod":"Dominant Condition",
          "TiebreakRule":"-1"
       }
    

Here's the long format response, prettified. Note the highlighted items, compare them to the short response above:

          <?xml version="1.0" encoding="UTF-8"?>
          <xml ...>
             <Group Name="Advanced Options" Mode="Advanced" aoiid="502" ruleid="471">
                <Group Name="Aggregation Method">
                   <Parameter Name="Aggregation Method" DescriptionID="V0"
                        ControlType="DropDownList" MultipleSelect="False"
                        MinimumSelected="1" MaximumSelected="1"
                        Default=Highlight begin."Dominant Condition"Highlight end. Value="Dominant Condition"
                        shortname=Highlight begin."AggregationMethod"Highlight end.>
                      <ListItem Name="Dominant Condition" Value="Dominant Condition"
                          DescriptionID="A1" />
                      <ListItem Name="Dominant Component" Value="Dominant Component"
                          DescriptionID="A2" />
                   </Parameter>
                </Group>
                <Group Name="Component Percent Cutoff">
                   <Parameter Name="Component Percent Cutoff" DescriptionID="V1"
                        ControlType="Text" UseRegEx="Optional" DataType="Integer"
                        MaximumLength="3" RegEx="^0*(100|[0-9]{0,2})$"
                        ValidationMessage="The component percent cutoff ..., or may be null."
                        MinimumValue="0" MaximumValue="100" Required="False"
                        Default=Highlight begin.""Highlight end. Value="" shortname=Highlight begin."ComponentPercentCutoff"Highlight end. />
                </Group>
                <Group Name="Tie-break Rule" Enabled="False">
                   <Parameter Name="Tie-break Rule" DescriptionID="V2"
                        ControlType="Radio" Default=Highlight begin."-1"Highlight end. Value="-1"
                        shortname=Highlight begin."TiebreakRule"Highlight end.>
                      <ListItem Name="Lower" Value="-1" />
                      <ListItem Name="Higher" Value="1" />
                   </Parameter>
                </Group>
                <Group Name="aoiid" shortname="aoiid" Enabled="False">
                   <Parameter Name="aoiid" shortname=Highlight begin."aoiid"Highlight end.
                        ControlType="Text" UseRegEx="Optional" DataType="Integer"
                        MaximumLength="10" RegEx="^0*(100|[0-9]{0,10})$"
                        MinimumValue="1" MaximumValue="2147483647" Required="True"
                        Default=Highlight begin."502"Highlight end. Value="502" />
                </Group>
                <Group Name="ruleid" shortname="ruleid" Enabled="False">
                   <Parameter Name="ruleid" shortname=Highlight begin."ruleid"Highlight end.
                        ControlType="Text" UseRegEx="Optional" DataType="Integer"
                        MaximumLength="10" RegEx="^0*(100|[0-9]{0,10})$"
                        MinimumValue="1" MaximumValue="2147483647" Required="True"
                        Default=Highlight begin."471"Highlight end. Value="471" />
                </Group>
             </Group>
          </xml>
    

Note the nested hierarchical structure (made plain by "prettifying"), the presence of a "shortname" attribute in each "Parameter", and in the case where a "Parameter" contains one or more "ListItem" elements within, the Parameter's "Value" and "Default" match one of the child "ListItem"'s "Value"s.

           <Parameter Name="Aggregation Method" DescriptionID="V0"
                ControlType="DropDownList" MultipleSelect="False"
                MinimumSelected="1" MaximumSelected="1"
                Default="Dominant Condition" Value="Dominant Condition"
                shortname="AggregationMethod">
              <ListItem Name="Dominant Condition" Value=Highlight begin."Dominant Condition"Highlight end.
                  DescriptionID="A1" />
              <ListItem Name="Dominant Component" Value=Highlight begin."Dominant Component"Highlight end.
                  DescriptionID="A2" />
           </Parameter>
    

Therefore specification of "Dominant Highlight begin.ComponentHighlight end." would be equally valid:

       {
          "ComponentPercentCutoff":"",
          "aoiid":"502",
          "ruleid":"471",
          "AggregationMethod":"Dominant Highlight begin.ComponentHighlight end.",
          "TiebreakRule":"-1"
       }
    

Those Parameters with Enabled="False" are not not to be changed.

The "V"-series "DescriptionID" values are as follows:

The "A"-series "DescriptionID" values are retrieved via query. Within the "Aggregation Method", for the ListItem

        <ListItem Name="Dominant Condition" Value="Dominant Condition" DescriptionID="A1" />
	

the query to retrieve the description for "Dominant Condition" would be:

There are a few interpretations that lack a complete set of default values. When the "short" data is used as-is with "getrating" an error occurs (in this instance you'd see "<ServiceException>An invalid Top Depth was specified.</ServiceException>")

Here's the short format data for "Calcium Carbonate (CaCO3)" (attributekey=98) followed by an excerpt from the XML:

    {
       "ComponentPercentCutoff":"",
          "TopDepth":"",
          "BottomDepth":"",
          "aoiid":"502",
          "ruleid":"98",
          "AggregationMethod":"Dominant Component",
          "TiebreakRule":"1",
          "InterpretNullsAsZero":"True",
    Highlight begin."HorizonAggregationMethod":"Depth Range"Highlight end.,
          "DepthUnits":"Centimeters"
    }

Note that the "HorizonAggregationMethod" defaults to "Depth Range". Within the "Depth Range" are two child parameters, "TopDepth" and "BottomDepth". Both show Required="True" and no default value is supplied for either:

         <Parameter Name="Layer Options (Horizon Aggregation Method)"
             DescriptionID="V4" ControlType="Radio"
            Default="Depth Range" Value="Depth Range"
            shortname="HorizonAggregationMethod">
            <ListItem Name="Surface Layer (Not applicable)" Value="Surface Layer" />
            <ListItem Name="Depth Range (Weighted Average)" Value="Depth Range">
               <Group Name="Depth Range">
                  <Range Name="Depth Range" Type="Integer">
                     <Parameter Name="Top Depth" ValidateWhenHidden="True"
                           ControlType="Text" UseRegEx="Optional"
                           DataType="Integer" MaximumLength="4"
                           RegEx="^0*[0-9]{1,4}$" shortname=Highlight begin."TopDepth"Highlight end.
                           ValidationMessage="Any horizon depth must be ..."
                           MinimumValue="0" MaximumValue="9999" Required="True"
    Highlight begin.Default=""Highlight end. Value="" />
                      <Parameter Name="Bottom Depth" ValidateWhenHidden="True"
                           ControlType="Text" UseRegEx="Optional"
                           DataType="Integer" MaximumLength="4"
                           RegEx="^0*[0-9]{1,4}$" shortname=Highlight begin."BottomDepth"Highlight end.
                           ValidationMessage="Any horizon depth must be ..."
                           MinimumValue="0" MaximumValue="9999" Required="True"
    Highlight begin.Default=""Highlight end. Value="" />
                  </Range>
                  <Group Name="Units of Measure">
                     <Parameter Name="Units of Measure" ControlType="Radio"
                    Default="Centimeters" Value="Centimeters" shortname="DepthUnits">
                        <ListItem Name="Inches" Value="Inches" />
                        <ListItem Name="Centimeters" Value="Centimeters" />
                     </Parameter>
                  </Group>
               </Group>
            </ListItem>
            <ListItem Name="All Layers (Weighted Average)" Value="All Layers" />
         </Parameter>

One valid approach is to provide both top and bottom depths, in this case the depth range is specified as 35 to 142 centimeters:

    {
       "ComponentPercentCutoff":"",
          "aoiid":"502",
          "ruleid":"98",
          "AggregationMethod":"Dominant Component",
          "TiebreakRule":"1",
          "InterpretNullsAsZero":"True",
    Highlight begin."HorizonAggregationMethod":"Depth Range"Highlight end.,
          "DepthUnits":"Centimeters",
    Highlight begin."TopDepth":"35"Highlight end.,
    Highlight begin."BottomDepth":"142"Highlight end.
    }

Another posssibility, if appropriate to your analytical requirements, is to specify "All Layers" for the "HorizonAggregationMethod":

    {
       "ComponentPercentCutoff":"",
          "aoiid":"502",
          "ruleid":"98",
          "AggregationMethod":"Dominant Component",
          "TiebreakRule":"1",
          "InterpretNullsAsZero":"True",
    Highlight begin."HorizonAggregationMethod":"All Layers"Highlight end.
    }

Running the Interpretation

       service=interpretation
       request=getrating
       shortformdata=
	     {
          "ComponentPercentCutoff":"",
          "aoiid":"502",
          "ruleid":"98",
          "AggregationMethod":"Dominant Component",
          "TiebreakRule":"1",
          "InterpretNullsAsZero":"True",
          "HorizonAggregationMethod":"All Layers"
         }
	

Your result should be similar to:

       {"interpresultid":"504"}
    

Regarding the JSON string you supply for the "shortformdata":
   - The names and associated values are case-sensitive.
   - Spaces, tabs and newlines are permitted outside of the quoted names and values.
   - When in doubt, consult the "long" format XML provided by "getruledata" for required paraeter names and value options.

Note that if the default shortformdata is correct for your chosen rating then you can skip its use and specify, in its place, the aoid and ruleid (as the "attributekey"):

       service=interpretation
       request=getrating
       aoiid=502
       attributekey=98
	

Using Interpretation Results

When an interpretation is run using "getrating" an "interpresultid" is returned (also called "AoiSoilThematicMapId"). This the key to data stored with the AOI, and may be queried using functions described in AoiSoilThematicMap {plus Legend, Rating} tables. The execution creates an AoiSoilThematicMap record (under the originally-specified AOI) and, under that, one or more records in the "AoiSoilThematicMapRating" and "AoiSoilThematicMapLegend" tables. The rating results are assigned a color and the possible colors and associated legend text are placed in the "AoiSoilThematicMapLegend".

The "interpresultid" may also be used to generate maps colored "thematic maps" through service requests to the SDA "Web Map Service" (WMS) web service, described in the Web Service Help page.

Ad-Hoc Ratings

Queries may be used to define "ad-hoc" interpretation results. For each map unit in an AOI, rating results may be defined (either as text or numeric or both) and a color (in the form "#rrggbb", where "rr" is a hexadecimal red value, from 00 to ff, "gg" and "bb" are the corresponding green and blue values). An optional legend may also be defined. Macro-created tables are used to hold the rating and optional legend data. An additional pair of macros is used to define a set of AoiSoilThematicMap, AoiSoilThematicMapRating and optional AoiSoilThematicMapLegend records, these macros return an "interpresultid". This ad-hoc AOI may be used as described above, in Using Interpretation Results.

The "rating table" is defined with the macro:

         ~DeclareAdHocRatingTable(@ratingTable)~
	

        (MapUnitKey int not null, MapUnitRatingString varchar(255) null,
        MapUnitRatingNumeric numeric(17, 6) null, RgbString varchar(11) not null)
	

        ~DeclareAdHocLegendTable(@legendTable)~
	

        (sequence int, LegendText varchar(255) not null, RgbString varchar(11) not null)
	

        ~LoadAdHocInterpretation(@pAoiId,@ratingTable,@interpresultid,@message)~
        ~LoadAdHocInterpretation(@pAoiId,@ratingTable,@legendTable,@interpresultid,@message)~
	

          ~DeclareInt(@aoiid)~
          select @aoiid = 502;

          insert into @ratingTable
          select  MapUnitKey, MapUnitRatingString, MapUnitRatingNumeric, RgbString
          from baf.dbo.aoisoilthematicmaprating
          where aoisoilthematicmapid = 1502

          ~DeclareAdHocLegendTable(@legendTable)~
          -- use the "AoiSoilThematicLegendID" as a sequence number - it is in the correct order
          insert into @legendTable (sequence, LegendText, RgbString)
          select AoiSoilThematicLegendID, LegendText, RgbString
          from baf.dbo.aoisoilthematicmaplegend
          where aoisoilthematicmapid = 1502

          ~LoadAdHocInterpretation(@aoiId,@ratingTable,@legendTable,@interpresultid,@message)~
          select @interpresultid [interpresultid], @message [message]
	

Reports

"Reports" are used to generate "DocBook"-formatted reports for an AOI. When a report is executed (see Running the Report) the results are returned immediately, these results are not stored in the AOI. The "catalog" of reports mirrors that presented by Web Soil Survey. Once an AOI is defined within WSS and you've switched to the "Soil Data Explorer" tab, over one hundred reports will be presented under the "Reports" tab.

All techniques described in this "Reports" discussion must be performed through the "post.rest" web service, see the Web Service Help Page.

Use Categories and the Catalog

A catalog of all reports that are applicable to a given pAOI may be requested from the post.rest service by specifying the following parameters:

      service=report
      request=getcatalog
      aoiid=...the pAoiId value...
  

The result is a JSON string, a sample excerpted here in "prettified" form:

  {
   "tables": [
    {
     "tablename": "reports",
     "folders": [
      {
         "foldername": "AOI Inventory",
         "reports": [
          {
           "reportname": "Component Description (Nontechnical)",
           "reportid": "461"
          }, ...
        ]
      },
      {
         "foldername": "Building Site Development",
         "reports": [
          {
           "reportname": "Dwellings and Small Commercial Buildings",
           "reportid": "17"
          }, ...
         ]
      }, ...
     ]
    }
   ]
  }
  

There are two important "shortnames" in each interpretation: the "reportname" (with a value such as "Component Description (Nontechnical)") and the corresponding "reportid" (with the corresponding value "461"). The "reportname" is for your edification, the "reportid" is used in the request for the report's input options ("getreportdata", described below).

The list provided by "getcatalog" may be filtered by specifying a WSS "Soil Use". The list of these uses may be requested with:

      service=report
      request=getusecategories
  

The response is:

      [
         {
          "usecategoryid": 0,
          "usecategoryname":"All Uses"
         },
         {
          "usecategoryid": 4,
          "usecategoryname":"Cropland"
         },
         {
          "usecategoryid": 6,
          "usecategoryname":"Forestland"
         },
         {
          "usecategoryid": 7,
          "usecategoryname":"Horticulture"
         },
         {
          "usecategoryid": 9,
          "usecategoryname":"Hayland/Pastureland"
         },
         {
          "usecategoryid": 10,
          "usecategoryname":"Rangeland"
         },
         {
          "usecategoryid": 11,
          "usecategoryname":"Recreation"
         },
         {
          "usecategoryid": 16,
          "usecategoryname":"Urban Uses"
         }
      ]
  

If you wish to filter by one of the above categories other than "All Uses", add the category's number to the getcatalog request. For example, for "Urban Uses" (usecategoryid=16), request:

      service=report
      request=getcatalog
      aoiid=...the pAoiId value...
      usecategoryid=16
  

Report Descriptions

You may retrieve the description of a report given its report id. The response is returned as DocBook xml.

For example here is the retrieval of the description for report "Dwellings and Small Commercial Buildings" (reportid=17) and its response (with most content removed):

      service=report
      request=getdescription
      reportid=17

      <?xml version="1.0" encoding="UTF-8"?>
      <section>
        <title>Dwellings and Small Commercial Buildings</title>
        <para>Soil properties influence the development of building sites, ...</para>
	    ...
        <para>Government ordinances and regulations that restrict certain ...</para>
      </section>

Report Data

Once you have a pAOI and an reportid you may request information about the report's required input data. Typically the "short" format may be used as-is to request that the report be executed (if you know ahead of time that the input data is complete and acceptable for your purposes by default, you can jump ahead and simply request the report, see Running the Report below).

Short Format

The "short" format ("short" is the default for a "getreportdata" request) presents the rule's default parameters for your immediate use when requesting report execution ("get report", below). It is a starting point for editing, should you need to change the defaults.

      service=report
      request=getreportdata
      aoiid=5940
      reportid=33
    

The response is a collection of "shortname"/"value" pairs, in this instance the prettified response is:

       {
         "aoiid":"5940",
         "reportid":"33",
         "reportname":"Taxonomic Classification of the Soils",
         "Includeminorsoils":"false",
         "Displaynationalmapunitsymbols":"false"
       }
    

Note that the aoiid and reportid are duplicated in the returned data.

Here's a more complicated short response for "Irrigated and Nonirrigated Yields by Map Unit Component", reportid=6:

       {
          "aoiid":"5940",
          "reportid":"6",
          "reportname":"Irrigated and Nonirrigated Yields by Map Unit Component",
          "Includeminorsoils":"false",
          "Cropnames":"",
          "Displaynationalmapunitsymbols":"false"
       }
    

Long Format

The long format response to "getreportdata", formatted as XML, provides extra information including full names for parameters (besides the "shortname"), attribute type, parameter validation constraints, default values, parameter options and nesting information for trees of options (the XML content maps exactly to the input form presented by WSS, browsing through assorted rating input forms in WSS and requesting the "long" format from SDA will be quite instructive). In order to request the long format, "format=long" must be added to the "getreportdata" request. Again for "Taxonomic Classification of the Soils",

      service=report
      request=getreportdata
      aoiid=5940
      reportid=33
      format=long
  

The short format response for "33" is:

       {
         "aoiid":"5940",
         "reportid":"33",
         "reportname":"Taxonomic Classification of the Soils",
         "Includeminorsoils":"false",
         "Displaynationalmapunitsymbols":"false"
       }
    

Here's the long format response, prettified. Note the highlighted items, compare them to the short response above:

  <?xml version="1.0" encoding="UTF-8"?>
  <xml xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="/schemas/ParameterXml.xsd">
     <Group Name="Options" Highlight begin.aoiidHighlight end.="5940" Highlight begin.reportidHighlight end.="33">
      <Parameter Name="Enter Survey Area Symbol" Tag="areasym" ControlType="None" Value="" />
      <Parameter Name="Or List of Mapunit Keys" Tag="mukeys" ControlType="None">
       <ListItem Name="1151102" Value="1151102" SelectedValue="true" />
       <ListItem Name="1151120" Value="1151120" SelectedValue="true" />
       <ListItem Name="1151129" Value="1151129" SelectedValue="true" />
       <ListItem Name="1151130" Value="1151130" SelectedValue="true" />
       <ListItem Name="1151132" Value="1151132" SelectedValue="true" />
       <ListItem Name="1151133" Value="1151133" SelectedValue="true" />
       <ListItem Name="1151135" Value="1151135" SelectedValue="true" />
       <ListItem Name="1151138" Value="1151138" SelectedValue="true" />
       <ListItem Name="1151142" Value="1151142" SelectedValue="true" />
       <ListItem Name="1151144" Value="1151144" SelectedValue="true" />
       <ListItem Name="1151145" Value="1151145" SelectedValue="true" />
       <ListItem Name="1151146" Value="1151146" SelectedValue="true" />
       <ListItem Name="2605935" Value="2605935" SelectedValue="true" />
       <ListItem Name="2605962" Value="2605962" SelectedValue="true" />
       <ListItem Name="2745888" Value="2745888" SelectedValue="true" />
       <ListItem Name="2768398" Value="2768398" SelectedValue="true" />
      </Parameter>
      <Parameter Name="Include minor soils?" Tag="includeminor" ControlType="Check" Value="" shortname="Highlight begin.IncludeminorsoilsHighlight end.">
       <ListItem Name="True" Value="1" SelectedValue="false" />
       <ListItem Name="False" Value="0" SelectedValue="false" />
      </Parameter>
      <Parameter Name="Display national mapunit symbols?" Tag="useNationalMapunits" ControlType="Check" Value="" shortname="Highlight begin.DisplaynationalmapunitsymbolsHighlight end.">
       <ListItem Name="True" Value="1" SelectedValue="false" />
       <ListItem Name="False" Value="0" SelectedValue="false" />
      </Parameter>
      <Group Name="aoiid" shortname="aoiid" Enabled="False">
       <Parameter Name="aoiid" shortname="aoiid" ControlType="Text" UseRegEx="Optional" DataType="Integer" MaximumLength="10" RegEx="^0*(100|[0-9]{0,10})$" MinimumValue="1" MaximumValue="2147483647" Required="True" Default="3243" Value="3243" />
      </Group>
      <Group Name="reportid" shortname="reportid" Enabled="False">
       <Parameter Name="reportid" shortname="reportid" ControlType="Text" UseRegEx="Optional" DataType="Integer" MaximumLength="10" RegEx="^0*(100|[0-9]{0,10})$" MinimumValue="1" MaximumValue="2147483647" Required="True" Default="33" Value="33" />
      </Group>
      <Group Name="reportname" shortname="reportname" Enabled="False">
       <Parameter Name="reportname" shortname="Highlight begin.reportnameHighlight end." ControlType="Text" DataType="String" MaximumLength="100" Required="False" Default="Taxonomic Classification of the Soils" Value="Taxonomic Classification of the Soils" />
      </Group>
     </Group>
  </xml>
    

Note the nested hierarchical structure (made plain by "prettifying"), the presence of a "shortname" attribute in each "Parameter", and in the case where a "Parameter" contians one or more "ListItem" elements within, the Parameter's "Value" and "Default" match one of the child "ListItem"'s "Value"s.

      <Parameter Name="Include minor soils?" Tag="includeminor" ControlType="Check" Value="" shortname="Highlight begin.IncludeminorsoilsHighlight end.">
       <ListItem Name="True" Value="Highlight begin.1Highlight end." SelectedValue="false" />
       <ListItem Name="False" Value="Highlight begin.0Highlight end." SelectedValue="false" />
      </Parameter>
    

Therefore specification of "Highlight begin.trueHighlight end." would be equally valid:

       {
         "aoiid":"5940",
         "reportid":"33",
         "reportname":"Taxonomic Classification of the Soils",
         "Includeminorsoils":"Highlight begin.trueHighlight end.",
         "Displaynationalmapunitsymbols":"false"
       }
    

Those Parameters with Enabled="False" are not not to be changed.

The "V"-series "DescriptionID" values are as follows:

The "A"-series "DescriptionID" values are retrieved via query. Within the "Aggregation Method", for the ListItem

        <ListItem Name="Dominant Condition" Value="Dominant Condition" DescriptionID="A1" />
	

the query to retrieve the description for "Dominant Condition" would be:

There is one report that lack a complete set of default values. When the "short" data is used as-is with "getreport" for this report an error occurs.

Running the Report

       service=report
       request=getreport
       shortformdata=
	     {
           "aoiid":"5490",
           "reportid":"119",
           "reportname":"Map Unit Description (Brief, Generated)",
           "Displaynationalmapunitsymbols":"false"
         }
	

Your result should be similar to:

<?xml version="1.0" encoding="UTF-8"?>
<section label="SoilReport">
   <title>Map Unit Description (Brief, Generated)</title>
   <para role="headnote">[Minor map unit components are excluded from this report]</para>
   <section label="Survey_Area">
      <title>McLean County, Illinois</title>
      <section>
         <title role="suppressTitle" />
         <para>
            <emphasis role="bold">Map Unit:</emphasis>
            243A--St. Charles silt loam, 0 to 2 percent slopes
         </para>
         <section>
            <title role="suppressTitle" />
            <para>
               <emphasis role="bold">Component:</emphasis>
               St. Charles (90%)
            </para>
            <para>The St. Charles component makes up 90 percent ...
    

Regarding the JSON string you supply for the "shortformdata":
   - The names and associated values are case-sensitive.
   - Spaces, tabs and newlines are permitted outside of the quoted names and values.
   - When in doubt, consult the "long" format XML provided by "getreportdata" for required paraeter names and value options.

Note that if the default shortformdata is correct for your chosen report then you can skip its use and specify, in its place, the aoid and reportid:

       service=report
       request=getreport
       aoiid=5940
       reportid=119
	

Using Report Results

When a report is run using "getreport" an the report content, as DocBook XML, must be captured, it is not preserved within the AOI. You are responsible for transforming the XML into a presentation format.

Ad-Hoc Reports

There is no mechanism for defining your own reports.

Deprecated Web Soil Survey Access

Additional table variables may be defined for capturing Web Soil Survey thematic map data. These variables are defined with the following macros:

~DeclareMapLegendTable(@varname)~
~DeclareMapRatingsTable(@varname)~
~DeclareAoiMapUnitGeogTable(@varname)~
	

These variables correspond with table types defined in the SDM database. The type names and column definitions are:

This example retrieves the data for a thematic map created in Web Soil Survey. The value for the thematic map ID (12345 in this example) was obtained using the Info function on the WSS map and explicitly used in the "Get" macros as a "literal" (i.e., exact) value:

-- Get the legend for the thematic map
~DeclareMapLegendTable(@legend_table)~
~GetMapLegend(12345,@legend_table)~
select LegendText, RgbString from @legend_table

-- Get the ratings for mapunit
~DeclareMapRatingsTable(@ratings_table)~
~GetMapRatings(12345,@ratings_table)~
select
    MapUnitKey, MapUnitRatingString, MapUnitRatingNumeric,
    RgbString from @ratings_table

-- Get the AOI mapunit polygons
~DeclareAoiMapUnitGeogTable(@mapunit_table)~
~GetAoiMapUnits(12345,polygon,@mapunit_table)~
select
    AreaSymbol, MapUnitSymbol, MapUnitName, NationalMapUnitSymbol,
    Acres, MapUnitKey, AoiMapUnitProj from @mapunit_table
	

Alternatively the thematic map ID may be held in a variable and the macros called using that variable name:

~DeclareInt(@tmID)~
Select @tmID = 12345;

-- Get the legend for the thematic map
~DeclareMapLegendTable(@legend_table)~
~GetMapLegend(@tmID,@legend_table)~
select LegendText, RgbString from @legend_table

-- Get the ratings for mapunit
~DeclareMapRatingsTable(@ratings_table)~
~GetMapRatings(@tmID,@ratings_table)~
select MapUnitKey, MapUnitRatingString, MapUnitRatingNumeric, RgbString from @ratings_table

-- Get the AOI mapunit polygons
~DeclareAoiMapUnitGeogTable(@mapunit_table)~
~GetAoiMapUnits(@tmID,polygon,@mapunit_table)~
select AreaSymbol, MapUnitSymbol, MapUnitName, NationalMapUnitSymbol, Acres, MapUnitKey, AoiMapUnitProj
from @mapunit_table