Building efficient multi geometry type database with PostGIS?

Question

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.

Answer 1

I think you may have misunderstood the docs. A geometry collection is something of the form:

GEOMETRYCOLLECTION(....).

Just because you have a mix of multipoints, multilinestrings and multipolygons in the same geometry column doesn't make them a geometry collection. multis are not considered geometry collections either.

If you truly plan to have GEOMETRYCOLLECTIONs in your geometry column,

You can use ST_DWithin with a small tolerance or no tolerance. ST_DWithin works with geometry collections -

http://postgis.net/docs/manual-2.2/ST_DWithin.html

So replace your ST_Intersects with (0 or low tolerance)

ST_DWithin(a_geometry, point_geometry, 0);

Answer 2

I think your plan to store each data subtype independently in a column is the right choice. As written in the book "PostGIS in Action", the use of the GeometryCollection subtype is not recommended: "In real-world applications, you should rarely define a data column as geometry-collection . Although having a collection is perfectly reasonable for storage purposes, using it within a function rarely makes any sense [...] You should be prepared to work with them, but avoid using them in your table design." (Obe R. and Hsu L., 2015).