The green points, buffered by one of the point attributes, should result in the three areas outlined in red (a table with three rows):
How can I do this with SpatiaLite?
If it's not possible, is there any other way to do this in QGIS?
4 Answers
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".
Buffer points by taking the radius from an attribute with SQL
CREATE TABLE "buffers" AS
SELECT ST_Buffer("geometry", "radius") as geometry FROM points;
Union the buffer areas with SQL
CREATE TABLE "combined" AS
SELECT ST_Union(geometry) as geometry FROM buffers;
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"
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" Commented May 3, 2016 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. Commented May 3, 2016 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
ElementaryGeometriesis empty (those observations might be related) Commented May 3, 2016 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 getPOLYGON (( 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. Commented May 3, 2016 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? Commented May 3, 2016 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
answered May 2, 2016 at 17:42
-
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. Commented May 2, 2016 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. Commented May 2, 2016 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. Commented May 2, 2016 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– WKTCommented May 2, 2016 at 21:28
ST_Buffer(geometry, radius)?