ST_MakeLine within CartoDB.js

Question

I'm building an interactive map using CartoDB.js in an HTML page. I have the user input an address that gets geocoded. I then use the user's lat/lon coordinates to perform SQL queries on CartoDB. The first query looks for a polygon that intersects that lat/lon, then selects three other points that share and ID number with that polygon. It looks like this:

WITH tstations AS 
    (SELECT * FROM dsny_refuse_recycl_net_wgs84 
    WHERE districtco = 
        (SELECT borocd FROM nycd 
        WHERE ST_Contains(the_geom,  ST_SetSRID(ST_MakePoint("+lon+","+lat+"),4326))
        ) 
    ) 
SELECT * FROM tstations

This returns me the three points that I want, as "tstations".

What I wanted to do next is create three lines, connecting the user's lat/lon point to each of the three tstations points, and to display these lines on the map.

I'm using the following query, which works on the CartoDB website's SQL window:

WITH tstations AS 
        (SELECT * FROM dsny_refuse_recycl_net_wgs84 
        WHERE districtco = 
            (SELECT borocd FROM nycd 
            WHERE ST_Contains(the_geom, ST_SetSRID(ST_MakePoint("+lon+","+lat+"),4326))
            ) 
        )     
SELECT null as cartodb_id,
ST_MakeLine
   (ST_Transform
       (ST_SetSRID(ST_MakePoint("+lon+","+lat+"),4326),3857),the_geom_webmercator)
       as the_geom_webmercator
FROM tstations

(I'm basing myself off this tutorial: http://docs.cartodb.com/tutorials/query_by_distance.html)

When I use the first query above within my HTML with CartoDB.js, it does work, and returns me the three points I want. But when I use the ST_MakeLine query, it doesn't work - CartoDB's API returns a 400-Bad Request error. This is what it looks like inside my HTML script:

function updateQuery() {
    sublayers[1].set({
      sql: "WITH tstations AS (SELECT * FROM dsny_refuse_recycl_net_wgs84 WHERE districtco = (SELECT borocd FROM nycd WHERE ST_Contains(the_geom, ST_SetSRID(ST_MakePoint("+lon+","+lat+"),4326)) ) ) SELECT null as cartodb_id, ST_MakeLine (ST_Transform (ST_SetSRID(ST_MakePoint("+lon+","+lat+"),4326),3857),the_geom_webmercator) as the_geom_webmercator FROM tstations",
      cartocss: "#dsny_refuse_recycl_net_wgs84_copy_copy{ line-color: #FF6600; line-width: 2; line-opacity: 0.7; }"
    });
}

Does anyone know what I'm doing wrong here? I assume it has something to do with using sublayers.set to generate the new lines, but I'm not sure.

Here's the API error response:

{"errors":["Postgis Plugin: ERROR:  syntax error at or near \"AS\"\nLINE 1: ...(ST_MakePoint(-74.0059413,40.7127837),4326),3857) AS the_geo...\n                                                             ^\n\nin executeQuery Full sql was: 'SELECT * FROM (SELECT d.cartodb_id, ST_MakeLine( d.the_geom_webmercator, ST_Transform(ST_SetSRID(ST_MakePoint(-74.0059413,40.7127837),4326),3857) AS the_geom_webmercator FROM dsny_refuse_recycl_net_wgs84 AS d JOIN borocd AS b ON d.districtco = b.borocd WHERE ST_Contains(b.the_geom, ST_SetSRID(ST_MakePoint(-74.0059413,40.7127837),4326))) as cdbq LIMIT 0'\n  encountered during parsing of layer 'layer1' in Layer"]}

Answer 1

Frankly no, I've read it through five times and all I see is too many subqueries, but nothing that should stop it from executing. The API should have some kind of error info embedded in the response, no? Is your table set public? The SQL API won't let you alter the query on a non-public table.

SELECT d.cartodb_id, 
        ST_MakeLine(
         d.the_geom_webmercator, 
         ST_Transform(ST_SetSRID(ST_MakePoint("+lon+","+lat+"),4326),3857)
         AS the_geom_webmercator
FROM dsny_refuse_recycl_net_wgs84 AS d
JOIN borocd AS b ON d.districtco = b.borocd
WHERE ST_Contains(b.the_geom, ST_SetSRID(ST_MakePoint("+lon+","+lat+"),4326))

I guess one unknown for me is whether dsny_refuse_recycl_net_wgs84.the_geom_webmercator is in fact a simple Point. If it's a MultiPoint, you'll have to strip out the multi-ness before handing to to ST_MakePoint.