Index a Table with Geometry Column

Question

I have a table called Stores with 10k rows which has a column location geometry(Point,4326).

CREATE INDEX "Stores_spatial_gix"
ON "Stores"
USING gist
(location);

Upon KNN query

explain analyze select *
 from "Stores"
 order by ST_distance_sphere(location,st_point(-82.373978, 29.633657)) limit 3

I get about 800ms each time. What am I doing wrong that it takes so long time?

Answer 1

Assuming you are using PostGIS 2+, you can make use of the <-> operator. See http://postgis.refractions.net/documentation/manual-2.0/geometry_distance_centroid.html

So in your example it might look like

SELECT * FROM Stores
ORDER BY location <-> 'SRID= 4326;POINT(-82.373978 29.633657)'::geometry limit 3;

Note the results may be returned misordered.

If you can put an ST_DWithin constraint in too, that may help out.

Answer 2

Here is what I ended up doing:

WITH index_query AS (
  SELECT ST_distance_sphere(location, 'SRID=4326;POINT(-82.373978 29.633657)'::geometry) as distance, ST_X("Stores"."location"), ST_Y("Stores"."location")
    FROM "Stores"
  ORDER BY location <-> 'SRID=4326;POINT(-82.373978 29.633657)'::geometry LIMIT 100) 
  SELECT * 
   FROM index_query 
  ORDER BY distance limit 10;

Results are spit out in 2.5ms in a table with 1M geometry records. The hybrid idea is taken from here to avoid misorderings.