SQL concentric rings (st_buffer) to an existing point layer

[Paulo Eduardo Cardoso]

Dear all,

I'm trying to create a polygon layer with series of concentric rings to a number of points from my reference layer. The rings will ultimately be converted into lines but I'm failing to achieve the first step.

An example for a single buffer to a point [vpoint ] layer.

-- Create polygon layer with 500m buffer to original points

CREATE TABLE buffer_geom AS

SELECT 

 PK_UID, d1,

 ST_Buffer(Geometry, 500) AS Geom

  FROM vpoints; -- original point layer

SELECT CreateSpatialIndex('buffer_geom','Geom');

-- Register the Geometry

SELECT RecoverGeometryColumn('buffer_geom','Geom', 3763, 'POLYGON');

I'd like to expand this procedure to add additional rings to 1000m, 1500m, ...

I've found some references to the process in PostGIS but could not replicate them to a specific case where a reference point layer already exists.

All the best,

Paulo 

[a.fu...@lqt.it]

On Tue, 25 Oct 2016 10:31:45 -0700 (PDT), Paulo Eduardo Cardoso wrote:
> -- Create polygon layer with 500m buffer to original points
> CREATE TABLE buffer_geom AS
> SELECT 
>  PK_UID, d1,
>  ST_Buffer(Geometry, 500) AS Geom
>   FROM vpoints; -- original point layer
> SELECT CreateSpatialIndex('buffer_geom','Geom');
> -- Register the Geometry
> SELECT RecoverGeometryColumn('buffer_geom','Geom', 3763, 'POLYGON');
>

Hi Paulo Eduardo,

the first SQL statement is fine, but the second and third
should be swapped, because building a Spatial Index strictly
requires a properly registered Geometry column.
you simply have to call RecoverGeometryColumn() before calling
CreateSpatialIndex()

bye Sandro

[Paulo Eduardo Cardoso]

Thank you Sandro,

I've successfully created concentric rings for a point layer with the following code

CREATE TABLE dist(

PK_UID INTEGER,

dist INTEGER

);

INSERT INTO dist (PK_UID, dist) 

  VALUES (1, 250);

INSERT INTO dist (PK_UID, dist) 

  VALUES (1, 500);

INSERT INTO dist (PK_UID, dist)

  VALUES (1, 750);

INSERT INTO dist (PK_UID, dist) 

  VALUES (2, 250);

INSERT INTO dist (PK_UID, dist) 

  VALUES (2, 500);

INSERT INTO dist (PK_UID, dist)

  VALUES (2, 750);

CREATE TABLE bdist AS

  SELECT D.PK_UID, D.dist, V.Geometry

     FROM dist AS D

  LEFT JOIN vpoints AS V

    ON D.PK_UID = V.PK_UID;

CREATE TABLE buffer_geom AS

SELECT 

 PK_UID, -- columns from vpoints

 CASE

   WHEN dist = 250 THEN

     ST_Buffer(Geometry, 250)

   WHEN dist = 500 THEN

     ST_Buffer(Geometry, 500)

   ELSE ST_Buffer(Geometry, 750)

   END Geom

  FROM bdist;

SELECT RecoverGeometryColumn('buffer_geom','Geom', 3763, 'POLYGON');

SELECT CreateSpatialIndex('buffer_geom','Geom');

I'm not sure I'm taking the best use from CASE CLAUSE  since I'm repeating all conditions with WHEN THEN ELSE....

Cound it work as a loop over the values used with CASE? The SQL machine available on Manifold GIS allows CASE CLAUSE to works exactly as a loop and allow values to be defined in the expression. Very Cleaver.

All the best

[Paulo Eduardo Cardoso]

Sorry. CASE can work as a LOOP just as expected.

CREATE TABLE buffer_geom AS

SELECT 

 PK_UID, -- columns from vpoints

 CASE dist

   WHEN 250 THEN

     ST_Buffer(Geometry, 250)

   ELSE ST_Buffer(Geometry, dist * 250)

   END Geom

  FROM bdist;

SELECT RecoverGeometryColumn('buffer_geom','Geom', 3763, 'POLYGON');

SELECT CreateSpatialIndex('buffer_geom','Geom');

Great!

[a.fu...@lqt.it]

On Wed, 26 Oct 2016 04:03:28 -0700 (PDT), Paulo Eduardo Cardoso wrote:
> I'm not sure I'm taking the best use from CASE CLAUSE  since I'm
> repeating all conditions with WHEN THEN ELSE....
>

Paulo Eduardo,

if your intended goal is building a set of circular buffers for
each input point always using the same distance values there is
a simpler solution not requiring at all WHEN THEN ELSE.
you simply have to exploit the very basic Cartesian Product
returned by a JOIN operation:

CREATE TABLE my_sizes (radius DOUBLE NOT NULL);

