2

I am using PostGIS/Tiger and I would like to find all locations within ~2 miles (~3218 meters) of a given location.

The location in question is obtained by just doing

select geography(the_geom) FROM tract WHERE tract_id = 'x'

So putting this query all together, we get:

SELECT tract_id FROM tract, 
        (SELECT geography(the_geom) FROM tract 
         WHERE tract_id = '36047055600') AS c(x) 
WHERE  ST_DWithin(c.x, geography(the_geom), 3218)

So I'm using ST_DWithin to find all the neighbors.

My query works great but is taking 14 seconds. I put an index on 'the_geom' column in my tract table, but it doesn't seem to help.

How can I improve my query?

1

The index on the_geom is close to useless. Since you are using geography(the_geom), you should index this.

CREATE INDEX geog_idx ON tract  USING gist (geography(the_geom));

Alternatively, you could just have a geography column.

  • WOW - so what I did was add a new column, clone the values of the_geom to be of type geography (I converted them on a new column) then added an index on that new geography column. Goes from 14 seconds to 1/2 a second! thank you so much! – Mike-751031 Jul 21 '17 at 13:58

Your Answer

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

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