3

I have a PostgreSQL table with 7000 records relating to grounds maintenance.

Each site has a site reference number and I want to assign a sequential parcel number within each site starting at 1 to n.

Siteid|parcelid
L001|1
L001|2
H001|1
H001|2
H001|3
etc.

I just can't figure it out.

1
  • Use of row_number() isn't a GIS-centric task. Database Administrators would be a better place to research this.
    – Vince
    Mar 28, 2023 at 12:31

2 Answers 2

6

You can use row_number and partition by site:

--Create a test table

drop table if exists public.test;
create table public.test (id serial, siteid varchar(10));
insert into public.test (siteid)
values ('L001'),('L002'),('L002'),('L001'), ('L001');

alter table public.test add column parcelid int; --Add the new column

"id"    "siteid"    "parcelid"
1       "L001"  
2       "L002"  
3       "L002"  
4       "L001"  
5       "L001"


--Calculate parcelid

update public.test a
set parcelid = sub.parcelid
from
(select id, row_number() over(partition by "siteid") parcelid from public.test) sub
where a.id=sub.id;

"id"    "siteid"    "parcelid"
1         "L001"    1
2         "L002"    1
3         "L002"    2
4         "L001"    2
5         "L001"    3
4

I'm not sure I completely understand your question, but [add autoincremental field] in the qgis toolbox may be able to help you. It can take all fields with a specific value and assign numbers to each to create values L001 to L'n' and H001 to H'n' and so forth

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.