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 DISTANCEFROM lightstrk a, lightstrk bWHERE b.objectid = 100000AND st_intersects(a.shape,st_buffer(b.shape,2000)) = 1AND st_distance(a.shape, b.shape) > 1000AND 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.