0

We have polygons stored in Oracle SRID 8307 (geodetic datatype), but need to perform various operations on this layer in a 2d cartesian/non-geodetic system based on the geographic coordinates.

I.e. this would correspond to casting from "geography" to "geometry" datatype SRID 4326 in Postgis.

My question basically is: What would the equivalent of "select geom::geometry from ..." (geom being a 'geography' datatype) in postgis be in Oracle? (ref: http://postgis.net/workshops/postgis-intro/geography.html#casting-to-geometry )

I've found that converting the data to a spatial type with SRID set to NULL, works. However this seems like a "duct tape" solution, not exactly elegant, so I am hoping that there is a "clean" alternative?

Improve this question
7
  • Why do you require a different SRID? Have you tried to use appropriate functions and encountered an error? Setting the SRID to NULL is always an error.
    – Vince
    Commented May 17, 2018 at 23:03
  • Yes what we really need is to perform operations in 2d cartesian latitude/longitude space rather than on a geodetic sphere, for a particular set of queries. Similar to casting between 'geometry' and 'geography' datatypes in postgis, where as a standard we might use EPSG 4326 for both. We need to answer whether e.g. one polygon is contained by another strictly in terms of whether the sets of lat/lon coordinates are within the boundaries of the other. So leaving out geodetic/great circle lines would greatly simplify things. But I have not found a way to do this in Oracle except set SRID to null.
    – Lars
    Commented May 18, 2018 at 1:31
  • No, that is nothing like casting between an 4326 geometry and a geography. You need to actually project the geometry, as ST_Transform does.
    – Vince
    Commented May 18, 2018 at 2:59
  • Thanks for taking the time to answer and sorry if I'm not making myself clear. My question basically is: What would the equivalent of "select geom::geometry from ..." (geom being a 'geography' datatype) in postgis be in Oracle? (ref: workshops.boundlessgeo.com/postgis-intro/… )
    – Lars
    Commented May 18, 2018 at 10:31
  • Please Edit the question to make clarifications. I do not recommend using PostGIS documentation to write Oracle SQL. It is a mistake to assume that there are direct equivalents for all PostgreSQL techniques in other database software.
    – Vince
    Commented May 18, 2018 at 10:50

1 Answer 1

Reset to default
1

I think the NULL srid is the simplest way to go. I mean you could go to the effort to create a custom projection that forces simple planar handling but it wouldn't treat the geometry any different than NULL. Oracle Spatial was designed to provide seamless handling of geodetic data. It's not uncommon that folks using Oracle Spatial are oblivious if their calculations are happening on a spheroid or not. So there is no special switch that I know of to force planar handling other than using a NULL srid.

Now the duct tapey bit might be that once your records have a NULL srid you can no longer execute SDO_CS.TRANSFORM against them. But then you know the original SRID and can always manually swap it back if you need to transform the data.

I think you are saying that the originators of your data decided back in the day to use a planar interpretation of geodetic coordinates to define and manage their dataset. That may have been a poor design decision - it would have been better to choose a nice appropriate projection for the area or just go all geodetic. I think that is where most of the negative feedback to your question comes from and I don't necessarily disagree. It seems duct-tapey simply as the designers of Oracle Spatial never thought it a good idea to do this. They'd have said that you have geodetic coordinates representing real world locations on the globe and why would you then not want to calculate on the spheroid? But I also understand we often have to deal with what is given.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.