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?

  • Concatenate coordinates of the point, if this is a point. – FelixIP Jun 13 '19 at 5:45
  • 2
    you can get creative here: (first) points coordinate(s) (or centroid) or ST_GeoHash, ST_AsGeoBuf and so on. but, what's wrong with adding an integer identity column? – geozelot Jun 13 '19 at 8:30
  • let me add, as a rule of thumb: a primary key should be as small as necessary (performance and size of its index) and its values should never change (e.g. via UPDATE)! I'd strongly suggest a (numeric) surrogate primary key. – geozelot Jun 13 '19 at 8:44
  • This is great! Thank you for suggesting so many approaches to manipulate the geometry column @ThingumaBob. I read up on ST_GeoHash() and from what I understand it generates a hash that can be decoded and that is pretty darn useful! – sujaypatil Jun 13 '19 at 9:34
  • 2
    A geohash does indeed create a hash, but, it's purpose isn't typically to create a primary key. And, depending on precision, you would get a lot of clashes, so, it would actually, be a pretty awful choice for a primary key. – John Powell Jun 13 '19 at 9:46

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.

  • 1
    Why would you prefer a UUID over a SERIAL type? It is larger, and unless you are concerned about global, as distinct from within you table, uniqueness, it seems like overkill. – John Powell Jun 13 '19 at 9:44
  • You misunderstand - I would prefer a UUID over geometry as a primary key. If a serial type won't 'cut it' as the OP says (presumably because this is a distributed system and serial is only unique in a given database) then UUID gives "better uniqueness" guaranteed between the DBs in the system. If this is not a distributed system then serial is absolutely fine IMO. But the OP believes that serial is not enough... which leaves UUID – MappaGnosis Jun 13 '19 at 9:49
  • Fair enough. Yes, it would be helpful if the OP stated what "won't cut it" means. It is quite possible to use SERIAL in a distributed system, using increments of more than 1, and different starting points, but it is somewhat fiddly, and in that context, a UUID would be better, agreed. – John Powell Jun 13 '19 at 9:51
  • To be clear, I think using geometry as a primary key is a pretty bad idea, I was just pointing out that you could. – John Powell Jun 13 '19 at 9:52

You can actually create a primary key directly on a geometry column. It had never actually occurred to me to do this, and it seems unnatural compared to either an arbitrary number, ie, a serial data type, or some meaningful attribute or unique combination of attributes of the geometry's properties. As you will almost certainly want a spatial index also, you will end up with two different types of index on the same column, which is wasteful of space, as a serial data type is smaller than a geometry, and guaranteed to be unique.

It is also perfectly reasonable to have the same geometry, but with different attributes, eg, point samples over time. You would be unable to support this with a spatial column as a primary key.

Here is a small example, using a random number generator to create a million points, and the DISTINCT keyword to ensure they are unique (which is extremely likely, but not certain).

CREATE TABLE spatial_primary (geom geometry);
INSERT INTO spatial_primary (geom) 
 (SELECT DISTINCT ST_Makepoint(random(), random()) 
         FROM generate_series(1, 1000000));

Create both a primary key on the geometry column and a spatial index.

ALTER TABLE spatial_primary ADD PRIMARY KEY (geom);
CREATE INDEX ix_spatial_primary ON spatial_primary USING GIST (geom);

Now, attempt to insert a duplicate:

INSERT INTO spatial_primary SELECT geom FROM spatial_primary LIMIT 1;

ERROR: duplicate key value violates unique constraint "spatial_primary_pkey" DETAIL: Key (geom)=(0101000000000000C9FA9BEB3F0000008BB9F1D63F) already exists.

So, while it is unusual and probably not best practice, nothing prevents you from creating a primary key on a geometry, so long as the geometries are unique. However, it is almost certainly better to use some other unique value, whether serial, UUID (as MappaGnosis suggests) or some attribute(s) of the geometry.

  • Valuable input on here John! I appreciate you taking the time. :) I'll be implementing your approach on a dummy database. I was just checking the prod db and turns out there are some duplicate geometries too. :/ – sujaypatil Jun 13 '19 at 9:52
  • To be clear, I don't think you should use this approach :-). I was just saying you could. SERIAL is by far the best approach if you need to add a primary key or, if you have a distributed system, a UUID, as MappaGnosis suggests. – John Powell Jun 13 '19 at 9:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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