Feature request: Applying a shape filter (circle, polygon) to a dataview

[jdgodchaux]

Context

A customer would like to apply a shape filter (circle, polygon) to a dataview. The customer believes that we can extend bounding box filtering to irregular polygons and circles. See their comments here:

the problem I’m facing is to apply a shape filter (circle, polygon) to a dataview. I’m aware you can change the sql on the source, but this affects how the layer is displayed. We need to have the layer unmodified on screen, then the user draws a shape, and some data from a dataview is returned based on that shape, without affecting the source. I opened this question on SO:

https://gis.stackexchange.com/questions/334156/apply-a-polygon-filter-to-cartojs-4-dataview

There is a way to achieve this through the dataviews?

Checking the carto 4.1.12-1, the line 19290 is where the code for boundingbox is declared. Probably will not be a huge enterprise to extend this to accept an array on lat/lngs. It is possible for your team to add this poly support? Or at least someone can guide me a little bit, so I can write the code to support the polygons? This is something very important for us. Later in the future a support for a circle would be great too.

What level of effort is involved in adding this as a feature, and can we add this feature to CARTO.js?

Support Issue and Zendesk tickets for more context

https://github.com/CartoDB/support/issues/2202

[kukukaka]

@VictorVelarde when you have some time would you mind taking a look at this request and make a rough estimate of the effort this would mean for us?

@jdgodchaux I would talk with you in private to gather more information.

[jesusbotella]

👋 !

I've been taking a look at this and, as they said, there's no way to filter a dataview by any shape other than a bounding box.

CARTO.js is not the one who filters the dataview or composes the SQL sentence that retrieves the data. Windshaft is the one who does that. We could modify the URL parameter and pass an array of values, but Windshaft wouldn't understand that. Here's the code which parses the bbox parameter.

So, from CARTO.js' side, we could create a filter for the source to filter a circle as they propose, but they would need to apply it to a different source than the one used in the layer, so that it doesn't affect the visualization, as they noted in their original message. They can do that already with their own SQL sentence.

The optimal solution would be to support polygon filtering in Windshaft for dataviews, which I believe that is not ready right now.

[jdgodchaux]

Hi @jesusbotella , @VictorVelarde , and @kukukaka , here's the response from our customer:

So windshaft speaks with sql…

var filterQueryTpl = dot.template([
    'SELECT * FROM ({{=it._sql}}) _cdb_bbox_filter',
    'WHERE {{=it._filters}}'
].join('\n'));

 var bboxFilterTpl = dot.template(
    'ST_Intersects({{=it._column}}, ST_Transform(ST_MakeEnvelope({{=it._bbox}}, 4326), {{=it._srid}}))'
);

I’m familiar with this since we basically do that when we call the sql api.

                        string function_name = req.QueryType.ToLower() == "intersect" ? "ST_Intersects" : "ST_Contains";
                        string shapeStr = req.ShapeData;
                        string finalSql = "";

                        JObject shapeJson = JObject.Parse(shapeStr);

                        foreach (JToken shape in shapeJson["coordinates"].Children())
                        {
                            if (finalSql.Length > 0) { finalSql += " UNION "; }
                            string baseSql = MapConfig.layergroup.layers.First().options.sql;
                            string shapeData = shape.ToString().Replace("\r\n", "").Replace(" ", "");
                            string medSql = where_clause + function_name + "(ST_SetSRID(ST_GeomFromGeoJSON('{ \"type\": \"Polygon\" , \"coordinates\": " + shapeData + " }'), 4326), the_geom) ";
                            finalSql += rgx.Replace(baseSql, medSql);
                        }

It doesn’t look like a huge enterprise to make a new filter.

Would it be possible to evaluate what our customer is saying?

[VictorVelarde]

This was already implemented