Ошибка PostgreSQL "столбец не существует" в CartoDB

Question

В настоящее время я работаю над запросом, который должен возвращать подмножество таблицы CartoDB (т.е. новую таблицу), отсортированные по близости к заданной точке. Я хочу отобразить метки на карте, соответствующие ближайшим, вторым ближайшим и т. Д., И подумал захватить это с помощью метода row_number () PostgreSQL в новом столбце:

SELECT
    *,
    ST_Distance(
        ST_GeomFromText('Point(-73.95623080000001 40.6738101)', 4326)::geography,
        the_geom::geography
    ) / 1609 AS dist,
    row_number() OVER (ORDER BY dist) as rownum
FROM locations
WHERE ST_Intersects(
   ST_GeomFromText(
      'Point(-73.95623080000001 40.6738101)', 4326
   ),
   the_geom
)
ORDER BY dist ASC

Однако, когда я пытаюсь это сделать, CartoDB/PostgreSQL возвращает следующую ошибку:

Error: column "dist" does not exist

Любые предложения о лучшем подходе или что-то я упускаю

Answer 1

Вы не можете использовать поле, рассчитанное на том же уровне.

SELECT (x1-x2)^2 + (y1-x2)^2  as dist, dist * 1.6 as miles
                                       ^^^^^
                                    undefined

Итак, вы создаете подзапрос.

SELECT dist * 1.6 as miles
FROM ( SELECT (x1-x2)^2 + (y1-x2)^2  as dist
       FROM table
     ) T

Answer 2

Вы не можете использовать псевдоним столбца в другом столбце. Здесь вы определяете dist для списка результатов, а также использовать его в row_number"s ORDER BY, Вы должны написать то же выражение, которое вы использовали для order вместо.

Answer 3

Хотя можно использовать производный внутренний запрос (который может быть проще для чтения и может быть оптимизирован в соответствии с другими правилами RA), также можно использовать порядковые номера для ссылки на столбцы, поскольку ограничение применяется только к вновь введенным именам.

Например, действует следующее:

SELECT
    ST_Distance(
        ST_GeomFromText('Point(-73.95623080000001 40.6738101)', 4326)::geography,
        the_geom::geography
    ) / 1609 AS dist
  -- column ordering changed to make ordinal obvious, as * can bite
  -- now column 'dist' is the first column and can be referenced by ordinal
  , row_number() OVER (ORDER BY 1) as rownum
  , *
FROM locations
WHERE ST_Intersects(
   ST_GeomFromText(
      'Point(-73.95623080000001 40.6738101)', 4326
   ),
   the_geom
)
-- this could also be written as ORDER BY 1 ASC, per ordinal
ORDER BY dist ASC

Answer 4

SELECT *,
    row_number() OVER (ORDER BY dist) as rownum
FROM(
SELECT
    *,
    ST_Distance(
        ST_GeomFromText('Point(-73.95623080000001 40.6738101)', 4326)::geography,
        the_geom::geography
    ) / 1609 AS dist
FROM locations
WHERE ST_Intersects(
   ST_GeomFromText(
      'Point(-73.95623080000001 40.6738101)', 4326
   ),
   the_geom
)
) i
ORDER BY dist ASC

Вы не можете получить доступ к псевдониму из того же выбора, поэтому поместите его во внутренний запрос