Buffering and Finding Overlaps
The BUFFER() function creates a radial feature around the points of the input feature. This is like a map showing a bomb-blast radius around a specific point, although it also can be an area around a point, or slid along a line or polygon boundary. The BUFFER() function creates a new polygon feature by nicely merging all the intermediate pieces into a smooth feature.
It takes two arguments: the shape to be buffered and the distance (or radius) around the feature that the buffer should be created at, in the same coordinate units as the feature itself.
Let's create a simple buffer around one of our example points. In the example, we provide the BUFFER() function with the value of 1, which is the radius of the buffer to be created around the feature. This means it will create a 1 degree radius around our point:
SELECT ASTEXT(BUFFER(shape, 1))
FROM location_points
WHERE id = 1;
We wrap it with ASTEXT() so we can read it on screen. The result shows a lot of output vertices on our polygon; it creates dozens of points to help create a smooth radius.
+--------------------------------+
|col1 |
+--------------------------------+
|POLYGON ((-120 52, -120.01921471959677 51.804909677983872, -120.07612046748871
51.617316567634909, -120.16853038769746 51.444429766980399, -120.29289321881345
51.292893218813454, -120.44442976698039 51.168530387697459, -120.61731656763492
51.076120467488714, -120.80490967798387 51.019214719596768, -121 51, -121.195090
32201613 51.019214719596768, -121.38268343236508 51.076120467488714, -121.555570
23301959 51.168530387697452, -121.70710678118654 51.292893218813454, -121.831469
61230254 51.444429766980399, -121.92387953251128 51.6193165676340-0, -121.980785
28040323 51.804909677983872, -122 52, -121.98078528040323 52.195090322016128, -1
21.92387953251129 52.382683432365084, -121.83146961230254 52.555570233019601, -1
21.70710678118655 52.707106781186546, -121.55557023301961 52.831469612302541, -1
21.3826834323651 52.923879532511286, -121.19509032201613 52.980785280403232, -12
1 53, -120.80490967798387 52.980785200403232, -120.61731656763292 52.92387953251
1286, -120.44442976698041 52.831469612302548, -120.29289321881346 52.70710678118
6546, -120.16853038769746 52.555570233019601, -120.07612046748872 52.38268343236
5091, -120.01921471959677 52.195090322016128, -120 52))|
+--------------------------------+
Now we insert this polygon into our location_polygons table:
INSERT INTO location_polygons (id, shape)
SELECT 2, BUFFER (shape, 1)
FROM location_points
WHERE id = 1;
Now we can also use that new (or temporary) polygon in other functions, without even having to store it in a table. Here we can test by using the WITHIN() function, which checks if one feature is inside the other. Let's use it against our location_points table to see if it can find which ID is actually inside the buffered area.
SELECT points.id
FROM location_points points, location_polygons polygons
WHERE WITHIN(points.shape, polygons.shape) = 1;
+------------+
|id |
+------------+
| 1|
+------------+
One feature meets the WHERE clause: points.id = 1, the very point we used to create the polygon we are comparing against.