Go to content Go to navigation and search

Home

Current Oracle Spatial Blog Articles

    GeoRaptor 4.x Update 2 (Project has Stalled)
    Tip: Drop that Spatial Index!
    Convert Single Geometry to Multi-part Geometry in Oracle Spatial
    Optimized Rectangle to 5 Point Polygon
    Centroid Package now supports Y ordinate seeding
    Convert GeoJSON document to Sdo_Geometry objects
    Implementation Of Travelling Salesman Problem
    Create Polygon From Bearings And Distances
    Function That Returns a Compass Point From a Whole Circle Bearing
    Playing around with Centroids by using different seed values
    Simple Oracle C Sprintf or Java String.format
    Some Oriented Point Functions
    Extracting Inner Rings Changed Ordinate Ordering: A Trap For Players Who Don't Read Documentation!
    PLS-00306: wrong number or types of arguments in call to 'SDO_GEOMETRY'
    Converting Google Earth Formatted Longitude/Latitude points to decimal degrees
    Oracle Business Intelligence Warehousing and Analytics - Spatial Summit
    How far inside, is inside? Measuring actual distance.
    Noding and building a polygon from single, overlapping linestrings
    Analyzing Spatial Query Performance Improvements in Oracle Spatial and Graph 12c Through Cross-Vendor Comparison
    ST_VertexN / ST_PointN - Extracting a specific point from any geometry
    Convert Single Point stored in SDO_ORDINATES to SDO_POINT_TYPE
    Aggregate APPEND Islands and XOR polygons
    Circular Arcs in Geodetic Polygons
    Some SDO_GEOMETRY/DIMINFO handling functions
    Applying And Extending Oracle Spatial - Book Released
    Changing all DIMINFO sdo_tolerance values for all metadata records in one go.
    Building Polygons from Incomplete Linestrings using ST_PolygonBuilder
    Computing Cardinal Directions to nearby geometries
    Intersecting two aggregated polygon layers with SC4O
    Spatial and Oracle 12c
    Update Triggers and SDO_GEOMETRY Equality
    Duplicate Geometry data and Data Models
    CENTROID package update
    How to calculate cumulative length of a linestring
    Useful Package of Wrapper Functions for Sdo_Util.AffineTransforms
    Compute Location from known Lat/Long point using delta easting and northing in miles
    SDO_AGGR_SET_UNION
    Sorting SDO_GEOMETRY data using the ORDER BY clause of a SELECT statement
    Creating linestrings from points
    Rounding Coordinates or Ordinates in SDO_GEOMETRY
    Effects of Sdo_Geometry Ordinate Precision on Performance
    Effects of Sdo_Geometry Ordinate Precision on Storage
    The Spatial filtering of geometries: The effect of tolerances on relationships
    Application of Delaunay Triangulation and Inverse Distance Weighting (IDW) in Oracle for Soils Interpolation
    Selecting all SDO_GTYPE values for all tables/sdo_geometry columns in a schema
    CENTROID package - Tips for Use
    Announcing the Spatial Companion For Oracle (SC4O)
    Filtering Rings (Oracle Spatial)
    Splitting a polygon using one or more linestrings
    isValid, isSimple, Dimension and CoordDim methods for SDO_Geometry
    Line Merging or Collecting lines together: ST_LineMerger
    ST_DeleteVertex for Oracle SDO_Geometry based on Jaspa/JTS
    3D/4D and SRID aware Conversion functions for SDO_Geometry: WKT and EWKT
    Topological vs Non-Topological Simplification/Generalization of Aggregated Area Geometies in Oracle
    Filtering very short linestrings via bitmap function index
    CENTROID For Oracle
    Gridding a sdo_geometry line/polygon object (Oracle)
    Finding centre and radius of a circular geometry
    Constraining geometry type for sdo_geometry column in a table.
    CASE Statements and SDO_GEOMETRY
    The Power of Constraints and Indexes for Spatial Constraints: stopping duplicate points
    Replacement for SDO_GEOM.RELATE - ST_Relate based on JTS
    Changing Oracle Spatial Index Parameters on existing index
    Writing Excel Spreadsheets files from within the Oracle database using Java and PL/SQL
    Writing xSV (eg csv) files from within the Oracle database using Java and PL/SQL
    A simple spike finder for Spatial/Locator
    JTS Java class compilation for 11g and above
    Random Spatial Search Procedure
    Geometry Snapping using JTS in Oracle
    Exposing JTS's MinimumBoundingCircle functionality
    Exposing JTS's Densifier functionality
    Using JTS's Comparison Functions - HausdorffSimilarityMeasure & AreaSimilarityMeasure with SDO_GEOMETRY
    Free JTS-based Area/Length Functions
    Handy way of systematically fixing polygon geometries with 13349 and other errors
    Standalone CENTROID package now available for download
    Free Union, Intersection, Xor and Difference Functions for Oracle Locator - Part 4 Processing Geodetic data
    Configurable Buffer: JTS and Oracle
    Free Union, Intersection, Xor and Difference Functions for Oracle Locator - Part 3
    Free Union, Intersection, Xor and Difference Functions for Oracle Locator - Part 2
    Free Union, Intersection, Xor and Difference Functions for Oracle Locator - Part 1
    Building Lines into Polygons in Oracle Locator / Spatial
    Finding Intersection Points between Line and Polygon
    SDO2GeoJSON
    Free version of sdo_length
    Alternative to my SQL based GetNumRings function
    External Tables and SDO_Geometry data.
    layer_gtype keyword issue when indexing linear data on 11g
    String Tokenizer for Oracle
    Free Aggregate Method for Concatenating 2D Lines in Oracle Locator 10g
    Reducing 5 Vertex Polygon to Optimized Rectangle
    Square Buffer
    Converting decimal seconds to string
    SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT - 13356 Issues
    Valid conversion unit values for Oracle sdo_geom.sdo_length()
    Removing Steps in Gridded Vector Data - SmoothGrid for Oracle
    Oracle Spatial DISJOINT search/filtering
    Creating SDO_Geometry from geometric data recorded in the columns of a table
    Concave Hull Geometries in Oracle 11gR2
    Projecting SDO_GEOM_METADATA DIMINFO XY ordinates
    Instantiating MDSYS.VERTEX_TYPE
    New PL/SQL Packages - Rotate oriented point
    GeoRaptor Development Team
    Fast Refreshing Materialized View Containing SDO_GEOMETRY and SDO_GEOM.SDO_AREA function
    Performance of PL/SQL Functions using SQL vs Pure Code
    Implementing the BEST VicGrid Projection in Oracle 10gR2
    Making Sdo Geometry Metadata Update Generic Code
    ORA-13011 errors when using SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT()
    Extract Polygons from Compound Polygon
    Detecting sdo_geometries with compound (3-point Arcs) segments
    GEOMETRY_COLUMNS for Oracle Spatial
    Convert GML to SDO_Geometry in Oracle 10gR2
    Spatial Sorting of Data via Morton Key
    Swapping Ordinates in an SDO_GEOMETRY object
    New To_3D Function
    Extend (Reduce/Contract/Skrink) Function for Oracle
    Loading and Processing GPX 1.1 files using Oracle XMLDB
    Loading Spatial Data from an external CSV file in Oracle
    Calling the Oracle Spatial shapefile loader from within the Oracle database itself
    Implementing SDO_VertexUpdate/ST_VertexUpdate for Oracle
    Implementing SDO_RemovePoint/ST_RemovePoint for Oracle
    Implementing SDO_AddPoint/ST_AddPoint for Oracle
    ESRI ArcSDE Exverted and Inverted Polygons and Oracle Spatial
    Funky Fix Ordinates By Formula
    Implementing a SetPoint/ST_SetPoint function in Oracle
    Implementing an ST_SnapToGrid (PostGIS) function for Oracle Spatial
    Generating random point data
    Implementing an Affine/ST_Affine function for Oracle Spatial
    Implementing a Scale/ST_Scale function for Oracle Spatial
    Implementing a Parallel/ST_Parallel function for linestring data for Oracle Spatial
    Implementing a Rotate/ST_Rotate function for Oracle Spatial
    Limiting table list returned when connecting to Oracle Database using ODBC
    ST_Azimuth for Oracle: AKA Cogo.Bearing
    Implementing a Translate/ST_Translate/Move function for Oracle Spatial
    Elem_Info_Array Processing: An alternative to SDO_UTIL.GetNumRings and querying SDO_ELEM_INFO itself
    Minumum Bounding Rectangle (MBR) Object Type for Oracle
    How to extract elements from the result of an sdo_intersection of two polygons.
    How to restart a database after failed parameter change
    Fixing failed spatial indexes after import using data pump
    generate_series: an Oracle implementation in light of SQL Design Patterns
    Multi-Centroid Shootout
    Oracle Spatial Centroid Shootout
    On the use of ROLLUP in Oracle SELECT statements
    Surrounding Parcels
    Spatial Pipelining
    Using Oracle's SDO_NN Operator - Some examples
    Converting distances and units of measure in Oracle Locator
    Split Sdo_Geometry Linestring at a known point
    Forcing an Sdo_Geometry object to contain only points, lines or areas
    Unpacking USER_SDO_GEOM_METADATA's DIMINFO structure using SQL
    Generating multi-points from single point records in Oracle Spatial
    Object Tables of Sdo_Geometry
    Oracle Locator vs Oracle Spatial: A Reflection on Oracle Licensing of the SDO_GEOM Package
    FAST REFRESHing of Oracle Materialized Views containing Sdo_Geometry columns
    Australian MGA/AMG Zone Calculation from geographic (longitude/latitude) data
    Loading Shapefiles (SHP) into Oracle Spatial
    Oracle Spatial Mapping and Map Rendering Performance Tips
    The significance of sdo_lb/sdo_ub in USER_SDO_GEOM_METDATA: Do I need it?
    Oracle Spatial Forum - Melbourne April 2007
    Layer_GTypes for spatial indexes
    Oracle's SQL/MM Compliant Types
    Tips and Tricks

