While reviewing a few PostGIS tickets recently, I noticed that spatial indexes are not always used. We have an accepted ticket for this #30267 (GeoDjango does not create correct PostGIS index for 3D geometry fields) – Django with guidance to seek further consensus. The following aims to set step though the issue in an aim to gain that consensus.
By default, GIS models have an index created for geometry fields, see docs.
Looking specifically at Geometry fields on PostGIS, different indexes are created for 2d and 3d fields, see source. To bring this to life a gist index is created in both cases with the 3d geometry using the gist_geometry_ops_nd opclasses.
- For a 2d geometry the index
CREATE INDEXlooks something like this:- CREATE INDEX app_building2d_geom_73320ff1_id ON public.app_building2d USING gist (geom)
- And for 3d
- CREATE INDEX app_building3d_geom_881d8105_id ON public.app_building3d USING gist (geom gist_geometry_ops_nd)
I think using `gist_geometry_ops_nd ` for 3d fields is a poor default choice and we should remove that opclass.
Example time. Here’s my workings. Let’s step through them. DryORM - Django ORM Playground
9 square polygons are created, and we can filter for those that intersect with the blue outline. The query for this is:
Building2D.objects.filter(geom__intersects=bbox)
In both cases the query returns 4 geomeries; however an explain shows that in the 2D model the index was used [See the ‘Explain Output’ section for (Building2D.intersects(bbox))] but on the 3D model a sequential scan was used [(Building3D.intersects(bbox))]
Looking at the PostGIS docs on the topic only a:
subset of functions make automatic use of a spatial index
And of this subset there are 3 which use a 3D index - none of which Django currently has support for.
- ST_3DIntersects
- ST_3DDWithin
- ST_3DDFullyWithin
Looking at the docs on gist_geometry_ops_nd we can also see that it can be used in the `&&&` index operator. Again Django doesn’t have support for this. See source.
As far as I can tell it is not possible to write a query using the provided functions that make use of the default index created on 3D geometries.
PostGIS docs are also quite cautious about recommending a nd index, see docs.
For a higher dimension index to be useful, the data must cover a wide range of that dimension, relative to the kinds of queries you are constructing.
A set of DEM points would probably be a poor candidate for a 3-D index, since the queries would usually be extracting a 2-D box of points, and rarely attempting to select a Z-slice of points.
A set of GPS traces in X/Y/T space might be a good candidate for a 3-D index, if the GPS tracks overlapped each other frequently in all dimensions (for example, driving the same route over and over at different times), since there would be large variability in all dimensions of the data set.
The rest of the script goes on to show that you can add the ST_3DIntersects as a lookup and that this will result in an Index Scan for both 2d and 3d models. However, the behaviour is different – if there’s a 3rd dimension in the supplied geometry this will play a part in the query result.
Finally, it also shows index being used for 2d within and contains lookups but not on the 3d model.
What to do?
I think the gist_geometry_ops_nd opclass should be removed when creating 3d indexes and as this is a better default. If folk want to retain that index they can add it using Meta.Indexes. Even if we were to add 3d lookups they don’t seem to be as useful as the 2d ones.
If we were to change the default I am unsure how we could go about this. Simply changing the type of index would be backward incompatable change and would leave databases in a different state than if migrations were applied from scratch. Just documenting the change seems not ideal to me.
I thought about an alternantive (lots of hand waving incoming) where we add a new kwarg to GeometryField/BaseSpatialField. Let’s call it “index_3d” (working title) and defaults to True. We could then immediately deprecate that to nudge folk to either set it to False, or they can set spatial_index to False and add a 3D index via Meta.Indexes (although that would probably add an add/remove index actions, not sure if that could be combined into a rename?). I’m not sure how plausible this approach is.
If we were to agree a change in the default is the right decision we can likely explore the implementation options further as a next step.
cc / @jacobtylerwalls