[Solved] Geographic or projected data based on SRID in Oracle

EverSQL Database Performance Knowledge Base

Geographic or projected data based on SRID in Oracle

Database type:

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.

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
CREATE INDEX table_name_idx_rownum ON TABLE_NAME (rownum);
The optimized query:
SELECT
        a.COLUMN_NAME.SDO_SRID 
    FROM
        TABLE_NAME a 
    WHERE
        a.rownum = 1

Related Articles



* original question posted on StackOverflow here.