Search

    Browse

RSS / Atom

Email me

textpattern

Creative Commons License
All Blog Articles, Data Models and Free Source Code by Simon Greener, The SpatialDB Advisor is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.

Converting distances and units of measure in Oracle Locator

Friday February 01 2008 at 04:50

Ever wanted to know what a decimal degree was in meters, nautical miles or feet?

It is something I often need to do in Oracle so I decided to do something about creating a function that would do this. This involved me having to hack my way into some of the mdsys coordinate system and distance units tables but I managed to come up with something that I have integrated in to the GEOM package of the free PL/SQL code available for download from my website.

The first table that we need to look at is the mdsys.SDO_DIST_UNITS table. This table is described as follows:

codesys@XE> desc mdsys.sdo_dist_units
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ------------
 SDO_UNIT                                                                         VARCHAR2(80)
 UNIT_NAME                                                               NOT NULL VARCHAR2(80)
 CONVERSION_FACTOR                                                                NUMBER

Let’s have a look at some of the entries of this table (I ignore NULL sdo_unit names in this article and in my code):

codesys@XE> select substr(sdo_unit,1,25) as unit,
  2         substr(unit_name,1,30) as name,
  3         conversion_factor
  4    from mdsys.sdo_dist_units
  5*  where sdo_unit is not null
