[GIS] PostGIS create table with multiple geometry

geometrypostgispostgresqlsql

I want to create a table with a geometry field in it. e.g.:

CREATE TABLE ptgeogwgs(gid serial PRIMARY KEY, geog geography(POINT) );

In my case, this geometry field can either be a POINT, a LINESTRING or a POLYGON. I couldn't find any examples where that unique column could take three different types. So I guess the type of geometry should be fixed in advance. Nonetheless, is is better to create one table for each geometry type or create multiple columns, one for each geometry type.

Furthermore, if I choose for the second option, are the two empty geometry fields stored as "null" and therefore taking some memory space or doesn't empty fields in PostGIS table take any space?

Best Answer

You can define a generic geometry type GEOMETRY (not to be confused with the actual column data type!), i.e.

CREATE TABLE ptgeogwgs (
  gid  SERIAL PRIMARY KEY,
  geog GEOGRAPHY(GEOMETRY, 4326)
);

to let the geog column accept all geometry types. Note that for GEOGRAPHY, if left blank, the CRS will be set to EPSG:4326 by default.

Client applications usually deny the work with a single generic geometry type column as well as multiple geometry columns in one table!

Related Question