Monday, July 7, 2014

Merge SpatiaLite polygon geometries with the GUnion operator in QGIS

I wanted to merge two polygon geometries in a SpatiaLite database (an example is the land feature layer shown in the screenshot below) into a single geometry using QGIS.
Two SpatiaLite polygons labelled with the primary key id number.
In QGIS, the usual command to perform this task is the Union command, but unfortunately it creates only Shapefiles. The alternative is to use the QSpatiaLite plug-in and run some SQL commands to do the job. The following steps show how to merge two polygons into one polygon using the SpatiaLite GUnion operator.

  1. In QGIS, select Database | SpatiaLite | QSpatiaLite.

    The QSpatiaLite dialog box appears.
  2. Note down the primary key numbers or any suitable identifier of the polygons to be merged, e.g. pkuid 10 and 20.
  3. In the SQL tab field, type in the SQL command.

    SELECT GUNION
    (
    (SELECT geometry FROM land WHERE pkuid = 10)
    ,
    (SELECT geometry FROM land WHERE pkuid = 20)
    )
    AS geometry


  4. Click Run SQL.

    A temporary new geometry object is created.


    Note: if the above SQL command execution is successful, then it is fine to proceed to create a new merged polygon feature and delete the original two polygons.
  5. In the SQL tab, type in the following SQL command to create and insert a merged polygon feature record.

    INSERT INTO land (geometry)
    SELECT GUNION
    (
    (SELECT geometry FROM land WHERE pkuid = 10)
    ,
    (SELECT geometry FROM land WHERE pkuid = 20)
    )
    AS geometry

  6.  Click Run SQL.

    A new row is inserted into the land table.
  7. In the SQL tab, type in the SQL command to delete the original two polygon features.

    DELETE FROM land WHERE pkuid = 10 OR pkuid = 20

  8. Click Run SQL.

    The original polygon features are deleted.
  9. Refresh the map view by toggling the land feature display off and on.

    The merged polygon is displayed.

No comments:

Related Posts with Thumbnails