ST_Distance Webmercator Error

Question

When applying the following query to my javascript I get this error:

SELECT c.addressno,
 ST_Distance(c.the_geom, 
 (SELECT the_geom FROM test_alignment_poly ORDER BY the_geom <-> c.the_geom LIMIT 1))
 as MinDistance FROM address_pt_subset c where c.addressno='8635'

{"error":["column \"the_geom\" does not exist"]}

After some research I found cartodb projections tutorial after applying the following information I was able to get the following query to work on the map view of the dash board:

SELECT c.addressno,the_geom_webmercator,cartodb_id,ST_Distance(c.the_geom,(SELECT     
  the_geom FROM test_alignment_poly ORDER BY the_geom <-> c.the_geom LIMIT 1))as  
  MinDistance FROM address_pt_subset c where c.addressno='8635'

Though as stated above when applying the same query to the API I still get a geom error.

Is the nested geom value causing the error?

I have tried many variation of transforming both geom values without success. I feel like I am close but I have run out of ideas.

Answer 1

I'm not familiar with the particular error you are getting or where in the system you are getting it. But try adding it to your result set and see what happens

SELECT 
  addressno,
  the_geom_webmercator,
  the_geom,
  cartodb_id,
  ST_Distance(
    the_geom,
    (SELECT the_geom FROM test_alignment_poly ORDER BY the_geom <-> c.the_geom LIMIT 1)
  ) as MinDistance 
FROM address_pt_subset c 
WHERE c.addressno='8635'

If you have any the_geom = NULL you may run into issues as well. In which case, try this,

SELECT 
  addressno,
  the_geom_webmercator,
  the_geom,
  cartodb_id,
  ST_Distance(
    the_geom,
    (SELECT the_geom FROM test_alignment_poly ORDER BY the_geom <-> c.the_geom LIMIT 1)
  ) as MinDistance 
FROM address_pt_subset c 
WHERE c.addressno='8635'
AND the_geom IS NOT NULL

Answer 2

I would suggest adding the_geom to one of the elements in your SELECT statement. I have found that if I don't select the_geom as part of my query from the API, I will get that response. Not sure if that is a bug in the API, it is by design, or I'm completely wrong. More than likely, it's the latter!