6
Because the GEOMETRY returned from ST_Transform is not covered by any index.
The planner chooses to run the points table against the polygon table and its GIST index on geometry, which is built with GEOMETRY(<TYPE>, 4326); using a transformed GEOMETRY, or any other derived GEOMETRY, does not match the index definition, and a sequential scan between ...
6
The convex hull algorithm is somewhat delicate, and ST_ConvexHull makes no effort to preserve and validate topology and geometry - it is possible that parts of the constructed hull collapses into lower-dimensional geometries during the process.
You'd need to dump the GEOMETRYCOLLECTION, and filter for a specifc geometry type:
CREATE TABLE boundary AS
...
5
@Geozelot has addressed why the polygon layer index is not usable.
Now there are more issues here.
The index on the point layer is also not usable because the query is starting from the point layer and then is doing a left join to the polygon layer. A full table scan of the point layer is to be done anyways, so the spatial index is not used. (and, as ...
2
The query is doing an implicit cross join between v and a, then you join a to n with a ON condition between n and v, which is invalid (the implicit cross join with v will occur after the explicit joins between a and n)
If you do want to keep the implicit cross join, you would have to swap v and a:
SELECT v.voronoipoly, n.nomcomar
from altcamp a, voronoi v
...
1
The solution from @geozelot works perfectly. Thanks, heaps.
I also manage to extract polygon by using ST_CollectionExtract.
Below is my revised query and the table is now accessible from QGIS too.
CREATE TABLE boundary AS
SELECT row_number() OVER () As id, ST_CollectionExtract(ST_ConcaveHull(ST_Collect(p.geom), 0.99),3) As geom
FROM mypoints p;
Only top voted, non community-wiki answers of a minimum length are eligible
