[Closed] OracleSpatialFunctions issues

[Closed] OracleSpatialFunctions issues

Postby NicolasW » Mon 06 Jan 2014 13:48

Hi,

I'm trying to use the spatial functions and I've some issues...

I've created a table in database with a SDO_GEOMETRY column, add the correct metadata in USER_SDO_GEOM_METADATA and created a spatial index. Here is the code :
Code: Select all
CREATE TABLE CITIES (
  ID NUMBER(9) NOT NULL,
  GEOMETRY SDO_GEOMETRY NULL,
  NAME VARCHAR(200 CHAR),
  CONSTRAINT PK_CITIES PRIMARY KEY (ID)
);

INSERT INTO CITIES VALUES (1, SDO_GEOMETRY('POINT (2.3508 48.8567)', 4326), 'Paris');
INSERT INTO CITIES VALUES (2, SDO_GEOMETRY('POINT (4.8422 45.7597)', 4326), 'Lyon');
INSERT INTO CITIES VALUES (3, SDO_GEOMETRY('POINT (5.37 43.2964)', 4326), 'Marseille');
INSERT INTO CITIES VALUES (4, SDO_GEOMETRY('POINT (-4.4856 48.3908)', 4326), 'Brest');
INSERT INTO CITIES VALUES (5, SDO_GEOMETRY('POINT (-1.1517 46.1591)', 4326), 'La Rochelle');
INSERT INTO CITIES VALUES (6, SDO_GEOMETRY('POINT (3.0633 50.6372 )', 4326), 'Lille');
INSERT INTO CITIES VALUES (7, SDO_GEOMETRY('POINT (1.9090 47.9025 )', 4326), 'Orleans');
INSERT INTO CITIES VALUES (8, SDO_GEOMETRY('POINT (1.444 43.6045 )', 4326), 'Toulouse');
INSERT INTO CITIES VALUES (9, SDO_GEOMETRY('POINT (0.1984 48.0077 )', 4326), 'Le Mans');
INSERT INTO CITIES VALUES (10, SDO_GEOMETRY('POINT (4.0347 49.2628 )', 4326), 'Reims');
INSERT INTO CITIES VALUES (11, SDO_GEOMETRY('POINT (-0.5783 44.8386 )', 4326), 'Bordeaux');
INSERT INTO CITIES VALUES (12, SDO_GEOMETRY('POINT (7.7486 48.5844)', 4326), 'Strasbourg');

INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
VALUES ('CITIES', 'GEOMETRY', SDO_DIM_ARRAY(SDO_DIM_ELEMENT('LAT', -90.0, 90.0, 0.5),  SDO_DIM_ELEMENT('LONG', -180.0, 180.0, 0.5)), 4326);

CREATE INDEX CITIES_SIDX1 ON CITIES(GEOMETRY) INDEXTYPE IS MDSYS.SPATIAL_INDEX;


All work great... When I use SqlDeveloper, I can use SDO_RELATE, SDO_NN queries without prob.

