CODEWITHSUNDEEP
sqlpostgresql
Mateusz Urbański
Mateusz Urbański

Reputation: 7872

Refactor sql query that uses PostGIS functions

I have a query that looks like this:

 SELECT *,
        ST_Distance(
          ST_GeographyFromText('SRID=4326;POINT(' || users.longitude || ' ' || users.latitude || ')'),
          ST_GeographyFromText('SRID=4326;POINT(-84.334078 45.273343)')) as distance
        FROM users
        WHERE ST_DWithin(
          ST_GeographyFromText('SRID=4326;POINT(' || users.longitude || ' ' || users.latitude || ')'),
          ST_GeographyFromText('SRID=4326;POINT(-84.334078 45.273343)'),
          2000
        )
        ORDER BY distance ASC;"

I've see some repetitions here. I'm wondering is there any way to make this query more readable?

Upvotes: 0

Views: 22

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125444

Lateral join:

select *, ST_Distance(a, b) distance
from
    users,
    ST_GeographyFromText('SRID=4326;POINT(' || users.longitude || ' ' || users.latitude || ')') a,
    ST_GeographyFromText('SRID=4326;POINT(-84.334078 45.273343)') b
where ST_DWithin(a, b, 2000)
order by distance asc;

Upvotes: 1

Related Questions