[postgis-users] Problem sorting polygons with generate_series

[Birgit Laggner]

Hi,

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 
('schema','newtable','the_geom','31467','MULTIPOLYGON',2);

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 
solution???

Thanks,

Birgit.