2

This

CREATE INDEX geography_gdx ON schema.table USING GIST (geog);

works, but

CREATE INDEX geography_gdx ON schema.table USING SPGIST (geog);

gives me

[42704] ERROR: data type geography has no default operator class for access method "spgist" Hint: You must specify an operator class for the index or define a default operator class for the data type.

geog is of type geography. The docs mention an example for geometry but do not explicitly exclude geography.

Questions: Is there a way to use a SPGiST index on geography? Or is that not feasible due to the spherical nature of the data?

Note: PostGIS version

POSTGIS="2.5.0 r16836" [EXTENSION] PGSQL="110" GEOS="3.6.2-CAPI-1.10.2 4d2925d6" PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 2.2.0, released 2017/04/28" LIBXML="2.9.4" LIBJSON="0.12.1" RASTER

  • I don't know which functions you plan on using, but have you thought of casting to (geom, 4326) and using that. What are you planning on using SP-gist for? The data structures it is aimed at supporting, kd-trees and the like, might not be the most obvious choice for geographic data anyway. I had a look at the SP-gist github site, and, didn't get too far. – John Powell Jan 25 at 13:11
  • @JohnPowell Thanks for the reply. For now, I just want to run a couple of performance tests to see which index is best for my data. What caught my attention about SP-GiST is that it 'repeatedly divide[s] the search space into partitions' (from the docs). I have a very large number of polygons and points covering the entire globe and the most common function will be ST_DWithin to make (somewhat random) selections from the entire dataset. I could then cast these selections to geometry if I need to use PostGIS functions that only work on geometry. – Michael Jan 26 at 8:01
  • Yes, SP-Gist is designed to support KD-trees and other spatial paritioning indexes. However, most of the geography functions are just wrappers around the underlying geometry functions, so I still wonder, whether you might be able to use a geometry, 4326 datatype, rather than a geography one? – John Powell Jan 28 at 11:03

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.