codesys@XE> /
UNIT                      NAME                           CONVERSION_FACTOR
------------------------- ------------------------------ -----------------
M                         Meter                                          1
METER                     Meter                                          1
KM                        Kilometer                                   1000
KILOMETER                 Kilometer                                   1000
CM                        Centimeter                                   .01
CENTIMETER                Centimeter                                   .01
MM                        Millemeter                                  .001
MILLIMETER                Millemeter                                  .001
MILE                      Mile                                    1609.344
NAUT_MILE                 Nautical Mile                               1852
SURVEY_FOOT               U.S. Foot                              .30480061
FOOT                      Foot (International)                       .3048
INCH                      Inch                                       .0254
YARD                      Yard                                       .9144
CHAIN                     Chain                                    20.1168
ROD                       Rod                                       5.0292
LINK                      Link                                  .201166195
MOD_USFT                  Modified American Foot                .304812253
CL_FT                     Clarke's Foot                         .304797265
IND_FT                    Indian Foot                           .304799518
LINK_BEN                  Link (Benoit)                         .201167651
LINK_SRS                  Link (Sears)                          .201167651
CHN_BEN                   Chain (Benoit)                        20.1167825
CHN_SRS                   Chain (Sears)                         20.1167651
IND_YARD                  Yard (Indian)                         .914398554
SRS_YARD                  Yard (Sears)                          .914398415
FATHOM                    Fathom                                    1.8288
British foot (1936)       British foot (1936)                   .304800749

