1

I'm not quite sure what the best way to phrase this is...

So in my DB there is pillars text array which is basically an enum where providers ordered what values meant the most to their business, from most important to providing that value for their clients, to least important.

I'm using PostGIS to query providers in a specific area, and want to return providers ordered first by the pillar that a client selected they were looking for, then by closest location.

so if the pillars all have values ['a', 'b', 'c', 'd'], in any order depending on what providers selected, and the client selected pillar c

the results of the query would preferably return any/all providers that have pillar c at array index 0 first, ordered by distance to geopoint, then by providers that have pillar c at array index 1 second ordered by distance to client geopoint, then idx 2, then idx 3

I'm really only looking for the top 3 results in all cases, and providers with pillar c at idx 1 would only be needed if there were less than 3 results for index 0

Is this possible to pull off in a single query? or should I just run it with a where clause and check the results length until I have 3 results?

The pillars column is indexed with a gin index btw

1 Answer 1

2

That sounds simple:

ORDER BY array_position(pillars, 'c'), st_distance(....)
1
  • Wow idk how I completely forgot that array_position exists Apr 10, 2021 at 11:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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