How to create a PostgreSQL index that includes Latitude/Longitude (using GIST), but also regular fields

Question

I have a table of locations that users of my app need to be able to search in. Baically "locations within x miles of me"

I'm using a query like this:

select * from job_locations 
   where earth_box(ll_to_earth(51.5, -0.085), 5000) @> ll_to_earth(latitude, longitude)  
   and earth_distance(ll_to_earth(51.5, -0.085), ll_to_earth(latitude, longitude)) < 5000;

And an index like this:

CREATE INDEX job_locations_lat_lng on job_locations USING gist(ll_to_earth(latitude, longitude));

However, this is a multi-tenant system, where all tables have a "tenant_id" field, and we always filter by that too. So ideally, I'd be able to have both tenant_id and ll_to_earth(lat, lng) in the index, which should presumably make searching much faster.

Is this possible? How do I create that index?

Thanks!
Daniel

bma · Accepted Answer · 2013-08-05 14:16:46Z

6

You will probably need the btree_gist extension to create the index to include the tenant_id field, which appears to have existed since at least Postgres 8,.4.

CREATE EXTENSION btree_gist;
CREATE INDEX job_locations_lat_lng ON job_locations USING gist(tenant_id, ll_to_earth(latitude, longitude));

answered Aug 5, 2013 at 14:16

bma

9,8562 gold badges35 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Daniel Magliola Over a year ago

Excellent, adding that extension fixed the problem. Thank you!!

Morteza Shahriari Nia Over a year ago

Quick question, is gist(ll_to_earth() a spatial index or a generic index of postgres?

bma Over a year ago

"ll_to_earth()" is part of the "earthdistance" module, see postgresql.org/docs/current/static/earthdistance.html. "gist" is an index type in core PostgreSQL, see postgresql.org/docs/current/static/textsearch-indexes.html

Collectives™ on Stack Overflow

How to create a PostgreSQL index that includes Latitude/Longitude (using GIST), but also regular fields

1 Answer 1

3 Comments

Your Answer

Collectives™ on Stack Overflow

How to create a PostgreSQL index that includes Latitude/Longitude (using GIST), but also regular fields

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest