2

I do have one more question pertaining to ST_Distance. I tried to add to my previous post but I made a new post. 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 and any additional help would be appreciated.

  • Are you sure you have a column called "the_geom" in test_alignment_poly? – BradHards Dec 31 '13 at 3:22
  • Yes I do have a column called the_geom in both tables – CrazySpatial Dec 31 '13 at 13:10
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
0

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!

  • It ended up being a syntax error though I am not sure why I was still receiving a the_geom error. – CrazySpatial Jan 2 '14 at 15:32

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.