Hi James,
Thanks it helped me (I didn't understood what you would for 'y') but I found
this way of doing :
select case when lag(o.geom) OVER (PARTITION BY o.geom) = o.geom then '' else
'x ' || o.nbre::text end ,d.code,d.numero,d.nomvoie,d.commune from
(select geom,count(*) as nbre from adress group by geom having count(*) >1) as
o,
lateral (select * from adress order by numero,nomvoie,commune ) as d
where st_within(o.geom,d.geom)
in this case, each group begins with 'x times'. I do not add any value in the
first column until next change. Not what I was thinking about at the beginning
but perhaps easier to understand !
Olivier
De : postgis-users [mailto:[email protected]] De la part de
James Keener
Envoyé : jeudi 20 juillet 2017 20:18
À : PostGIS Users Discussion
Objet : Re: [postgis-users] add group number to a group by clause
You could try something with a window function. Something like case when lag(x)
= x then y else y + 1 end
On Thu, Jul 20, 2017 at 1:53 PM, Olivier Leprêtre <[email protected]> wrote:
Hi,
I have sets of points which are piled up by groups. I found how to show the
different groups with the query below but I didn't find how to add a group
number for each group. I tried with "over partition" but it seems that it's not
possible to partition with geom column.
Here is the query :
select o.nbre,d.code,d.numero,d.nomvoie,d.commune from
(select geom,count(*) as nbre from adress group by geom having count(*) >1) as
o,
lateral (select * from adress) as d where st_within(o.geom,d.geom)
it returns the piled points preceded with piled points count.
3 pointa (3 piled points)
3 pointf
3 pointg
2 point1 (2 pp)
2 point2
4 pntw (4 pp)
4 pntx
4 pnty
4 pntz
How can I add a group column like this ?
1 3 pointa
1 3 pointf
1 3 pointg
2 2 point1
2 2 point2
3 4 pntw
3 4 pntx
3 4 pnty
3 4 pntz
Thanks for any idea,
Olivier
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient>
Garanti sans virus.
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient>
www.avast.com
_______________________________________________
postgis-users mailing list
[email protected]
https://lists.osgeo.org/mailman/listinfo/postgis-users
---
L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel
antivirus Avast.
https://www.avast.com/antivirus
_______________________________________________
postgis-users mailing list
[email protected]
https://lists.osgeo.org/mailman/listinfo/postgis-users
