QGIS 3.14 vs PostgreSQL 11.5 : same query, different result

Question

My layer has 82431 points. Each point has an attribute var with a single value a, or b or c,...
I snap points, group by geometry and count the number depending the var attribute.

So I start with :

geom | var
pt      a
pt      b
pt1     a
pt1     a
pt1     b
pt2     c
...

And want :

geom count(a) count(b) count(c)
pt      1       1        0
pt1     2       1        0
pt2     8       0        1
...

In QGIS 3.14, with a shapefile, this query gives through a virtual layer the correct result so to speak 157 rows (1 per geometry) and the columns with count :

select st_snaptogrid(m.geometry, 25) as geom,
count( case when var Like 'a' THEN 1 END), 
count( case when var Like 'b' THEN 1 END ),
count( case when var Like 'c' THEN 1 END )
from mytbl m
group by geom

Now, I uploaded the shapefile into PostgreSQL 11.5. The same query (geom instead of geometry though) does NOT group geometries. I have 64967 rows with some weird values in the columns between 0 and 10... Obviously, I do something wrong but where ?

gid id_fiche var geom
1   "74760" "Lithique"  "POINT(566780.44 6913793.64)"
2   "74763" "Lithique"  "POINT(566780.1 6913793.78)"
3   "74764" "Lithique"  "POINT(566780.2 6913793.46)"
4   "74765" "Céramique" "POINT(566780.43 6913793.45)"
5   "74766" "Céramique" "POINT(566780.11 6913793.41)"
6   "74767" "Lithique"  "POINT(566780.18 6913793.28)"
7   "76359" "Faune" "POINT(566780.22 6913793.79)"
8   "76360" "Lithique"  "POINT(566780.19 6913793.73)"
9   "76361" "Lithique"  "POINT(566780.14 6913793.3)"
10  "74768" "Lithique"  "POINT(566780.13 6913793.09)"
11  "74769" "Lithique"  "POINT(566780.43 6913793.03)"
12  "76362" "Lithique"  "POINT(566780.36 6913793.16)"
13  "76363" "Lithique"  "POINT(566780 6913792.98)"
14  "76364" "Céramique" "POINT(566780.3 6913792.94)"
15  "76365" "Lithique"  "POINT(566780.28 6913792.86)"
16  "76366" "Lithique"  "POINT(566780.37 6913792.73)"
17  "40700" "Céramique" "POINT(566781.51 6913795.88)"
18  "74759" "Lithique"  "POINT(566780.71 6913793.59)"
19  "74761" "Lithique"  "POINT(566780.5 6913794.14)"
20  "74762" "Lithique"  "POINT(566780.2 6913794.17)"
21  "76358" "Céramique" "POINT(566780.21 6913794.14)"
22  "76385" "Céramique" "POINT(566781.64 6913795.66)"
23  "76386" "Céramique" "POINT(566781.84 6913795.43)"
24  "76387" "Faune" "POINT(566781.73 6913795.3)"
25  "76388" "Lithique"  "POINT(566781.7 6913794.22)"
26  "76389" "Lithique"  "POINT(566781.67 6913794.18)"
27  "76390" "Céramique" "POINT(566781.98 6913795.05)"
28  "76391" "Céramique" "POINT(566782.18 6913794.2)"
29  "76393" "Lithique"  "POINT(566781.77 6913794.73)"
30  "76394" "Lithique"  "POINT(566781.84 6913795.78)"
31  "76395" "Lithique"  "POINT(566781.95 6913793.64)"
32  "76397" "Lithique"  "POINT(566782.26 6913793.48)"
33  "76402" "Céramique" "POINT(566781.88 6913793.38)"
34  "108456"    "Lithique"  "POINT(566782.69 6913796.39)"
35  "108457"    "Lithique"  "POINT(566782.74 6913796.6)"
36  "108458"    "Céramique" "POINT(566782.67 6913796.65)"
37  "108459"    "Céramique" "POINT(566782.53 6913796.65)"
38  "42136" "Céramique" "POINT(566780.13 6913789.78)"
39  "74770" "Lithique"  "POINT(566780.49 6913792.82)"
40  "74771" "Lithique"  "POINT(566781.05 6913793.11)"
41  "74772" "Lithique"  "POINT(566780.75 6913792.53)"
42  "74773" "Lithique"  "POINT(566780.41 6913792.2)"
43  "74774" "Lithique"  "POINT(566780.97 6913792.03)"
44  "74775" "Lithique"  "POINT(566781.03 6913791.8)"
45  "74776" "Lithique"  "POINT(566780.62 6913791.63)"
46  "74777" "Céramique" "POINT(566781.04 6913790.59)"
47  "76367" "Lithique"  "POINT(566780.57 6913792.51)"
48  "76368" "Faune" "POINT(566781.11 6913792.42)"
49  "76369" "Lithique"  "POINT(566780.85 6913792.63)"
50  "76370" "Céramique" "POINT(566780.91 6913792.81)"