From this we can see that CONVERSION_FACTOR expresses how long a unit (1) of measure (eg FOOT) is terms of meters (eg .3048)

OK, so now we know how to convert between any two units in this table. Here is a function that will do it.

create or replace Function Convert_Unit( p_from_unit in varchar2,
                         p_value     in number,
                         p_to_unit   in varchar2 )
    return number
  Is
    v_from_conversion_factor number;
    v_to_conversion_factor   number;
  Begin
    If ( p_value is null or p_from_unit is null or p_to_unit is null ) Then
        raise_application_error( codesys.Constants.c_i_null_parameter,
                                 codesys.Constants.c_s_null_parameter,False );
    End If;
    -- Check if p_from_unit exists by getting the necessary conversion factor to meters
    BEGIN
      -- Note that the conversion_factor is a conversion factor between v_from_unit and 1 metre.
      SELECT conversion_factor
        INTO v_from_conversion_factor
        FROM mdsys.sdo_dist_units
           WHERE sdo_unit = UPPER(p_from_unit)
             AND ROWNUM = 1;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          raise_application_error( codesys.Constants.c_i_invalid_unit,
                                   codesys.Constants.c_s_invalid_unit || ' ' || p_from_unit);
    END;
    -- Check if p_to_unit exists by getting the necessary conversion factor to meters
    BEGIN
      -- Note that the conversion_factor is a conversion factor between v_to_unit and 1 metre.
      SELECT conversion_factor
        INTO v_to_conversion_factor
        FROM mdsys.sdo_dist_units
           WHERE sdo_unit = UPPER(p_to_unit)
             AND ROWNUM = 1;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          raise_application_error( codesys.Constants.c_i_invalid_unit,
                                   codesys.Constants.c_s_invalid_unit || ' ' || p_to_unit);
    END;
    -- Do the computation
    RETURN ( p_value * v_from_conversion_factor ) / v_to_conversion_factor;
  End Convert_Unit;

And some examples on how to use this function (also a part of the GEOM package):

codesys@XE> select convert_unit('CHAIN',1,'LINK') from dual;
CONVERT_UNIT('CHAIN',1,'LINK')
------------------------------
                    100.000897

But what if we have data coded to a SRID and want to convert from its unit of measure to one of those in the mdsys.sdo_dist_units table? For example, you will note that there is no sdo_unit for ‘Decimal Degrees’ in which longitude/latitude data is expressed. We need to look at the definition of a SRID to find the conversion information we need. This is held in the MDSYS.CS_SRS table.

codesys@XE> desc mdsys.cs_srs
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ------------------
 CS_NAME                                                                          VARCHAR2(80)
 SRID                                                                    NOT NULL NUMBER(38)
 AUTH_SRID                                                                        NUMBER(38)
 AUTH_NAME                                                                        VARCHAR2(256)
 WKTEXT                                                                           VARCHAR2(2046)
 CS_BOUNDS                                                                        MDSYS.SDO_GEOMETRY

