Problem with spherical vs ellipsoidal

Hi there,
I’m using Oracle Spatial to convert my GML to SDO_GEOMETRY. Initially, I used this syntax:
SDO_CS.TRANSFORM(SDO_UTIL.FROM_GML311GEOMETRY(sem_apis.unescape_rdf_value(geom)), 28992, 4326) as geom
When I view the data in APEX, there seems to be some distortion:
However, the original data appears correct in QGIS:

I believe the issue might be related to the functions I used. I came across this documentation link: Coordinate Systems (Spatial Reference Systems).
It seems there are certain details to consider when it comes to ellipsoidal versus spherical math during coordinate transformations, depending on the tools used.
I’m wondering what parameter to use for “USE_SPHERICAL”? Would it be like this?

SDO_CS.TRANSFORM(SDO_UTIL.FROM_GML311GEOMETRY(sem_apis.unescape_rdf_value(geom)), ‘USE_SPHERICAL’, 4326) as geom

or perhaps there is a better approach?
Thank you in advance for answering my question!

Kind regards,

Hi Maryam,

It’s been a looong time since I last used oracle, and there could be other issues at hand (for example: which projection is Apex using when viewing the data?), but I would not use USE_SPHERICAL. Because what that does, is that it will start to use sperical math instead of elli[soidal math for the transformaition - which is the wrong thing to use (see Oracle’s documentation).
It may be that Oracle’s transformation parameters are not correct, also see this Stack Overflow post. Even though that is a few years old, the latest answer is pretty recent - it may help you.
Then the next thing is: what is Apex using to show your data? What projection is that base map that you display under the data in? The distortion could also be because of the base map being slightly misplaced.

And last, but not least: Why are you transforming to EPSG:4326? Is that necessary? Wouldn’t it be easier to use either EPSG:28992 in Oracle, thereby removing the need for transformation?


1 like

Hi Stefan,

Thank you for your answer!
You are right! USE_SPHERICAL was completely wrong approach.
I removed SDO_CS.TRANSFORM and 4326 and keep SDO_UTIL.FROM_GML311GEOMETRY(sem_apis.unescape_rdf_value(geom) But nothing changed.
I am using turtles and SPARQL query, here is my query:
SELECT URI, label, manhole_elev, (SDO_UTIL.FROM_GML311GEOMETRY(sem_apis.unescape_rdf_value(geom))) as geom from table(sem_match(’
PREFIX gwsw:
?URI ?label ?point (SUBSTR(STR(?geo), 0, STRLEN(STR(?geo))) AS ?geom) (SUBSTR(STR(?value_hight), 0, STRLEN(STR(?value_hight))) AS ?manhole_elev)
?URI gwsw:isPartOf ?s .
?s a gwsw:Rioleringsgebied .
?s rdfs:label ?label .
?URI gwsw:hasAspect ?benode.
?benode gwsw:hasPart ?uri1.
?uri1 gwsw:hasConnection ?t.
?t gwsw:hasAspect ?o.
?t gwsw:hasConnection ?hoog.
?hoog gwsw:hasAspect ?hight.
?hight gwsw:hasValue ?value_hight.
?o a gwsw:Punt.
?o gwsw:hasValue ?geo.
FILTER (?label = “vlijmen”)
}’,sem_models(‘HEUDSEN_GML_CORRECTED’), null, null, null, null, ’ ', null, null,‘MARYAMSAJJADIAN’, ‘RDF_NETWORK’));

I changed the GML format to WKT using python code and created a table. Then, I inserted all data inside of database, then I inserted user_sdo_geom_metadata, but I have an error for the spatial index:

Hi Maryam,
I’m a bit confused here, it’s not entirely clear what you have and what you are trying to do, and how you are trying to achieve what it is you’re trying to achieve. First things first:

1 When you get ORA-13365 when trying to create an index on an sdo_geometry column, that means that the SRID in the user_sdo_geom_metadata and the SRID from (at least one of) the actual geometry in that column is not the same. You can check that with the following sql:

select * from user_sdo_geometry_metadata where table_name=‘manhole_wkt’;
select mh.geom.sdo_srid, count(*) from manhole_wkt mh group by mh.geom.sdo_srid;

That will show you which SRID you’ve set for the metadata, and which SRID is actually set on the geometry (and if you accidentally have different SRID’s on your geoetry, it will show you which ones and how many).

2 I have no idea what this means:

When using Oracle, I always used SQL Plus and SQL Developer, since they are free and come with the product. So I can’t help you with thoseI’m afraid.

3 I also do not understand this: In the SQL you show, you are using sdo_util.from_gml311geometry. Then you say “I changed the GML format to WKT using python code and created a table. Then, I inserted all data inside of database”. If your data is WKT, the use of sdo_util.from_gml311geometry is not going to do anything, you should use sdo_util.from_wktgeometry.

4 you are using sem_apis.unescape_rdf_value(geom) in your call to sdo_util.from_gml311geometry. I don’t know what sem_apis.unescape_rdf_value does, but it seems like you are feeding sdo_geometry into that function, which you then feed into sdo_util.from_gml311geometry. That makes no sense. sdo_util.from_gml311geometry expects a well-formed gml string, sem_apis.unescape_rdf_value also expects a varchar2 as input, yet you seem to feed it sdo_geometry? What are you trying to achieve with that sql?

5 It seems that you already have sdo_geometry in your table, since you said that one of your first screenshots is Apex showing the data. If you already have converted the data into sdo_geometry, there is no need at all to use any sdo_util.from_$$$$$ functions if you want to select that geometry.

So what does your table look like? What are the results from desc manhole_wkt? And what are the results from select * from manhole_wkt where rownum<5; ? And, most important: What is it you are trying to achieve?

Hi Stefan,
Thanks for your answer!
The problem is with sdo_util.from_wktgeometry() function because after conversion, SRID is empty.

2- I am also using Oracle developer and inside of sql you can have SPARQL query using semi_match function.
3- I have my dataset in a turtle file with geometries in gml format .
The results of conversion form GML is not accurate and manhole are displaced. because there are some characters in GML.
So, I changed the GML to KWT.
The problem with WKT, there is an error for spatial index.
The problem is 2001, NULL, MDSYS.SDO_POINT_TYPE and NULL must be 4326
MDSYS.SDO_GEOMETRY(2001, NULL, MDSYS.SDO_POINT_TYPE(5.19717930687861, 51.6950070913828, NULL), NULL, NULL)

There is a problem during conversion.
I can see datasets in APEX but I get error if I activate the spatial index:

If I want to display data in spatial studio, I need Spatial index otherwise, I will get this error:

Kind regards,

Now you are confusing things. Oracle does not store WKT in sdo_geometry, it is after importing WKT to sdo_geometry that the SRID stays null. That happens because WKT generally does not say anything about the SRID. It’s easy enough to fix:

update manhole_wkt mh set mh.geom.sdo_srid = 4326;

But you must make sure that the user_sdo_metadata also has 4326 as SRID. Once those things are fixed, you should be able to create a spatial index. Be aware that the object index_manhole_wkt will already exist, even if the index creation failed! So you will have to drop the index first, possibly using the force option.

If you have to work with Oracle Spatial a lot, it helps if you read the concept-chapter in the documentation: Conceptual and Usage Information

As I said, it’s been at least 6 years or more since I last did any work with Oracle Spatial, so I’m not entirely up-to-date anymore - but you can always ask :wink:

Dit topic is 180 dagen na het laatste antwoord automatisch gesloten. Nieuwe antwoorden zijn niet meer toegestaan.