There a specific requirements where I need a geometry column in table to be flexible ( It can line or point or polygon or all of it )

Justification for requirements : main concern is to avoid the complexity of the having too many tables for each geometry type as we will be having many categories.

Possible solution I think can be are :

either use geometry column or create 3 different geom columns for point, line, polygon on single table

Has anyone been through this situation before ?

what will be possible pros and cons of each of above 2 implementation strategy ?

  • 1
    it depends on yours needs, one geometry column is ok but if you need to publish your table on geoserver then geoserver takes the geometry type from geom column constraint, but you workaround this issue with a view – geogeek Jan 3 '18 at 8:03
  • 2
    Yes, it is perfectly reasonable to store different geometry types in the same column. You can always filter out the ones you need using ST_GeometryType = 'ST_.......' However, as geogeek says, without knowing more about your workload and queries, it is hard to give a more coherent answer. – John Powell Jan 3 '18 at 8:10
  • 1
    It would be interesting to know more about your database structure. When I read your question I'm thinking of: having 1 table with many categories and a primary key (or unique ID) and 3 tables (3 geometry types) keeping the ID ( e.g. as foreign key, 1:n) and the geometry type. This would be the solution to store the data. For visualization purposes you can create views like mentioned in the answers from geogeek and John. – Stefan Jan 3 '18 at 8:18
  • 1
    Do your features still have just one geometry instead of many, for example both polygon and point geometries? And how are you going to edit and use your data? For sure it is difficult to edit tables which have multiple geometry columns (think about situation when a feature has a point geometry but you want to change that into polygon geometry) with standard GIS clients. Clients like QGIS can't even handle mixed geometry types in one geometry field but more flexible clients exist, for example OpenJUMP. – user30184 Jan 3 '18 at 9:09
  • 1
    See also: gis.stackexchange.com/questions/44475/… – Stefan Jan 3 '18 at 9:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.