# Import Lat/Lon from SQL Server using JDBC

**URL:** <https://discuss.elastic.co/t/import-lat-lon-from-sql-server-using-jdbc/139244>\
**Category:** Logstash\
**Created:** [July 9, 2018, 11:07pm UTC](https://discuss.elastic.co/t/import-lat-lon-from-sql-server-using-jdbc/139244 "2018-07-09T23:07:50Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Scott\_Newby](https://avatars.discourse-cdn.com/v4/letter/s/eb8c5e/32.png) [@Scott\_Newby](https://discuss.elastic.co/u/Scott_Newby)\
**Post date:** [July 9, 2018, 11:07pm UTC](https://discuss.elastic.co/t/import-lat-lon-from-sql-server-using-jdbc/139244/1 "2018-07-09T23:07:50Z")

</div>

I'm trying to import a few fields into an existing index, 3 doubles and a geo\_point. Spent the better part of the day with this one without luck.

Here is my index mapping setup:

```
 "mappings": {
"_doc": {
"properties": {
"paidin": {
"type": "double"
},
"risk": {
"type": "double"
},
"paidout": {
"type": "double"
},
"location": {
"type": "geo_point"
}
}
}

```

Here is my config:

```
input {
   jdbc {
      jdbc_driver_library => "C:\Program Files\Microsoft JDBC Driver 6.4 for SQL Server\sqljdbc_6.4\enu\mssql-jdbc-6.4.0.jre7.jar"
      jdbc_driver_class => "com.microsoft.sqlserver"
      jdbc_connection_string => "jdbc:sqlserver://server_name=db_name;user= ****;password=*****"
      jdbc_user => " *****"
      clean_run => true
      statement => "SELECT top 20 [paidin] as paidin,[risk] as risk,[paidout] as paidout, lat as latitude, lon as longitude FROM [dbo].[Locations] WHERE lat > 0"
   }
}
filter {
   mutate {
    add_field => {"[location][lon]" => "%{longitude}"}
    add_field => {"[location][lat]" => "%{latitude}"}
    }
   
   mutate {
          remove_field => ["latitude", "longitude", "@version","@timestamp"]
      }
}
output {
   elasticsearch {
      hosts => "localhost"
      index => "geo_points"
      action => "index"
	  document_type => "type1"
   }
   stdout {
      codec => "rubydebug"
   }
}

```

here is the std-out rubydebug

```
{
           "paidin" => "5811.00",
    "paidout"=> "2681.00",
       "risk"=> "0.634000",
         "location" => {
        "lon" => "-78.118546",
        "lat" => "38.460266"
    }

```

here is the error when trying to get it into the index...

```
Could not index
 event to Elasticsearch. {:status=>400, :action=>["index", {:_id=>nil, :_index=>
"geo_points", :_type=>"type1", :_routing=>nil}, #<LogStash::Event:0x3834913e>],
:response=>{"index"=>{"_index"=>"geo_points", "_type"=>"type1", "_id"=>"awK5gGQB
EWz9z5vG4rXt", "status"=>400, "error"=>{"type"=>"illegal_argument_exception", "r
eason"=>"Rejecting mapping update to [geo_points] as the final mapping would hav
e more than 1 type: [_doc, type1]"}}}}

```

Any help would be appreciated

---

<div class="post-metadata">

**Author:** ![Scott\_Newby](https://avatars.discourse-cdn.com/v4/letter/s/eb8c5e/32.png) [@Scott\_Newby](https://discuss.elastic.co/u/Scott_Newby)\
**Post date:** [July 9, 2018, 11:48pm UTC](https://discuss.elastic.co/t/import-lat-lon-from-sql-server-using-jdbc/139244/2 "2018-07-09T23:48:29Z")

</div>

Answer - \_doc as the mapping document type clashed with the doc type of apparently the location array...

Changed mapping from \_doc to doc

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [August 6, 2018, 11:48pm UTC](https://discuss.elastic.co/t/import-lat-lon-from-sql-server-using-jdbc/139244/3 "2018-08-06T23:48:29Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.