Difference between datatypes GEOMETRY and GEOMETRY(GeometryZ, 32618) in PostGIS

geometrypostgispostgresql

I have just started working with PostGIS, Just curious which is the correct format to store 3D data in PostGIS, Although both are giving same results:

CREATE TABLE buildings_3d (
  id           Int PRIMARY KEY,
  name         varchar(100),
  geom         geometry
 ) ;


CREATE TABLE buildings_3d (
id       int PRIMARY KEY,
name     VARCHAR(100),
geom     GEOMETRY(GeometryZ, 32618)
);


INSERT INTO buildings_3d (Id, name, geom) VALUES (
1,
'Building 1',
ST_GeomFromText('POLYGON Z ((0 0 0, 
                            10 0 0, 
                            10 10 5, 
                             0 10 5, 
                             0 0 0))', 
32618)
);

Best Answer

Your second example adds a type modifier-level constraint. It will prevent all attempts to store 2D or 4D geometries in that column, as well as any geometry that's not in CRS 32618. You can only store a null or a 3-dimensional geometry in 32618 if you pick that one.

There's zero performance advantage going either way so it's all down to whether you want to:

  1. guard the column and deal with errors whenever you stumble upon non-3D and/or non-32618 shape that refuses to get in there,
  2. modify your code so that it makes sure data sourced from the table has the right SRID and expected number of dimensions.

Alternatively, the column can also be constrained with check constraints, if you register it manually.

Related Question