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?

Improve this question

1 Answer 1

Reset to default
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]
Improve this answer
4
  • 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
    Commented Aug 9, 2013 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
    Commented Aug 11, 2013 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
    Commented Aug 12, 2013 at 10:12
  • I see your point now. Thanks for walking me through it.
    – mtn.biker
    Commented Aug 12, 2013 at 15:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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