[postgis-users] Problem sorting polygons with generate_series

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


Hi Regina,

I did not yet understand how your code exactly works, but it does...

Thank you very much for your prompt and helpful reply!

Birgit.


Paragon Corporation schrieb:
> 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
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>   

-- 
Dipl.-Geoökol. Birgit Laggner

Johann Heinrich von Thünen-Institut,
Bundesinstitut für Ländliche Räume, Wald und Fischerei
Institut für Ländliche Räume
Bundesallee 50
38116 Braunschweig

Johann Heinrich von Thünen-Institute
Federal Research Institute for Rural Areas, Forestry and Fisheries
Institute of Rural Areas
Bundesallee 50
D-38116 Braunschweig
Germany

Tel.: (0531) 596 - 5240
Fax: (0531) 596 - 5599
E-Mail: birgit.laggner at vti.bund.de
Internet: www.vti.bund.de




More information about the postgis-users mailing list