Create a Layer with a dynamic buffer

[Paulo Eduardo Cardoso]

Hi,

I have a database with a point layer used as reference for a sampling scheme. For each point I need a buffer with a given distance. The position of reference points can change (due to sampling contingencies) and so it would be good to have buffers changing accordingly.

I know that it is possible to buffer a geometry with Buffer( geom Geometry , dist Double precision ) and that the buffer should be a registered view. I'm not being able to achieve this.

Any help will be much appreciated.

All the best,

[mj10777]

I am not sure if I understand this correctly.

- with 'be a registered view' 

-- do you mean a SpatialView, where the geometry being shown would be a result of the source geometry after a ST_Buffer(geometry,nn) ?

--> SpatialView should not contain a geometry that is dynamically created (if I remember correctly)

- 'The position of reference points can change'

-- would that mean that the distance used with ST_Buffer could be row specific?

--> with a view, distance would be static - unless you use an extra field 'distance' that would be retrieved with a sub-query

SELECT
 ogc_fid,
 GEOMETRY,
 ST_Buffer(GEOMETRY,100)AS geom_100,

 ST_Buffer(GEOMETRY,(SELECT (ogc_fid*1000)AS distance FROM test0 sub_query WHERE sub_query.ogc_fid=test0.ogc_fid))AS geom_1000

FROM test0;

Mark

[mj10777]

A sub-query would not even be needed if the field distance is in the same row

- instead of '(ogc_fid*1000)' here would be the field 'distance'

SELECT

 ogc_fid,GEOMETRY,

 ST_Buffer(GEOMETRY,10) geom_10,

 ST_Buffer(GEOMETRY,(ogc_fid*100)) AS geom_100

FROM "test0"

SELECT

 ogc_fid,GEOMETRY,

 ST_Buffer(GEOMETRY,10) geom_10,

 ST_Buffer(GEOMETRY,distance) AS geom_100

FROM "test0"

