Macros & Advanced Queries

Overview

What This Guide Covers

The advanced SDA API extends plain SQL with macros, spatial functions, and service endpoints for areas of interest, interpretations, and reports.

§1 What Are Macros §2 Variable Macros §3 Table Macros §4 Spatial Macros §5 AOI Creation Macros §6 Persistent AOI Types §7 Tabular Spatial Functions §8 Interpretations §9 Reports

Macros

Shorthand Expansion

Tilde-delimited statements that expand into DECLARE, table definitions, and multi-step spatial operations — making complex queries concise.

Persistent AOI

Server-Side Areas of Interest

Define an area once — from an SSA, a mukey list, a WKT polygon, a shapefile, or a GeoJSON — and reuse it across spatial clips, interpretation runs, and map renders.

Spatial Functions

WKT Intersection Queries

Server-side functions return areasymbol, mukey, or geometry for any polygon sent as WKT — both WGS84 and Web Mercator projections supported.

01

§1 Macros

What Are Macros?

A macro is a shorthand statement the SDA server expands into full T-SQL before execution. Every macro is surrounded by tilde characters with no embedded whitespace, and variable names are prefixed with @.

1

Macro Syntax Rules

The three rules that govern every macro statement

Advanced ▼

Macros follow three strict rules:

Surrounded by tilde characters: ~MacroName(args)~
No embedded whitespace inside the tildes
Variable names prefixed with @ — e.g., @aoi, @aoiid

Expansion example: ~DeclareGeometry(@aoi)~ expands to DECLARE @aoi geometry; — the server does the substitution before running your SQL.

-- Declare a varchar variable and assign a value
~DeclareVarchar(@ssa,20)~
select @ssa = 'WI025';
select @ssa as [SurveyArea]

02

§2 Variable Macros

Simple Variable Macros

These macros declare a single SQL variable of the specified type. They replace verbose DECLARE @var type; syntax with a one-liner.

2

Numeric & Date Types

Integer, float, decimal, date, and time variable declarations

Advanced ▼

Macro	Expands To	Notes
~DeclareBigint(@v)~	`DECLARE @v bigint;`	64-bit integer
~DeclareInt(@v)~	`DECLARE @v int;`	Most common integer
~DeclareSmallint(@v)~	`DECLARE @v smallint;`	-32768 to 32767
~DeclareTinyint(@v)~	`DECLARE @v tinyint;`	0 to 255
~DeclareFloat(@v)~	`DECLARE @v float;`	Double precision
~DeclareReal(@v)~	`DECLARE @v real;`	Single precision
~DeclareBit(@v)~	`DECLARE @v bit;`	Boolean 0/1
~DeclareDecimal(@v,p,s)~	`DECLARE @v decimal(p,s);`	Fixed precision & scale
~DeclareNumeric(@v,p,s)~	`DECLARE @v numeric(p,s);`	Alias for decimal
~DeclareDate(@v)~	`DECLARE @v date;`	Date only
~DeclareDatetime(@v)~	`DECLARE @v datetime;`	Date + time
~DeclareTime(@v)~	`DECLARE @v time;`	Time only

3

String & Spatial Types

Char, varchar, geometry, and geography variable declarations

Advanced ▼

Macro	Expands To	Notes
~DeclareChar(@v,n)~	`DECLARE @v char(n);`	Fixed-length string
~DeclareVarchar(@v,n)~	`DECLARE @v varchar(n);`	Variable-length string
~DeclareVarchar(@v,max)~	`DECLARE @v varchar(max);`	Unlimited text
~DeclareGeometry(@v)~	`DECLARE @v geometry;`	Planar spatial — Web Mercator
~DeclareGeography(@v)~	`DECLARE @v geography;`	Geodetic — WGS84

~DeclareChar(@a,3)~
~DeclareVarchar(@b,4)~
~DeclareVarchar(@c,max)~
~DeclareDecimal(@d,5,2)~
~DeclareGeometry(@aoi)~

select @a = 'WI0', @b = '025', @c = 'Dane County, WI', @d = 3.14;
select @a+@b as areasymbol, @c as description, @d as pi_approx

03

§3 Table Variable Macros

Table Variable Macros

These macros declare an in-memory table variable with a predefined schema — essential for passing mukey lists into spatial functions and accumulating geometry results.

4

Simple Table Variables

Integer and varchar table variables for passing lists to spatial functions

Advanced ▼

Macro	Schema Created
~DeclareIntTable(@t)~	Single column: `i int`
~DeclareVarchar255Table(@t)~	Single column: `s varchar(255)`

