Distance for Spatial Data using NetTopologySuite

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
avalor
Posts: 15
Joined: Tue 18 Feb 2014 17:39

Distance for Spatial Data using NetTopologySuite

Post by avalor » Fri 04 Apr 2014 11:03

I'm using dotConnect for Oracle 8.3.125.0 and NetTopologySuite as Spatial Service.

When I try to get the distance between two points:
- POINT (-0.4246 38.4099)
- POINT (-0.5199 38.3961)

The result should be '8464' meters. I get this result when using the DbGeography.Distance function in our code targeting our SqlServer database. Also obtained this value when I was developing using Oracle ODP.Net Provider. But now that I've switched to dotConnect, I'm getting '0,0962939769663716'.

Test Code:

Code: Select all

Console.WriteLine("Distance between {0} and {1}: {2}", start.AsText(), end.AsText(), start.Distance(end));
Result:
Distance between POINT (-0.4246 38.4099) and POINT (-0.5199 38.3961): 0,0962939769663716


When I execute this sentence directly in Oracle Developer I also get correct results:

Code: Select all

select  sdo_geom.sdo_distance(
  sdo_geometry(2001, 4326, sdo_point_type(-0.4246, 38.4099, null), null, null),
  sdo_geometry(2001, 4326, sdo_point_type(-0.5199, 38.3961, null), null, null),
  0.005)
from dual;
I've this settings in Web.config

Code: Select all

<SpatialOptions SpatialServiceType="NetTopologySuite" AlwaysUseGeographyDefaultSrid="true" GeographyDefaultSrid="4326" />
And also checked that coordinates are being stored in database using SRID=4326

Am I missing something or doing something wrong?

Shalex
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Re: Distance for Spatial Data using NetTopologySuite

Post by Shalex » Tue 08 Apr 2014 16:25

Code: Select all

select  sdo_geom.sdo_distance(
  sdo_geometry(2001, 4326, sdo_point_type(-0.4246, 38.4099, null), null, null),
  sdo_geometry(2001, 4326, sdo_point_type(-0.5199, 38.3961, null), null, null),
  0.005)
from dual;
returns 8464,19172543766.

But

Code: Select all

select sdo_geom.sdo_distance(
  sdo_geometry(2001, null, sdo_point_type(-0.4246, 38.4099, null), null, null),
  sdo_geometry(2001, null, sdo_point_type(-0.5199, 38.3961, null), null, null),
  0.005)
from dual;
results in 0,0962939769663705.

The difference is caused by different SDO_SRID used in the queries.
avalor wrote:The result should be '8464' meters. I get this result when using the DbGeography.Distance function in our code targeting our SqlServer database. Also obtained this value when I was developing using Oracle ODP.Net Provider. But now that I've switched to dotConnect, I'm getting '0,0962939769663716'.

Test Code:

Code: Select all

Console.WriteLine("Distance between {0} and {1}: {2}", start.AsText(), end.AsText(), start.Distance(end));
Result:
Distance between POINT (-0.4246 38.4099) and POINT (-0.5199 38.3961): 0,0962939769663716
Please send us a small test project (with the corresponding DDL/DML script) where the same code returns 8464,19172543766 with ODP.NET but 0,0962939769663705 with dotConnect for Oracle.

avalor
Posts: 15
Joined: Tue 18 Feb 2014 17:39

Re: Distance for Spatial Data using NetTopologySuite

Post by avalor » Thu 10 Apr 2014 09:12

Hi! I've just submitted a set of three projects to reproduce the problem when obtaining the distance between two points in EF and using NetTopologySuite

The package contains a SQL script that creates a table with a spatial column and inserts two rows. Then the three projects get the distance between that two points:

- The first project uses ODP.NET Managed Driver 4.121.1.0*
- The second project uses dotConnect with EF5
- The third project uses dotConnect with EF6

* Using ODP.NET, sdo_geometry columns are not supported, so I had to create a property to simulate it. You can look at it in 'COORDENADAExtension.cs'

In the first project, the result is the same that using a SQL Server Database.
For what I've understood in your previous answer, it seems that, somehow, SRID information is not being taked into consideration when computing the distance, am I right?

I hope this helps to find out what's happening

avalor
Posts: 15
Joined: Tue 18 Feb 2014 17:39

Re: Distance for Spatial Data using NetTopologySuite

Post by avalor » Wed 16 Apr 2014 08:23

Any news on this? Did you receive my test project?

Shalex
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Re: Distance for Spatial Data using NetTopologySuite

Post by Shalex » Thu 17 Apr 2014 09:39

Your project with ODP.NET uses spatial types from Microsoft.SqlServer.Types.dll.
dotConnect for Oracle supports and uses a different spatial service - NetTopologySuite.

This provider-independent code demonstrates that NetTopologySuite doesn't take into account SRID when calculating the distance between two points:

Code: Select all

    var reader = new NetTopologySuite.IO.WKTReader();
    var start = reader.Read("SRID=4326; POINT(-0.4246 38.4099)");
    var end = reader.Read("SRID=4326; POINT(-0.5199 38.39619)");
    var distance = start.Distance(end); // 0.096281120163820788
Workarounds:

1. Use Microsoft.SqlServer.Types.dll with dotConnect for Oracle in the way like you did with ODP.NET via COORDENADAExtension.cs. Comment (or remove) the following line in \TestDistance\TestDotConnectDistance2\App.Config to use Microsoft.SqlServer.Types.dll instead of NetTopologySuite.dll in runtime:

Code: Select all

    <SpatialOptions SpatialServiceType="NetTopologySuite" AlwaysUseGeographyDefaultSrid="true" GeographyDefaultSrid="4326" />
Disadvantage of this approach is that you cannot use COORD property in LINQ to Entities queries.

2. With NetTopologySuite.dll, calculate the distance at the database side. For this, use the LINQ to Entities query:

Code: Select all

  var distance = (from coordenada in db.COORDENADA
                    let coord1 = db.COORDENADA.OrderBy(x => x.COORDENADAID).Take(1).COORD
                    let coord2 = db.COORDENADA.OrderByDescending(x => x.COORDENADAID).Take(1).COORD
                select coord1.Distance(coord2)).FirstOrDefault();
The following query reads both points with the distance between them in one round-trip:

Code: Select all

var distanceAndPointsQuery = (from coordenada in db.COORDENADA
    let coord1 = db.COORDENADA.OrderBy(x => x.COORDENADAID).Take(1).COORD
    let coord2 = db.COORDENADA.OrderByDescending(x => x.COORDENADAID).Take(1).COORD
    select new { Coord1 = coord1, Coord2 = coord2, Distance = coord1.Distance(coord2) }
var distanceAndPointsResult = distanceAndPointsQuery.FirstOrDefault();

avalor
Posts: 15
Joined: Tue 18 Feb 2014 17:39

Re: Distance for Spatial Data using NetTopologySuite

Post by avalor » Mon 05 May 2014 15:35

Hi! I've created a new issue in NetTopologySuite project site:

https://code.google.com/p/nettopologysu ... ail?id=182

I will let you know what answer I get from them.

Post Reply