[postgis-users] Strange behavior with empty geometries GROUP BY
Pierre Racine
Pierre.Racine at sbf.ulaval.ca
Tue Jun 20 14:41:57 PDT 2017
Hi,
When I try to count the occurrence of a number of empty geometries like this:
SELECT ST_AsText(geom) txt, count(*)
FROM (
SELECT 'POINT(0 0)'::geometry geom
UNION ALL
SELECT 'POINT(0 0)'::geometry geom
UNION ALL
SELECT 'POINT(0 0)'::geometry geom
UNION ALL
SELECT 'POINT(0 1)'::geometry geom
UNION ALL
SELECT 'LINESTRING(0 0,0 1)'::geometry geom
UNION ALL
SELECT 'GEOMETRYCOLLECTION EMPTY'::geometry geom
UNION ALL
SELECT 'POINT EMPTY'::geometry geom
) foo
GROUP BY geom;
The 'GEOMETRYCOLLECTION EMPTY' and the 'POINT EMPTY' do not aggregate and I get:
txt count
POINT(0 0) 2
POINT EMPTY 1
POINT(0 0) 1
LINESTRING(0 0,0 1) 1
GEOMETRYCOLLECTION EMPTY 1
POINT(0 1) 1
If I remove any geometry other than the two EMPTY ones like this (I removed the first one 'POINT(0 0)'), the two EMPTY geometries DO aggregate:
SELECT ST_AsText(geom) txt, count(*)
FROM (
SELECT 'POINT(0 0)'::geometry geom
UNION ALL
SELECT 'POINT(0 0)'::geometry geom
UNION ALL
SELECT 'POINT(0 1)'::geometry geom
UNION ALL
SELECT 'LINESTRING(0 0,0 1)'::geometry geom
UNION ALL
SELECT 'GEOMETRYCOLLECTION EMPTY'::geometry geom
UNION ALL
SELECT 'POINT EMPTY'::geometry geom
) foo
GROUP BY geom;
Result:
txt count
POINT(0 0) 2
LINESTRING(0 0,0 1) 1
POINT EMPTY 2
POINT(0 1) 1
Any idea why empty geometries aggregation seem so dependent on what others geoms are in the table?
Sorry I could not build a more simple example.
Thanks,
Pierre
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170620/f3a09aa0/attachment.html>
More information about the postgis-users
mailing list