[postgis-users] Problem sorting polygons with generate_series

[Paragon Corporation]

Hi Birgit,

This is a tricky thing to do in SQL.  The easiest way I can think of of
doing this (may not be the most efficient) is to use a correlated subquery.
For this you don't really need the temp count table you set up.  Although
you do need some field that can order the records in some arbitrary way

Something like this

--for your original table - just add a dummy serial if you don't have one
already
ALTER TABLE schema.table1 ADD COLUMN gid serial;
--Add a compound index on your new gid and the ID field to make this go
faster something like
CREATE UNIQUE INDEX idx_table1_id_gid
   ON schema.table1 USING btree (ID_table1, gid);

--Now insert into your new table
INSERT INTO schema.newtable (ID, sort_nr, the_geom) 
SELECT o.ID_table1 as ID_new, (SELECT COUNT(t1.gid) 
					FROM schema.table1 t1 
				WHERE t1.ID_table1 = o.ID_table1 AND t1.gid
<= o.gid) As sort_nr,
		 o.the_geom
FROM schema.table1 o


Hope that helps,
Regina 

 

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Birgit
Laggner
Sent: Wednesday, July 23, 2008 5:02 AM
To: PostGIS Users Discussion
Subject: [postgis-users] Problem sorting polygons with generate_series

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.

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users