I want to create a multicolumn spatial index on a geography point and integer in order to do efficient spatial queries. The integer is a user_id and queries are always limited to a single user. Is a multicolumn gin index the best approach?

  • 1
    I wouldn't think gin would work very well for something that is two-dimensional, even though it is a point, a gist (R-tree) index still uses a 2D bounding box for lookup. One option might be use a geohash of the point and concatenate the userID in some way, at which point you can use a good old b-tree, or create a multi-column b-tree index on userID and geohash. Databases that don't support R-trees, use geohashes for fast spatial lookups, so I have seen similar techniques in MongoDB, for example, but have never tried in Postgres. – John Powell Sep 10 '18 at 13:22
  • 1
    Make an expression index on 3D points, with user ID as Z? – CL. Sep 12 '18 at 7:03
  • These are good suggestions but I was hoping to do it in a way that was more transparent – Gregology Sep 12 '18 at 18:09

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.