cube
cube copied to clipboard
cube-js
Geo filters support
Problem
We want to write a custom SQL filter with 2 columns(lat ,long).
Example SQL
SELECT * FROM WHERE (point( -122.4194, 37.7749) <@> point(longitude, latitude)) < 15;
Hey @sourav-jha ! It can be achieved by introducing dimension as following:
cube(`Cube`, {
// ...
dimensions: {
distanceFromPoint: {
sql: `(point( -122.4194, 37.7749) <@> point(${CUBE}.longitude, ${CUBE}.latitude))`,
type: `number`
}
}
})
@paveltiunov these values are (-122.4194, 37.7749) dynamic, it will change per query.
SELECT * FROM WHERE (point( long_passed_by_user, lat_passed_by_user) <@> point(longitude, latitude)) < 15;
@sourav-jha There's no straightforward way to use geo-filters as of now. Let's use this issue to track the status of this problem. The workaround is to have longitude and latitude dimensions and use gt lt filters and FILTER_PARAMS to extract information about the center as following:
cube(`Cube`, {
sql: `select * from geo_points WHERE ${FILTER_PARAMS.Cube.longitude.filter((fromLongitude, toLongitude) =>
FILTER_PARAMS.Cube.latitude.filter((fromLatitude, toLatitude) => `(point((${fromLongitude} + ${toLongitude}) / 2, (${fromLatitude} + ${toLatitude}) / 2) <@> point(longitude, latitude)) < 15`)
)}`,
dimensions: {
longitude: {
sql: `longitude`,
type: `number`
},
latitude: {
sql: `latitude`,
type: `number`
}
}
});
@paveltiunov is this below SQL possible:
select c1, (c2 * user_input) AS c3 from t1 where c1 and c2 are columns of table t1 and use_input is some dynamic value provided by the user