-- Collect farmland-of-statewide-importance mukeys from ND053
~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';

-- Create pAOI from the list
~CreateAoiFromMukeyList(@mukeyList,@aoiid,@message)~
select count(*) [MukeyCount] from SDA_Get_AoiMapunit_By_AoiId(@aoiid);
select @aoiid [aoiid], @message [message]

5

Geometry & Geography Table Variables

Two-column tables pairing an ID or string key with a spatial geometry/geography column

Advanced ▼

Macro	Schema: (col1, col2)
~DeclareIdGeomTable(@t)~	`id int, geom geometry`
~DeclareIdGeogTable(@t)~	`id int, geog geography`
~DeclareVarchar255GeomTable(@t)~	`s varchar(255), geom geometry`
~DeclareVarchar255GeogTable(@t)~	`s varchar(255), geog geography`

Pattern: ~DeclareIdGeomTable(@intersected)~ is the input container for ~GetClippedMapunits(...)~. Then ~DeclareIdGeogTable(@areas)~ holds the converted geodetic result for STArea() calculations.

04

§4 Spatial Macros

Clipping & Geometry Macros

These macros perform the heavy spatial work — clipping soil polygons to your AOI, converting between geometry and geography, and splitting multi-part geometries.

6

GetClippedMapunits & Geometry Conversion

Clip soil polygons to a WKT boundary and compute area in square meters

Advanced ▼

Macro	Purpose
~GetClippedMapunits(@aoi,polygon,geo,@out)~	Clip mupolygon to @aoi; put results in @out (id=mukey, geom=clipped geometry, WGS84)
~GetClippedMapunits(@aoi,polygon,proj,@out)~	Same, but in Web Mercator projection
~GetGeogFromGeomWgs84(@in,@out)~	Convert geometry table (WGS84) → geography table for `STArea()` in m²
~SplitIdGeomTable(@in,@out)~	Split multi-part geometries into individual rows

Unit note: STArea() returns square meters. Multiply by 0.000247105 to get acres.

05

§5 AOI Creation Macros

Persistent AOI Creation

Three macros create a persistent area of interest server-side and return an @aoiid integer you reuse in all subsequent queries, interpretation runs, and report requests.

7

Three AOI Creation Patterns

From a survey area symbol, a mukey list, or an existing WSS AOI

Advanced ▼

Macro	Creates pAOI From
~CreateAoiFromSsa(@ssa,@aoiid,@message)~	A soil survey area areasymbol (e.g. `'CA795'`)
~CreateAoiFromMukeyList(@mukeyList,@aoiid,@message)~	A `~DeclareIntTable~` populated with mukeys
~CreateAoiFromWssAoi(@wAoiId,@pAoiId,@message)~	An existing Web Soil Survey transient AOI ID

SSA-based pAOI

~DeclareVarchar(@ssa,20)~
select @ssa = 'CA795';
~CreateAoiFromSsa(@ssa,@aoiid,@message)~
select @aoiid [aoiid], @message [message]

WSS AOI Import

~DeclareInt(@wAoiId)~
select @wAoiId = 25731;
~CreateAoiFromWssAoi(@wAoiId,@pAoiId,@message)~
select @pAoiId as pAoiId, @message as message

06

§6 Persistent AOI

Four pAOI Types

A persistent Area of Interest lives on the SDA server and is referenced by its numeric aoiid. Four creation methods cover every use case — from a simple survey area to a GeoJSON FeatureCollection with filter expressions.

8

pAOI Creation via Web Service

REST endpoint patterns for all four pAOI types

Advanced ▼

Type 1 — Survey Area (SSA)

service=aoi&request=create&ssa=CA795

Type 2 — Mukey List

service=aoi&request=create&mukeylist=1860695,1860696,1860697

Type 3a — WKT Polygon (WGS84)

service=aoi&request=create&aoicoords=polygon((-89.573 43.073,...,-89.573 43.073))

Coordinate system: WKT coords are assumed WGS84 (EPSG:4326). Multi-polygon is also supported.

Type 3b — GeoJSON

// POST to: service=aoi&request=create
// Body parameter: aoicoords=<GeoJSON below>
{
  "type": "FeatureCollection",
  "features": [
    {
      "type": "Feature",
      "geometry": {"type":"Polygon","coordinates":[[...]]},
      "properties": {"partName": "North Field"}
    },
    {
      "type": "Feature",
      "geometry": {"type":"Polygon","coordinates":[[...]]},
      "properties": {"partName": "South Field"}
    }
  ]
}

