Go to content Go to navigation and search

Home

Current Oracle Spatial Blog Articles

    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
    GeoRaptor 4.x Update 2
    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.

Oracle Spatial Mapping and Map Rendering Performance Tips

Thursday May 24 2007 at 01:22

There are lots of things one can do to improve performance in mapping environments because of a lot of the visualisation is based on “background” or read-only data.

Here are 10 “tips” that I find useful:

1. Spatially sort read-only data.

This tip makes sure that data that is close to each other in space are next to each other on disk! Dan gave a good suggestion when he referenced Chapter 14, “Reorganize the Table Data to Minimize I/O” pp 580- 582, Pro Oracle Spatial. But just as easily one can create a table as select … where sdo_filter() where the filtering object is an optimized rectangle across the whole of the dataset. (This is quite quick on 10g and above but much slower on earlier releases.)

There are many methods for doing this, I suggest you consider using a Morton space key based sort – ORDER BY Morton(gx,yy) a per this article.

2. Generalise data

2.1. Simplification

Rendering spatial data can be expensive where the data is geometrically detailed (many vertices) esp where the data is being visualised at smaller scales than it was captured at. So, if your “zoom thresholds” allow 1:10,000 data to be used at 1:100,000 then you are going to have problems. Consider pre-generalising the data (see sdo_util.simplify) before deployment. You can add multiple columns to your base table to hold this data. Be careful with polygon data because generalising polygons that share boundaries will create gaps etc as the data is more generalised. Often it is better to export the data to a GIS which can maintain the boundary relationships when generalising (say via topological relationships).

Oracle’s MapViewer has excellent on-the-fly generalisation but here one needs to be careful. Application tier caching (cf Bryan’s comments below) can help here a lot.

2.2. Coordinate Precision

Oracle does not enforce a coordinate precision on the data it stores. It only applies its sdo_tolerances when processing data in its spatial functions.

Many spatial data loader software loads ordinates to the precision it finds the are described in. So, if the X ordinate of a point is described as 300,000.1234567 then those loader software often will load exactly that. But if the actual ordinate’s observed value cannot be any better than 1cm, consider loading 300,000.12 rather than the full number.

Why?

Because:

3. Consider “denormalising” data

There is an old adage in databases that is “normalise for edit, denormalise for performance”. When we load spatial data we often get it from suppliers in a fairly flat or normalised form. In consort with spatial sorting, consider denormalising the data via aggregations based on a rendering attribute and some sort of spatial unit.

For example, if you have 1 million points stored as single points in SDO_GEOMETRY.SDO_POINT which you want to render by a single attribute containing 20 values, consider aggregating the data using this attribute AND some sort of spatial BUCKET or BIN. So, consider using SDO_AGGR_UNION coupled with Spatial Analysis and Mining package functions to GROUP (the data) BY << column_name >> and a set of spatial extents.

4. Tablespace use

When creating tables via denormalisation, sorting and/or ordinate precision reduction, it may be useful to create the target table in such a way that its blocks are as full as possible and packed next to each other in the tablespace. (Consider tablespace defragmentation beforehand.) Also, if the data is READ ONLY consider setting the PCTFREE to 0 in order to pack the data up into as small a number of blocks as possible.

Finally, talk to your DBA in order to find out how the oracle database’s physical and logical storage is organised. Is a SAN being used or SAME arranged disk arrays? Knowing this you can organise your spatial data and indexes using more effective and efficient methods that will ensure greater scalability. (See 2.2. Coordinate Precision above.)

5. SDO_TOLERANCE, Clean Data

If you are querying data other than via MBR (eg find all land parcels that touch each other) then make sure that your sdo_tolerance values are appropriate. I have seen sites where data captured to 1cm had an sdo_tolerance value set to a millionth of a meter!

A corollary to this is make sure that all your data passes validation at the chosen sdo_tolerance value before deploying to visualisation. Run sdo_geom.validate_geometry()/validate_layer()…

6. RTree Spatial Indexing

At 10g and above lots of great work went in to the RTree indexing. So, make sure you are using RTrees and not QuadTrees. Also, many GIS applications create sub-optimal RTrees by not using the additional parameters available at 10g and above.

6.1 If your table/column sdo_geometry data contains only points, lines or polygons then let the RTree indexer know (via layer_gtype) as it can implement certain optimizations based on this knowledge.

