Driver version
7.0.0 and 7.1.1
SQL Server version
Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)
Aug 22 2017 17:04:49
Copyright (C) 2017 Microsoft Corporation
Developer Edition (64-bit) on Linux (Ubuntu 16.04.3 LTS)
Client Operating System
SQL Server for Linux docker image (2017-latest): https://hub.docker.com/r/microsoft/mssql-server-linux/
Docker version 18.06.1-ce, build e68fc7a
Host OS: Ubunutu 17.10
JAVA/JVM version
Java(TM) SE Runtime Environment (build 1.8.0_181-b13)
and
java version "10.0.2" 2018-07-17
Table schema
create table geotest (id integer not null primary key, location geography not null)
Problem description
This might be just my misunderstanding of how to use geography types, but there seems to be a discrepancy between querying the database using Geography objects versus using Lat and Long attributes on columns.
Suppose the following code is used to insert data:
try (ISQLServerPreparedStatement stat = con.prepareStatement("insert into geotest (id, location) values (?, ?)").unwrap(ISQLServerPreparedStatement.class))
{
stat.setInt(1, 1);
stat.setGeography(2, Geography.point(-30, 50, 4326));
stat.executeUpdate();
}
Reading using Geography objects is fine:
try (ISQLServerPreparedStatement stat = con.prepareStatement("select location from geotest").unwrap(ISQLServerPreparedStatement.class);
ISQLServerResultSet rs = stat.executeQuery().unwrap(ISQLServerResultSet.class))
{
rs.next();
Geography g = rs.getGeography(1);
System.out.println("Select with geography object: latitude=" + g.getLatitude() + ", longitude=" + g.getLongitude());
}
where the result lat/long is expected. But this code that uses Lat and Long in the query itself:
try (ISQLServerPreparedStatement stat = con.prepareStatement("select location.Lat, location.Long from geotest").unwrap(ISQLServerPreparedStatement.class);
ISQLServerResultSet rs = stat.executeQuery().unwrap(ISQLServerResultSet.class))
{
rs.next();
double latitude = rs.getDouble(1);
double longitude = rs.getDouble(2);
System.out.println("Select with database function: latitude=" + latitude + ", longitude=" + longitude);
}
reverses the latitude and longitude values.
Furthermore, inserting values which would be in-range for lat/long but out of range if they were reversed:
double bigLatitude = 89; //Just in range for a latitude
double bigLongitude = 179; //Just in range for a longitude
System.out.println("Inserting geo-point with latitude=" + bigLatitude + ", longitude=" + bigLongitude);
try (ISQLServerPreparedStatement stat = con.prepareStatement("insert into geotest (id, location) values (?, ?)").unwrap(ISQLServerPreparedStatement.class))
{
stat.setInt(1, 2);
stat.setGeography(2, Geography.point(bigLatitude, bigLongitude, 4326));
stat.executeUpdate();
}
results in an Error converting data type varbinary to geography, however reversing those two values results in a successful insertion.
Reproduction code
Maven project with tests to reproduce the issue is here:
https://bitbucket.org/prunge/sqlserver-geography-tester/src/master/src/test/java/au/net/causal/sqlservergeotest/GeoTestIT.java
Driver version
7.0.0 and 7.1.1
SQL Server version
Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)
Aug 22 2017 17:04:49
Copyright (C) 2017 Microsoft Corporation
Developer Edition (64-bit) on Linux (Ubuntu 16.04.3 LTS)
Client Operating System
SQL Server for Linux docker image (2017-latest): https://hub.docker.com/r/microsoft/mssql-server-linux/
Docker version 18.06.1-ce, build e68fc7a
Host OS: Ubunutu 17.10
JAVA/JVM version
Java(TM) SE Runtime Environment (build 1.8.0_181-b13)
and
java version "10.0.2" 2018-07-17
Table schema
create table geotest (id integer not null primary key, location geography not null)
Problem description
This might be just my misunderstanding of how to use geography types, but there seems to be a discrepancy between querying the database using
Geographyobjects versus usingLatandLongattributes on columns.Suppose the following code is used to insert data:
Reading using
Geographyobjects is fine:where the result lat/long is expected. But this code that uses
LatandLongin the query itself:reverses the latitude and longitude values.
Furthermore, inserting values which would be in-range for lat/long but out of range if they were reversed:
results in an
Error converting data type varbinary to geography, however reversing those two values results in a successful insertion.Reproduction code
Maven project with tests to reproduce the issue is here:
https://bitbucket.org/prunge/sqlserver-geography-tester/src/master/src/test/java/au/net/causal/sqlservergeotest/GeoTestIT.java