5

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)
);
1

2 Answers 2

6

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.

5

It depends on your needs. With GEOMETRY(GeometryZ, 32618) you can only insert geometries which have a Z coordinate in EPSG:32618. Otherwise the insert fails with errors:

ERROR:  Geometry SRID (32617) does not match column SRID (32618)
 
ERROR:  Column has Z dimension but geometry does not

A generic geometry column geom geometry without constraints allows inserting all kind of geometries and in any SRID.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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