4

I need to do KNN- and within-region queries for POI data. I store the POI locations in a geography column. Since I understand that ST_DISTANCE doesn't use the geography index, I added a geometry index in order to use the <-> operator with indexed performance. This works nicely (unfortunately not over the 180° meridian but that's OK in my case).

Now I'm asking myself for which operations a geography index is actually useful?

1

This tutorial/explanation seems to contradict your question's premise(?). But the pertinent part says:

The difference is under the covers: the geography index will correctly handle queries that cover the poles or the international date-line, while the geometry one will not. There are only a small number of native functions for the geography type:

    ST_AsText(geography) returns text
    ST_GeographyFromText(text) returns geography
    ST_AsBinary(geography) returns bytea
    ST_GeogFromWKB(bytea) returns geography
    ST_AsSVG(geography) returns text
    ST_AsGML(geography) returns text
    ST_AsKML(geography) returns text
    ST_AsGeoJson(geography) returns text
    ST_Distance(geography, geography) returns double
    ST_DWithin(geography, geography, float8) returns boolean
    ST_Area(geography) returns double
    ST_Length(geography) returns double
    ST_Covers(geography, geography) returns boolean
    ST_CoveredBy(geography, geography) returns boolean
    ST_Intersects(geography, geography) returns boolean
    ST_Buffer(geography, float8) returns geography [1]
    ST_Intersection(geography, geography) returns geography [1]
  • I'm aware of the functions for the geography type. My question is which of them actually use the index. ST_DISTANCE apparently doesn't. – Ortwin Gentz Aug 9 '13 at 21:54
  • The reason why I find this question confusing is that there is no reason to use an index - spatial nor otherwise - to solve a math calculation like finding the distance between two places. So it makes sense that ST_Distance doesn't take advantage of a spatial index. I'd expect the other geography functions that 'use' a spatial index are those that select/operate on a table of geometries, like ST_Intersects, ST_Covers. ie; there's probably no benefit to using an index to writing out a geography to text (ST_AsText). – mtn.biker Aug 11 '13 at 3:41
  • 1
    I mentioned my use case of k-nearest neighbors (KNN). This basically involves queries like SELECT * FROM pois, ST_DISTANCE(pois.location, <reference point>) AS Distance ORDER BY Distance DESC LIMIT 10. It makes a huge difference whether such queries use an index or not. – Ortwin Gentz Aug 12 '13 at 10:12
  • I see your point now. Thanks for walking me through it. – mtn.biker Aug 12 '13 at 15:18

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.