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"]}