I've a console app in VS 2013 with an edmx model that contain only this table. I've referenced the Devart providers, NetTopologySuite 1.13.1 (and GeoApi).
Here is my program.cs code :
Code: Select all
        static void Main(string[] args)
        {
            using (var context = new POCGeometryModel())
            {
                var monitor = new OracleMonitor() { IsActive = true };
                context.SetSpacialServiceType(SpatialServiceType.NetTopologySuite);

                var wktParis = "SRID=4326;POINT(2.3508 48.8567)";
                var wktParisPoly = "SRID=4326;POLYGON((2 48, 3 48, 3 49, 2 49, 2 48))";

                var parisGeom = DbGeometry.FromText(wktParis);
                var parisPoly = DbGeometry.FromText(wktParisPoly);
                if (parisPoly != null)
                {
                    Console.WriteLine("Performing a SDO_RELATE on CITIES where CITY in a {0}", wktParisPoly);
                    try
                    {
                        var query1 = from c in context.CITIES
                                     where OracleSpatialFunctions.SdoRelate(c.GEOMETRY, parisPoly, "mask=INSIDE") == OracleSpatialFunctions.True
                                     select c;
                        foreach (var c in query1)
                        {
                            Console.WriteLine("ID: {0}, NAME: {1}, GEOMETRY: {2}", c.ID, c.NAME, c.GEOMETRY.AsText());
                        }
                    }
                    catch (Exception e)
                    {
                        while (e != null)
                        {
                            Console.WriteLine(e.Message);
                            e = e.InnerException;
                        }
                    }
                }
                if (parisGeom != null)
                {
                    try
                    {
                        var withinDistanceParameters = string.Format("distance={0} unit={1}", 200, "KM");
                        Console.WriteLine("Performing a SDO_WITHIN_DISTANCE on CITIES where CITY is near {0} with parameters {1}", parisGeom, withinDistanceParameters);

                        var query2 = from c in context.CITIES
                                     where OracleSpatialFunctions.SdoWithinDistance(c.GEOMETRY, parisGeom, withinDistanceParameters) == OracleSpatialFunctions.True
                                     select c;
                        foreach (var c in query2)
                        {
                            Console.WriteLine("ID: {0}, NAME: {1}, GEOMETRY: {2}", c.ID, c.NAME, c.GEOMETRY.AsText());
                        }
                    }
                    catch (Exception e)
                    {
                        while (e != null)
                        {
                            Console.WriteLine(e.Message);
                            e = e.InnerException;
                        }
                    }
                }
                if (parisGeom != null)
                {
                    Console.WriteLine("Performing a SDO_NN on CITIES where CITY NEAR {0}", wktParis);

                    try
                    {
                        var query3 = from c in context.CITIES
                                     where OracleSpatialFunctions.SdoNn(c.GEOMETRY, parisGeom, "sdo_num_res=5") == OracleSpatialFunctions.True
                                     select c;
                        foreach (var c in query3)
                        {
                            Console.WriteLine("ID: {0}, NAME: {1}, GEOMETRY: {2}", c.ID, c.NAME, c.GEOMETRY.AsText());
                        }
                    }
                    catch (Exception e)
                    {
                        while (e != null)
                        {
                            Console.WriteLine(e.Message);
                            e = e.InnerException;
                        }
                    }
                }
            }
            Console.ReadLine();
        }


The SdoRelate works fine... The two others have different errors :
Code: Select all
Performing a SDO_RELATE on CITIES where CITY in a SRID=4326;POLYGON((2 48, 3 48,
3 49, 2 49, 2 48))
ID: 1, NAME: Paris, GEOMETRY: POINT (2.3508 48.8567)
Performing a SDO_WITHIN_DISTANCE on CITIES where CITY is near SRID=4326;POINT (2.3508 48.8567) with parameters distance=200 unit=KM
An error occurred while executing the command definition. See the inner exception for details.
Unable to cast object of type 'System.String' to type 'System.Data.Entity.Spatial.DbGeography'.
Performing a SDO_NN on CITIES where CITY NEAR SRID=4326;POINT(2.3508 48.8567)
An error occurred while reading from the store provider's data reader. See the inner exception for details.
ORA-13249: SDO_NN cannot be evaluated without using index
ORA-06512: at "MDSYS.MD", line 1723
ORA-06512: at "MDSYS.MDERR", line 17
ORA-06512: at "MDSYS.PRVT_IDX", line 9


For the SdoDistance, I've no clue. For the SdoNN, i've read that the function need a hint to know that the function must use the spatial index... The weird part is that the query generated in SQL is like this :
Code: Select all
SELECT
"Extent1".ID,
(CASE WHEN "Extent1".GEOMETRY IS NULL THEN NULL ELSE 'SRID=' || NVL("Extent1".GEOMETRY.SDO_SRID, '0') || ';' || SDO_UTIL.TO_WKTGEOMETRY("Extent1".GEOMETRY) END) AS GEOMETRY,
"Extent1".NAME
FROM MYLEAD.CITIES "Extent1"
WHERE ((SDO_NN("Extent1".GEOMETRY, SDO_GEOMETRY(:p__linq__0, :p__linq__0_srid), 'sdo_num_res=5')) = 'TRUE') AND (SDO_NN("Extent1".GEOMETRY, SDO_GEOMETRY(:p__linq__0, :p__linq__0_srid), 'sdo_num_res=5') IS NOT NULL)

The SDO_NN functions appears twice... The second is not necessary and if I delete it and replace the parameters in sqldeveloper it works fine...

Help me, i'm going mad :)

Nicolas
Last edited by NicolasW on Thu 09 Jan 2014 17:28, edited 1 time in total.
NicolasW
 
