This post is part of a series entitled Spatial is not Special, where I will illustrate how spatial constructs in SQL provide us with a rich toolset for geographers doing spatial analysis. To illustrate these concepts, I will be showing examples from my book Statistical Problem Solving in Geography. Even though PostGRES, SQLServer, MySQL, spatialite, and Oracle all support spatial SQL, these examples will be presented using Manifold GIS. The example dataset is a Manifold 8.0 .map file and can be found here.
Chapter 7 of my book Statistical Problem Solving in Geography illustrates a number of different spatial sampling schemes. In the next few posts we will look at random, systematic, stratified (proportional and disproportional), and the more advanced systematic unaligned point sample.
This first example shows how to create a random point sample on top of an ordinary grid. If you wanted to write a little code, you could create a for loop and run the query as many times as you like. But, we are going to cheat a little and generate some empty records with a query. It’s sort of a silly way of doing things, but Manifold doesn’t allow scripting in SQL unless you write an external script – and, I am trying to keep these examples all in SQL.
The query will create a random sample of the specified number of points. Let’s assume that we want to create 100 point samples, and that we have a vector file called StudyArea which is simply a single area feature as a box (with 4 coordinates in each corner. The SQL to do this is:
1. SELECT (rnd*RectWidth(BoundingBox(g)) 2. + MinX(BoundingBox(g)), 3. rnd*RectHeight(BoundingBox(g)) 4. +MinY(BoundingBox(g))) 5. FROM ( 6. SELECT [Geom (I)] g, Segments([Geom (I)], 24) AS J 7. FROM [StudyArea] 8. ) 9. SPLIT BY Coords(J)
Instead of starting at line 1, lets evaluate lines 6 – 9. Remember, we want to create 100 records. We have 4 points that make up our box, so we want to split those 4 points into more points – specifically, we need 96 other points (96 + 4 = 100). To do that, we will segmentize the geometry of the box using the SEGMENTS function. In line 6, you can see that we are turning segmenting our geometry into 24 segments. Unfortunately, this will return a single geometry with 96 segments. So, we are going to use the SPLIT BY function to split the segmented geometry by their coordinates. When we do that, we get the 96 coordinates from our segments, plus our original 4 segments from the box. This is the cool thing about spatial not being special – we treat the geometry like any other data object, and then use an aggregate clause to do some interesting things with it.
If you are like me, you are probably thinking – what a kludge hack. Yes, its kind of a kludge, but SQL lets us do it, and we can obtain the goal we wanted – 100 records.
Now, for each of those 100 records we have the original geometry of the box, which we called g. In line 1, we are generating the bounding box of the geometry (BoundingBox), and then returning the bounding box’s width (RectWidth) which will give us the length of our X axis. That value is multiplied by a random number, which gives us a random X distance that we add to the lower left corner of the box (MinX). The result is a random X coordinate along the X axis of the study area. We then do the same thing with the Y coordinate.
The result is 100 randomly generating points inside our study area. Try it with the example in the .map file. I ran it 4 times and got these results (remember, there will be a different result for each random number generation):
