Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I have the following query, which takes on average about 4 seconds. How might one improve the query such that the same response comes back faster?

SELECT * FROM geometries g 
    ORDER BY 
      ST_Distance(g.wkb_geometry, 
         ST_SetSRID(ST_Point(-77.8252923, 39.4328016), ST_SRID(g.wkb_geometry))) 
    LIMIT 1

(I understand that this actually finds the nearest geometry to a point, which is the same as the geometry in which the point falls unless the point doesn't fall in any geometry. I'm open to seeing other approaches as well.)

share|improve this question

1 Answer 1

up vote 4 down vote accepted

You are using the wrong function. Use ST_Intersects instead.

SELECT *
FROM geometries g
WHERE ST_Intersects(g.wkb_geometry, ST_SetSRID(ST_Point(-77.8252923, 39.4328016), 4326));

Update 1: Remember to add a spatial index:

CREATE INDEX geometries_geo_idx ON geometries USING GIST ( wkb_geometry );

and of course, update the statistics of the tables

VACUUM ANALYZE
share|improve this answer
    
The query is still nearly 4 seconds long...what can I do to improve that? –  Chris Cashwell Oct 5 '12 at 18:43
    
most likely, you do not have a spatial index on the geometry. Updating answer –  Ragi Yaser Burhum Oct 5 '12 at 18:44
    
We actually do have a spatial index on wkb_geometry. Any other thoughts? –  Chris Cashwell Oct 5 '12 at 18:50
3  
@ChrisCashwell Did you switch to the ST_Intersects query? Your original query cannot take advantage of the GIST index. –  Scro Oct 5 '12 at 19:26
2  
@ChrisCashwell Just want to make sure I am getting this right. Are you telling me that you switched the query, created the index, vacuum analyzed it, and that the result of that is a similar runtime? –  Ragi Yaser Burhum Oct 5 '12 at 19:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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