3

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

  • The table is public in the sense that anyone can read from it, but I don't think you can write to it if that matters. You can send SQL queries to it via this API: ncc1989.cartodb.com/api/v2/sql?q={SQL statement} Also, dsny_refuse_recycl_net_wgs84.the_geom_webmercator is a MultiPoint, but the first SELECT WHERE ST_Contains statement always selects a single point from it. – blimp Apr 13 '15 at 14:17

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.