6.2 With 10g you can set the RTree’s spatial index data block use via sdo_pct_free. Consider setting this parameter to 0 if the table/column sdo_geometry data is read only.

6.3 If a table/column is in high demand (eg it is the most commonly used table in all visualisations) you can consider loading (a part of) the RTree index into memory. Now, with the RTree indexing, the sdo_non_leaf_tbl=true parameter will split the RTree index into its leaf (contains actual rowid reference) and non-leaf (the tree built on the leaves) components. Most RTrees are built without this so only the MDRT_9AA9A$ secondary tables are built. But if sdo_non_leaf_tbl is set to true you will see the creation of an additional MDNT_9AA9A$ secondary table (for the non_leaf part of the rtree index). Now, if appropriate, the non_leaf table can be loaded into memory via the following:

ALTER TABLE MDNT_9AA9A$ STORAGE ( BUFFER_AREA KEEP );

This is NOT a general panacea for all performance problems. One should investigate other options before embarking on this (cf Tom Kyte’s books such as Expert Oracle Database Architecture, 9i and 10g Programming Techniques and Solutions.)

6.4 (NO LONGER RECOMMENDEDORACLE’S RTREE INDEXES ARE SELF-MANAGING) Don’t forget to check your spatial index data quality regularly. Because many sites use GIS package GUI tools to create tables, load data and index them, there is a real tendency to not check what they have done or regularly monitor the objects. Check the SDO_RTREE_QUALITY column in USER_SDO_INDEX_METADATA and look for indexes with an SDO_RTREE_QUALITY setting that is > 2. If > 2 consider rebuilding or recreating the index.

7. The rendering engine.

Whatever rendering engine one uses make sure you try and understand fully what it can and cannot do. AutoDesk’s MapGuide is an excellent product but I have seen it simply cache table/column data and never dynamically access it. Also, I have been at one site which was running Deegree and MapViewer and MapViewer was so fast in comparison to Deegree that I was called in to find out why. I discovered that Deegree was using SDO_RELATE(… ANYINTERACT …) for all MBR queries while MapViewer was using SDO_FILTER. Just this difference was causing some queries to perform at < 10% of the speed of MapViewer!!!!

8. Don’t draw data that are sub-pixel.

As one zooms out objects become smaller and smaller until they reach a point where the whole object can be drawn within a single pixel. If you have control over your map visualisation application you might want to consider setting the SDO_FILTER / SDO_RELATE parameters’ “min_resolution” flag dynamically so that its value is the same as the number of meters / pixel (eg min_resolution=10). If this is set Oracle Spatial will only include spatial objects in the returned search set if one side of a geometry’s MBR is greater than or equal to this value. Thus any geometries smaller than a pixel will not be returned. Very useful for large scale data being drawn at small scales and for which no selection (eg identify) is required. With Oracle MapViewer this behaviour can be set via the generalized_pixels parameter.

9. Network fetch

If your rendering engine (app server) and database are on separate machines you need to investigate what sort of fetch sizes are being used when returning data from queries to the middle-tier. Fetch sizes for attribute only data rows and rows containing spatial data can be, and normally are, radically different. Accepting the default settings for these sizes could be killing you (as could the sort_area_size of the Oracle session the application server has created on the database). For example I have been informed that MapInfo Pro uses a fixed value of 25 records per fetch when communicating with Oracle. I have done some testing to show that this value can be too small for certain types of spatial data. SQL Developer’s GeoRaptor uses 100 which is generally better (but this one can modify this). Most programmers accept defaults for network properties when programming in ADO/ODBC/OLEDB/JDBC: just be careful as to what is being set here. (This is one of the great strengths of ArcSDE: its TCP/IP network transport is well written, tuneable and very efficient.)

10. Physical Format

Finally, while Oracle’s excellent MapViewer requires data its spatial data to be in Oracle, other commercial rendering engines do not. So, consider using alternate, physical file formats that are more optimal for your rendering engine. For example, Google Earth Enterprise “compiles” all the source data into an optimal format which the server then serves to Google Earth Enterprise clients. Similarly, a shapefile on local disk to the application server (with spatial indexing) may be faster that storing the data back in Oracle on a database server that is being shared with other business databases (eg Oracle financials). If you don’t like this approach and want to use Oracle only consider using a dedicated Oracle XE on the application server for the data that is read only and used in most of your generated maps eg contour or drainage data.

Just some things to think about.

regards
Simon

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 [12]

Great recommendations all around. Cheers!

