Prevent Manticore from setting sql_attr_floatvalue to 0.000000 for null values

General Discussions - English
1

I have a MySql table with latitude and longitude columns like this

`latitude` double DEFAULT NULL,
 `longitude` double DEFAULT NULL.

When a row does not have data in these columns the cell just contains NULL.

select * from geo_locations where city = 'london colney';      
+-------+----------------+---------+---------------+---------------+--------------+------------+----------+-----------+
| id    | country        | state   | county        | city          | country_code | state_code | latitude | longitude |
+-------+----------------+---------+---------------+---------------+--------------+------------+----------+-----------+
| 27201 | United Kingdom | England | Hertfordshire | London Colney | GB           | ENG        |     NULL |      NULL |
+-------+----------------+---------+---------------+---------------+--------------+------------+----------+-----------+

However, these NULL latitude and longitude cells in Manticore have changed to 0.000000.

MySQL [(none)]> SELECT * FROM geolocations WHERE MATCH('@(country,state,county,city,state_code) "^London colney, england$"/2') limit 10 OPTION field_weights=(country=10, state=9, county=4, city=3, state_code=2);
+-------+----------------+---------+---------------+---------------+--------------+------------+-----------+-----------+
| id    | country        | state   | county        | city          | country_code | state_code | latitude  | longitude |
+-------+----------------+---------+---------------+---------------+--------------+------------+-----------+-----------+
| 27201 | United Kingdom | England | Hertfordshire | London Colney | GB           | ENG        |  0.000000 |  0.000000 |
| 27202 | United Kingdom | England |               | London        | GB           | ENG        | 51.507702 | -0.119000 |
+-------+----------------+---------+---------------+---------------+--------------+------------+-----------+-----------+

When the latitude and longitude cells are NULL I need them to remain NULL or empty cells in Manticore.

Is this achievable?

2

you could use null values only inside json fields but not for plain attributes

3

I don’t understand. Do you mean change my manticore.conf?
Ideally, I would just set the default value for sql_attr_float = latitude sql_attr_float = longitude to NULL.

4

We currently don’t support NULL for scalar attributes. NULL is supported only in JSON attributes.If you really need NULL, you must create a JSON attribute like {"latitude":XXX, "longitude":XXX}

5

It doesn’t have to be NULL. I just don’t want Manticore to change it to 0.000000 because this is a genuine coordinate. It could be an empty cell.

6

plain attributes are always have values. They could not be an empty cell , null, undefined and so on. If you really need such behavior use JSON attribute and put lat and long there. Or for plain attributes you have to use another attribute as flag that lat and long are valid or need to filter out due to invalid values. Or you could replace null values with some big values to skip these big values at your app

7

I use the arbitrary value 10 for this

sql_query = SELECT id,name,IFNULL(RADIANS(latitude),10) as latitude, ..

Ok, that is when storing it as radians. Can just use any value over 180 for lat/long.