GeoJSON supports a filter parameter (SQL WHERE clause) and a partname template like [PLU Number],[Id];{0} ({1}) for composing part labels from feature properties.

Type 4 — WSS AOI Import

service=aoi&request=create&wssaoi=12345

Querying a pAOI

-- Replace 502 with the aoiid returned from the creation call
select * from SDA_Get_AoiMapunit_By_AoiId(502);
select * from SDA_Get_Aoi_By_AoiId(502);

9

pAOI Query Functions

All server-side functions available once you have an aoiid

Advanced ▼

Function	Returns
SDA_Get_Aoi_By_AoiId(@id)	AOI metadata: acres, creation method, multipart flag, geometry
SDA_Get_AoiMapunit_By_AoiId(@id)	AoiId, AoiMapunitId, MapUnitKey
SDA_Get_AoiPart_By_AoiId(@id)	Part-level metadata: name, acreage, geometry (aoicoords pAOIs only)
SDA_Get_AoiSoilMapunitPolygon_By_AoiId(@id)	Clipped polygon geometries per map unit
SDA_Get_AoiSoilMapunitLine_By_AoiId(@id)	Clipped line geometries
SDA_Get_AoiSoilMapunitPoint_By_AoiId(@id)	Point features
SDA_Get_AoiSoilThematicMap_By_AoiId(@id)	Interpretation thematic maps stored with this AOI
SDA_Get_AoiSoilThematicMapRating_By_AoiId(@id)	Per-mukey ratings with color strings
SDA_Get_AoiSld_By_AoiId(@id)	Styled Layer Descriptor XML for WMS rendering

Part-level queries: Append _And_AoiPartId(@id, @partId) to any function name to filter results to a single part of a multipart AOI.

07

§7 Tabular Spatial Functions

WKT Intersection Functions

These server-side table-valued functions accept a WKT polygon and return areasymbol, mukey, or geometry — no macro required. Available in both WGS84 (WktWgs84) and Web Mercator (WktWm) flavors.

10

Areasymbol & Mukey Functions

Identify which survey areas and map units overlap a polygon

Advanced ▼

Function	Returns
SDA_Get_Areasymbol_from_intersection_with_WktWgs84(wkt)	areasymbol of overlapping survey areas
SDA_Get_Areasymbol_from_intersection_with_WktWm(wkt)	Same, Web Mercator coordinates
SDA_Get_Mukey_from_intersection_with_WktWgs84(wkt)	mukey of overlapping map units
SDA_Get_Mukey_from_intersection_with_WktWm(wkt)	Same, Web Mercator
SDA_Get_AreasymbolWKTWgs84_from_Areasymbol(ssa)	Boundary WKT of a survey area in WGS84
SDA_Get_MupolygonWktWgs84_from_Mukey(mukey)	Polygon WKT for a specific mukey
SDA_Get_MupolygonWktWgs84_from_MukeyTable(@table)	Polygons for a list of mukeys

11

Polygon, Line & Point Geometry Functions

Return actual geometry WKT for map unit polygons, lines, and points

Advanced ▼

Function	Geometry Type
SDA_Get_MupolygonWktWgs84_from_Mukey(mukey)	Polygon — single mukey
SDA_Get_MupolygonWktWm_from_Mukey(mukey)	Polygon — single mukey, Web Mercator
SDA_Get_MupolygonWktWgs84_from_MukeyTable(@t)	Polygon — list of mukeys
SDA_Get_MupointWktWgs84_from_Mukey(mukey)	Miscellaneous area points
SDA_Get_MupointWktWgs84_from_MukeyTable(@t)	Points — list of mukeys
SDA_Get_MulineWktWgs84_from_Mukey(mukey)	Linear features
SDA_Get_MulineWktWgs84_from_MukeyTable(@t)	Lines — list of mukeys
SDA_Get_Sapolygonkey_from_intersection_with_WktWgs84(wkt)	Survey area polygon key
SDA_Get_Mupolygonkey_from_intersection_with_WktWgs84(wkt)	Map unit polygon key
SDA_Get_Featlinekey_from_intersection_with_WktWgs84(wkt)	Feature line key
SDA_Get_Featpointkey_from_intersection_with_WktWgs84(wkt)	Feature point key

Naming pattern: All functions follow SDA_Get_[Object]Wkt[Proj]_from_[Input](arg). Swap Wgs84↔Wm to switch coordinate systems. Append Table to the source name to accept a table variable instead of a scalar.

