0

I'm currently working on a query that should return a subset (i.e. "new") CartoDB 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 do that by using the Postgres 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

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

Error: column "dist" does not exist

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

  • try just OVER () – Mike T Mar 1 '16 at 21:57
  • Tried that - problem is the query is for a subset of the original table, and therefore the row numbers don't correspond to the newly created table (not sure if I'm phrasing that correctly...) – Dan Mar 2 '16 at 3:58
2
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
  )
) a
ORDER BY dist ASC

You need to use a nested query. See the docs on why aliases cannot be used in ORDER BY statements.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.