After working with ArcIMS for 7 years, I’ve learned to accept some limitations. Sometimes though, requirements dictate that we “bend the rules” a bit. One recent challenge was to determine the distinct values for a field in an ArcIMS layer. Since ArcIMS queries have no “distinct” clause, we had to get creative. Here are the two options we came up with:
- Query for a bunch of records (say, 50). Get the unique field values from those 50, and query again, this time filtering out records that are NOT the ones we’ve just found. Repeat until you get no new records from ArcIMS or until you have more records than you can handle, whichever comes first.
- Formulate a special WHERE expression, in the form OWNER.SCHEMA.TABLE.OBJECTID IN (SELECT MAX(OBJECTID) FROM TABLE GROUP BY UNIQUE_FIELD). A real-life example that can be used on our Geocortex IMF Demonstration Site (try the query builder on the Geocode Streets layer) is: SDE_CHAR_VMB.SDE_CHAR_VMB.CNTY_STREETS_V.OBJECTID IN (SELECT MAX(OBJECTID) FROM CNTY_STREETS_V GROUP BY SUBDIVISIO)
The first option is the only possibility for shapefile-based data sources, and for SDE data sources with non-unique OBJECTIDs. I’m partial to the second one though because it takes only one query, it’s very fast (even for finding the 200 unique values in layers with hundreds of thousands of features), and it showed me that subqueries in WHERE expressions to ArcIMS are possible.
I’m curious to see what other clever things can be done with ArcIMS using sub-queries…
That’s pretty clever.
I was wondering if there’s a trick to get the SUM of (a selection of) features? subfields=”SUM(COUNT)” doesn’t seem to work.
Sort of…with varying results.
Returning a computed value requires that we “encode” that computed value into an existing field. I like OBJECTID for that, but it sometimes can give us only rough results depending on what we’re asking. Some examples, all still using the Geocode Streets layer on the demonstration site:
To get the count of features, we encode the result of COUNT into the OBJECTID:
SDE_CHAR_VMB.SDE_CHAR_VMB.CNTY_STREETS_V.OBJECTID IN (SELECT COUNT(*) FROM CNTY_STREETS_V WHERE R_JURIS=’HUNTERSVILLE’)
This gives us the number of street segments inside or bordering (right side only
) on HUNTERSVILLE(though there are better ways in ArcIMS to get the feature count, this serves as a decent example).
To get the AVG value, we can do:
SDE_CHAR_VMB.SDE_CHAR_VMB.CNTY_STREETS_V.OBJECTID IN (SELECT FLOOR(AVG(LL_ADD)) FROM CNTY_STREETS_V WHERE R_JURIS=’HUNTERSVILLE’)
Which gives us the average value of lower left address of all street segments in HUNTERSVILLE, to the nearest whole number (starting to lose some precision here).
Now, to get SUM we can do this:
SDE_CHAR_VMB.SDE_CHAR_VMB.CNTY_STREETS_V.OBJECTID IN (SELECT SUM(LL_ADD)/1000 FROM CNTY_STREETS_V WHERE R_JURIS=’HUNTERSVILLE’)
Which gives us the sum of all lower left addresses of all street segments in HUNTERSVILLE. This loses much precision in this case, but sometimes your queries don’t need much (if any at all) precision loss.
Notice that in all three cases, we’re not *really* getting back what we’ve asked for, but we’re getting back a feature whose OBJECTID is equal to (or close to) what we’ve asked for. So in essence, we’re “encoding” our value into the OBJECTID.
We needed to divide our SUM by 1000 in the last case because there is no feature with an OBJECTID which is as high as the sum. This resulted in a lack of precision, since the OBJECTID of my returned feature is 25614, meaning the SUM is 25,614,000 plus or minus 1000. Sometimes close enough is better than nothing
Incidentally, I’ve also discovered that we can use JOIN expressions in our sub-selects – even joining to tables *not* in the current map service. Certainly underscores the need to restrict access to your tables by the SDE user you are connecting as in the AXL…
Hello.
I the first time here.
I wish to show to you,new Foto
http://marisa-tomei-nude.blogspot.com