Skip to main content

An Inquiry into IN Query

I had to select a ton of records today for a project in ArcGIS. You know the normal method, hellish SQL repetition: ID = 1 OR ID = 5 OR ID = 6 OR ID = 12 OR ID = 14 OR ID = 27 OR ID = 41 OR ID = 43


The past few months I've been working a lot in SQL Server and this experience made this repetition seem like too much work. So I got lazy and I made an inquiry into IN query: ID IN (1,5,6,12,14,27,41,43)


And it just worked. There's no documentation in the main help file that even mentions it. If you do open the ArcGIS Help and search on "SQL Reference" nearly half way down you find a section on Subqueries. Here they explain that IN queries are supported in geodatabases and EXISTS is supported as well! Who knew? How was I supposed to know this if I hadn't gotten lazy?

Here's what you'll find in the ArcGIS Help File:
Subqueries
NOTE: Coverages, shapefiles, and other non-geodatabase file-based data sources do not support subqueries. Subqueries done on a versioned ArcSDE feature class which has been registered without the option to move edits to base will not return features stored in the delta tables. File geodatabases provide the limited support for subqueries explained in this section, while personal and ArcSDE geodatabases provide full support. For information on the full set of subquery capabilities of personal and ArcSDE geodatabases, refer to your DBMS documentation.

A subquery is a query nested within another query. It can be used to apply predicate or aggregate functions or to compare data with values stored in another table. This can be done with the IN or ANY keywords. For example, this query would select only the countries that are not also listed in the table indep_countries:

"COUNTRY_NAME" NOT IN (SELECT "COUNTRY_NAME" FROM indep_countries)

This query would return the features with a GDP2006 greater than the GDP2005 of any of the features contained in countries:

"GDP2006" > (SELECT MAX("GDP2005") FROM countries)

For each record in the table, a subquery may need to parse all the data in its target table. It may be extremely slow to execute on a large dataset.

Subquery support in file geodatabases is limited to the following:

IN predicate. For example:

"COUNTRY_NAME" NOT IN (SELECT "COUNTRY_NAME" FROM indep_countries)

Scalar subqueries with comparison operators. A scalar subquery returns a single value. For example:

"GDP2006" > (SELECT MAX("GDP2005") FROM countries)

For file geodatabases, the set functions AVG, COUNT, MIN, MAX, and SUM can only be used within scalar subqueries.

EXISTS predicate. For example:

EXISTS (SELECT * FROM indep_countries WHERE "COUNTRY_NAME" = 'Mexico')

OperatorDescription
[NOT] EXISTSReturns TRUE if the subquery returns at least one record; otherwise, it returns FALSE. For example, this expression returns TRUE if the OBJECTID field contains a value of 50:

EXISTS (SELECT * FROM parcels WHERE "OBJECTID" = 50)

EXISTS is supported in file, personal, and ArcSDE geodatabases only.

[NOT] INSelects a record if it has one of several strings or values in a field. When preceded by NOT, it selects a record if it doesn't have one of several strings or values in a field. For example, this expression searches for four different state names:

"STATE_NAME" IN ('Alabama', 'Alaska', 'California', 'Florida')

For file, personal, and ArcSDE geodatabases, this operator can also be applied to a subquery:

"STATE_NAME" IN (SELECT "STATE_NAME" FROM states WHERE "POP" > 5000000)


Remix IN with some external SQL that EXISTS here in ArcGIS One-to-Many Labeling and you might confuse ArcSDE for an RDMS. Just don't try look for anything meaningful in a RELATE.

Comments

Popular posts from this blog

Simple HTTP Redirect with Querystring in IIS7

HTTP Redirect seems simple enough. Always was in IIS6 and in IIS7 there's even a button labeled HTTP Redirect that promises relative redirects.  It looks like it'll be as easy Apache finally.  That is until you try to redirect a querystring.  Then everything bombs.

Turns out it still is relatively easy, except you have to know that Microsoft changed $S$Q to $V$Q. Why? $Ss and $Gs I suspect.

And How.
In our example we'll redirect all pages under http://olddomain.com/content to http://mydomain.com/content.
Pick the virtual directory you want to redirect. e.g. http://olddomain.com/content Click HTTP Redirect under IIS in the IIS management console.In the HTTP Redirect Dialog:
Check Redirect requests to this destinationEnter your new path ending with $V$Q.  e.g. http://mydomain.com$V$QCounter-intuitively check Redirect all request to exact destination (instead of relative destination)Choose the appropriate Status Code (Permanent or Temporary)Apply Changes and Test

Maintaining Cross-Database Referential Integrity

It is often convenient to house your master tables in a separate database from application specific databases so that primary keys are copasetic and multiple databases have access to the same lookups.

Common examples would include employees, counties, regions and other lookup tables.

Once these tables are in a separate database however it is no longer possible to simply drag-and-drop a relationship between them to maintain referential integrity.

Here's a Solution
For this example we'll use an Observation table, tied to a master list of Sex, that's right, Sex (we could call it Gender to be all PC, but these are animals, not Pat in the Personnel Department.)

Step 1:
We create a new view called PIC_Sex in our Application Database by querying the table PIC_Sex in our Master Database:

CREATE VIEW dbo.PIC_Sex AS SELECT SexID, Sex, Rank FROM Master.dbo.PIC_Sex ORDER BY Rank
Yes, that's right, Male and Female! Slugs are awesome.

Step 2:
We create a new user-defined function xdf_Val…

Serving up KML in IIS 6

To serve up KML in IIS 6, you have to add a few MIME Types. The easiest way to do this is to apply new MIME Type settings globally by changing the properties on your server's "Web Sites" folder in IIS.


Google Earth reads KML and KMZ files. The MIME type for KML files is

* application/vnd.google-earth.kml+xml

The MIME type for KMZ files is

* application/vnd.google-earth.kmz

Source: Google KML Tutorial


To add a MIME type to a Web site or directory

1. In IIS Manager, right-click the Web site or Web site directory for which you want to add a MIME type, and click Properties.

2. Click the HTTP Headers tab.

3. Click MIME Types.

4. Click New.

5. In the Extension box, type the file name extension.

6. In the MIME type box, type a valid MIME type. If you define a MIME type that has already been defined at a higher level, you are prompted to select the level where the MIME type should reside.

To create a MIME type for an undefined MIME type, type an asterisk (*) in the Extension box, an…