[postgis-users] TR: add group number to a group by clause

Olivier Leprêtre o.lepretre at gmail.com
Fri Jul 21 00:07:08 PDT 2017

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 !




De : postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] 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 <o.lepretre at gmail.com> wrote:



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,











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
postgis-users at lists.osgeo.org


L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel antivirus Avast.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170721/22b4310a/attachment.html>

More information about the postgis-users mailing list