I'm trying to build a PostGIS database which must be able to store (multi)points, (multi)linestrings and (multi)polygons. The goal is to efficiently querying the data with ST_Intersects and other related functions.
My first look was at GeometryCollection. I thought that was tailor-made for my needs, i thought i'll just dump various geometry types to a GeometryCollection, create an index and forget about it. Well, then i realized that ST_Intersects does not support GeometryCollection.
So my plan B is to store each data type independently in a column (with indexes of course) and query the database using OR clauses for each data type column like:
WHERE (ST_Intersects(a_geometry, point_geometry) OR ST_Intersects(a_geometry, linestring_geometry) OR ST_Intersects(a_geometry, polygon_geometry))
With proper indexes set, this looks efficient, but in this day and age i suspect there must be some better way? Some better approach that is common enough but i am not aware of?
All queries on database will be
SELECTs. Data insertion will be a one time process.