Elasticsearch-jdbc scientific notation issue

(Mark Varley) #1

Hi - I am using ES2.3.3 and the corresponding latest version of elasticsearch-jdbc. I am trying to import rows from a Postgres 9.5 DB where there are numbers stored as float8. Some of the numbers that are close to zero with several decimal places are stored coming across into Elasticsearch in scientific notation but with the decimal places truncated.

Example: 0.0000283 (2.83e-05) in Postgres is importing as 2.83 in Elasticsearch

As these values are being used for geographic coordinates the impact is significant and in some cases the locations are appearing in the wrong country. I have searched the forums and cannot find any known issue in the Postgres JDBC driver.

Any help on this would be much appreciated. Happy to submit a PR if you could point me in the direction of where to look?

(Mark Varley) #2

The solution I have come up with is to add explicit roundings in the query and a scale to the mapping however I still think the default behaviour of treating Postgres floats in scientific notation as numbers is dangerous. For other Postgres/PostGIS users I strongly recommend following the example below, ST_X and ST_Y functions return floats and without specifying the scale and rounding you leave it up to the driver to determine how to handle these.

The mapping I am using is as follows:

    "type": "jdbc",
    "jdbc": {
        "sql": "select round(ST_X(the_geom)::numeric,8) as \"location.lon\", round(ST_Y(the_geom)::numeric,8) as \"location.lat\" from my_table",
        "index": "my_index",
        "type": "my_type",
        "scale": 8

(system) #3