PostgreSQL“列不存在". CartoDB中的错误 [英] PostgreSQL "column does not exist" error in CartoDB

问题描述

我目前正在处理一个查询，该查询应返回CartoDB表(即新表)的子集，该表按与给定点的接近程度排序.我想在地图上显示与最近，第二近等相对应的标签，并想通过在新列中使用PostgreSQL row_number()方法来捕获该标签:

I'm currently working on a query that should return a subset of a CartoDB table (i.e. a new table) sorted by proximity to a given point. I want to display labels on the map corresponding to the closest, second closest, etc. and thought to capture that by using the PostgreSQL row_number() method in a new column:

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返回以下错误:

However, when I try this, CartoDB/PostgreSQL returns the following error:

Error: column "dist" does not exist

是否有关于更好方法的建议或我所缺少的东西?

Any suggestions on a better approach or something I'm missing?

推荐答案

您不能使用在同一级别计算的字段.

You CANT use a field calculated on the same level.

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

因此您创建了一个子查询.

So you create a subquery.

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

这篇关于PostgreSQL“列不存在". CartoDB中的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！