I love the Tabulate Area command in ArcGIS. The ability to create a cross tabulation of two feature datasets is very cool. SQL has cross tabulation commands (heck, even Excel does this through a pivot table), and because we are saying that spatial data is just another datatype, we can leverage the cross tabulation queries in SQL to work on, say a spatial relationship like land use values from two different years, instead of looking at sales by store.
Lets assume we have land use values from two different years: 1970 and 2000 (I use this example with my students every year). We will also assume that there are two fields called LC1970 and LC2000. An example map looks like the following:
The SQL code to generate this is actually standard use of the TRANSFORM command with a PIVOT command. This is standard SQL stuff – the only spatial thing is that we are creating a table of the intersection between the 1970 and 2000 land use categories.
TRANSFORM SUM(Area(g))
SELECT lc2000
FROM
(SELECT ClipIntersect([lu1970].[Geom (I)],[lu2000].[Geom (I)]) AS g,
[lc1970],[LC2000]
FROM [lu1970],[lu2000]
)
WHERE lc1970 <> “” and lc2000 <> “”
GROUP BY lc2000
PIVOT lc1970
The ClipIntersect is a spatial SQL construct that performs a geometric intersection of the two layers on-the-fly, in memory. BTW, this is another benefit of SQL – there are no orphaned files lying around – most of it is done in memory and then released.
The resulting table from the example data set looks like this:
I built a copy of the sample data (two tables with overlapping geoms, with the same table and field names)
Running the query as c&p above results in unexpected token at line 8 “””
Line 8 WHERE lc1970 “” and lc2000 “”
I replaced this with WHERE g IS NOT NULL to remove records that do not have a Geom (I) after the clip intersect.
The query then runs as expected.
Incidently, just being able to do the clip intersect and pivot has saved me hours in a regular task.
And a follow up. I posted on my experience with the query as written on the georeference forum. Manifold 8 does a validation check that may cause expected but unwelcome behaviour. There is an alternative discussed.
http://www.georeference.org/forum/t122670.15
Pingback: Spatial is Not Special – Spatial interaction | gisadvising