[Closed] OracleSpatialFunctions issues
Posted: 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 :
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 :
The SdoRelate works fine... The two others have different errors :
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 :
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
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;
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();
}
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)
Help me, i'm going mad
Nicolas