Aggregating point geometries and creating envelope using Virtual layer in QGIS

Question

I am trying to group points using a Virtual layer and create a rectangle covering an extents but I cannot get it to return the geometry.

Current code is this:

SELECT
    VP.Page,
    st_envelope(st_collect(VP.geometry)) AS geometry
FROM
    "Viewpoint Locations" AS VP
GROUP BY
    VP.Page

Coordinates are in OSGB

Sample point:

wkt_geom    ele name    VP  Easting Northing    Date    Time_Only   Distance    Bearing Page
PointZ (347471.40698287688428536 169679.50496837147511542 20.05986800000000159) 20.059868   632 01  347471.407  169679.505  15/11/2021  12:55:23    630.613 165.476 1

Link to file: https://drive.google.com/open?id=101V1qc9KstZ-e9jh-AlUzlT5EAfTkVFf&authuser=gwrichardson%40gmail.com&usp=drive_fs

Answer 1

After inspecting your input data 'Viewpoint Locations.geojson' I could assume that the visualization issue caused by How QGIS treats your GeoJSON (probably because "QGIS does not support GeometryCollection as a valid geometry type.").

Consult GeoJSON driver help page for detailed explanations on options

When I checked the details of your input file it may look absolutely fine.

However, when one try this innocent query over input data, one can get a better understanding that something goes wrong.

SELECT
    VP.Page,
    st_astext(VP.geometry) AS geomtxt
FROM
     "Viewpoint Locations" AS VP 

and the Virtual layer will look as following

As you can see Virtual layer treats each point as GEOMETRYCOLLECTION() instead of initial Point (PointZ). Unfortunately, the ST_Force2D() does not work in Virtual layer.

---

As a workaround you may follow this workflow:

Step 1. Use the "Drop M/Z values" geoalgorithm

Step 2. Apply the same query

SELECT
    VP.Page,
    st_envelope(st_collect(VP.geometry)) AS geometry
FROM
    "Viewpoint Locations" AS VP
GROUP BY
    VP.Page

and get the following output

---

IMHO GeoJSON is not yet in the ideal "relationship" with QGIS. See for instance this thread: Merging layers with different geometry (PointZ and MultiLineStringZ) in QGIS.

However, I could use your query to make envelops with my own Point (PointZ) layer stored in the shapefile, see image below.

---

References:

• Opening GeoJSON file with mixed geometries in QGIS?
• QGIS does not show the Geometry Collection Polygons from JSON file