carto - Map doesn't appear with custom CartoDB query

I'm trying to use CartoSQL from CartoDB.js to create a layer. Here is the SQL query I am trying to make:

SELECT 
  rpa_nj_hsip_hospitals_compressed.flood, 
  rpa_nj_hsip_hospitals_compressed.the_geom 
FROM 
  rpa_nj_hsip_hospitals_compressed 

All the columns are in the table –– I checked. This is the query string and you should see the same error I am seeing.

What am I doing wrong?

Answer

Ah, there is an important piecing missing that is needed by our map tiler, the the_geom_webmercator column. Change your query to this and it will work,

SELECT 
  rpa_nj_hsip_hospitals_compressed.flood, 
  rpa_nj_hsip_hospitals_compressed.the_geom,
  rpa_nj_hsip_hospitals_compressed.the_geom_webmercator 
FROM 
  rpa_nj_hsip_hospitals_compressed 

What is the_geom_webmercator?

In CartoDB there is a hidden column on all of your tables called the_geom_webmercator. It automatically turns anything in the_geom into a webmercator projected geometry. If you change a value in the_geom, it will change in the_geom_webmercator without you needing to do anything. It helps make you maps fast, but it also means you need to include it in custom queries.

What about queries that modify the_geom on the fly?

Say you are doing something like an ST_Buffer on the_geom. You just need to transform the result to webmercator and alias the result to the_geom_webmercator. So take the query,

SELECT ST_Buffer(the_geom, 0.01) the_geom FROM table_name

You would do,

SELECT ST_Transform(ST_Buffer(the_geom, 0.01),3857) the_geom_webmercator FROM table_name

And now it would show up on the map. ST_Transform(geometry, SRID) reprojects geometries. 3857 is the SRID of the web mercator projection