2

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?

3

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

  • cartodb_id column also must be selected for the query to work. – picardo Mar 3 '14 at 16:49
  • 1
    yep, it is good practice to include cartodb_id because it is needed whenever there is interactivity – andrewxhill Mar 3 '14 at 16:55
  • @andrewxhill I'm having this same issue, but can't get a query with ST_Area to work. Is this what I should be doing? SELECT * , round((ST_Area(the_geom::geography)*(0.000247105))::numeric, 2) as Acres , the_geom_webmercator , cartodb_id FROM ood_newdevelopments_3785 – DPSSpatial Mar 6 '14 at 15:06
  • close, "*" selects all the columns, so the_geom_webmercator is already in there. if you list it a second time it will give you an err. same for cartodb_id. here, SELECT * , round((ST_Area(the_geom::geography)*(0.000247105))::numeric, 2) as Acres FROM ood_newdevelopments_3785 – andrewxhill Mar 6 '14 at 19:48
  • Ok it seems to be working, but the query is exactly the same as it was before I changed it, which is weird... did something change behind the scenes in the last week or so that prompted this issue and the one that started this thread? Thanks, @andrewxhill!!! – DPSSpatial Mar 6 '14 at 20:10

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.