English

How To: Generate a result set based upon concentric rings with st_geometry

Summary

The concentric ring model, also known as the Burgess model, is used to categorize data based on concentric circles around a common centroid.

This spatial analytical process can be accomplished using SQL, an st_geometry attribute and the st_distance operator.

The instructions provided demonstrate how to execute a SQL statement to generate concentric rings and a categorized result set. The SQL consists of multiple SQL statements using a UNION to merge each result set into one result set with an attribute to identify the category (distance) where each row resides.

Procedure

Using one point layer one can execute a SQL statement to identify a set of points within a given set of distances from a central point. The output produces a result set based upon concentric rings that can then be further analyzed to identify spatial clustering.

The following example uses a point layer representing lightning strikes. The objective is to categorize the lightning strikes based on a set of equivalent distances, 0 to 1000 Meters, 1001 to 2000 Meters and 2001 to 3000 Meters.

Code:
SQL> SELECT 1 AS RING, a.objectid, st_distance(a.shape,b.shape) AS DISTANCE
FROM lightstrk a, lightstrk b
WHERE b.objectid = 100000
AND st_intersects(a.shape,st_buffer(b.shape,1000)) = 1
AND a.objectid != 100000
UNION
SELECT 2 AS RING, a.objectid, st_distance(a.shape,b.shape) AS DISTANCE
FROM lightstrk a, lightstrk b
WHERE b.objectid = 100000
AND st_intersects(a.shape,st_buffer(b.shape,2000)) = 1
AND st_distance(a.shape, b.shape) > 1000
AND st_distance(a.shape, b.shape) <= 2000
UNION
SELECT 3 AS RING, a.objectid, st_distance(a.shape,b.shape) AS DISTANCE
FROM lightstrk a, lightstrk b
WHERE b.objectid = 100000
AND st_intersects(a.shape,st_buffer(b.shape,3000)) = 1
AND st_distance(a.shape, b.shape) > 2000
ORDER BY DISTANCE;

RING OBJECTID DISTANCE
---------- ---------- ----------
1 1391406 424.3
1 15449 659.7
1 1560188 851.2
1 13709 979.4
2 1290559 1383.5
2 1468424 1392.7
2 897690 1421.5
2 1937778 1489.2
2 256875 1763.1
2 576109 1903.6
2 974152 1954.8
3 15390 2194.1
3 572980 2452.0
3 1551819 2786.2


Each SELECT statement uses the st_buffer operator to buffer the lightning strike (where ObjectID = 10000) by a specified distance, either 1000, 2000 or 3000 Meters. The output from the buffer operator is next used as input to the st_intersects operator to identify all other intersecting lightning strikes.

The additional st_distance filters in the second and third subquery are applied to the result set to return just the lightning strikes within 2000 and 3000 Meters.

Code:
SELECT 2 AS RING, a.objectid, st_distance(a.shape,b.shape) AS DISTANCE
FROM lightstrk a, lightstrk b
WHERE b.objectid = 100000
AND st_intersects(a.shape,st_buffer(b.shape,2000)) = 1
AND st_distance(a.shape, b.shape) > 1000
AND st_distance(a.shape, b.shape) <= 2000


The example above demonstrates how the st_distance operator is applied to the WHERE clause to classify all lightning strikes that are greater than 1000 Meters and less than or equal to 2000 Meters from the central strike.

The syntax 1 AS RING, 2 AS RING and 3 AS RING represent attributes for classifying each set of lightning strikes.

The UNION is applied to each SELECT statement to produce one result set.