2

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?

2
  • If you don't add a POINT type, and keep it generic geography (with no type), does it allow any geometry type to be stored in that column? Oct 28, 2019 at 17:01
  • 4
    You can use geometry type "geometry" and save all kind of geometries into the same column. However, many client programs have difficulties to deal with such tables. Same applies to tables with multiple geometry colums, but it is not problem free to have a triplet of tables either. You must make a decision and select what kind of problems you want to have.
    – user30184
    Oct 28, 2019 at 17:01

1 Answer 1

5

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!

Your Answer

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

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