1

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)"
J. Monticolo
16k1 gold badge30 silver badges65 bronze badges
asked Dec 2, 2019 at 10:28
6
  • 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 ? Commented Dec 2, 2019 at 10:44
  • 1
    Is this helpful: dba.stackexchange.com/questions/176860/…? 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. Commented Dec 2, 2019 at 13:20
  • @Jochen Schwarze not really but it is close to. Thx Commented Dec 2, 2019 at 13:23
  • @Leehan: can you provide some real data (copy/paste a subset of your mytbl for testing ? Commented Dec 2, 2019 at 13:28

1 Answer 1

1

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
answered Dec 2, 2019 at 14:45
3
  • 1
    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. Commented Dec 2, 2019 at 14:56
  • 2
    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. Commented Dec 2, 2019 at 15:08
  • You are completey right. That was the issue. Commented Dec 3, 2019 at 7:03

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.