2

I've been using PostGIS' ST_DumpPoints to 'explode' multipolygon layer into points with its corresponding polygon part, ring and index information. As QGIS uses a SpatiaLite engine against its Virtual Layers, I thought it would be nice to incorporate this functionality using SQLite\SpatiaLite functions and making it provider agnostic. For this end, I searched the latest SpatiaLite function reference list for a function that would resemble the ST_DumpPoints, but couldn't find any.

Is there a SpatiaLite equivalent to PostGIS' ST_DumpPoints function? If not, I would be happy for ideas how to port its functionality to a valid SQLite\SpatiaLite SQL statement that would work inside QGIS.

EranGeo
  • 1,162
  • 8
  • 20
  • 3
    Have a look at this and that. You can get the individual points by using a recursive query... I find it far from being user friendly – JGH Sep 21 '23 at 20:01
  • Thanks @JGH. This answer is exactly what I was looking for. It 'explodes' multipolygon features to their vertices while extracting all the associated part, ring and vertex index number. It even omits the undesired extra ending vertex, that each polygon ring must have in order to be considered valid (closed line). It also creates a field indicating if the ring is 'interior' or 'exterior'. – EranGeo Sep 22 '23 at 12:19
  • Even though my question is not an exact duplicate of this question, and after taking the looking at this thread, I'm voting to close this question as duplicate. – EranGeo Sep 22 '23 at 12:30

1 Answers1

1

See https://www.gaia-gis.it/gaia-sins/spatialite-sql-latest.html

DissolvePoints

a Geometry (actually corresponding to a POINT or MULTIPOINT) will be returned.¨

The input Geometry is arbitrary: any POINT will remain unaffected, but any LINESTRING or RING will be dissolved into elementary Points corresponding to each Vertex.

NULL will be returned if any error is encountered

If the result is a MultiPolygon it can be split into a set of points with ElementaryGeometries

Will create a new out_table directly corresponding to in_table. The output table will be arranged in such a way that each row will always contain an elementary Geometry; so each time that a MULTI-type Geometry is found in the input table it will be split into many distinct rows.

Because the function takes a table as input the multipoints from DissolvePoints must be saved into a table first. The full synopsis of ElementaryGeometries is

ElementaryGeometries( in_table Text , geom_column Text , out_table Text , out_pk Text , out_multi_id Text ) : Integer

I tested that with the following steps I could create a point table that has referenced to the id of the original multipolygon. However, it is not possible to get an index for rings with ElementaryGeometries, and for getting an index for polygon parts in a multipolygon ElementaryGeometries should be used two times:

  • explode multipolygon into a polygon table with ElementaryGeometries
  • use DissolvePoints for the single polygons
  • explode the MultiPoints with ElementaryGeometries

The three steps to make a Point layer "points" from a MultiPolygon layer "multi" in SpatiaLite.

create table temp as
select ogc_fid,DissolvePoints(geometry) as geometry
from multi;

select RecoverGeometryColumn(
'temp','geometry',0,'GEOMETRY');


select ElementaryGeometries(
'temp','geometry','points','point_id','source_id');

In this example the primary key is "ogc_fid" and SRID=0 but the SQL must be adopted to match with the real data.

user30184
  • 65,331
  • 4
  • 65
  • 118
  • Thanks @user30184. The DissolvePoints function returns a multipoint geometry for each poygon\multipolygon geometry. This differs from the ST_DumpPoints function which returns a set of point (several rows). It is still useful for me, but not entirely correspond to what I want to accomplish. – EranGeo Sep 21 '23 at 19:39
  • 1
    It is possible to get further with ElementaryGeometries. I added an example and tested that the SQL works with spatialite-gui. – user30184 Sep 22 '23 at 07:13
  • Thank you @user30184. I Tried using your answer but got an error message regarding the create table part. I then looked into another thread and found a perfect answer for my question with this answer. Thank you for the effort and time. – EranGeo Sep 22 '23 at 12:48
  • That is a perfect answer showing excellent SQL knowledge, I upvoted. Only thing that can go wrong with create table is that you have not modified the query to match your table and attribute names, but because it would not resolve your whole problem it would be waste of time to look on it further. – user30184 Sep 22 '23 at 13:07