# Jdbc river and geospatial field **URL:** \ **Category:** Elasticsearch\ **Created:** [December 19, 2013, 6:43pm UTC](https://discuss.elastic.co/t/jdbc-river-and-geospatial-field/14955 "2013-12-19T18:43:32Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![Nikolay\_Chankov](https://avatars.discourse-cdn.com/v4/letter/n/a8b319/32.png) [@Nikolay\_Chankov](https://discuss.elastic.co/u/Nikolay_Chankov)\ **Post date:** [December 19, 2013, 6:43pm UTC](https://discuss.elastic.co/t/jdbc-river-and-geospatial-field/14955/1 "2013-12-19T18:43:32Z")
Hi everyone, I have the following case: I've managed to configure jdbc river and connect MySql to ES. I've created an index called "venues" and it has the same structure as the mysql table (it's a flat object under the \_source). Each node has 2 fields: lat and lng, which are decimal in the mysql table. My question is: how it's possible to make geospatial search (filter and sort) based on these 2 fields OR how to make a geo point based on these 2 fields? Your help is much appreciated! Regards -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/424c5a6e-c2af-45d7-8e59-b65d2564ba7d%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/424c5a6e-c2af-45d7-8e59-b65d2564ba7d%40googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![jprante](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jprante/32/44941_2.png) [@jprante](https://discuss.elastic.co/u/jprante)\ **Post date:** [December 19, 2013, 7:54pm UTC](https://discuss.elastic.co/t/jdbc-river-and-geospatial-field/14955/2 "2013-12-19T19:54:02Z")
My suggestion is to use two SQL columns "location.lat" and "location.lon" instead of flat "lat" and "lng". With "type": "geo\_point" mapping, a geo search should be straightforward. Jörg -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAKdsXoHsZENrafjGR7ejFa8\_gehi4WHR3WnqmVqhX5G%2BimPXSg%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAKdsXoHsZENrafjGR7ejFa8_gehi4WHR3WnqmVqhX5G%2BimPXSg%40mail.gmail.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![Nikolay\_Chankov](https://avatars.discourse-cdn.com/v4/letter/n/a8b319/32.png) [@Nikolay\_Chankov](https://discuss.elastic.co/u/Nikolay_Chankov)\ **Post date:** [December 19, 2013, 9:45pm UTC](https://discuss.elastic.co/t/jdbc-river-and-geospatial-field/14955/3 "2013-12-19T21:45:08Z")
Thanks for the hint, Jörg I will try it! On Thursday, December 19, 2013 7:54:02 PM UTC, Jörg Prante wrote: > My suggestion is to use two SQL columns "location.lat" and "location.lon" > instead of flat "lat" and "lng". With "type": "geo\_point" mapping, a geo > search should be straightforward. > > Jörg -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/4a554eb3-21cf-479b-87fe-b779533bafb3%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/4a554eb3-21cf-479b-87fe-b779533bafb3%40googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![Nikolay\_Chankov](https://avatars.discourse-cdn.com/v4/letter/n/a8b319/32.png) [@Nikolay\_Chankov](https://discuss.elastic.co/u/Nikolay_Chankov)\ **Post date:** [December 20, 2013, 12:11am UTC](https://discuss.elastic.co/t/jdbc-river-and-geospatial-field/14955/4 "2013-12-20T00:11:45Z")
Hi Jörg, I've reached the desired result, but it's working strangely. Here is what I mean. If I run this (delete the river and the index itself and then recreate them): curl -XDELETE localhost:9200/\_river/venues\_river curl -XDELETE '[http://localhost:9200/venues](http://localhost:9200/venues)' curl -XPUT 'localhost:9200/\_river/venues\_river/\_meta' -d '{ "strategy" : "simple", "type" : "jdbc", "jdbc" : { ... }, "index" : { "index" : "venues", "type" : "venue" }, "mappings" : { "venue" : { "properties" : { .... "location" : {"type" : "geo\_point"} } } } }' even though the location type is set as geo\_point, when I check the url: [http://localhost:9200/venues/\_mapping](http://localhost:9200/venues/_mapping) I can see that there is no type assigned to the location. It has { venues: { venue: { properties: { ... location: { properties: { lat: {type: "double"}, lng: {type: "double"} } }, } } } } Although if I create a new mapping like this: curl -XPUT '[http://localhost:9200/venues/geo/\_mapping](http://localhost:9200/venues/geo/_mapping)' -d ' { "venue" : { "properties" : { "location" : {"type" : "geo\_point"} } } }' I can see that the second one the location has type "geo\_point". If I replace /geo/ with /venue/ it return error MergeMappingException[Merge failed with failures {[Can't merge a non object mapping [location] with an object mapping [location]]}] Could that be a bug or because the location has sub nodes it doesn't set the type? Thanks for the quick response! On Thursday, December 19, 2013 9:45:08 PM UTC, Nikolay Chankov wrote: > Thanks for the hint, Jörg > > I will try it! > > On Thursday, December 19, 2013 7:54:02 PM UTC, Jörg Prante wrote: > > > My suggestion is to use two SQL columns "location.lat" and "location.lon" > > instead of flat "lat" and "lng". With "type": "geo\_point" mapping, a geo > > search should be straightforward. > > > > Jörg -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/83168f6c-0a66-4622-9402-228deb0cd275%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/83168f6c-0a66-4622-9402-228deb0cd275%40googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![jprante](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jprante/32/44941_2.png) [@jprante](https://discuss.elastic.co/u/jprante)\ **Post date:** [December 20, 2013, 8:08am UTC](https://discuss.elastic.co/t/jdbc-river-and-geospatial-field/14955/5 "2013-12-20T08:08:02Z")
You must use "lat" and "lon" as field names within geo\_type, not "lat" and "lng". Jörg -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAKdsXoHXzq1PmgEc2oxfxfcMprU%3DA0noGUbzZCovawLg%3DESEAw%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAKdsXoHXzq1PmgEc2oxfxfcMprU%3DA0noGUbzZCovawLg%3DESEAw%40mail.gmail.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![Nikolay\_Chankov](https://avatars.discourse-cdn.com/v4/letter/n/a8b319/32.png) [@Nikolay\_Chankov](https://discuss.elastic.co/u/Nikolay_Chankov)\ **Post date:** [December 20, 2013, 9:29pm UTC](https://discuss.elastic.co/t/jdbc-river-and-geospatial-field/14955/6 "2013-12-20T21:29:13Z")
Thanks Jörg I've managed to fix it by first problem, basically by creating the index before creating the river and then everything went well. But, I tried to reproduce the same on another server, fresh install with the latest stable elasticsearch 0.90.8 as well as latest jdbc and mysql jdbc connector (my development server uses old version of ES). So, I've managed to install the server as well as to connect it to mysql But now the problem is that when I run The command to create the river it doesn't put the data into the proper index even though it was specified. Here is the current set of commands which I am using to create the index and river: curl -XDELETE localhost:9200/\_river/venues\_river curl -XDELETE '[http://localhost:9200/venues](http://localhost:9200/venues)' curl -XPUT '[http://localhost:9200/venues/](http://localhost:9200/venues/)' curl -XPUT '[http://localhost:9200/venues/venue/\_mapping](http://localhost:9200/venues/venue/_mapping)' -d ' { "venue" : { "properties" : { "object" : { "type" : "string" }, "id" : { "type" : "integer" }, "name" : { "type" : "string" }, "description" : { "type" : "string" }, "town" : { "type" : "string" }, "town\_id" : { "type" : "integer" }, "county" : { "type" : "string" }, "county\_id" : { "type" : "integer" }, ``` "phone" : { "type" : "string" }, "web" : { "type" : "string" }, "type" : { "type" : "string" }, "type_id" : { "type" : "integer" }, "postcode" : { "type" : "string" }, "created" : { "type" : "date" }, "modified" : { "type" : "date" }, "address" : { "type" : "string" }, "email" : { "type" : "string" }, "slug" : { "type" : "string" }, "location" : {"type" : "geo_point"} } } ``` }' curl -XPUT 'localhost:9200/\_river/venues\_river/\_meta' -d '{ "strategy" : "simple", "type" : "jdbc", "jdbc" : { "driver" : "com.mysql.jdbc.Driver", "url" : "jdbc:mysql://localhost:3306/database", "user" : "user", "password" : "pass", "sql" : "select \* from search\_venues" }, "index" : { "index" : "venues", "type" : "venue" } }' The data itself is inserted, but instead to be inserted on "venues" index, it is in "jdbc", so I can access it by localhost:9200/jdbc/\_search... Strangely enough, this script is working as expected on my development server which is: ES: 0.90.5 JDBC: elasticsearch-river-jdbc-2.2.1.jar Mysql JDBC mysql-connector-java-5.1.26-bin.jar I am sorry, by asking probably stupid questions... Thank you in advance On Friday, December 20, 2013 8:08:02 AM UTC, Jörg Prante wrote: > You must use "lat" and "lon" as field names within geo\_type, not "lat" and > "lng". > > Jörg -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/36a3eec8-25a3-499a-b7b2-3a868741ac35%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/36a3eec8-25a3-499a-b7b2-3a868741ac35%40googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![jprante](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jprante/32/44941_2.png) [@jprante](https://discuss.elastic.co/u/jprante)\ **Post date:** [December 20, 2013, 9:32pm UTC](https://discuss.elastic.co/t/jdbc-river-and-geospatial-field/14955/7 "2013-12-20T21:32:52Z")
I have changed in the latest JDBC river the configuration format. There is no "index" subsection anymore, just the "jdbc" subsection: curl -XPUT 'localhost:9200/\_river/venues\_river/\_meta' -d '{ "strategy" : "simple", "type" : "jdbc", "jdbc" : { "driver" : "com.mysql.jdbc.Driver", "url" : "jdbc:mysql://localhost:3306/database", "user" : "user", "password" : "pass", "sql" : "select \* from search\_venues", "index" : "venues", "type" : "venue" } }' Jörg -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAKdsXoFkwyjNrYQ1UUPz2cX3J9oGFojpX%3Dx9nK\_N7my5O5m64w%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAKdsXoFkwyjNrYQ1UUPz2cX3J9oGFojpX%3Dx9nK_N7my5O5m64w%40mail.gmail.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![Nikolay\_Chankov](https://avatars.discourse-cdn.com/v4/letter/n/a8b319/32.png) [@Nikolay\_Chankov](https://discuss.elastic.co/u/Nikolay_Chankov)\ **Post date:** [December 21, 2013, 1:11am UTC](https://discuss.elastic.co/t/jdbc-river-and-geospatial-field/14955/8 "2013-12-21T01:11:19Z")
Thank you for the support! I will try it monday. On Friday, December 20, 2013 9:32:52 PM UTC, Jörg Prante wrote: > I have changed in the latest JDBC river the configuration format. There is > no "index" subsection anymore, just the "jdbc" subsection: > > curl -XPUT 'localhost:9200/\_river/venues\_river/\_meta' -d '{ > "strategy" : "simple", > "type" : "jdbc", > "jdbc" : { > "driver" : "com.mysql.jdbc.Driver", > "url" : "jdbc:mysql://localhost:3306/database", > "user" : "user", > "password" : "pass", > "sql" : "select \* from search\_venues", > "index" : "venues", > "type" : "venue" > } > }' > > Jörg -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/ff5bb92b-8f1b-4d6d-821d-b5a26213334b%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/ff5bb92b-8f1b-4d6d-821d-b5a26213334b%40googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [December 21, 2013, 7:47am UTC](https://discuss.elastic.co/t/jdbc-river-and-geospatial-field/14955/9 "2013-12-21T07:47:29Z")
In latest version, Jorg moves index/type settings to jdbc object. See example on front page here: [GitHub - jprante/elasticsearch-jdbc: JDBC importer for Elasticsearch](https://github.com/jprante/elasticsearch-river-jdbc) -- David 😉 Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs Le 20 déc. 2013 à 22:29, Nikolay Chankov [nchankov@gmail.com](mailto:nchankov@gmail.com) a écrit : Thanks Jörg I've managed to fix it by first problem, basically by creating the index before creating the river and then everything went well. But, I tried to reproduce the same on another server, fresh install with the latest stable elasticsearch 0.90.8 as well as latest jdbc and mysql jdbc connector (my development server uses old version of ES). So, I've managed to install the server as well as to connect it to mysql But now the problem is that when I run The command to create the river it doesn't put the data into the proper index even though it was specified. Here is the current set of commands which I am using to create the index and river: curl -XDELETE localhost:9200/\_river/venues\_river curl -XDELETE '[http://localhost:9200/venues](http://localhost:9200/venues)' curl -XPUT '[http://localhost:9200/venues/](http://localhost:9200/venues/)' curl -XPUT '[http://localhost:9200/venues/venue/\_mapping](http://localhost:9200/venues/venue/_mapping)' -d ' { "venue" : { "properties" : { "object" : { "type" : "string" }, "id" : { "type" : "integer" }, "name" : { "type" : "string" }, "description" : { "type" : "string" }, "town" : { "type" : "string" }, "town\_id" : { "type" : "integer" }, "county" : { "type" : "string" }, "county\_id" : { "type" : "integer" }, ``` "phone" : { "type" : "string" }, "web" : { "type" : "string" }, "type" : { "type" : "string" }, "type_id" : { "type" : "integer" }, "postcode" : { "type" : "string" }, "created" : { "type" : "date" }, "modified" : { "type" : "date" }, "address" : { "type" : "string" }, "email" : { "type" : "string" }, "slug" : { "type" : "string" }, "location" : {"type" : "geo_point"} } } ``` }' curl -XPUT 'localhost:9200/\_river/venues\_river/\_meta' -d '{ "strategy" : "simple", "type" : "jdbc", "jdbc" : { "driver" : "com.mysql.jdbc.Driver", "url" : "jdbc:mysql://localhost:3306/database", "user" : "user", "password" : "pass", "sql" : "select \* from search\_venues" }, "index" : { "index" : "venues", "type" : "venue" } }' The data itself is inserted, but instead to be inserted on "venues" index, it is in "jdbc", so I can access it by localhost:9200/jdbc/\_search... Strangely enough, this script is working as expected on my development server which is: ES: 0.90.5 JDBC: elasticsearch-river-jdbc-2.2.1.jar Mysql JDBC mysql-connector-java-5.1.26-bin.jar I am sorry, by asking probably stupid questions... Thank you in advance > On Friday, December 20, 2013 8:08:02 AM UTC, Jörg Prante wrote: > You must use "lat" and "lon" as field names within geo\_type, not "lat" and "lng". > > Jörg -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/36a3eec8-25a3-499a-b7b2-3a868741ac35%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/36a3eec8-25a3-499a-b7b2-3a868741ac35%40googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/A9D35C11-2F98-4478-96DD-6C3B13972826%40pilato.fr](https://groups.google.com/d/msgid/elasticsearch/A9D35C11-2F98-4478-96DD-6C3B13972826%40pilato.fr). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 6, 2017, 1:59am UTC](https://discuss.elastic.co/t/jdbc-river-and-geospatial-field/14955/10 "2017-07-06T01:59:49Z")