Devart Forums

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;

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();
        }

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

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)

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'

Code: Select all

OracleEntityProviderConfig.Instance.SpatialOptions.SpatialServiceType = spatialServiceType;

Code: Select all

PM> install-package EntityFramework -pre