Hello!
Do you already have some solution for this problem? I’m having the same problem and I can’t solve it.
If any alternatives were created for grid maps, this will help a lot.
@Eric1 The workaround is to delete old fingerprints from your database, which will then make Metabase create updated fingerprints, when it sync the database next time.
Make sure you have backups of your application database.
UPDATE metabase_field SET fingerprint = NULL, fingerprint_version = 0
Hello!
I did this process and solve a part of the problem.
Now the binned option is shown in my metabase in the latitude and longitude fields, but when I use this option to use the grid map this error is shown:
The data type is float in the database.
I set the field in the metabase to latitude and longitude.
An example of latitude and longitude that I have in the bank is:
I have already verified that it has no null value in my data.
My Diagnostic Information:
{
"browser-info": {
"language": "pt-BR",
"platform": "Win32",
"userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/86.0.4240.193 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "Cp1252",
"java.runtime.name": "Java(TM) SE Runtime Environment",
"java.runtime.version": "1.8.0_261-b12",
"java.vendor": "Oracle Corporation",
"java.vendor.url": "http://java.oracle.com/",
"java.version": "1.8.0_261",
"java.vm.name": "Java HotSpot(TM) Client VM",
"java.vm.version": "25.261-b12",
"os.name": "Windows 10",
"os.version": "10.0",
"user.language": "pt",
"user.timezone": "America/Sao_Paulo"
},
"metabase-info": {
"databases": [
"postgres",
"h2"
],
"hosting-env": "unknown",
"application-database": "postgres",
"application-database-details": {
"database": {
"name": "PostgreSQL",
"version": "12.4"
},
"jdbc-driver": {
"name": "PostgreSQL JDBC Driver",
"version": "42.2.8"
}
},
"run-mode": "prod",
"version": {
"tag": "v0.37.2",
"date": "2020-11-16",
"branch": "release-x.37.x",
"hash": "25e5f70"
},
"settings": {
"report-timezone": "UTC"
}
}
}
My log:
:json_query {:type "query", :query {:source-table 65, :aggregation [["count"]], :breakout [["binning-strategy" ["field-id" 102] "default"] ["binning-strategy" ["field-id" 103] "default"]]}, :database 33, :parameters [], :middleware {:js-int-to-string? true, :add-default-userland-constraints? true}}, :native nil, :status :failed, :class clojure.lang.ExceptionInfo, :stacktrace ["--> query_processor.middleware.binning$fn__45697$calculate_num_bins__45702.invoke(binning.clj:68)" "query_processor.middleware.binning$fn__45722$resolve_default_strategy__45727$fn__45728.invoke(binning.clj:82)" "query_processor.middleware.binning$fn__45722$resolve_default_strategy__45727.invoke(binning.clj:74)" "query_processor.middleware.binning$resolve_options.invokeStatic(binning.clj:164)" "query_processor.middleware.binning$resolve_options.invoke(binning.clj:151)" "query_processor.middleware.binning$fn__45839$update_binned_field__45844$fn__45855.invoke(binning.clj:200)" "query_processor.middleware.binning$fn__45839$update_binned_field__45844.invoke(binning.clj:187)" "query_processor.middleware.binning$update_binning_strategy_in_inner_query$replace_45878__45879.invoke(binning.clj:210)" "mbql.util.match$replace_in_collection.invokeStatic(match.clj:135)" "mbql.util.match$replace_in_collection.invoke(match.clj:126)" "query_processor.middleware.binning$update_binning_strategy_in_inner_query$replace_45878__45879.invoke(binning.clj:210)" "mbql.util.match$replace_in_collection$iter__26258__26262$fn__26263.invoke(match.clj:132)" "mbql.util.match$replace_in_collection.invokeStatic(match.clj:131)" "mbql.util.match$replace_in_collection.invoke(match.clj:126)" "query_processor.middleware.binning$update_binning_strategy_in_inner_query$replace_45878__45879.invoke(binning.clj:210)" "query_processor.middleware.binning$update_binning_strategy_in_inner_query.invokeStatic(binning.clj:210)" "query_processor.middleware.binning$update_binning_strategy_in_inner_query.invoke(binning.clj:206)" "query_processor.middleware.binning$update_binning_strategy_STAR_.invokeStatic(binning.clj:221)" "query_processor.middleware.binning$update_binning_strategy_STAR_.invoke(binning.clj:218)" "query_processor.middleware.binning$update_binning_strategy$fn__45892.invoke(binning.clj:229)" "query_processor.middleware.resolve_fields$resolve_fields$fn__46409.invoke(resolve_fields.clj:24)" "query_processor.middleware.add_dimension_projections$add_remapping$fn__40564.invoke(add_dimension_projections.clj:318)" "query_processor.middleware.add_implicit_clauses$add_implicit_clauses$fn__40771.invoke(add_implicit_clauses.clj:141)" "query_processor.middleware.add_source_metadata$add_source_metadata_for_source_queries$fn__41169.invoke(add_source_metadata.clj:105)" "query_processor.middleware.reconcile_breakout_and_order_by_bucketing$reconcile_breakout_and_order_by_bucketing$fn__48337.invoke(reconcile_breakout_and_order_by_bucketing.clj:98)" "query_processor.middleware.auto_bucket_datetimes$auto_bucket_datetimes$fn__45480.invoke(auto_bucket_datetimes.clj:125)" "query_processor.middleware.resolve_source_table$resolve_source_tables$fn__46456.invoke(resolve_source_table.clj:46)" "query_processor.middleware.parameters$substitute_parameters$fn__48122.invoke(parameters.clj:114)" "query_processor.middleware.resolve_referenced$resolve_referenced_card_resources$fn__46508.invoke(resolve_referenced.clj:80)" "query_processor.middleware.expand_macros$expand_macros$fn__47127.invoke(expand_macros.clj:158)" "query_processor.middleware.add_timezone_info$add_timezone_info$fn__41200.invoke(add_timezone_info.clj:15)" "query_processor.middleware.splice_params_in_response$splice_params_in_response$fn__49058.invoke(splice_params_in_response.clj:32)" "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__48348$fn__48352.invoke(resolve_database_and_driver.clj:33)" "driver$do_with_driver.invokeStatic(driver.clj:61)" "driver$do_with_driver.invoke(driver.clj:57)" "query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__48348.invoke(resolve_database_and_driver.clj:27)" "query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__47345.invoke(fetch_source_query.clj:267)" "query_processor.middleware.store$initialize_store$fn__49067$fn__49068.invoke(store.clj:11)" "query_processor.store$do_with_store.invokeStatic(store.clj:46)" "query_processor.store$do_with_store.invoke(store.clj:40)" "query_processor.middleware.store$initialize_store$fn__49067.invoke(store.clj:10)" "query_processor.middleware.cache$maybe_return_cached_results$fn__46385.invoke(cache.clj:214)" "query_processor.middleware.validate$validate_query$fn__49076.invoke(validate.clj:10)" "query_processor.middleware.normalize_query$normalize$fn__47470.invoke(normalize_query.clj:22)" "query_processor.middleware.add_rows_truncated$add_rows_truncated$fn__41038.invoke(add_rows_truncated.clj:36)" "query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__49043.invoke(results_metadata.clj:147)" "query_processor.middleware.constraints$add_default_userland_constraints$fn__46748.invoke(constraints.clj:42)" "query_processor.middleware.process_userland_query$process_userland_query$fn__48211.invoke(process_userland_query.clj:136)" "query_processor.middleware.catch_exceptions$catch_exceptions$fn__46691.invoke(catch_exceptions.clj:174)" "query_processor.reducible$async_qp$qp_STAR___39827$thunk__39828.invoke(reducible.clj:103)" "query_processor.reducible$async_qp$qp_STAR___39827.invoke(reducible.clj:109)" "query_processor.reducible$sync_qp$qp_STAR___39836$fn__39839.invoke(reducible.clj:135)" "query_processor.reducible$sync_qp$qp_STAR___39836.invoke(reducible.clj:134)" "query_processor$process_userland_query.invokeStatic(query_processor.clj:237)" "query_processor$process_userland_query.doInvoke(query_processor.clj:233)" "query_processor$fn__49234$process_query_and_save_execution_BANG___49243$fn__49246.invoke(query_processor.clj:249)" "query_processor$fn__49234$process_query_and_save_execution_BANG___49243.invoke(query_processor.clj:241)" "query_processor$fn__49278$process_query_and_save_with_max_results_constraints_BANG___49287$fn__49290.invoke(query_processor.clj:261)" "query_processor$fn__49278$process_query_and_save_with_max_results_constraints_BANG___49287.invoke(query_processor.clj:254)" "api.dataset$fn__52603$fn__52606.invoke(dataset.clj:59)" "query_processor.streaming$streaming_response_STAR_$fn__37131$fn__37132.invoke(streaming.clj:73)" "query_processor.streaming$streaming_response_STAR_$fn__37131.invoke(streaming.clj:72)" "async.streaming_response$do_f_STAR_.invokeStatic(streaming_response.clj:66)" "async.streaming_response$do_f_STAR_.invoke(streaming_response.clj:64)" "async.streaming_response$do_f_async$fn__24639.invoke(streaming_response.clj:85)"], :context :ad-hoc, :error (not ("Inteiro maior que zero" 0)), :row_count 0, :running_time 0, :preprocessed nil, :ex-data {:type :schema.core/error, :value 0, :error (not ("Inteiro maior que zero" 0))}, :data {:rows [], :cols []}} [5bedc1b4-ec2a-4876-a8b5-b8117eb3411b] 2020-11-24T16:04:50-03:00 DEBUG metabase.middleware.log POST /api/dataset 202 [ASYNC: completed] 315,8 ms (6 chamadas ao banco de dados) Conexões de banco de dados do aplicativo: 0 / 4 Threads do Jetty: 4 / 50 (0 ocioso, 0 na fila) (56 total de threads ativas) Consultas ativas: 0 (0 na fila) [5bedc1b4-ec2a-4876-a8b5-b8117eb3411b] 2020-11-24T16:04:57-03:00 DEBUG metabase.middleware.log GET /api/setting 200 3,9 ms (0 chamadas ao banco de dados) Conexões de banco de dados do aplicativo: 0 / 4 Threads do Jetty: 6 / 50 (0 ocioso, 0 na fila) (56 total de threads ativas) Consultas ativas: 0 (0 na fila) [5bedc1b4-ec2a-4876-a8b5-b8117eb3411b] 2020-11-24T16:04:57-03:00 DEBUG metabase.middleware.log GET /api/session/properties 200 42,9 ms (2 chamadas ao banco de dados) Conexões de banco de dados do aplicativo: 0 / 4 Threads do Jetty: 7 / 50 (0 ocioso, 0 na fila) (56 total de threads ativas) Consultas ativas: 0 (0 na fila) [5bedc1b4-ec2a-4876-a8b5-b8117eb3411b] 2020-11-24T16:04:57-03:00 DEBUG metabase.middleware.log GET /api/setup/admin_checklist 200 64,7 ms (11 chamadas ao banco de dados) Conexões de banco de dados do aplicativo: 0 / 4 Threads do Jetty: 6 / 50 (0 ocioso, 0 na fila) (56 total de threads ativas) Consultas ativas: 0 (0 na fila) [5bedc1b4-ec2a-4876-a8b5-b8117eb3411b] 2020-11-24T16:04:59-03:00 DEBUG metabase.middleware.log GET /api/util/bug_report_details 200 12,2 ms (1 chamadas ao banco de dados) Conexões de banco de dados do aplicativo: 0 / 4 Threads do Jetty: 5 / 50 (0 ocioso, 0 na fila) (56 total de threads ativas) Consultas ativas: 0 (0 na fila)
@Eric1 I’m not sure what’s going wrong. I cannot reproduce, when I create a test table with latitude/longitude and only add values below zero (south west).
Try posting the output of /api/field/:id where :id is the ID of your latitude/longitude column. You can find the ID by looking in the URL, when going to Admin > Data Model > (database) > (table) > (column) 
This is the line of: /api/field/:id
Here the complete CSV: CSV in my github
I sent it by CSV, because I couldn´t think a better way to send the data.
If you want a screenshot of a specific piece of data, I can send
On my github I also have the comparison with the latitude and longitude of the sample-test.
@Eric1 I cannot reproduce. Which version of Postgres are you using? And can you provide a full SQL example, so I can reproduce?
My postgres version: 13.1
SQL to reproduce:
CREATE TABLE public.test
(
latitude double precision,
longitude double precision
);
ALTER TABLE public.test
OWNER to database_name;
I created a table “test”. Don’t forget to change the database name.
And I inserted only a value for example:
INSERT INTO "test" VALUES (-27.137453079223633, -52.5982666015625)
I used this example with only one value in the metabase and I still have with the same problem.
@Eric1 Okay, so I have been able to reproduce, but only with your example. If I add one more position (like -15,-15), and reset fingerprints again, then it works fine. I’m not sure I completely understand what is going on with this error, since it seems to be specific to Postgres.
I have created an issue on it:
https://github.com/metabase/metabase/issues/13914 - upvote by clicking 
on the first post
I’ll just piggybck here that I am also experiencing this issue and can confirm there are no nulls in my lat and long field values. My field type in metabase is set to location. Im not super technical but if there is anything I can post to help find a solution please let me know. Eager to find a solution as this grid map would be of great value to our company
I have a solution that temporarily solves the problem.
How to reproduce:
UPDATE metabase_field SET fingerprint = '{"global":{"distinct-count":2491,"nil%":0.0},"type":{"type/Number":{"min":25.775827,"q1":35.302705923023126,"q3":43.773802584662,"max":70.6355001,"sd":6.390832341883712,"avg":39.87934670484002}}}', fingerprint_version = 5 WHERE name = 'my_latitude_field'
and
UPDATE metabase_field SET fingerprint = '{"global":{"distinct-count":2491,"nil%":0.0},"type":{"type/Number":{"min":-166.5425726,"q1":-101.58350792373135,"q3":-84.65289348288829,"max":-67.96735199999999,"sd":15.399698968175663,"avg":-95.18741780363999}}}', fingerprint_version = 5 WHERE name = 'my_longitude_field'
I copied the fingerprint from sample-dataset (latitude and longitude) and my grid map worked.
But after a while, the metabase goes back to the last fingerprint (when I set the fingerprint = NULL) and the problem returns.
When I set the fingerprint = NULL, the metabase changes to:
{"global":{"distinct-count":1,"nil%":0.0},"type":{"type/Number":{"min":-26.183141708374023,"q1":-26.183141708374023,"q3":-26.183141708374023,"max":-26.183141708374023,"sd":0.0,"avg":-26.183141708374023}}}
I copied the fingerprint from the sample-dataset again, and my grid map worked, but I think the metabase may to return for the last fingerprint again, this have some solution?
Another thing, now I am using the grid map, but I can't to change the data that are showneed on the grid, how on example:
Is it possible to put other names and data in the box? If it is not possible, can I suggest this in any metabase forum?
Thanks for all the help!
`
@Eric1 The fingerprinting will be done automatically, when it’s null. This entire post, and all the issues referenced (before the issue I created for you) are only about how to refresh the fingerprint.
There’s an issue open to be able to have more control over the tooltip:
https://github.com/metabase/metabase/issues/12308 - upvote by clicking on the first post
Yes, when SET fingerprint = NULL my grid map doesn´t worked, but when I did this:
My grid map worked, Should I post on Binning not working in table with a single row · Issue #13914 · metabase/metabase · GitHub?
I think this information is important to make the solution permanent.
@Eric1 But you can write anything into the fingerprint, which will change the way Metabase does binning.
Setting fingerprint to null will mean that it will be fingerprinted again on next sync.
It’s not a solution. It’s a hack to get around an incorrect handling of how the calculation of fingerprints works.
Ok. So what I need to do is wait for a solution to my problem.
Is there any more information that I can send that helps?
So, I've encountered this issue off and on since I reported it in April of 2020. The good news is it's gotten a lot better and I've come up with implementation methodologies that pretty much fix it. The first step is to understand your data. Binning is going to work on date, integer, decimal, lat/long fields (float or decimal), fields. If your data types are something other than that, like a VARCHAR, you won't have an option to bin that field (obviously). The second step is to understand the values in your table for that column. If you have NULL or widely disparate values, it messes with the bin fingerprinting behavior. I recommend writing a view that returns valid data. Then, use that view to base your visualizations off of. The third step is to check to make sure the data model in metabase accurately recognizes your column's data type. Finally, make sure to set the option to "Periodically refingerprint tables" to true. 95% of the time, these steps will resolve the issue. I've used this approach on Athena, Postgres, MySQL, SQL Server, and Redshift and had good outcomes on each.
3 Likes