— Bryan Hall · 26 May 2007, 02:23 · #

Its cool article

— Adaddm · 26 September 2007, 22:19 · #

Great ADVISE, Simon. One question on the AutoDesk Mapguide comment. Do you mean the table and column names caching in MapGuide Author, for which you have to explicitly press the refresh button, or are you referring to some other caching mechanism?

— Luc Van Linden · 25 October 2007, 07:52 · #

Luc,

At a site running MapGuide 6.5 (not MapServer MapGuide or MapGuide Author) I saw MapGuide, in production, running against data in Oracle whose spatial indexes were broken (ie could not be used to conduct queries inside the database via SELECT … SDO_FILTER etc). From this I concluded that MapGuide was reading the whole table and caching the results and not dynamically querying the database. I may have been wrong to conclude this. Any comments from anyone on this?

regards
Simon

Simon · 6 November 2007, 22:07 · #

Simon, Good article.
I have an index with SDO_RTREE_QUALITY equal to 6,62738379. I used drop and create the index again using and order by for sdo_geometry column and the result is the same.
Is there any more comment about this ?

— Eriovaldo · 15 January 2009, 00:36 · #

If I look at the SDO_RTREE_QUALITY column in the MDSYS.SDO_INDEX_METADATA_TABLE table for two particular tables/indexes in my database, I get 6.8 and 10.53914864.

But if I run this in SQLPlus:

SELECT SDO_TUNE.QUALITY_DEGRADATION(‘GIS’, ‘xxxxxxxxx’) FROM DUAL;

(Note that the second argument xxxxxxxx is the INDEX_NAME not the TABLE_NAME)

For the same two tables/indexes I get 0.99999999877793 and 1.00000000011382 respectively.

So, use the SDO_TUNE function in preference to looking at MDSYS.SDO_INDEX_METADATA_TABLE.SDO_RTREE_QUALITY. Try doing the same for your table.

regards
Simon

Simon Greener · 15 January 2009, 01:43 · #

Very good post. I have been searching for this post since many days. Now I have implemented the same for my site.

Term Paper · 1 March 2010, 11:01 · #

Hello… I may sound completely naive but, can someone tell me how to load spatial data into oracle mapviewer…?

I am a student and i have downloaded oracle 11g, mapviewer toolkit from OTN and am experimenting with it….

I was able to run MVdemo in mapviewer…. But now i want to load my own map along with styling details, themes and maps tables…

It would be of great help if anyone could point me in the right direction or even give me an example data that i can use to understand how things work with oracle mapviewer…
I intend to develope a small project of my own…. So any help would be very very appreciated..

Regards

— Vikky · 10 May 2010, 14:23 · #

I think you may have misunderstood the meaning of the SDO_RTREE_QUALITY. My reading suggests that this is an arbitrary value not really a quality. The value is filled when the index is rebuilt. Applying the same algorithm to the data at a later date and comparing gives a ratio of how it was when the index was optimal and how it is now.

If you use the following query

select sdo_index_name, SDO_TUNE.QUALITY_DEGRADATION(’{schema name}’,sdo_index_name) degradation from user_sdo_index_metadata

then you will see the degradation. If this goes over 2 then consider rebuilding the index. ({schema} is the schema with the spatial data).

Nick · 16 September 2010, 11:02 · #

Nick,

Thanks for commenting.

I believe you are right. I used to use SDO_TUNE.QUALITY_DEGRADATION as you recommended but changed to the (incorrect) method around the time of this article.

The issue of monitoring RTree index quality as at 11gR2 is no longer needed. The 11gR2 documentation for this function states:

Note:
This function is deprecated, and it will not be documented in future releases. You should not use this function. It is not needed because Spatial indexes are self-tuning.

So, if you are on version 11gR1 or earlier oof the database then use the SDO_TUNE function but for 11gR2 onwards, it appears we can now stop monitoring RTree index quality.

regards and thanks.
Simon

Simon Greener · 21 September 2010, 06:58 · #

Hello.
Did you mean
ALTER TABLE mdNt9999 STORAGE (BUFFER_POOL KEEP ) ?

MDRT.. or MDNT.. ? What will happens if we will load the two tables into memory?

Regards and thanks

— Denis · 15 August 2012, 14:16 · #

Denis,

Yes, quite correct. The article has been corrected.

Thanks

regards
Simon

— Simon Greener · 16 August 2012, 01:42 · #