Optimize the following Postgress Query

Question

Can you please help me optimize the following query? I don't like the fact that I'm calling ST_Covers multiple times. I've searched all over StackOverflow but I'm completely stumped. Any help is appreciated. Thank you.

if I try to select more than one column, I get this: subquery must return only one column. That's why I separated them into 3 different select statements

SELECT
   *,
   (SELECT id as city_id FROM cities WHERE ST_Covers(boundary, location))
   (SELECT name as city_name FROM cities WHERE ST_Covers(boundary, location)),
   (SELECT rent_fee FROM cities WHERE ST_Covers(boundary, location))
FROM cars

my tables look like this:

cars

• id
• location
• vin
• manufacturer

cities

• id
• name
• boundary
• rent_fee

desired output:

• id
• location
• vin
• manufacturer
• city_id
• city_name
• rent_fee

P.S. There's no relation between the cars and cities tables, and I'd like to keep it that way.

Answer 1

SELECT
   cars.*, city_id, city_name, rent_fee 
FROM cars left join cities on ST_Covers(boundary, location)

If there is more than one cities that ST_Covers a car, this will return more than one row for the car. (Your current query will throw an error in such case.) If you aren't OK with that, then you need to decide and describe what should happen in that case.