CODEWITHSUNDEEP
pythonsqldjangopostgresql
MayTheSchwartzBeWithYou
MayTheSchwartzBeWithYou

Reputation: 1177

SQL query radius based on latitude and longitude of a linked model

I am having issues executing one query as I can't link properly two models together.

ModelA contains a OneToOne relationship with a Location(latitude, longitude) table. The database is Postgres.

The query is failing on:

                (
                  SELECT * FROM "myapp_location" 
                  WHERE id=location_id
                ) as location,

The message it's giving me is: subquery must return only one column

Here is the Query:

'''
            SELECT * FROM 
          (
                SELECT id, location_id,
                (
                  SELECT * FROM "myapp_location" 
                  WHERE id=location_id
                ) as location,
                (
                  3956 * 2 *
                  ASIN(
                    SQRT(
                      POWER(
                        SIN(({latitude}- location.latitude) *  pi()/180 / 2), 2) +
                        COS({latitude} * pi() / 180) *
                        COS(location.latitude * pi() / 180) *
                        POWER(SIN(({longitude} - location.longitude) * pi() / 180 / 2), 2)
                    )
                  )
                )
                AS distance 
                FROM {model} 
                ORDER BY distance
              ) items WHERE distance <= {miles} '''.format(latitude=latitude, longitude=longitude, miles=miles,
                                                           model=model)

Does anyone have any good suggestions? Much appreciated.

Upvotes: 0

Views: 90

Answers (1)

iklinac
iklinac

Reputation: 15738

Subquery can return only one selected value column

This subquery of yours has no purpose anyway 

           SELECT id, location_id,
            (
              SELECT * FROM "myapp_location" 
              WHERE id=location_id
            ) as location,

convert following to join instead

Something like following would be probably way to go

SELECT *,
   ( 3956 * 2 * ASIN( SQRT( POWER( SIN(({latitude}- location.latitude) * 
pi()/180 / 2), 2) + COS({latitude} * pi() / 180) * COS(location.latitude * 
pi() / 180) * POWER(SIN(({longitude} - location.longitude) * pi() / 180 / 
2), 2) ) ) ) AS distance
FROM {model}
JOIN myapp_location ON myapp_location.id = location_id
WHERE distance <= {miles}
ORDER BY distance

Upvotes: 2

Related Questions