Generate a result set based upon concentric rings with st_geometry

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.