[GIS] For which operations a PostGIS Geography index is useful

geography-data-typepostgispostgis-2.0spatial-index

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?

Best Answer

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]

Related Solutions

[GIS] PostGIS : Does the <-> operator work for Geography

No, the <-> operator is not defined on geography. You can use the geometry operator and a cast to geography on the resultants

ST_Distance(geom::geography, ST_GeogFromText(''))

to get the final distances, but potentially if your objects are over the poles or dateline or in the far north the initial geometry ordering of <-> won't be correct so things won't work perfectly.

[GIS] Is it possible that a good spatial index just cannot be made

Spatial fragmentation can make a random spatial distribution of data perform poorly, no matter how you tune the spatial index. Try duplicating the table by exporting the features by county id or some other attribute that clusters the data (or if you have to, by a systematic search grid that partitions the data by at least in ten tiles in each dimension), then rebuilding the index. If the data had been fragmented, you might see a significant improvement in spatial search efficiency.

Note: Optimizing for spatial search could have an impact on non-spatial queries; if your queries are compounnd in nature (e.g., time window and spatial envelope), then reorgainizing to split the difference (spatially, by month) can be a compromise, as can keeping two copies of the table, with differing clustered indexes, and queries directed to the table which is most friendly to the constraints.

Best Answer

Related Solutions

[GIS] PostGIS : Does the <-> operator work for Geography

[GIS] Is it possible that a good spatial index just cannot be made

Related Question