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?