Jdbc river and geospatial field

Nikolay_Chankov · December 19, 2013, 6:43pm

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.
To view this discussion on the web visit 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.

jprante · December 19, 2013, 7:54pm

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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/CAKdsXoHsZENrafjGR7ejFa8_gehi4WHR3WnqmVqhX5G%2BimPXSg%40mail.gmail.com.
For more options, visit https://groups.google.com/groups/opt_out.

Nikolay_Chankov · December 19, 2013, 9:45pm

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.
To view this discussion on the web visit 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.

Nikolay_Chankov · December 20, 2013, 12:11am

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'

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 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' -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.
To view this discussion on the web visit 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.

jprante · December 20, 2013, 8:08am

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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/CAKdsXoHXzq1PmgEc2oxfxfcMprU%3DA0noGUbzZCovawLg%3DESEAw%40mail.gmail.com.
For more options, visit https://groups.google.com/groups/opt_out.

Nikolay_Chankov · December 20, 2013, 9:29pm

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'
curl -XPUT 'http://localhost:9200/venues/'
curl -XPUT '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.
To view this discussion on the web visit 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.

jprante · December 20, 2013, 9:32pm

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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/CAKdsXoFkwyjNrYQ1UUPz2cX3J9oGFojpX%3Dx9nK_N7my5O5m64w%40mail.gmail.com.
For more options, visit https://groups.google.com/groups/opt_out.

Nikolay_Chankov · December 21, 2013, 1:11am

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.
To view this discussion on the web visit 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.

dadoonet · December 21, 2013, 7:47am

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

--
David
Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs

Le 20 déc. 2013 à 22:29, Nikolay Chankov 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'
curl -XPUT 'http://localhost:9200/venues/'
curl -XPUT '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.
To view this discussion on the web visit 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.

--
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.
To view this discussion on the web visit 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.