For example, let’s look at the projection information for WGS84 (SRID = 8307). I have formatted the output for readability.

codesys@XE> select wktext
  2    from mdsys.cs_srs
  3   where srid = 8307;
WKTEXT
-----------------------------------------------------------------------------------------------------------------------------------
GEOGCS [ "Longitude / Latitude (WGS 84)", 
         DATUM ["WGS 84", 
                SPHEROID ["WGS 84", 6378137, 298.257223563]
               ], 
         PRIMEM [ "Greenwich", 0.000000 ], 
         UNIT ["Decimal Degree", 0.01745329251994330]
       ]

OK, we can see that the unit of measure for this geographic coordinate system is our “Decimal Degrees”! But how do we access it?

In my downloadable PL/SQL code there is a string tokenizer which I can use as follows:

codesys@XE> SELECT rownum as id,
  2         substr(trim(both ' ' from replace(b.column_value,'"')),1,40) as token
  3    FROM mdsys.cs_srs a,
  4         TABLE(codesys.Tokenizer(a.wktext,',[]')) b
  5*  WHERE srid = 8307
codesys@XE> /
        ID TOKEN
---------- ----------------------------------------
         1 GEOGCS
         2 Longitude / Latitude (WGS 84)
         3 DATUM
         4 WGS 84
         5 SPHEROID
         6 WGS 84
         7 6378137
         8 298.257223563
         9 PRIMEM
        10 Greenwich
        11 0.000000
        12 UNIT
        13 Decimal Degree
        14 0.01745329251994330

For a projected coordinate system eg SRID 2964 “NAD27 / Alaska Albers” the tokens would be:

codesys@XE> SELECT rownum as id,
  2         substr(trim(both ' ' from replace(b.column_value,'"')),1,40) as token
  3    FROM mdsys.cs_srs a,
  4         TABLE(codesys.Tokenizer(a.wktext,',[]')) b
  5*  WHERE srid = 2964
codesys@XE> /
        ID TOKEN
---------- ----------------------------------------
         1 PROJCS
         2 NAD27 / Alaska Albers
         3 GEOGCS
         4 NAD27
         5 DATUM
         6 North American Datum 1927 (EPSG ID 6267)
         7 SPHEROID
         8 Clarke 1866 (EPSG ID 7008)
         9 6378206.4
        10 294.978698213905820761610537123195175418
        11 PRIMEM
        12 Greenwich
        13 0.000000
        14 UNIT
        15 Decimal Degree
        16 0.01745329251994328
        17 PROJECTION
        18 Alaska Albers (EPSG OP 15020)
        19 UNIT
        20 U.S. Foot
        21 .304800609601219202438404876809753619507

So, all we have to do is iterate over this list to extract the conversion unit for a coordinate system. In this case the second parameter of the SPHEROID entry (line 7 of the tokens for srid 8307). Since both (sdo_dist_unit and wktext) conversion units are expressed relative to meters we now have the ability to convert a distance expressed in the units of measure of a coodinate system to any unit of measure in the mdsys.sdo_dist_units table via a simple equation:

new_value = ( value x srid_conversion_factor ) / unit_conversion_factor

One other thing, for geographic coordinate systems (first parameter = GEOCS and not PROJCS) the value associated with the “Decimal Degrees” UNIT must be multiplied by the radius of the earth. Thus the equation would be:

new_value = ( value x srid_conversion_factor x radius_of_earth ) / unit_conversion_factor

So, we can now construct a function that will do this conversion (please excuse the length of this):

