I am working on the app which is connecting the Firebase database, My App is basically a marketplace app. I have logging all the user search history to the Firebase Analytics as a Custom events, Which I have linked to the Big query for the analytics or suggestion purpose.
Here is my Custom Events log example:
FirebaseAnalytics mFireBaseAnalytics = FirebaseAnalytics.getInstance(context);
Bundle params = new Bundle();
params.putString("SEARCHER_ID", "xxxx_id");
params.putString("KEYWORDS", "Shoe");
params.putDouble("LATITUDE", 12.00);
params.putDouble("LONGITUDE", 80.00);
mFireBaseAnalytics.logEvent("SEARCH_PRODUCT", params);
This data will be saved in the Biq Query in app_events table.
From this events I have to query the List of keywords search popular on my local area for example within 5 Km range. I am not really sure how to achieve this. Because see the result below how it's actually stored in the Big query.
"event_dim": [
{
"date": "20170718",
"name": "SEARCH_PRODUCT",
"params": [
{
"key": "LATITUDE",
"value": {
"string_value": null,
"int_value": null,
"float_value": null,
"double_value": "12.9632747"
}
},
{
"key": "SEARCHER_ID",
"value": {
"string_value": "-KkphUBI3_v3kdGq6qTa",
"int_value": null,
"float_value": null,
"double_value": null
}
},
{
"key": "LONGITUDE",
"value": {
"string_value": null,
"int_value": null,
"float_value": null,
"double_value": "80.2131347"
}
},
{
"key": "KEYWORDS",
"value": {
"string_value": "shoe cloths",
"int_value": null,
"float_value": null,
"double_value": null
}
},
],
}
]
For this format I made a query like below using standard SQL Query
SELECT
SQRT(POW(69.1*( (
SELECT
params.value.double_value
FROM
x.params
WHERE
params.key = 'LATITUDE') - 12.00), 2) + POW(69.1*(80.098 - (SELECT
params.value.double_value
FROM
x.params
WHERE
params.key = 'LONGITUDE') ) * COS((SELECT
params.value.double_value
FROM
x.params
WHERE
params.key = 'LATITUDE') / 57.3), 2)) AS distance,
(
SELECT
params.value.string_value
FROM
x.params
WHERE
params.key = 'KEYWORDS') AS KEYWORDS,
(
SELECT
params.value.string_value
FROM
x.params
WHERE
params.key = 'SEARCHER_ID') AS SEARCHER_ID,
(
SELECT
params.value.string_value
FROM
x.params
WHERE
params.key = 'LATITUDE') AS LATITUDE,
(
SELECT
params.value.string_value
FROM
x.params
WHERE
params.key = 'LONGITUDE') AS LONGITUDE
FROM
`app_name.app_events_*`,
UNNEST(event_dim) AS x
WHERE
x.name = 'SEARCH_PRODUCT'
AND (
SELECT
params.value.string_value As SearchId
FROM
x.params
WHERE
params.key = 'SEARCHER_ID' ) = '-xx_id'
With this query I am facing 2 issues. 1) I am getting distance as NULL 2) I am not sure how to filter this result within 5 KM Range.
Help me resolve this.
PS: I have another temporary Biqquery table which I am directly inserting data to that and from that I can get the expected result using below query.
SELECT
SQRT(POW(111.1*( MAX(Lattitude) - 12.9632691), 2) + POW(111.1*(80.2140816 - MAX(Longitude) ) * COS( MAX(Lattitude) / 57.3), 2)) AS distance,
MAX(SearchKeyword) as SearchKeyword,
SearcherArea,
COUNT( SearcherArea) AS totalCount
FROM
`HNF.LastFewHoursData`
WHERE
SearchKeyword LIKE '%%'
GROUP BY
SearcherArea
HAVING
distance <= 5
ORDER BY
totalCount DESC
LIMIT
5
So I have only problem with query the Events table which are updated by the Firebase Analytics.
