Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

Is it possible to define a part of a geometry column as the Primary Key in a PostGIS database?

For example, when I want to store 3D points in a database, using the x/y coordinate as the primary key.

Thanks for helping!

share|improve this question
2  
I don't think it's good practice to use coordinates in the primary key. What if they change? –  underdark Mar 10 '13 at 18:48
1  
I'm agree with underdark. But nevertheless what is your exact issue? You can't extract coordinates and fill an appropriate column? Or you have troubles with a trigger creation? –  SS_Rebelious Mar 10 '13 at 20:13
    
The coordinates won't change, because they represent a given grid. I don't want, that a point is saved twice in the database/grid. So I want to describe, that only one point with the coordinates (x_p,y_p,z_p) can have the coordinates (x_p,y_p). –  seventhup Mar 11 '13 at 7:54
    
You could try with a trigger to validate your constraints on INSERT and UPDATE (with ST_Equals) and perhaps a simple sequence for your PrimaryKey –  Víctor Velarde Mar 11 '13 at 8:35
add comment

1 Answer

No. Well not with a gist index. I think in pre 1.5 you could with a btree (but that would only work for points), but I wouldn't recommend it even if you can.

You might want to look at exclusion constraints. It will be somewhat lossy because the bbox is a bit fatter to hold the point, but might be good enough for you needs.

http://www.postgresql.org/docs/current/static/ddl-constraints.html (see way at the bottom of the page). Alternatively you can do a unique key index on ST_X(geom), ST_Y(geom)

The benefit of that is you can control the rounding precision, like

   CREATE UNIQUE INDEX uidx_tbl_geomxy  ON tbl 
     USING btree (CAST(ST_X(geom) AS numeric(16,5)), CAST(ST_Y(geom) As numeric(16,5) ));
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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