4

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) )

Improve this question
1
  • 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.
    – Inactivated Account
    Commented Aug 11, 2014 at 18:00

1 Answer 1

Reset to default
2

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.

Improve this answer
3
  • 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
    Commented Oct 27, 2014 at 16:09
  • which method are going with?
    – andrewxhill
    Commented Oct 30, 2014 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
    Commented Nov 3, 2014 at 15:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.