I'm using PostGIS/Tiger for a mapping solution.

For a given longitude, latitude (derived when someone clicks on a point in my Google Map), I want to retrieve the closest point from my table. My table has 5 columns on it:

id, customvalue, the_geom, longitude, latitude

(so I already have a longitude/latitude and geometry for each row)

For a given long/lat, I would like to find the closest row in the database. I achieve it in this way:

    SELECT * FROM mytable 
    WHERE ST_Distance_Sphere( ST_Point(ST_X(ST_Centroid(the_geom)), 
    (ST_MakePoint(-73.94039154052734, 40.68779755192939))) <= 300

This works and gives me my results! The 300 I just made up as it seems to give me 2-3 (but not thousands). I then just take the first record and i'm done.

The problem is this query takes about 10-13 seconds to run each time. I would love to speed this up.

I have two possible solutions I would like help on:

  1. This query is overkill and not constructed right because I'm not taking advantage of the fact I already have a geometry, long, and lat on each row in my table, and I should construct the query differently.

  2. The query is actually correct, but I am missing some indexes that could help me out and make things much faster.

  • try the <-> or <#> operators instead of st_distance_sphere they use indexes and are way faster. however you need to read the documentation on them. I believe <-> operator in 9.5 and above computes the distance from the boundary geometries but before 9.5 it might be the centroid, I dont know how that will affect your query because you are using points already.. – ziggy Jul 20 '17 at 16:09
  • Welcome to GIS SE. As a new user, please take the Tour. In it, you'll see that we use a "Focused question / Best answer" model that requires only one question per Question. Please Edit the question. – Vince Jul 20 '17 at 16:10
  • All of this, ST_Point(ST_X(ST_Centroid is completely redundant and you can just use ST_Centroid(the_geom) which already returns a point. This, will most likely stop any spatial indexes from working that you might have. Also, for this kind of query, it is usually better to use something like ST_DWithin to limit the search space with a a spatial index, than to calc all distances and then compare to a number, such as 300. – John Powell Jul 21 '17 at 16:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.