I have a PostgreSQL/PostGIS table which stores data about all the warning signs around the world. The map data is provided to me by our vendor, and the data is such that no possible combination of columns from the table can be used to define a suitable primary key for the table.
My objective is to create a primary key and creating a new AUTO_INCREMENT-like column with plain integers is not going to cut it.
Since the only column with actually unique data is the geometry column provided to me, is there any way to use this data in order to develop/create a suitable primary key?
Best Answer
You don't say why a new auto increment column with plain integers will not "cut it" but a standard robust alternative to the unique ID problem is to use a UUID. You can set this witha default too for new data imports.
If you want to use the geometry then you could go with John Powell's answer or altenatively, for point data, you could use a geohash (ST_geohash). A geohash can also be useful for clustering your data (though you can do that on the geometry directly).
However, you should bear in mind that geometry can't really be guaranteed to be unique unless you have first checked for the presence of duplicate geometries. Even then, it may be perfectly legitimate to have two distinct features with identical geometry (depending on use-case) and you'd therefore want different primary keys. So I'd consider geometry to be a poor choice as a primary key for these reasons and personally opt for UUID.