Showing Linestring from SQL statement with CartoDB

I have cartodb sql statement to return what polygons are intersected by a linestring, but I'd like to have the linestring visualized over the polygons on the results. Also not add the linestring to the database.

SELECT country, permit_inf, permit_req, the_geom_webmercator FROM airspaceshape_1 WHERE ST_Intersects( the_geom, ST_SetSRID( ST_GeomFromGeoJSON('{ "type": "LineString","coordinates": [ [27.456667,5.065], [30.456667,-1.088] ] }'),4326) )

• this is a bit confusing, but the same SQL that generates the intersection can be used to create a column (in your SQL view) that reports the linestring, and that can be used in a label for the intersecting features. – DPSSpatial Aug 11 '14 at 18:00

1 Answer

There are a couple of options.

Option 1

You could do the query exactly like you have it, then add your linestring using GeoJSON on the client. You'd use CartoDB.js to draw you polygon layer and then use Leaflet to add the GeoJSON layer on top.

Option 2

Use a multilayer visualization. The bottom layer you could have as you polygons (styled accordingly. The second layer you could have as a linestring map (I'd setup a style with a fake line and then just delete the line before you publish so it is an empty table waiting for a line)

layers
.getSubLayer(0)
.setSQL(
"SELECT country, permit_inf, permit_req, the_geom_webmercator FROM airspaceshape_1 WHERE ST_Intersects( the_geom, ST_SetSRID( ST_GeomFromGeoJSON('{ "type": "LineString","coordinates": [ [27.456667,5.065], [30.456667,-1.088] ] }'),4326) )"
);
layers
.getSubLayer(1)
.setSQL(
"SELECT ST_Transform(ST_SetSRID( ST_GeomFromGeoJSON('{ "type": "LineString","coordinates": [ [27.456667,5.065], [30.456667,-1.088] ] }'),4326),3857) AS the_geom_webmercator"
);


Option 3

You could visualize them on the same layer. It is complicated and could get messy. But you'd setup your CartoCSS with a geometry type filter (e.g. [mapnik-geometry-type='Polygon']) and then do a UNION of your result plus a SQL statement just getting the line. Again, this would be messy. Possible, but I don't recommend it.

• Thanks much for the help @andrewxhill, I have one last issue with my coding. I've incorporated your help, but am having trouble getting both layers to show. – user28674 Oct 27 '14 at 16:09
• which method are going with? – andrewxhill Oct 30 '14 at 16:51
• I have the layers set up as like your option 2, but then calling the layers in, only the first layer (polygons) show, the route line does not show. cartodb.createLayer(map, layerUrl) .addTo(map) .on('featureOver', function(layer, infowindow) { layer.createSubLayer(subLayerOptions1); layer.getSubLayer(0).set(subLayerOptions); – user28674 Nov 3 '14 at 15:04