create or replace Function Convert_Distance( p_srid  in number,
                             p_value in number,
                             p_unit  in varchar2 := 'Meter' )
           Return number 
  Is
    v_unit                   varchar2(1000) := UPPER(p_unit);
    v_unit_conversion_factor number;
    v_srid_conversion_factor number;
    v_radius_of_earth        number := 6378137;  -- Default
    v_length                 number;
    v_srid                   mdsys.cs_srs.SRID%TYPE;
    v_token_id               number;
    v_token                  varchar2(4000);
    v_geocs                  boolean;
    cursor c_cs_tokens(p_srid in number)
    Is
       select rownum as id,
              substr(trim(both ' ' from replace(b.column_value,'"')),1,40) as token
         from mdsys.cs_srs a,
              table(codesys.Tokenizer(a.wktext,',[]')) b
         where srid = p_srid;
  Begin
    If ( p_srid is null ) Then
        -- Normally Oracle assumes a NULL srid is planar but 
        -- this could be planar feet, or meters etc so throw an error
        raise_application_error( codesys.Constants.c_i_null_srid,
                                 codesys.Constants.c_s_null_srid,False );
    End If;
    If ( p_value is null ) Then
        raise_application_error( codesys.Constants.c_i_null_parameter,
                                 codesys.Constants.c_s_null_parameter,False );
    End If;
    -- Check if p_unit exists by getting the necessary conversion factor to meters
    BEGIN
      -- Note that the conversion_factor is a conversion factor between v_unit and 1 metre.
      SELECT conversion_factor 
        INTO v_unit_conversion_factor
        FROM mdsys.sdo_dist_units
           WHERE sdo_unit = v_unit
             AND ROWNUM = 1;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          raise_application_error( codesys.Constants.c_i_invalid_unit, 
                                   codesys.Constants.c_s_invalid_unit || v_unit);
    END;
    -- Check if SRID exists
    BEGIN
      SELECT srid
        INTO v_srid
        FROM mdsys.cs_srs
       WHERE srid = p_srid;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          raise_application_error( codesys.Constants.c_i_invalid_srid, 
                                   codesys.Constants.c_s_invalid_srid || p_srid);
    END;
    -- We need to get the conversion factor to meters and earth's radius for the supplied SRID. 
    -- This can only be gotten by getting the WKTEXT in mdsys.cs_srs, breaking it into tokens, 
    -- and finding the right ones:
    -- SPHEROID + 2 tokens = Radius
    -- Last UNIT + 1 = conversion unit
    -- Last UNIT + 2 = conversion unit value
    FOR rec IN c_cs_tokens(p_srid) LOOP
      If ( rec.id = 1 ) Then
        v_geocs :=  case rec.token when 'GEOGCS' then true else false end;
      ElsIf ( rec.token = 'SPHEROID' ) Then
        v_token    := rec.token;
        v_token_id := rec.id + 2;
      ElsIf ( rec.token = 'UNIT' ) Then
        v_token    := rec.token;
        v_token_id := rec.id + 2;
      End If;
      If ( rec.id = v_token_id ) Then
        If ( v_token = 'SPHEROID' ) Then
          v_radius_of_earth := to_number(rec.token);
        ElsIf ( v_token = 'UNIT' ) Then
          v_srid_conversion_factor := to_number(rec.token);
        End If;
      End If;
    END LOOP;
    If ( v_geocs ) Then
      v_srid_conversion_factor := v_srid_conversion_factor * v_radius_of_earth; 
    End If;
    -- OK, now we have a conversion factor from p_unit to meters
    -- and a conversion factor for the units to meters
    -- The returned value is: p_value * v_srid_conversion_factor (to get value in meters) / v_unit_conversion_factor (to convert from meters to the unit)
    -- 
    return ( p_value * v_srid_conversion_factor ) / v_unit_conversion_factor;
  End Convert_Distance;

Some examples:

codesys@XE> select     Convert_Distance(8311,1,'Meter') as meters_per_degree,
  2                1 / Convert_Distance(8311,1,'Meter') as degrees_per_metre
  3           from dual;
METERS_PER_DEGREE DEGREES_PER_METRE
----------------- -----------------
       111319.491        8.9832E-06
