In our book we illustrated the use of quadrat analysis for determining whether points were random, clustered, or distributed. Figure 14.9 from the book showed a point sample of 2,500 points, and Table 14.4 showed the mathematical calculation for quadrat analysis.
The calculations look pretty daunting, don’t they? But, in actuality, its basic arithmetic. In this blog I am only going to illustrate how we obtained the correct variance to mean ratio using spatial SQL. If you want to understand quadrat analysis, check out the book, or do a web search.
Essentially, we need to determine number of cells that contain a certain amount of points. For instance, how many cells contain only 1 point. Or, how many cells contain 17 points. To accomplish that, we have to figure out how many point are in each cell. To do that, we simply issue a contains clause in SQL:
(SELECT COUNT(*) AS numpts, [quadrat].[ID] FROM [random] AS A, [quadrat] WHERE CONTAINS([quadrat].id,[A].id) GROUP BY [quadrat].id)
Remember, the COUNT clause counts up the number of points, but the group by clause groups it by the cell id (in this case, the layer is called quadrat).
Unfortunately, this query tells us the number of points in each cell. It does not tell us how many point have only 1, 2, or n number of cells. To find that, we have to obtain the COUNT of the number of cells that have n points. So, we wrap the query above into another query that counts up the number cells that have a specific number of points – so, when we group the data, we are actually grouping it by the number of points (numpts) computed in the previous query. This means we have a table that tells us how many cells have a specific number of points in it.
Now, while we are at it, we may as well compute some of the other things in the table like “total points” and fx^2.
(SELECT numpts AS [number of quadrats], COUNT(*) AS [Number of points per cell],numpts * COUNT(*) AS [Total Points], COUNT(*) * numpts^2 [Fx^2] FROM (SELECT COUNT(*) AS numpts, [quadrat].[ID] FROM [random] AS A, [quadrat] WHERE CONTAINS([quadrat].id,[A].id) GROUP BY [quadrat].id) GROUP BY numpts)
So now we have the table shown in Table 14.4. But, what we really need is to determine the variance, mean, and the ratio. So, to do that, we wrap the previous query into another query:
SELECT (((SUM([fx^2]) – (SUM([total points])^2/ (SELECT COUNT(*) FROM [quadrat])))/(SELECT COUNT(*) FROM [quadrat])-1))/(SUM([total points])/(SELECT COUNT(*) FROM [quadrat])) FROM (SELECT numpts AS [number of quadrats], COUNT(*) AS [Number of points per cell],numpts * COUNT(*) AS [Total Points], COUNT(*) * numpts^2 [Fx^2] FROM (SELECT COUNT(*) AS numpts, [quadrat].[ID] FROM [random] AS A, [quadrat] WHERE CONTAINS([quadrat].id,[A].id) GROUP BY [quadrat].id) GROUP BY numpts)
yep, this is a bloody mess. But, it does in fact work. All those SELECT statements in the top are issuing sub queries that are getting us our values for N, average, etc. Now, this is an example of where using a programming language and storing many of the values would be much more advantageous. However, the point here is that with SQL and a basic CONTAINS clause, we can perform quadrat analysis on any dataset. Just make sure your points are in a layer called points and you quadrat cells are in a layer called quadrat.