1

I need to perform an explode, like PostGIS ST_DUMP on the QGIS plugin using pyspatialite.

There is a dot macro .elemgeo but I don't know if it is possible to call it from the pyqgis plugin via pyspatialite.

The version I use doesn´t have ElementaryGeometries table.

QGIS 2.8.1 Wien
Sqlite 3.7.17
SpatiaLite 4.1.1

2 Answers 2

2

PostGIS St_Dump is used to split a GeometryCollection into multiples records (rows).

You can use the new SpatiaLite:VirtualElementary

1) open a SpatiaLite database

from pyspatialite import dbapi2 as db
conn = db.connect('multipoly.db3')
cur = conn.cursor()
sql = "SELECT AsText(Geometry) from mulipol"
cur = conn.cursor()
for i in cur.execute(sql):
    print i 
(u'MULTIPOLYGON(((-0.527529 0.556978, -0.117798 0.290653, -0.256082 0.019206, -0.709347 -0.088348, -0.862996 0.183099, -0.734955 0.398207, -0.527529 0.556978)), ((0.199744 0.060179, 0.548015 0.129321, 0.729834 0.039693, 0.814341 -0.139565, 0.701665 -0.385403, 0.476312 -0.50064, 0.268886 -0.423816, 0.189501 -0.229193, 0.199744 0.060179)), ((-0.37644 -0.295775, -0.115237 -0.359795, -0.033291 -0.580026, -0.115237 -0.741357, -0.307298 -0.859155, -0.588988 -0.692702, -0.65557 -0.475032, -0.37644 -0.295775)))',)

2) Then you can use the

final example shows how you can construct a SQL query dynamically resolving all complex geometries form a whole table into individual elementary items

sql = "SELECT t.id, e.item_no, ST_AsText(e.geometry) FROM multipol AS t JOIN ElementaryGeometries AS e ON (e.f_table_name = 'multipol' AND e.origin_rowid = t.id);"
cur = conn.cursor()
for i in cur.execute(sql):
   print i 
(1, 0, u'POLYGON((-0.527529 0.556978, -0.117798 0.290653, -0.256082 0.019206, -0.709347 -0.088348, -0.862996 0.183099, -0.734955 0.398207, -0.527529 0.556978))')
(1, 1, u'POLYGON((0.199744 0.060179, 0.548015 0.129321, 0.729834 0.039693, 0.814341 -0.139565, 0.701665 -0.385403, 0.476312 -0.50064, 0.268886 -0.423816, 0.189501 -0.229193, 0.199744 0.060179))')
(1, 2, u'POLYGON((-0.37644 -0.295775, -0.115237 -0.359795, -0.033291 -0.580026, -0.115237 -0.741357, -0.307298 -0.859155, -0.588988 -0.692702, -0.65557 -0.475032, -0.37644 -0.295775))')
1
  • Hi @gene, thanks for your attention, but the problem is that the version I use for old projects does not have the ElementaryGeometries table. In the new projects I have already implemented this way. Anyway, thanks again.
    – Sassá
    Dec 15, 2016 at 11:55
1

Try the ElementaryGeometries function:

SELECT ElementaryGeometries (<source-table>, <geom>, <target-table>, 'pk', 'old-pk');

It is documented like this:

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

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 mamy distinct rows. out_pk is the name to be set for the output Primary Key, and out_multi_id is the name to be set for a second column within the output table where to store the original Primary Key. The optional argument transaction determines if an internal SQL Transaction should be automatically started or not (the default setting if not explicitly overridden is TRUE).

  • SpatiaLite version ..: 4.3.0a
  • SQLite version ......: 3.22.0
1
  • ElementaryGeometries fails if <target-table> already exists so precede with select DropGeoTable(<target-table>)
    – ca0v
    Nov 28, 2018 at 13:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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