Can a PostGIS table or view have two geometry columns?

Question

I am just wondering if it is possible for PostGIS to have two geometry columns.

I use shapefile a lot, which has only one "Shape" column for geometry and all geometries in that column has the same geometry type (i.e. POINT, POLYLINE, POLYGON).

I wonder what are the rules for PostGIS regarding the geometry column? Can we have two geometry columns. If so, which is the default when one refer to a row? I've seen the use of the_geom or geom some times, are these names required?

I read that PostGIS maintains a view geometry_columns about geometry columns in tables. The view has a field called f_geometry_column. I also read that PostGIS takes care of the maintenance of this view automatically. That's what makes me wonder the rules under which it operates.

-- Update --

I have the question because as mentioned above, the way the geometry_columns view is organized seems to suggest that the correspondence between a table and its geometry column is one to one. Is that right?

Also, if geometry is just another attribute type like Numeric or string, why do we need catalogs like geometry_columns.

Answer 1

You can have many geometry columns. You can name them whatever you want.

There is no 'default' geometry. It's just another attribute. That would be like asking which is the default date for a feature holding multiple dates in separate columns. Typically in a program like QGIS you would specify which geometry column you want to draw. If you want to draw multiple geometries, you would add separate layers to the table of contents. Working in PostGIS of course, you can access all the geometries associated with a record in any query.

I have found this useful in the past for creating columns holding different definitions of centroid for features whose initial geometry was polygon. It is great to avoid duplicating information, and you can even define triggers so that when the original polygon changes, the centroid updates automatically. This is a lot better than working with shapefiles.

The organisation of geometry_columns does not suggest that one table must have one geometry column. This example is from the documentation, note the f_table_name, it is the same, with two geometries with different SRIDs:

   f_table_name   | f_geometry_column | srid | type
------------------+-------------------+------+-------
 vw_pois_ny_parks | geom              | 4326 | POINT
 vw_pois_ny_parks | geom_2160         | 2160 | POINT

This table is not editable, and all geometry columns are automatically registered in it.

Why does it need this view? I can't really speak to that. I suspect it is something to do with the geometry type being defined by the PostGIS extension, and/or the need to note the SRID of a geometry column for use when transforming. But I don't really have a good answer for that.