0

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.

  • Define just one geometry column to be of a general GEOMETRY type and you can store all kind of geometries into it. Some clients like QGIS will have troubles because it can't handle points, lines, and polygons in a same layer but if you work inside PostGIS it does not matter. – user30184 Apr 18 '16 at 6:59
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);

  • i'll go back to the docs for a re-read but please help me straight this out. my column on database is of type geometry(GeometryCollection,4326), and one record there contains a mix of (multi)points,(multi)linestrings and (multi)polygons. Isn't that a geometry collection? If not what it is? Multipoints is not a geometry collection, but a mixture of multipoints and multilinestrings is a geometry collection? Am i correct? – Sharky Apr 18 '16 at 6:54
  • 2
    You want your column to be geometry(geometry, 4326) . The geometry subtype type is a catchall for any 2d geometry (including multies and geometry collection). GeometryCollection only supports things like GEOMETRYCOLLECTION. Yes the record with mix of multipoints etc. would be a geometry collection - but probably looks like GEOMETRYCOLLECT(MULTIPOINT(..), MULTIPOLYGON(..)). – LR1234567 Apr 18 '16 at 10:32
  • So you mean "a database which must be able to store (multi)points, (multi)linestrings and (multi)polygons.for each feature? In that case the both GeometryCollection and three separate geometries are good in theory. – user30184 Apr 18 '16 at 23:48
  • You are right that a mixture of different geometries can only be stored as GeometryCollection. However, GeometryCollection doesn not need to be a mixture, this is also valid GEOMETRYCOLLECTION ( POINT ( -40 20 )). – user30184 Apr 18 '16 at 23:56
  • for the record i went with geometry(geometry, 4326) as advised in your comment thanks! – Sharky Aug 11 '16 at 13:53
1

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).

Your Answer

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

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