INSERT INTO my_sizes (radius) VALUES (50.0);
INSERT INTO my_sizes (radius) VALUES (150.0);
INSERT INTO my_sizes (radius) VALUES (200.0);
INSERT INTO my_sizes (radius) VALUES (300.0);
INSERT INTO my_sizes (radius) VALUES (500.0);

CREATE TABLE my_buffers AS
SELECT p.id, s.radius, ST_Buffer(p.geom, s.radius) AS geom
FROM my_points AS p, size AS s;

SELECT RecoverGeometryColumn('my_buffers', 'geom', 3003, 'POLYGON',
'XY');
SELECT CreateSpatialIndex('my_buffers', 'geom');

bye Sandro

[Paulo Eduardo Cardoso]

This is ultra elegant (and so harder to achieve).

One question: How could we add a primary key with unique values to my_buffers? I'm trying to create a layer with linestrings (polygons borders) from by_buffers.

[Paulo Eduardo Cardoso]

Regarding adding a unique id to my buffers and convert it to linestring, I think I did it right with the following:

CREATE TABLE dist(

dist DOUBLE NOT NULL

);

INSERT INTO dist (dist) VALUES (250.0);

INSERT INTO dist (dist) VALUES (500.0);

INSERT INTO dist (dist) VALUES (750.0);

INSERT INTO dist (dist) VALUES (1000.0);

CREATE TABLE bdist AS

  SELECT V.PK_UID AS PID, D.dist, V.Geometry

     FROM dist AS D

  CROSS JOIN vpoints V;

  

CREATE TABLE buffer_geom(

PK_UID INTEGER NOT NULL PRIMARY KEY,

PID INTEGER,

dist

);

SELECT AddGeometryColumn('buffer_geom', 'Geom', 3763, 'POLYGON','XY');

INSERT INTO buffer_geom (PID, dist, Geom)

  SELECT p.PK_UID AS PID, s.dist, ST_Buffer(p.Geometry, s.dist) AS Geom 

    FROM vpoints AS p, dist AS s

  ORDER BY PID;

SELECT RecoverGeometryColumn('buffer_geom','Geom', 3763, 'POLYGON');

SELECT CreateSpatialIndex('buffer_geom','Geom');

DROP TABLE dist;

DROP TABLE bdist;

-- Polygons to Lines

CREATE TABLE buffer_lines(

 PK_UID INTEGER NOT NULL PRIMARY KEY, -- PRIMARY KEY,

 PID INTEGER,

 dist INTEGER

);

INSERT INTO buffer_lines (PID, dist)

 SELECT PID, dist

  FROM buffer_geom;

SELECT AddGeometryColumn('buffer_lines', 'Geom', 3763, 'MULTILINESTRING','XY');

-- Populate the linestring geometry with the converted polygon geometry

UPDATE buffer_lines SET Geom=(

 SELECT (CastToMultiLinestring(

   ST_LinesFromRings(P.Geom)

   )

 )

  FROM buffer_geom AS P

   WHERE (buffer_lines.PK_UID = P.PK_UID

   )

);

SELECT CreateSpatialIndex('buffer_lines','Geom');

I think it is correct.

Thank you a lot Sandro!

[a.fu...@lqt.it]

On Wed, 26 Oct 2016 06:51:35 -0700 (PDT), Paulo Eduardo Cardoso wrote:
> This is ultra elegant (and so harder to achieve).
>
> One question: How could we add a primary key with unique values to
> my_buffers? I'm trying to create a layer with linestrings (polygons
> borders) from by_buffers.
>

Hi Paulo Edoardo,

SQLite has few intrinsic limitations, and does not support creating
or changing Primary / Foreign Keys on behalf of an already existing
table.
so we absolutely must create any required PK or FK by executing an
explicit CREATE TABLE statement.
Nothing really difficult anyway; here is a practical example:

CREATE TABLE my_sizes (radius DOUBLE NOT NULL);

INSERT INTO my_sizes (radius) VALUES (50.0);
INSERT INTO my_sizes (radius) VALUES (150.0);
INSERT INTO my_sizes (radius) VALUES (200.0);
INSERT INTO my_sizes (radius) VALUES (300.0);
INSERT INTO my_sizes (radius) VALUES (500.0);

CREATE TABLE my_buffers (
buffer_id INTEGER PRIMARY KEY AUTOINCREMENT,
point_id INTEGER NOT NULL,
radius DOUBLE NOT NULL,
CONSTRAINT fk_buf_pts FOREIGN KEY (point_id)
REFERENCES my_points (id));

SELECT AddGeometryColumn('my_buffers', 'geom', 3003, 'POLYGON', 'XY');

SELECT CreateSpatialIndex('my_buffers', 'geom');

INSERT INTO my_buffers (buffer_id, point_id, radius, geom)
SELECT NULL, p.id, s.radius, ST_Buffer(p.geom, s.radius)
FROM tpl_paline AS p, my_sizes AS s;

bye sandro

[Paulo Eduardo Cardoso]

I'll try this. Thanks again Sandro.