2

I'm using PostGIS/Tiger for geocoding I am trying to find the first/nearest tract for a given long/lat, and while this query works great every time, it takes anywhere from 2-5 seconds to complete.

Any opportunities to make it faster? I added an index on the tract table's "the_geom" column (it looks like I have two indexes, a gist and a regular)

Here is my query:

        SELECT tract_id, intptlat, intptlon 
        FROM tract 
        WHERE ST_Distance_Sphere(ST_Point(ST_X(ST_Centroid(the_geom)),        
         ST_Y(ST_Centroid(the_geom))), 
        (ST_MakePoint(-118.2436849, 34.0522342))
       ) <= 650 
        LIMIT 1

I suspect the way I'm handling the distance might be the issue in the where clause, but I'm a bit stuck.

Vince
  • 20,017
  • 15
  • 45
  • 64
Mike-751031
  • 161
  • 1
  • 9

1 Answers1

3

Your query seems slightly odd to me since you are going from a point to point with ST_Point(ST_X(ST_Centroid. That is going to slow things down for sure. Another thing is that you don't get the nearest neighbour, you just get the first geom within 650 meters (which might very well be the correct one, but just as well the wrong one).

A better solution imho is to use the nearest neighbour function with:

SELECT tract_id, intptlat, intptlon
FROM tract
ORDER BY geom <-> ST_Transform(ST_SetSRID(ST_MakePoint(-118.2436849, 34.0522342),4326),4269)
LIMIT 1;
tilt
  • 3,269
  • 13
  • 24
  • Thanks for the help @tilt - the query window errors saying 'operation on mixed SRID geometries' when I run that. Any idea why? I have two columns by the way, one is the_geog which is geography, and one is the_geom which is geometry (the_geog is the_geom::geometry) – Mike-751031 Jul 26 '17 at 19:22
  • Whoops, me bad. There should have been ST_SetSrid in the query. I assume that your tract's the_geom is in the EPSG:4326 projection (lat/lon). If not, you have to find out which one and add a transform to the point. – tilt Jul 26 '17 at 19:42
  • thanks @tilt looks like it still doesn't work but possibly it's as you said that's b/c the projection is not right. Any advice on how to figure that out? What is the strategy, to convert something? Apologies i'm not terribly familiar with this.. I saw a post that talks about altering the actual column https://gis.stackexchange.com/questions/34612/how-to-change-the-srid-of-exisisting-data-in-postgis is this a good idea? – Mike-751031 Jul 26 '17 at 19:44
  • There should be no need for that. What is the output of SELECT ST_Srid(the_geom) FROM tract LIMIT 1 ? – tilt Jul 26 '17 at 19:48
  • 4269 - I put that # in there and it worked. FAST! Milliseconds! Thank you so much for your help and taking the time out. Much appreciated. – Mike-751031 Jul 26 '17 at 19:56
  • Just to be sure: I have updated my answer with your information. Please check if that corresponds with your final query. – tilt Jul 26 '17 at 20:02
  • no mine was just this: SELECT tract_id, intptlat, intptlon FROM tract ORDER BY the_geom <-> ST_SetSRID(ST_MakePoint(-118.2436849, 34.0522342), 4269) LIMIT 1; is there a difference? – Mike-751031 Jul 26 '17 at 20:12