ArcIMS Table Joins On-The-Fly

Did you know that you can join tables on the fly with ArcIMS? The PC didn’t know that either. That’s right, I said it: The PC didn’t know. But the Chamber Project has a requirement to be able to show parcels that are over 5 acres exclusively. Creating another view wasn’t a strong option - coaxing the actual acreage out of the CAMA database is about as easy as herding cats. What we really needed was the area field that ArcGIS can see on SDE layers but that ArcIMS is blissfully ignorant of. So The PC had to do some thinking. Ten minutes later, he exited the bathroom with a solution.

The key is the SPATIALQUERY tag. It is used in both GET_FEATURES and GET_IMAGE requests to specify the content returned and send where-clause queries. It looks like this:

…..
<GET_FEATURES outputmode=”xml” geometry=”false”>
<LAYER id=”PARCELS” />
<SPATIALQUERY subfields=”#ALL#” where=”TAX.TAXDW.PARCEL_P.PID=’12312312’”>
</LAYER>
…..

…..
<GET_IMAGE>
<PROPERTIES>
…..
<LAYERLIST>
<LAYERDEF id=”PARCELS”>
<SPATIALQUERY subfields=”#ALL#” where=”TAX.TAXDW.PARCEL_P.PID= ‘12312312’”/>
…..
</LAYERDEF>

What the spatial query can also do is join tables on the fly. In this case, we wanted the AREA field that is stored in the the F (feature) table for the parcel layer in SDE, and we only wanted parcels larger than 5 AC. Here’s what the code looks like for a GET_IMAGE request:

<LAYERLIST>
<LAYERDEF id=”TAXPARCELS”>
<SPATIALQUERY subfields=”#SHAPE#
TAX.TAXDW.PARCEL_P.OBJECTID TAX.TAXDW.PARCEL_P.PID TAX.TAXDW.F92.AREA”
where=”TAX.TAXDW.PARCEL_P.OBJECTID = TAX.TAXDW.F92.FID AND TAX.TAXDW.F92.AREA > ‘217800’” jointables=”TAX.TAXDW.F92” />
</LAYERDEF>
</LAYERLIST>

Here we’ve taken the F table (F92), and joined the table to the parcel layer by the objectid in the parcel layer and the fid in the F table. You probably recognize the basic syntax as that of a left outer join in SQL. Since the area field is also exposed to us, we send an additional condition for an area of greater than 217,800 square feet (1 AC = 43,560 feet). Set the jointables parameter to the F table, and that’s it. Now when we send the GET_IMAGE request, the only parcels we’ll see are the ones larger than 5 AC.

You can use this same technique to join any layer to any table in SQL Server. It’s fast, it saves a lot of time in data prep, and it makes SDE views somewhat obsolete. Give it a try the next time you need to join some tables to your SDE layer in your ArcIMS project.