Geographic or projected data based on SRID in Oracle

Question

Background: My application needs to display the MBR of the spatial data (geometry) stored in Oracle. For this, I'm currently using Oracle's SDO_AGGR_MBR() function, but it is very slow. On researching a little, I found a function SDO_TUNE.EXTENT_OF() which also computes MBR and is much faster than SDO_AGGR_MBR. It has 2 problems though. It works only with 2D data in projection coordinates. To tap into the performance benefits of EXTENT_OF, I decided to use it for projected data and fallback to SDO_AGGR_MBR for geographic data.

The Problem: I started out with an assumption that all data with SRID between 4000 to 5000 is geographic but that is not entirely true. I found a table/view named MDSYS.CS_SRS which stores the coordinate system information.

I'm planning to find the SRID using query:

select a.COLUMN_NAME.SDO_SRID from TABLE_NAME a where rownum = 1;

and then using this SRID to query MDSYS.CS_SRS to find out whether the data is geographic or projected. It has column named WKTEXT whose rows start with either PROJCS or GEOGCS.

I could prototype this and it seems to work but entirely confident this is the right approach. The query above fetches the SRID of the first row of the data. I don't know if SRID can be different in a single column. Another assumption I'm making is the text in WKTEXT column. I'll be in a lot of trouble if it's not PROJCS/GEOGCS in all cases and if the values change between different releases of Oracle. In fact, right now, I'm just assuming that PROJCS means projection CS and GEOGCS means geographic CS and I'm not sure if it's right..

I wonder if there's an easier way to find out whether the spatial data in an Oracle DB is projection or geographic.

Answer 1

You did not say which version of the database you are looking at. I am assuming 10gR2 or later.

The easiest is to check table SDO_COORD_REF_SYS:

SQL> select srid, coord_ref_sys_kind from sdo_coord_ref_sys where srid in (4326, 4327, 27700, 7405);

      SRID COORD_REF_SYS_KIND
---------- --------------------
      4326 GEOGRAPHIC2D
      4327 GEOGRAPHIC3D
      7405 COMPOUND
     27700 PROJECTED

4 rows selected.