SRID=4326;POINT(1 2)
SRID=4326;POLYGON((11 2,10.98629534754573 1.476640437570562, ...
SRID=4326;POLYGON((101 2,100.8629534754573 -3.233595624294378, ...

[Jukka Rahkonen]

'mj10777' via SpatiaLite Users kirjoitti 2016-10-22 16:37:

> I am not sure if I understand this correctly.
> - with 'be a registered view'
> -- do you mean a SpatialView, where the geometry being shown would be
> a result of the source geometry after a ST_Buffer(geometry,nn) ?
> --> SpatialView should not contain a geometry that is dynamically
> created (if I remember correctly)

I believe that sometimes it is OK to have dynamically created geometries
in the view, for example if the new geometry is simplified version of
the original. But if native geometries have R-tree index and the view
makes reprojecting with ST_Transform(.. then the result is probably
unusable. This is probably something worth some thinking, testing, and
writing documentation.

-Jukka Rahkonen-

[Paulo Eduardo Cardoso]

Dear Mike

I'm not sure how to proceed using your approach and obtain a dynamic layer. I would like to end with a spatial layer to display in QGIS (or available for Intramaps ROAM).

I'll try to clarify

By 'a registered view' I mean a SpatialView showing the result of a fixed distance buffer to a source geometry (a set of points in this case) after applying a ST_buffer. Exactly as you suggested.

--> SpatialView should not contain a geometry that is dynamically created (if I remember correctly)

Didn't know about this limitation. Additionally, as explained elsewhere by Sandro, a view cannot be built with a geometry different from the source geometry.

The dynamic geometry would be interesting in this case since the location of points can change (not the buffered distance).

Distance will be static and could be obtained from a source field value or declared into the SQL.

What would be the next steps following your SELECT example?

[mj10777]

-- Drop (a possibly existing) Geometry TABLE
--- including any eventual SpatialIndex, metadata and statistics definitions
SELECT DropGeoTable(buffer_geom);
-- (re-)CREATE TABLE with Geometry 

CREATE TABLE buffer_geom AS

SELECT 

 ogc_fid,

 ST_Buffer(GEOMETRY,100)AS geom_100

FROM test0;
-- Register the Geometry (adapt srid and Geometry-Type as needed)
SELECT RecoverGeometryColumn('buffer_geom','geom_100',4326,'POINT');

Mark

 

[Jukka Rahkonen]

Hi,

You can use the DB Manager of QGIS, build SQL query
select st_buffer(geometry,50) as geom from points;
and add the result into QGIS map as a new layer.

Query can be saved and reused later.

-Jukka Rahkonen-

> --
> You received this message because you are subscribed to the Google
> Groups "SpatiaLite Users" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to spatialite-use...@googlegroups.com.
> To post to this group, send email to
> spatiali...@googlegroups.com.
> Visit this group at https://groups.google.com/group/spatialite-users.
> For more options, visit https://groups.google.com/d/optout.

[Paulo Eduardo Cardoso]

Thank you Jukka and Mark

I've successfully created a new buffer layer from my source [vpoints] object using the st_buffer() proposed by Mark.

I've also created a view from vpoints but only when subseting vpoints (keeping the same geometry).

I've failed to create a view (v500) of vpoints with the st_buffer() (resulting in polygons) with the sql proposed by Jukka (select PK_UID AS uid, st_buffer(geometry, 500) as geom from vpoints;). When I did DBManager created a table instead.

I'm convinced that: 1) Spatial Views can only be create when original geometry is no changed and 2) Spatial View may not be suited to create dynamic layer (that will adjust when reference layer change).

[mj10777]

Another possibility could be:

- add another geometry to the table (geom_buffer)  

- add INSERT and UPDATE triggers to the original geometry

-- that would UPDATE geom_buffer when any changes were made to the original geometry

- create a SpatialView that would display geom_buffer

-------

CREATE TRIGGER vw_ins_segments_berlin_1862

 INSTEAD OF INSERT ON segments_berlin_1862

BEGIN

 INSERT OR REPLACE INTO berlin_segments_create

 (id_segment,name,notes,text,belongs_to_01,belongs_to_02,valid_since,valid_until,soldner_segment,soldner_segment_buffer)

  VALUES

  (NEW.id_segment,

   CASE WHEN NEW.name IS NULL THEN  "" ELSE NEW.name END,

   CASE WHEN NEW.notes IS NULL THEN  "" ELSE NEW.notes END,

   CASE WHEN NEW.text IS NULL THEN  "" ELSE NEW.text END,

   CASE WHEN NEW.belongs_to_01 IS NULL THEN  "" ELSE NEW.belongs_to_01 END,

   CASE WHEN NEW.belongs_to_02 IS NULL THEN  "" ELSE NEW.belongs_to_02 END,

   CASE WHEN NEW.valid_since IS NULL THEN  "0001-01-01" ELSE NEW.valid_since END,

   CASE WHEN NEW.valid_until IS NULL THEN  "3000-01-01" ELSE NEW.valid_until END,

   NEW.soldner_segment,
   NEW.soldner_segment_buffer=ST_Buffer(NEW.soldner_segment,100)

  );

END;

-------

CREATE TRIGGER vw_upd_segments_berlin_1862

 INSTEAD OF UPDATE OF 

  id_segment,name,notes, text,belongs_to_01,belongs_to_02,valid_since,valid_until,soldner_segment,soldner_segment_buffer

 ON segments_berlin_1862

BEGIN

 UPDATE berlin_segments_create

 SET 

  name =  CASE WHEN NEW.name IS NULL THEN  "" ELSE NEW.name END, 

  notes = CASE WHEN NEW.notes IS NULL THEN  "" ELSE NEW.notes END,

  text = CASE WHEN NEW.text IS NULL THEN  "" ELSE NEW.text END,

  belongs_to_01 = CASE WHEN NEW.belongs_to_01 IS NULL THEN  "" ELSE NEW.belongs_to_01 END,

  belongs_to_02 = CASE WHEN NEW.belongs_to_02 IS NULL THEN  "" ELSE NEW.belongs_to_02 END,

  valid_since = CASE WHEN NEW.valid_since IS NULL THEN  "0001-01-01" ELSE NEW.valid_since END, 

  valid_until = CASE WHEN NEW.valid_until IS NULL THEN  "3001-01-01" ELSE NEW.valid_until END,

  soldner_segment = NEW.soldner_segment,
  soldner_segment_buffer = ST_Buffer(NEW.soldner_segment,100)

  -- the primary key known to the view ḿust be used !

 WHERE id_segment = OLD.id_segment;

END;

-------

This would fulfill the conditions of a SpatialView and 'update' itself automatically.

But be aware that TRIGGERs can be very tricky and must be thoroughly tested. 

Mark

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

On Mon, 24 Oct 2016 08:11:04 -0700 (PDT), Paulo Eduardo Cardoso wrote:
> I'm convinced that: 1) Spatial Views can only be create when original
> geometry is no changed and 2) Spatial View may not be suited to
> create
> dynamic layer (that will adjust when reference layer change).
>

Hi Paulo Eduardo,

both your assumptions are actually true, and here is a short
rationale explaining why.

1. many Desktop GIS applications (QGIS for first) pretend to
known in advance the exact Type (= Class) of all Geometries
expected to be stored into a layer.
unhappily many Spatial Operators could eventually return
different types depending on their input values and arguments;
just to say, ST_Buffer() will usually return a mix of Polygons
and Multipolygons, but under some exceptional condition
(e.g. radius = zero) it could even return Point/Multipoint
or Linestring/Multilinestring.
and in some rare case we can also expect to get out some
GeometryCollection (a "forbidden" Type absolutely unsupported
by many GIS applications, as e.g. QGIS).
short conclusion: there is no way to guess in advance the
exact Geometry Type returned by dynamically calling a Spatial
Operator on behalf of unknown data; querying the whole dataset
is the only way to safely determine the actual Geometry Type
returned by an arbitrary, unqualified View.
Unhappily this one is an intrinsically costly operation,
and could easily end up in very sluggish performances when
your input dataset contains a lot of features.
definitely not a good access strategy.

2. you can expect to get decent (= fast) performances on any
Desktop GIS application only when each layer is effectively
supported by some kind of Spatial Index (except when you
are just kidding on some toy dataset containing a very
limited number of features).
Unfortunately there is no way to build a Spatial Index
capable to support a dynamic View returning random values;
but querying a large layer not supported by any Spatial
Index will surely cause a deadly slowness.

Conclusion: SpatiaLite will accept as a valid Spatial View
only the ones satisfying the following conditions:
a) all geometries must be stored as fixed, immutable column
values. and consequently could never be the dynamic result
of some Spatial Operator.
b) all geometries must keep exact trace of the DB table
from there they come and of the corresponding ROWID,
so to allow querying an eventual Spatial Index.

bye Sandro

[Paulo Eduardo Cardoso]

Thank you Mark. The approach with triggers would be challenging!

Sandro, thank you for your feedback. The explanation makes sense for me. I (unfortunately) understand the rationale.

I was trying to provide end users with all layers with no manipulation needed. I think I could programmatically build the buffers and rebuild them when points change.

I'm now looking for a procedure to cast my polygons from st_buffer() into lines (better for labeling in QGIS).

All the best

[mj10777]

On Monday, 24 October 2016 19:24:29 UTC+2, Paulo Eduardo Cardoso wrote:

Thank you Mark. The approach with triggers would be challenging!

Sandro, thank you for your feedback. The explanation makes sense for me. I (unfortunately) understand the rationale.

I was trying to provide end users with all layers with no manipulation needed. I think I could programmatically build the buffers and rebuild them when points change.

I'm now looking for a procedure to cast my polygons from st_buffer() into lines (better for labeling in QGIS).

This is what I use for this task: 

- Note: MultLineString, just in case

UPDATE berlin_polygons SET soldner_linestring=CastToMultiLinestring(ST_LinesFromRings(soldner_polygon)) WHERE soldner_polygon IS NOT NULL;

Mark

Mark
 

[Paulo Eduardo Cardoso]

Thank you Mark. That work just fine!

My approach

my base theme was a shapefile with points with a unique [PK_UID] and a distance [d1] column that resulted in a vpoints 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;

--SELECT AddGeometryColumn('buffer_geom', 'Geom', 3763, 'POLYGON','XY'); --Already defined by st_buffer

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

-- Register the Geometry (adapt srid and Geometry-Type as needed)

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

-- Create a table to receive the LINESTRING elements extracted  from polygons

CREATE TABLE buffer_lines(

 PK_UID INTEGER NOT NULL PRIMARY KEY,

 d1 INTEGER

);

INSERT INTO buffer_lines (PK_UID, d1)

 SELECT PK_UID, d1

  FROM buffer_geom;

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

-- Populate the linestring geometry with the converted polygon geometry

UPDATE buffer_lines SET Geom=(

 SELECT (CastToLinestring(

   ST_LinesFromRings(P.Geom)

   )

 )

  FROM buffer_geom AS P

   WHERE (buffer_lines.PK_UID = P.PK_UID

   )

);

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

One question: Can the SQL be simplified, avoiding creating the polygon layer?

[mj10777]

See my note from yesterday

- adding an extra geometry_buffer to the main table

-- with INSERT and UPDATE TRIGGERs 

Mark