Posts: 5
Joined: Mon 06 Jan 2014 13:27

Re: OracleSpatialFunctions issues

Postby NicolasW » Tue 07 Jan 2014 09:52

I've found a workaround but the problem is still there...

The work around is to use a IDbCommandInterceptor with EF6. The IDbCommandInterceptor check with RegExx if the DbCommand.CommandText contains SDO_NN(xxxx) = 'TRUE' AND SDO_NN(xxx) IS NOT NULL and delete from the request the AND SDO_NN(xxx) IS NOT NULL.

The request work fine like this but I still not understand why, when I use OracleSpatialFunctions, the where clause always contains the oracle function twice (one for = 'TRUE' that is the correct behavior and one for IS NOT NULL that is not expected).

Is this an intended behavior, a bug or a configuration problem ?
NicolasW
 
Posts: 5
Joined: Mon 06 Jan 2014 13:27

Re: OracleSpatialFunctions issues

Postby Shalex » Wed 08 Jan 2014 13:15

All tests are succeded in our environment with your code using dotConnect for Oracle v8.1.65.

This SQL was generated for the SdoNN:
Code: Select all
SELECT
"Extent1".ID,
(CASE WHEN "Extent1".GEOMETRY IS NULL THEN NULL ELSE 'SRID=' || NVL("Extent1".GEOMETRY.SDO_SRID, '0') || ';' || SDO_UTIL.TO_WKTGEOMETRY("Extent1".GEOMETRY) END) AS GEOMETRY,
"Extent1".NAME
FROM MYLEAD.CITIES "Extent1"
WHERE (SDO_NN("Extent1".GEOMETRY, SDO_GEOMETRY(:p__linq__0, :p__linq__0_srid), 'sdo_num_res=5')) = 'TRUE'

Please give us the following information so that we can identify the reason of the difference in behaviour:
1) the exact version and edition of your Oracle server and Oracle client
2) the exact version of EntityFramework you have installed in your project (you can check it in packages.config)
3) specify all non-default options of EF-provider

JIC: Our blog article about Entity Framework Spatials support is available at http://blog.devart.com/enhanced-entity-framework-spatials-support-for-oracle-mysql-and-postgresql.html.
We recommend using Entity Developer (the Devart Entity Model item, *.edml) instead of EDM Wizard/Designer (the ADO.NET Entity Data Model, *.edmx) because it has a lot of advantages.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Re: OracleSpatialFunctions issues

Postby NicolasW » Wed 08 Jan 2014 13:47

Hi,

Thanks for your reply.

Oracle server version : 11.2.0.1
Oracle client version : 12.1.0.1
Entity Framework : 6.0.2

All the EF Provider configuration is standard. I've just registred the devart provider. In my code, I only change the spatial service type.
Code: Select all
OracleEntityProviderConfig.Instance.SpatialOptions.SpatialServiceType = spatialServiceType;
NicolasW
 
Posts: 5
Joined: Mon 06 Jan 2014 13:27

Re: OracleSpatialFunctions issues

Postby Shalex » Wed 08 Jan 2014 15:17

Tell us also editions of your Oracle server and Oracle client.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Re: OracleSpatialFunctions issues

Postby NicolasW » Wed 08 Jan 2014 15:25

I used virtual machine so for the oracle server, I've tried on :
- Oracle EE 11g release 2
- Oracle Express 12g

And the client is the client installed with the oracle express 12g.

I'm not a expert with Oracle but if you tell me how to get better information, I'll give them to you.
NicolasW
 
Posts: 5
Joined: Mon 06 Jan 2014 13:27

Re: OracleSpatialFunctions issues

Postby Shalex » Thu 09 Jan 2014 11:11

We have reproduced the problem. This is a bug in EntityFramework.dll v6.0.2. It is fixed in the newer ("6.1.0-alpha1") version. You can install it in your project with the following command in Package Manager Console:

Code: Select all
PM> install-package EntityFramework -pre

Notify us about the result.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

[Closed] OracleSpatialFunctions issues

Postby NicolasW » Thu 09 Jan 2014 17:27

I've just tried and it woooorkssss :)

Thanks, it's an EF6 bug...

Nicolas
NicolasW
 
Posts: 5
Joined: Mon 06 Jan 2014 13:27


Return to dotConnect for Oracle