codesys@XE> select     Convert_Distance(8311,1,'Foot') as feet_per_degree,
  2                1 / Convert_Distance(8311,1,'Foot') as degrees_per_foot
  3           from dual;
FEET_PER_DEGREE DEGREES_PER_FOOT
--------------- ----------------
     365221.426       2.7381E-06
codesys@XE> select     Convert_Distance(2964,1,'Meter') as feet_per_metre,
  2                1 / Convert_Distance(2964,1,'Meter') as metres_per_foot
  3           from dual;
FEET_PER_METRE METRES_PER_FOOT
-------------- ---------------
     .30480061      3.28083333

These functions have been integrated into my GEOM PL/SQL package. These functions are useful in the context of my packages because I have recently added the ability to convert special elements of an sdo_geometry (eg rectangles, circles and circular arcs) to vertex-to-vertex connected segments. These require an arc2chord value which is expressed in dataset units. So, for geographic data this is decimal degrees not meters (unlike Oracle itself). Similarly, the tolerance parameter of the sdo_centroid function is similarly expressed in dataset units.

If anyone finds any errors in my work, please drop me a line.

Creative Commons License

post this at del.icio.uspost this at Diggpost this at Technoratipost this at Redditpost this at Farkpost this at Yahoo! my webpost this at Windows Livepost this at Google Bookmarkspost this to Twitter

Comment [4]

Hello,
I find your articles very helpful and interesting. I was wondering if you can guide me to a link or something that may contain how to figure out surrounding blocks to a given block.

Thanks in advance
Ash Agarwal

— Ashok Agarwal · 14 October 2008, 16:27 · #

Ash,

See my article

Simon

Simon Greener · 18 February 2009, 02:30 · #

Hi Simon,
I’m Luciano from Brazil, newbe, since june taking my first steps in Oracle Spatial.
It’s the first time a write but already read yours articles several times.
My doubt is how about Oracle handles calculations of large AOIs/Geometries(that spread acros more then one UTM zone, projections).
I expect that Oracle split the aois, make calcs separated and then sums the results… but I wasn’t able to find any documentation, article, book, mention talking about this…
Do you know where I can find this information?!
Thanks in advance.
And thanks for sharing you knowledge, your posts helped me a lot!!
Best regards
Luciano

Luciano Santana · 28 September 2009, 21:00 · #

Luciano,

> I’m Luciano from Brazil, newbe, since june taking my first steps in Oracle Spatial.

Nice to meet you. Sorry for the delay in responding: been rather busy. Also, I have tried writing to you at your email address but your servers reject the emails.

> It’s the first time a write but already read yours articles several times.

I hope you found them useful. I have more to add but little time in which to complete them.

> My doubt is how about Oracle handles calculations of large AOIs/Geometries(that spread acros more then one UTM zone, projections). I expect that Oracle split the aois, make calcs separated and then sums the results… but I wasn’t able to find any documentation, article, book, mention talking about this… Do you know where I can find this information?!

The question is not easy to answer as Oracle, as a private software company, does not normally release details about its algorithms and implementations. This is very much the case for the Spatial team.

All I can say is that if you have data that crosses multiple UTM zones and you want to conduct processing against all the data then you two choices:

1. Choose a non-UTM projection that does not have the UTM boundary limits (eg Lambert Conformal Conic with 2 standard latitudes is normally a good one);
2. Choose a geodetic longitude/latitude datum;

After choosing you new projection/datum project your UTM data into it and then conduct your analysis.

Note. If you choose a geodetic datum then note that your processing time will be a lot slower (Oracle indicates this in the Spatial documentation) and also less precise as the max tolerance you can use is 0.05m ie 5cm. Projected data processing is much faster so consider 1) before 2).

Finally, you could try posting you question on the Oracle Spatial Forum on Technet

> And thanks for sharing you knowledge, your posts helped me a lot!!

If you have any questions that might result in a new blog post let me know.

regards
Simon

Simon · 5 October 2009, 03:24 · #