Which PostGIS SRID is most efficient for a spatial index?

Question

I have a PostGIS-enabled database with a table called locations that stores latitude-longitude points (SRID 4326) in a column called coordinates. However, all of my lookups on that table convert the points to a metric projection (SRID 26986) mainly to do distance comparisons.

Obviously I'd like to create a spatial index on the coordinates column. My question is, which is the best (most computationally efficient) SRID to use in the coordinates spatial index in this case?

I can either index using SRID 4326...

CREATE INDEX locations_coordinates_gist 
ON locations 
USING GIST (coordinates);

Or using SRID 26986...

CREATE INDEX locations_coordinates_gist 
ON locations 
USING GIST (ST_Transform(coordinates, 26986));

score 4 · Accepted Answer · answered Nov 01 '12 at 20:49

I discovered this helpful information reading the PostGIS documentation on the ST_Transform function...

If using more than one transformation, it is useful to have a functional index on the commonly used transformations to take advantage of index usage.

So it seems the answer is, use both! I have created two indices, one with each SRID.

Which PostGIS SRID is most efficient for a spatial index?

1 Answers1