[postgis-users] Problem sorting polygons with generate_series

Birgit Laggner birgit.laggner at vti.bund.de
Wed Jul 23 02:02:03 PDT 2008


I have a dataset of multipolygons and some of them have identical IDs. 
Now, I would like to sort these out and give every sequence with 
identical IDs a serial numbering (1,2,...). My idea was:

create table schema.newtable
(ID integer,
sort_nr smallint
select addgeometrycolumn 

create temp table count1
ID integer,
count smallint

insert into count1
select ID_table1 as ID, count(ID_table1) as count
from schema.table1
group by ID
having count(ID_table1) > 1
order by ID

insert into schema.newtable (ID, sort_nr, the_geom)
select o.ID_table1 as ID_new, generate_series(1,2) as sort_nr, 
o.the_geom, c.ID
from count c, schema.table1 o
where o.ID_table1=c.ID
order by ID_new

but this doesn't seem to work, because from the last SELECT, I get the 
double polygons not in twofold but as four rows with identical IDs.

Anybody knows why this happens and what I should do to come to the right 



More information about the postgis-users mailing list