Comments (8)
I don't see this as an issue anymore. I think originally I was confused as to why I was seeing STASTGO and SSURGO data being returned together, and the second issue now being discussed in issue #38 . If users want to exclude STATSGO from their results, they can simply include "areasymbol != 'US'" in their WHERE argument. Although we could add a toggle as you suggest above. I think the best course I action is to describe in the details and example section of the help file. I will get on that, and then close this issue.
example:
test <- get_mapunit_from_SDA(WHERE = "areasymbol != 'US' AND muname LIKE '%Miami%'")
from soildb.
Original query, with mu.mukey added, ~ 5 seconds run-time:
SELECT DISTINCT mu.nationalmusym, compname, comppct_r, compkind, majcompflag, localphase, slope_r, tfact, wei, weg, drainagecl, elev_r, aspectrep, map_r, airtempa_r, reannualprecip_r, ffd_r, nirrcapcl, nirrcapscl, irrcapcl, irrcapscl, frostact, hydgrp, corcon, corsteel, taxclname, taxorder, taxsuborder, taxgrtgroup, taxsubgrp, taxpartsize, taxpartsizemod, taxceactcl, taxreaction, taxtempcl, taxmoistscl, taxtempregime, soiltaxedition, cokey, mu.mukey
FROM legend l
INNER JOIN mapunit mu ON mu.lkey = l.lkey
INNER JOIN (
SELECT MIN(nationalmusym) nationalmusym2, MIN(mukey) AS mukey2
FROM mapunit
GROUP BY nationalmusym
) AS mu2 ON mu2.nationalmusym2 = mu.nationalmusym
INNER JOIN (
SELECT compname, comppct_r, compkind, majcompflag, localphase, slope_r, tfact, wei, weg, drainagecl, elev_r, aspectrep, map_r, airtempa_r, reannualprecip_r, ffd_r, nirrcapcl, nirrcapscl, irrcapcl, irrcapscl, frostact, hydgrp, corcon, corsteel, taxclname, taxorder, taxsuborder, taxgrtgroup, taxsubgrp, taxpartsize, taxpartsizemod, taxceactcl, taxreaction, taxtempcl, taxmoistscl, taxtempregime, soiltaxedition, cokey , mukey AS mukey2
FROM component
) AS c ON c.mukey2 = mu2.mukey2
WHERE compname = 'Amador'
ORDER BY cokey, compname, comppct_r DESC ;
Modified, based on my interpretation of the intended result, ~ 0.5 seconds run-time:
SELECT mu.nationalmusym, compname, comppct_r, compkind, majcompflag, localphase, slope_r, tfact, wei, weg, drainagecl, elev_r, aspectrep, map_r, airtempa_r, reannualprecip_r, ffd_r, nirrcapcl, nirrcapscl, irrcapcl, irrcapscl, frostact, hydgrp, corcon, corsteel, taxclname, taxorder, taxsuborder, taxgrtgroup, taxsubgrp, taxpartsize, taxpartsizemod, taxceactcl, taxreaction, taxtempcl, taxmoistscl, taxtempregime, soiltaxedition, cokey, mu.mukey
FROM legend l
INNER JOIN mapunit mu ON mu.lkey = l.lkey
INNER JOIN component co ON mu.mukey = co.mukey
WHERE compname = 'Amador'
ORDER BY cokey, compname, comppct_r DESC ;
And, further simplified:
SELECT mu.nationalmusym, compname, comppct_r, compkind, majcompflag, localphase, slope_r, tfact, wei, weg, drainagecl, elev_r, aspectrep, map_r, airtempa_r, reannualprecip_r, ffd_r, nirrcapcl, nirrcapscl, irrcapcl, irrcapscl, frostact, hydgrp, corcon, corsteel, taxclname, taxorder, taxsuborder, taxgrtgroup, taxsubgrp, taxpartsize, taxpartsizemod, taxceactcl, taxreaction, taxtempcl, taxmoistscl, taxtempregime, soiltaxedition, cokey, mu.mukey
FROM
mapunit mu
INNER JOIN component co ON mu.mukey = co.mukey
WHERE compname = 'Amador'
ORDER BY cokey, compname, comppct_r DESC ;
All three queries return the same data, 58 rows worth.
I get 48 rows of data when querying the same data in the SoilWeb copy of FY2018 SSURGO, and 10 rows from the SoilWeb copy of STATSGO.
Ok, jut as Stephen suspected--only took me that much typing to figure out what was clearly stated in the description.
Next query, this one works as expected:
SELECT mu.nationalmusym, compname, comppct_r, compkind, majcompflag, localphase, slope_r, tfact, wei, weg, drainagecl, elev_r, aspectrep, map_r, airtempa_r, reannualprecip_r, ffd_r, nirrcapcl, nirrcapscl, irrcapcl, irrcapscl, frostact, hydgrp, corcon, corsteel, taxclname, taxorder, taxsuborder, taxgrtgroup, taxsubgrp, taxpartsize, taxpartsizemod, taxceactcl, taxreaction, taxtempcl, taxmoistscl, taxtempregime, soiltaxedition, cokey, mu.mukey
FROM legend
INNER JOIN mapunit mu ON mu.lkey = legend.lkey
INNER JOIN component co ON mu.mukey = co.mukey
WHERE compname = 'Amador'
-- critical: filter out STATSGO data
AND legend.areasymbol != 'US'
ORDER BY cokey, compname, comppct_r DESC ;
That is better!
Looks like either something recently changed in SDA, or, we have been doing it wrong for a couple of years now. Yikes.
from soildb.
A couple of suggestions / questions for this and related SDA functions:
- there aren't many cases when
DISTINCT
is required and typically suggests a non-specific join condition - I don't understand the purpose of the
duplicates
argument: does this imply some level of additional filtering for a non-specific WHERE clause?
Opened #37 for the follow-up work in manual pages and tutorials.
from soildb.
I don't mind fixing this in a branch and then submitting a pull request or changing directly. Unfortunately we just missed the last submission to CRAN (Monday).
from soildb.
Update from Phil on geometry
Yes, all spatial intersection tests apply to only SSURGO data - the spatial intersections are performed against the sapolygon, mupoint, muline and/or mupolygon tables as appropriate.
Both SSURGO and STATSGO non-spatial data are in the “tabular” tables (legend, mapunit, component, etc.), the STATSGO spatial data are only in the gsmmupolygon table. In order to work directly with STATSGO data you have the option to use the intrinsic SQL Server geometry functions against the gsmmupolygon. For example, given a WGS84 WKT string describing an area in Wayne County OH you can perform the equivalent of “SDA_Get_Mukey_from_intersection_with_WktWgs84()” with the following:
select distinct mukey from gsmmupolygon
where mupolygongeo.STIntersects(
geometry::STPolyFromText(
'polygon(
(-81.92624 40.82873,-81.92624 40.90403,-81.75872 40.90403,-81.75872 40.82873,-81.92624 40.82873))',
4326)
) = 1
The intrinsic functions are documented by Microsoft, start at https://technet.microsoft.com/en-us/library/bb933973%28v=sql.110%29.aspx
from soildb.
Should queries that hit SDA implicitly filter STATSGO (tabular) records or leave it to a toggle in the function arguments?
from soildb.
Any updates? Happy to test / check.
from soildb.
Done
from soildb.
Related Issues (20)
- fetchSCAN: use standard methods for managing curl handles, connection timeout etc. HOT 5
- downloadSSURGO() HTTP error 400 HOT 6
- `get_SDA_*` functions should return "ID"/mukey value in first column HOT 1
- redundant columns in default RAT of `mukey.wcs()` and `ISSR800.wcs()` HOT 2
- `fetchLDM()` add `area_type` argument
- .pickBestEcosite() consideration of record_when_last_updated HOT 1
- Enhancing PLSS2LL and BLM API tools from sharpshootR HOT 2
- SDA_spatialQuery() for multiple features fails HOT 6
- Spotty NCCPI data HOT 7
- soilDB::mukey.wcs() fails when there is no mukey data in gSSURGO HOT 10
- get_mapunit_from_SDA() fails for some mukeys HOT 8
- test mukey.wcs against local calls to crop() on mukey grid HOT 12
- mirror WCS across SoilWeb servers HOT 2
- Randomly bad calls to fetch data HOT 7
- fetchGDB child tables HOT 5
- upgrade `createStaticNASIS()` selected set behavior HOT 1
- `get_SDA_interpretation` sometimes throws warning for NCCPI HOT 3
- `.create_wide_reason` failing when `dsn` passed to `get_SDA_interpretation` HOT 7
- `createSSURGO()` should create indices for tables/columns commonly used by soilDB queries HOT 2
- use of aqp::rgb2munsell() in soilDB HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from soildb.