I have a shp file with about 500 odd records in it. It has an ID field but I want to add in a new field with a unique reference number. Can I use the field calculator to generate a unique reference? I’m looking for the reference to be alpha numeric, so something like UG_1 or UG1. Alternatively I could load the data into PostGIS and create the field and reference here but I’m still learning the ropes of SQL statements so I’m presuming that this is possible?
Use the Field Calculator's record option '$id' and add +1 after the expression to avoid a Zero-value. To create the text, simply use a string-expression.
CREATE TABLE land ( -- make the "id" column a primary key; this also creates -- a UNIQUE constraint and a b+-tree index on the column gid SERIAL PRIMARY KEY, name TEXT, the_geom geometry );
then you can INSERT data like this
INSERT INTO land (name, the_geom) VALUES ('small land', ST_PointFromText('POINT(-71.064544 42.28787)'));
INSERT INTO land (name, the_geom, id) VALUES ('small land', ST_PointFromText('POINT(-71.064544 42.28787)'), DEFAULT);
Btw. My opinion is that using ALPHA NUMERIC serials is bad idea. It's just easier to compare int's when you need to