The green points, buffered by one of the point attributes, should result in the three areas outlined in red (a table with three rows):

enter image description here

How can I do this with SpatiaLite?

If it's not possible, is there any other way to do this in QGIS?

  • Should be doable with SQL inside Spatialite. Do you mean that the point table has a numeric attribute, let's call it "radius", and you would like to start by buffering ST_Buffer(geometry, radius)?
    – user30184
    May 2 '16 at 16:28
  • @user30184 yes, the buffer radius is based on a numeric value
    – Christoph
    May 2 '16 at 16:33
  • For QGIS see this gis.stackexchange.com/questions/25061/…
    – Taras
    May 2 '21 at 7:20

Here is a step-wise process. Queries were made with Spatialite-gui and visualizations with OpenJUMP.

Take some points into table "points" with an attribute "radius".

enter image description here

Buffer points by taking the radius from an attribute with SQL

SELECT ST_Buffer("geometry", "radius") as geometry FROM points;

enter image description here

Union the buffer areas with SQL

CREATE TABLE "combined" AS
SELECT ST_Union(geometry) as geometry FROM buffers;

enter image description here

Union is one big multipolygon at this stage. Split it to three distinct geometries with SQL

SELECT ElementaryGeometries('combined', 'geometry', 'elements', 'ID', 'poly_ID');

Have a look at the new table "elements"

enter image description here

The result is close to your sketch, isn't it?

Note: You must execute some additional SQL statements or use the tools of the spatialite-gui besides the ones I have written for registering the geometry columns of the interim tables. I hope my answer is complete enough for a proof of concept.

You can write ST_Union and ST_Buffer into same SQL but it is necessary to create a physical table "combined" and register it into geometry_columns for making ElementaryGeometries function to work.

  • What do you mean by "registering the geometry columns of the interim tables"? Is that what is outlined here: gaia-gis.it/spatialite-2.4.0-4/spatialite-cookbook/html/… ? That would make sense to me because I can omit doing those those steps manually when I run an SQL query in QSpatialite and hit "Create spatial table and view in QGIS"
    – Christoph
    May 3 '16 at 7:22
  • I mean the same thing. Current Spatialite version is 4.3 and version 4.4 is soon out but the old cookbook seems to be still usable. See AddGeometryColumn and RecoverGeometryColumn at gaia-gis.it/gaia-sins/spatialite-sql-latest.html and compare with the cookbook. Spatialite-gui and Qspatialite have special tools for that purpose. If you create a new table with CREATE AS SELECT... it will be unusable for QGIS and ElementaryGeometries because they both want to check some metadata from the metadata tables.
    – user30184
    May 3 '16 at 7:35
  • st_union returned something that looked exactly like the original buffered points, unlike what you showed in your screenshot. The result of the call to ElementaryGeometries is empty (those observations might be related)
    – Christoph
    May 3 '16 at 7:40
  • Hard to say why you are getting different result. What does your Spatialite return with this query that simulates your use case with simple overlapping rectangles: SELECT ST_AsText(ST_Union(ST_GeomFromText( 'POLYGON (( 320 320, 320 400, 380 400, 380 320, 320 320 ))'),ST_GeomFromText(' POLYGON (( 340 280, 340 360, 400 360, 400 280, 340 280 ))'))) I get POLYGON (( 320 320, 320 400, 380 400, 380 360, 400 360, 400 280, 340 280, 340 320, 320 320 )) from Spatialite and also from PostGIS with the same query.
    – user30184
    May 3 '16 at 9:10
  • that seems to work fine. After trying again from scratch I can also achieve the desired result with your instructionson my own data. Unfortunately, I have not been able to create a view-like thing out of these because ElementaryGeometrieswill always create a new table, wouldn't it?
    – Christoph
    May 3 '16 at 9:42

In QGIS you'd use the merge shapefiles function, just be sure to choose the correct couples/triplets/whatever. From the program menu, Vector -> Data management tools -> merge shapefiles


You could to use Dissolve Tool

But, perhaps you could to create a buffer using the parameter:

Dissolve result=YES enter image description here

  • I think this comes close to what I need and I'll try it. One drawback is that I need to actually create a field with the desired buffer radius, while a database query allows me to calculate it.
    – Christoph
    May 2 '16 at 19:41
  • You could to create a view with a field with the desired buffer radius, and then you could to use "Variable distance Buffer" (SAGA): Input Layer= View from DB. May 2 '16 at 20:27

In SQL, it is an aggregation query. You can do something like:

Select st_Union(geom) from your_table group by the_attribute_which_make_each_group
  • Unfortunately there is no attribute other than the buffered points' overlapping-ness, so at least one step is missing here. Creating a grouping attribute from the buffered points in the first place is something I can't come up with myself.
    – Christoph
    May 2 '16 at 19:45
  • The "group by" could use the result of a spatial query. Check this: postgis.net/docs/manual-2.2/ST_ClusterIntersecting.html
    – WKT
    May 2 '16 at 21:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.