1

Here is my problem: I had a polygon layer with an index which looks like this: id, population 100, 26 200, 12 300, 45 ...

I edited the polygon layer and divided some of the polygons into smaller polygons (approximately 3-7 subpolygons). I already took care of having my data splitted between subzones (according to population density). So now I have this: id, population 100, 22 100, 1 100, 3 200, 6 200, 6

I would like to create a new index that reflects the old one. For instance: oldId, newId, population 100, 100, 22 100, 101, 1 100, 102, 3 200, 200, 6 200, 201, 6

Things I tried: Defining a sequence:

DROP SEQUENCE IF EXISTS increment_id; CREATE TEMP SEQUENCE increment_id INCREMENT BY 1 MINVALUE 0; SELECT id, id+nextval('increment_id') AS new_id FROM polygon_mapping WHERE id = 100;

THis works well for a single id to rename (the WHERE clause), but I don't know how to restart the sequence for every id.

I made some thinking around using the 'lag' function to compare current id with previous id. But I don't manage make it work.

Any suggestions? Thank you

ps: I went through Reset auto increment counter in postgres where they reset the SEQUENCE but I don't manage to make it work in a SELECT clause.

Community
  • 1
  • 1
Antoine
  • 108
  • 5

1 Answers1

1

Maybe using generate_series()?

SELECT id, generate_series(id,id + count(*)) AS newid 
FROM polygon_mapping GROUP BY id;

If you want to select additional attributes, use a subquery and group the attributes using array_agg, than select the values from the array in the primary query:

SELECT id, 
       generate - 1 + id AS newid,
       population_array[generate] 
FROM (
   SELECT id,
          generate_series(1,count(*)) AS generate,
          array_agg(population) AS population_array 
   FROM polygon_mapping GROUP BY id
) AS foo ORDER BY newid,id;
Tom-db
  • 6,528
  • 3
  • 30
  • 44
  • Hey! Thank you for answering, it is very close to what I am looking for ! The thing is: I am also trying to keep the rest of the information (there are other columns : id, data1, data2... 100, 0.5, 0.2 And you method won't let me have this additional information. Do you have any clue how to get it? – Antoine Oct 17 '16 at 14:07
  • btw: I am a newbe with 1 reputation score, so I can't upvote your answer which is usefull :( – Antoine Oct 17 '16 at 14:32
  • Do you mean, the same informations should appear redundantly in every "sub" polygon? Maybe you can update your question with more complete example – Tom-db Oct 17 '16 at 18:25
  • Good suggestion: I updated the question so it reflects better what I am trying to achieve. Thank you for the suggestion: I was trying to make the question simple, I turned out making it a different question... – Antoine Oct 18 '16 at 14:58
  • Thank you very much Tommaso, it is working perfectly ! (I am marking the answer as correct, but I still can't upvote your answer because i have no reputation points) – Antoine Oct 19 '16 at 15:55