08

§8 Interpretations

Running Interpretations

Interpretations are derived ratings — computed from soil properties by predefined rules. You request a catalog of available interpretations, configure rule parameters, and receive an interpresultid linking results back to your pAOI.

12

Interpretation Catalog & Use Categories

Discover which interpretations are available for your AOI

Advanced ▼

service=interpretation&request=getusecategories

usecategoryid	Category
0	All Uses
4	Cropland
6	Forestland
7	Horticulture
9	Hayland / Pastureland
10	Rangeland
11	Recreation
16	Urban Uses

service=interpretation&request=getcatalog&aoiid=502&usecategoryid=16

The response JSON lists folders and attributekey values for every interpretation in the selected category. Pass an attributekey to getruledata to see configurable parameters.

13

Running an Interpretation

Configure rule parameters and receive a result ID for thematic mapping

Advanced ▼

1 — Get rule defaults

service=interpretation&request=getruledata&aoiid=502&attributekey=98

2 — Run with shortformdata

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

Response: {"interpresultid": "504"} — use this ID to query SDA_Get_AoiSoilThematicMapRating_By_AoiId for per-mukey ratings and colors.

Simplified (default parameters)

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

14

Ad-Hoc Interpretations

Upload your own ratings table to attach a custom thematic map to a pAOI

Advanced ▼

When predefined rules don't fit your use case, populate a custom rating table and load it as an ad-hoc interpretation:

Macro	Purpose
~DeclareAdHocRatingTable(@t)~	Declares table: (MapUnitKey int, MapUnitRatingString varchar(255), MapUnitRatingNumeric numeric(17,6), RgbString varchar(11))
~DeclareAdHocLegendTable(@t)~	Declares table: (sequence int, LegendText varchar(255), RgbString varchar(11))
~LoadAdHocInterpretation(@pAoiId,@ratings,@interpresultid,@msg)~	Loads ratings (no legend)
~LoadAdHocInterpretation(@pAoiId,@ratings,@legend,@interpresultid,@msg)~	Loads ratings + custom legend

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

~DeclareAdHocRatingTable(@ratingTable)~
insert into @ratingTable(MapUnitKey,MapUnitRatingString,MapUnitRatingNumeric,RgbString)
values
  (1860695, 'High Productivity',    0.82, '34 139 34'),
  (1860696, 'Medium Productivity',  0.55, '255 165  0'),
  (1860697, 'Low Productivity',     0.21, '178  34 34');

~DeclareAdHocLegendTable(@legendTable)~
insert into @legendTable(sequence,LegendText,RgbString)
values
  (1,'High Productivity',  '34 139 34'),
  (2,'Medium Productivity','255 165  0'),
  (3,'Low Productivity',   '178  34 34');

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

09

§9 Reports

Generating Reports

Reports produce DocBook XML documents matching Web Soil Survey's catalog. Results are returned immediately — not persisted — so the calling application is responsible for transforming the XML to a presentation format.

15

Report Service Endpoints

Catalog, describe, configure, and run soil reports for a pAOI

Advanced ▼

Request Pattern	Returns
`service=report&request=getusecategories`	JSON list of use category IDs and names
`service=report&request=getcatalog&aoiid=N`	JSON folder tree of report names and IDs
`service=report&request=getdescription&reportid=N`	DocBook XML description of the report
`service=report&request=getreportdata&aoiid=N&reportid=N`	JSON shortformdata template with defaults
`service=report&request=getreport&shortformdata={...}`	DocBook XML report for the configured parameters
`service=report&request=getreport&aoiid=N&reportid=N`	Report with all default parameters

Common Report IDs

reportid	Report Name
461	Component Description (Nontechnical)
17	Dwellings and Small Commercial Buildings
33	Taxonomic Classification of the Soils
119	Map Unit Description (Brief, Generated)
6	Irrigated and Nonirrigated Yields by Map Unit Component

// Simplified form — uses all defaults:
// GET https://sdmdataaccess.sc.egov.usda.gov/Tabular/post.rest
// service=report&request=getreport&aoiid=5940&reportid=33

// Configured form (shortformdata):
{
  "aoiid": "5940",
  "reportid": "33",
  "reportname": "Taxonomic Classification of the Soils",
  "Includeminorsoils": "false",
  "Displaynationalmapunitsymbols": "false"
}

Known defect: Report ID 39 (Selected Soil Interpretations) returns an HTML error when called via the short format data pathway. Use the simplified endpoint for this report.

Reference