Try : SELECT t1.geom2, count(case when t1.var Like 'a' Then 1 END), ... FROM (SELECT st_astext(st_snaptogrid(m.geometry, 25)) as geom2, var FROM mytbl m) t1 GROUP BY t1.geom2 ? — J. Monticolo, Commented Dec 2, 2019 at 10:44
Is this helpful: dba.stackexchange.com/questions/176860/…? — Jochen Schwarze, Commented Dec 2, 2019 at 12:38
@J. Monticolo It works fine with PostgreSQL, no problem. I was wondering why the same query wasn't returning the same result dpending spatialite or PostgreSQL. Maybe I'm asking myself too many questions : I have to admit that the way it is. — Leehan, Commented Dec 2, 2019 at 13:20
@Leehan: can you provide some real data (copy/paste a subset of your mytbl for testing ? — J. Monticolo, Commented Dec 2, 2019 at 13:28

J. Monticolo · Accepted Answer · 2019-12-02 14:45:14Z

With the QGIS data AS (copy and paste it as memory layer, and change CRS as 2154 - RGF93) :

wkt_geom    gid id_fiche    var
Point (566780.44 6913793.64)    1   74760   Lithique
Point (566780.1 6913793.78) 2   74763   Lithique
Point (566780.2 6913793.46) 3   74764   Lithique
Point (566780.43 6913793.45)    4   74765   Céramique
Point (566780.11 6913793.41)    5   74766   Céramique
Point (566780.18 6913793.28)    6   74767   Lithique
Point (566780.22 6913793.79)    7   76359   Faune
Point (566780.19 6913793.73)    8   76360   Lithique
Point (566780.14 6913793.3) 9   76361   Lithique
Point (566780.13 6913793.09)    10  74768   Lithique
Point (566780.43 6913793.03)    11  74769   Lithique
Point (566780.36 6913793.16)    12  76362   Lithique
Point (566780 6913792.98)   13  76363   Lithique
Point (566780.3 6913792.94) 14  76364   Céramique
Point (566780.28 6913792.86)    15  76365   Lithique
Point (566780.37 6913792.73)    16  76366   Lithique
Point (566781.51 6913795.88)    17  40700   Céramique
Point (566780.71 6913793.59)    18  74759   Lithique
Point (566780.5 6913794.14) 19  74761   Lithique
Point (566780.2 6913794.17) 20  74762   Lithique
Point (566780.21 6913794.14)    21  76358   Céramique
Point (566781.64 6913795.66)    22  76385   Céramique
Point (566781.84 6913795.43)    23  76386   Céramique
Point (566781.73 6913795.3) 24  76387   Faune
Point (566781.7 6913794.22) 25  76388   Lithique
Point (566781.67 6913794.18)    26  76389   Lithique
Point (566781.98 6913795.05)    27  76390   Céramique
Point (566782.18 6913794.2) 28  76391   Céramique
Point (566781.77 6913794.73)    29  76393   Lithique
Point (566781.84 6913795.78)    30  76394   Lithique
Point (566781.95 6913793.64)    31  76395   Lithique
Point (566782.26 6913793.48)    32  76397   Lithique
Point (566781.88 6913793.38)    33  76402   Céramique
Point (566782.69 6913796.39)    34  108456  Lithique
Point (566782.74 6913796.6) 35  108457  Lithique
Point (566782.67 6913796.65)    36  108458  Céramique
Point (566782.53 6913796.65)    37  108459  Céramique
Point (566780.13 6913789.78)    38  42136   Céramique
Point (566780.49 6913792.82)    39  74770   Lithique
Point (566781.05 6913793.11)    40  74771   Lithique
Point (566780.75 6913792.53)    41  74772   Lithique
Point (566780.41 6913792.2) 42  74773   Lithique
Point (566780.97 6913792.03)    43  74774   Lithique
Point (566781.03 6913791.8) 44  74775   Lithique
Point (566780.62 6913791.63)    45  74776   Lithique
Point (566781.04 6913790.59)    46  74777   Céramique
Point (566780.57 6913792.51)    47  76367   Lithique
Point (566781.11 6913792.42)    48  76368   Faune
Point (566780.85 6913792.63)    49  76369   Lithique
Point (566780.91 6913792.81)    50  76370   Céramique

and PostGIS data (beware to don't have already a public.mytbl) :

--DROP TABLE IF EXISTS public.mytbl;
CREATE TABLE public.mytbl (
  gid SERIAL PRIMARY KEY,
  id_fiche VARCHAR(6),
  var VARCHAR(10)
);

SELECT AddGeometryColumn('public', 'mytbl', 'geometry', 2154, 'POINT', 2);

INSERT INTO public.mytbl (id_fiche, var, geometry) VALUES
('74760', 'Lithique', ST_GeomFromText('POINT(566780.44 6913793.64)', 2154)),
('74763', 'Lithique', ST_GeomFromText('POINT(566780.1 6913793.78)', 2154)),
('74764', 'Lithique', ST_GeomFromText('POINT(566780.2 6913793.46)', 2154)),
('74765', 'Céramique', ST_GeomFromText('POINT(566780.43 6913793.45)', 2154)),
('74766', 'Céramique', ST_GeomFromText('POINT(566780.11 6913793.41)', 2154)),
('74767', 'Lithique', ST_GeomFromText('POINT(566780.18 6913793.28)', 2154)),
('76359', 'Faune', ST_GeomFromText('POINT(566780.22 6913793.79)', 2154)),
('76360', 'Lithique', ST_GeomFromText('POINT(566780.19 6913793.73)', 2154)),
('76361', 'Lithique', ST_GeomFromText('POINT(566780.14 6913793.3)', 2154)),
('74768', 'Lithique', ST_GeomFromText('POINT(566780.13 6913793.09)', 2154)),
('74769', 'Lithique', ST_GeomFromText('POINT(566780.43 6913793.03)', 2154)),
('76362', 'Lithique', ST_GeomFromText('POINT(566780.36 6913793.16)', 2154)),
('76363', 'Lithique', ST_GeomFromText('POINT(566780 6913792.98)', 2154)),
('76364', 'Céramique', ST_GeomFromText('POINT(566780.3 6913792.94)', 2154)),
('76365', 'Lithique', ST_GeomFromText('POINT(566780.28 6913792.86)', 2154)),
('76366', 'Lithique', ST_GeomFromText('POINT(566780.37 6913792.73)', 2154)),
('40700', 'Céramique', ST_GeomFromText('POINT(566781.51 6913795.88)', 2154)),
('74759', 'Lithique', ST_GeomFromText('POINT(566780.71 6913793.59)', 2154)),
('74761', 'Lithique', ST_GeomFromText('POINT(566780.5 6913794.14)', 2154)),
('74762', 'Lithique', ST_GeomFromText('POINT(566780.2 6913794.17)', 2154)),
('76358', 'Céramique', ST_GeomFromText('POINT(566780.21 6913794.14)', 2154)),
('76385', 'Céramique', ST_GeomFromText('POINT(566781.64 6913795.66)', 2154)),
('76386', 'Céramique', ST_GeomFromText('POINT(566781.84 6913795.43)', 2154)),
('76387', 'Faune', ST_GeomFromText('POINT(566781.73 6913795.3)', 2154)),
('76388', 'Lithique', ST_GeomFromText('POINT(566781.7 6913794.22)', 2154)),
('76389', 'Lithique', ST_GeomFromText('POINT(566781.67 6913794.18)', 2154)),
('76390', 'Céramique', ST_GeomFromText('POINT(566781.98 6913795.05)', 2154)),
('76391', 'Céramique', ST_GeomFromText('POINT(566782.18 6913794.2)', 2154)),
('76393', 'Lithique', ST_GeomFromText('POINT(566781.77 6913794.73)', 2154)),
('76394', 'Lithique', ST_GeomFromText('POINT(566781.84 6913795.78)', 2154)),
('76395', 'Lithique', ST_GeomFromText('POINT(566781.95 6913793.64)', 2154)),
('76397', 'Lithique', ST_GeomFromText('POINT(566782.26 6913793.48)', 2154)),
('76402', 'Céramique', ST_GeomFromText('POINT(566781.88 6913793.38)', 2154)),
('108456', 'Lithique', ST_GeomFromText('POINT(566782.69 6913796.39)', 2154)),
('108457', 'Lithique', ST_GeomFromText('POINT(566782.74 6913796.6)', 2154)),
('108458', 'Céramique', ST_GeomFromText('POINT(566782.67 6913796.65)', 2154)),
('108459', 'Céramique', ST_GeomFromText('POINT(566782.53 6913796.65)', 2154)),
('42136', 'Céramique', ST_GeomFromText('POINT(566780.13 6913789.78)', 2154)),
('74770', 'Lithique', ST_GeomFromText('POINT(566780.49 6913792.82)', 2154)),
('74771', 'Lithique', ST_GeomFromText('POINT(566781.05 6913793.11)', 2154)),
('74772', 'Lithique', ST_GeomFromText('POINT(566780.75 6913792.53)', 2154)),
('74773', 'Lithique', ST_GeomFromText('POINT(566780.41 6913792.2)', 2154)),
('74774', 'Lithique', ST_GeomFromText('POINT(566780.97 6913792.03)', 2154)),
('74775', 'Lithique', ST_GeomFromText('POINT(566781.03 6913791.8)', 2154)),
('74776', 'Lithique', ST_GeomFromText('POINT(566780.62 6913791.63)', 2154)),
('74777', 'Céramique', ST_GeomFromText('POINT(566781.04 6913790.59)', 2154)),
('76367', 'Lithique', ST_GeomFromText('POINT(566780.57 6913792.51)', 2154)),
('76368', 'Faune', ST_GeomFromText('POINT(566781.11 6913792.42)', 2154)),
('76369', 'Lithique', ST_GeomFromText('POINT(566780.85 6913792.63)', 2154)),
('76370', 'Céramique', ST_GeomFromText('POINT(566780.91 6913792.81)', 2154))
;

and with the query under QGIS Db Manager or under PostGIS, the result is the same :

SELECT t1.geom2,
count(case when t1.var Like 'Céramique' Then 1 END) AS c,
count(case when t1.var Like 'Faune' THEN 1 END) AS f,
count(case when t1.var Like 'Lithique' THEN 1 END) AS l
FROM (
  SELECT
    st_astext(st_snaptogrid(m.geometry, 25)) as geom2,
    var
  FROM mytbl m
) t1
GROUP BY t1.geom2

Result :

geom2   c   f   l
POINT(566775 6913800)   15  3   32

Yes I agree in PostGIS and DB manager. But like I said in first post, when using a virtual layer (Spatialite) in QGis the query : select st_snaptogrid(m.geometry, 25) as geom, count( case when var Like 'C_ram%' THEN 1 END), count( case when var Like 'Lith%' THEN 1 END ) from mytabl m group by geom returns the correct result without the FROM SELECT (). I was (am) confused about this. — Leehan, Commented Dec 2, 2019 at 14:56
That's, I think, because you group by geom when geom is the same name as the PostGIS table geometry column. If, under PostGIS, in your query, you change as geom2 and group by geom2, you'll find the same result as virtual layer. — J. Monticolo, Commented Dec 2, 2019 at 15:08

Stack Exchange Network

QGIS 3.14 vs PostgreSQL 11.5 : same query, different result

1 Answer 1

Your Answer

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

Hot Network Questions

QGIS 3.14 vs PostgreSQL 11.5 : same query, different result

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

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

Related

Hot Network Questions

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