0

I am trying to use Elasticsearch indexed on a MySQL table to find all addresses that are within x km from a particular data point. I have indexed the table with the following:

{
  "type": "jdbc",
  "jdbc": {
       "strategy": "simple",
       "url": "jdbc:mysql://hostname/databasename",
       "user": "username",
       "password": "password",
       "sql": "SELECT name,address1,city,state,zip,lat as `location.lat`,lng as `location.lon` FROM addresses",
       "poll": "24h",
       "max_retries": 3,
       "max_retries_wait": "10s",
       "index" : "teststores",
       "type" : "providers"
   },
   "index": {
       "index": "addressindex",
       "autocommit": "true",
       "type": "mysql",
       "bulk_size": 100,
       "type_mapping": {
           "location_mapping" : { 
                "properties" : {
                    "pin" : {
                        "type" : "geo_point"
                    }
                }       
            }
        }
   }
}

An example of the indexed data is the following:

"_index": "teststores",
"_type": "providers",
"_id": "Rue2Yxo7SSa_mi5-AzRycA",
"_score": 1,
"_source": {
    "zip": "10003",
    "name": "I Salon",
    "state": "NY",
    "address1": "150 East 14th Street",
    "location":{
                "lat": 40.7337,
                "lon": -73.9881
               },
    "city": "New York"
}

I want to adjust the following query to use lat and lng for calculating the distance.

{ 
  "query": {
       "filtered": {
       "query": {
          "match_all": {
           }
        },
        "filter": {
           "geo_distance" : {
               "distance" : "2km",
               "pin.location" : {
                    "lat" : 40.686511,
                    "lon" : -73.986574
                }
            }
        }
     }
  },
}

How can I adjust this to make the distance work and get all addresses within x kilometers?

Improve this question
4
  • Have you defined a mapping for your geo_point? Commented May 9, 2014 at 20:40
  • No, I'm about a day old to elasticsearch so I'm not quite sure how to do that. Can you provide an example? Commented May 10, 2014 at 6:40
  • I've read through quite a bit of documentation but remain confused on how to create the mapping. However, it does seem that I needed to make some adjustments to my query as I have edited. Any hints? Commented May 10, 2014 at 9:07
  • Have a look at this example, if it remains unclear let me know and I will write you an answer on your question. Commented May 10, 2014 at 11:24
Related questions
2
about ElasticSearch jdbc river
2
ElasticSearch - creating JDBC river
0
MariaDB and Elasticsearch
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.