Distance for Spatial Data using NetTopologySuite

Distance for Spatial Data using NetTopologySuite

Postby 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?
avalor
 
Posts: 15
Joined: Tue 18 Feb 2014 17:39

Re: Distance for Spatial Data using NetTopologySuite

Postby 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.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Re: Distance for Spatial Data using NetTopologySuite

Postby 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

Postby avalor » Wed 16 Apr 2014 08:23

Any news on this? Did you receive my test project?
avalor
 
Posts: 15
Joined: Tue 18 Feb 2014 17:39

Re: Distance for Spatial Data using NetTopologySuite

Postby 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();
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Re: Distance for Spatial Data using NetTopologySuite

Postby avalor » Mon 05 May 2014 15:35

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

https://code.google.com/p/nettopologysuite/issues/detail?id=182

I will let you know what answer I get from them.
avalor
 
Posts: 15
Joined: Tue 18 Feb 2014 17:39


Return to dotConnect for Oracle