# Jdbc river and geo\_point from longitude and latitude **URL:** \ **Category:** Elasticsearch\ **Created:** [May 10, 2015, 4:41am UTC](https://discuss.elastic.co/t/jdbc-river-and-geo-point-from-longitude-and-latitude/442 "2015-05-10T04:41:37Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![kitex](https://avatars.discourse-cdn.com/v4/letter/k/f14d63/32.png) [@kitex](https://discuss.elastic.co/u/kitex)\ **Post date:** [May 10, 2015, 4:41am UTC](https://discuss.elastic.co/t/jdbc-river-and-geo-point-from-longitude-and-latitude/442/1 "2015-05-10T04:41:37Z")
I am getting error while creating geo\_point from longitude and latitude. It does not recognize location variable. ``` PUT /_river/fault/_meta { "type" : "jdbc", "jdbc" : { "url" : "jdbc:postgresql://localhost:5432/gis", "user" : "test", "password" : "test", "sql" : [ { "statement" : "select location_id as _id, latitude as \"location.lat\",longitude \"location.lon\",zone from alarms" } ], "index" : "fault", "type" : "alert_status", "schedule": "0 0/1 * * * ?" }, "type_mapping" : { "alert_status" : { "properties" : { "location" : { "type" : "geo_point", "lat_lon" : true } } } } } ``` How to create geo\_point without error? I am using postgres/elasticsearch1.5.1 ``` [2015-05-10 10:20:00,063][ERROR][river.jdbc.RiverPipeline] java.lang.IllegalArgumentException: illegal head: location java.io.IOException: java.lang.IllegalArgumentException: illegal head: location at org.xbib.elasticsearch.river.jdbc.strategy.simple.SimpleRiverSource.fetch(SimpleRiverSource.java:353) at org.xbib.elasticsearch.river.jdbc.strategy.simple.SimpleRiverFlow.fetch(SimpleRiverFlow.java:220) at org.xbib.elasticsearch.river.jdbc.strategy.simple.SimpleRiverFlow.execute(SimpleRiverFlow.java:149) at org.xbib.elasticsearch.plugin.jdbc.RiverPipeline.request(RiverPipeline.java:88) at org.xbib.elasticsearch.plugin.jdbc.RiverPipeline.call(RiverPipeline.java:66) at org.xbib.elasticsearch.plugin.jdbc.RiverPipeline.call(RiverPipeline.java:30) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745) Caused by: java.lang.IllegalArgumentException: illegal head: location at org.xbib.elasticsearch.plugin.jdbc.util.PlainKeyValueStreamListener.merge(PlainKeyValueStreamListener.java:294) at org.xbib.elasticsearch.plugin.jdbc.util.PlainKeyValueStreamListener.values(PlainKeyValueStreamListener.java:153) at org.xbib.elasticsearch.river.jdbc.strategy.simple.SimpleRiverSource.processRow(SimpleRiverSource.java:824) at org.xbib.elasticsearch.river.jdbc.strategy.simple.SimpleRiverSource.nextRow(SimpleRiverSource.java:777) at org.xbib.elasticsearch.river.jdbc.strategy.simple.SimpleRiverSource.merge(SimpleRiverSource.java:510) at org.xbib.elasticsearch.river.jdbc.strategy.simple.SimpleRiverSource.execute(SimpleRiverSource.java:405) at org.xbib.elasticsearch.river.jdbc.strategy.simple.SimpleRiverSource.fetch(SimpleRiverSource.java:332) ... 9 more ``` ---
**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:** [May 10, 2015, 1:16pm UTC](https://discuss.elastic.co/t/jdbc-river-and-geo-point-from-longitude-and-latitude/442/2 "2015-05-10T13:16:10Z")
You must assign IDs to your ES docs you create from DB using the "\_id" column name. Otherwise you will merge all rows in the result set into one ES doc which is probably not what you want. Also, the "type\_mapping" must be moved inside "jdbc" object and must be accompanied by and "index\_settings" parameter, or it won't be used. ---
**Author:** ![kitex](https://avatars.discourse-cdn.com/v4/letter/k/f14d63/32.png) [@kitex](https://discuss.elastic.co/u/kitex)\ **Post date:** [May 10, 2015, 1:38pm UTC](https://discuss.elastic.co/t/jdbc-river-and-geo-point-from-longitude-and-latitude/442/3 "2015-05-10T13:38:00Z")
When I concatenate latitude and longitude in query then I can use geo\_point mapping but not with current situation. e.g. `latitude || ',' || longitude as \"geopoint\"` Although location would appear as geo\_point field I can not create map from it as the field as location field (geo\_point alias) column is blank. ![](https://sea2.discourse-cdn.com/elastic/uploads/default/213/550ef064346a22c3.png) My new PUT request is as follows: ``` PUT /_river/fault/_meta { "type" : "jdbc", "jdbc" : { "url" : "jdbc:postgresql://localhost:5432/gis", "user" : "test", "password" : "test", "sql" : [ { "statement" : "select location_id as _id, latitude as \"location.lat\",longitude \"location.lon\",latitude||','||longitude as geopoint ,zone from alarms" } ], "index" : "fault", "type" : "alert_status", "schedule": "0 0/1 * * * ?", "index_settings" : { "index" : { "number_of_shards" : 1 } }, "type_mapping" : { "alert_status" : { "properties" : { "location" : { "type" : "geo_point", "lat_lon" : true } } } } } } ``` I have tried with [this](http://gijs.github.io/blog/2014/03/24/spatial-elastic-search-with-a-postgresql-river/) tutorial as well but same error. ---
**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:** [May 10, 2015, 8:40pm UTC](https://discuss.elastic.co/t/jdbc-river-and-geo-point-from-longitude-and-latitude/442/4 "2015-05-10T20:40:29Z")
There is no need to concatenate. You can try to adapt this working example from new upcoming "noriver' branch, it is very close to actual river based releases. > ---
**Author:** ![kitex](https://avatars.discourse-cdn.com/v4/letter/k/f14d63/32.png) [@kitex](https://discuss.elastic.co/u/kitex)\ **Post date:** [May 11, 2015, 8:50am UTC](https://discuss.elastic.co/t/jdbc-river-and-geo-point-from-longitude-and-latitude/442/5 "2015-05-11T08:50:27Z")
@jprante It solved the issue.But when I add 100 other columns it suddenly stops working. Thanks. ---
**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:** [May 11, 2015, 8:27pm UTC](https://discuss.elastic.co/t/jdbc-river-and-geo-point-from-longitude-and-latitude/442/6 "2015-05-11T20:27:52Z")
Yes, the order of columns is significant for correct JSON construction. ---
**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, 12:14am UTC](https://discuss.elastic.co/t/jdbc-river-and-geo-point-from-longitude-and-latitude/442/7 "2017-07-06T00:14:35Z")