Monday May 09, 2016

Benefits of the 12c SDO_POINTINPOLYGON Function

Guest Post By: Nick Salem, Distinguished Engineer, Neustar and Technical Chair, Oracle Spatial SIG

The mdsys.SDO_POINTINPOLYGON function API is a new feature that was released in Oracle Database 12c. There is a nice blog post that explains how this feature can be used to address the challenges of ingesting large amounts of spatial data where you can handle the loading and querying of large spatial data sets without the overhead associated with creating and maintaining a spatial index. The example shows how SDO_POINTINTPOLYGON can really benefit massive scale operations, such as those using Exadata environments.

In this post, I would like to cover some other benefits that the SDO_POINTINPOLYGON feature provides that can be very helpful – especially for applications servicing a large number of concurrent spatial operations. This can greatly improve performance for such applications that run on either Exadata or non-Exadata environments. The fact that the SDO_POINTINPOLYGON does not use a spatial index means that you can leverage data stored in an external table or a global temporary table to perform spatial point-in-polygon queries. Global temporary tables are great for multi-session environments because every user session has their own version of the data for the same global temporary table, without any contention or row locking conflicts between sessions. Furthermore in 12c, Oracle introduced some major performance optimizations to global temporary tables that result in substantially lower redo and undo generation. You will need to make sure system parameter temp_undo_enabled is set to TRUE to ensure that the 12c global temporary tables optimization is fully in effect.

Below is a screenshot from Neustar’s ElementOne platform with a map showing a trade area and a set of uploaded customer points.

At Neustar, our clients work with a lot of transient work data as part of a multi-step process for various spatial and analytical use cases. Let’s put together a quick PL/SQL script that you can use to test drive the power of the SDO_POINTINPOLYGON function. Here, I use a simple polygon in the San Diego area and generate a set of random customer points in and around the polygon. Then, I populate the global temporary table. The script is configurable: you can increase or decrease the number of randomly generated customer points, and how far from the polygon centroid you may want to allow points to extend to. Once you have the data populated, you can run the SDO_POINTINPOLYGON queries in serial or in parallel, or change some of the optional MASK parameters. Here’s a screenshot of the test polygon and a sample of randomly generated 1,000 customer points.

1. Create a global temporary table

Ok, so let’s first create a global temporary table =>

create global temporary table TMP_SPATIAL_POINT (
x number,

y number,
id varchar2(512) )

on commit preserve rows;

2. Generate a set of random points and populate the global temporary table

Next, let’s run the following script to populate table TMP_SPATIAL_POINT. The script has two variables: maxDistanceInMeters and numberOfPoints in the PL/SQL declaration section that you can adjust as needed. If you want to generate more points, then you can change the value of numberOfPoints from 1000 to a greater number. In this example, I also have maxDistanceInMeters set to 4000. This will ensure that no customer points get generated further than 4000 meters away from the polygon centroid; this can be increased or decreased as needed. The script goes through a loop up to the numberOfPoints variable and uses the SDO_UTIL.POINT_AT_BEARING function to plot points around the centroid of the polygon using randomly generated values. The goal of the script is to quickly create some test data you can play with. Of course, you can also change the test polygon as well.

polygon sdo_geometry;
centroid sdo_geometry;
newPoint sdo_geometry;
maxDistanceInMeters number := 4000;
numberOfPoints number := 10000;
type tRecs is table of tmp_spatial_point%rowtype;
recs tRecs := tRecs();
polygon := SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1,1003,1),
-117.06045,32.697641, -117.09753,32.696774, -117.1044,32.680882));
centroid := SDO_GEOM.SDO_CENTROID( polygon, 0.05 );
for i in 1 .. numberOfPoints loop
newPoint := SDO_UTIL.POINT_AT_BEARING( start_point => centroid,
bearing => dbms_random.value(0,6.283),
distance => dbms_random.value(1,maxDistanceInMeters));
recs(i).id := i;
recs(i).x := newPoint.sdo_point.x;
recs(i).y := newPoint.sdo_point.y;
end loop;
execute immediate ‘truncate table tmp_spatial_point’;
forall i in recs.first .. recs.last
insert into tmp_spatial_point values ( recs(i).x, recs(i).y, recs(i).id ) ;

3. Run SDO_POINTINPOLYGON queries (in serial or parallel)

Ok, now we can start performing queries using the SDO_POINTINPOLYGON function. Here’s a sample query that returns the counts of points that fall inside the polygon. The params parameter is optional; if omitted, a MASK=ANYINTERACT query will be performed.

set timing on
SDO_PointInPolygon( cur => cursor(select * from tmp_spatial_point),
geom_obj => SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1,1003,1),
SDO_ORDINATE_ARRAY(-117.1044,32.680882, -117.08895,32.661808, -117.06148,32.675102,
-117.06045,32.697641, -117.09753,32.696774, -117.1044,32.680882)),
tol => 0.05,
Params => 'MASK=INSIDE'
) t ;

Here’s a another example of the query using parallelism and with the params parameter omitted. 

select /*+ parallel(8) */
SDO_PointInPolygon( cur => cursor(select * from tmp_spatial_point),
geom_obj => SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1,1003,1),
SDO_ORDINATE_ARRAY(-117.1044,32.680882, -117.08895,32.661808, -117.06148,32.675102,
-117.06045,32.697641, -117.09753,32.696774, -117.1044,32.680882)),
tol => 0.05
) t ;

The SDO_POINTINPOLYGON function has been built to leverage Oracle’s parallel processing capability.  To demonstrate the magnitude of performance gain when utilizing parallelism, I modified the point generation script in part 2 to populate a million points with a max distance of 5,000 meters from the center point.  I then tested the SDO_POINTINPOLYGON query with no parallelism, and then with parallelism of 2, 4 and 8.  Here are the elapsed response times:

Level of parallelism

Elapsed time


13.28 secs


9.62 secs


6.03 secs


3.43 secs 

Utilizing parallelism can greatly shorten query processing times.  You can use these scripts in your environment to generate different numbers of points, test various levels of parallelism, and compare the response times. 

Thursday Apr 14, 2016

Oracle OpenWorld 2016 Call for Proposals - Now Open!

The Oracle OpenWorld customer call for proposals (CFP) is now live on the OpenWorld 2016 site.   We encourage customers and partners to submit your outstanding use case studies for big data, spatial, graph and other Oracle technologies for consideration. Deadline is May 9.

Submit your abstracts early -- and good luck!

Friday Feb 26, 2016

Spatial & Graph Summit Presentations From BIWA Summit ’16 Now Available

Over 24 presentations on spatial, map visualization, and graph technologies for database, big data, and cloud platforms were delivered at BIWA Summit '16 – the Oracle Big Data + Analytics + Spatial + YesSQL Community 3-Day User Conference, Jan. 26-28 at Oracle HQ.

Slides and materials from technical sessions, hands on labs, and customer and partner use cases are now available.

Topics included best practices, point clouds/city models, rasters, mapping, big data technologies.  New customer use cases came from government, telco, transportation, and energy, featuring Oracle Spatial and Graph/DB 12c deployments.  See below for the session list.

For some user tips to get started on 2 new hands on labs for Big Data Spatial and Graph, view Tracy McLane’s blog post here.

And for more great presentations from BIWA Summit 16 – from tracks such as Advanced Analytics, BI, Data Warehousing, Big Data, and YesSQL, visit the BIWA Summit page.

Many thanks to the BIWA Committee for making the event a success!

Featured BIWA Session
| What’s New with Spatial and Graph? Technologies to Better Understand Complex Relationships
James Steiner, Oracle
With the emergence of IoT, Cloud services, mobile tracking, social media, and real time systems, we're finding new ways to incorporate social and location information into business workflows.  Learn how Oracle's spatial and graph technologies address these developments.  New offerings for the Cloud, and NoSQL and Hadoop platform are discussed.  A BIWA '16 featured talk.

[Read More]

Wednesday Jan 06, 2016

Tips for Switching Between Geodetic and Mercator Projections

Guest Post By: Nick Salem, Distinguished Engineer, Neustar and Technical Chair of the Oracle Spatial SIG

Note:  Thanks to Nick Salem for this valuable tip on handling multiple coordinate systems to optimize performance and storage!

Oracle Spatial and Graph provides a feature rich coordinate system transformation and management capability for working with different map projections.  This includes utilities that convert spatial data from one coordinate system to another, from 2D to 3D projections, create EPSG rules, deal with various input and output formats and more.

If you deal with geodetic data, you may have run into the need to display your data points and areas onto aerial or terrain maps.  For this, you could utilize the SDO_CS.TRANSFORM function to dynamically convert your geometries to the destination projection system.  The challenge we had at Neustar was that our customers wanted the option to switch frequently back and forth between our MapViewer geodetic base maps and aerial and terrain base maps with the Mercator projection.  They wanted to do this in a seamless and responsive manner.  And some of our customer datasets are fairly large.  The Neustar ElementOne system holds billions of rows of geospatial data.  We wanted to provide our customers with the capability to switch projections for any of their geometries, but we also wanted our system to scale and maintain quick responsiveness.  Coordinate transformation operations can be expensive, especially if they are performed on large volumes of geometries.  

Initially, we tried to dynamically perform coordinate transformations on the fly for customer requests, but this did not give us the best performance, and resulted in some of the same geometries going through the same repetitive transformation over again and again.

The solution for us was to maintain and manage two coordinate systems for all of our customer geometries.  For every spatial data record, we have two SDO_GEOMETRY columns, one to store the latitude/longitude geodetic data and other to store the Mercator projection data.  We use the geodetic geometries for queries and all spatial operations, and we use the Mercator projection solely for map visualizations.  The advantage of this approach is that every geometry goes through only one coordinate transformation during the data loading or updating process.  And for query visualizations, performance is optimal, since the data is already available for display.  This results in the best customer experience and snappy response times.  Another advantage of visualizing geodetic data using the Mercator projection is that radii appear circular instead of oval looking.

Here’s a picture from Neustar’s ElementOne platform showing a 3 mile radius trade area.

One obvious disadvantage of this approach is that it requires more storage as you store and manage two sets of geometry columns.  If you take a closer look at the geometries created by the coordinate transformations, the resulting geometry may include a greater amount of precision than your application actually needs.  A good rule of thumb is to only include the least amount of precision required to support your needs.  Let’s take a quick look at an example of converting a geodetic (8307) latitude/longitude point geometry to the Mercator (3785) projection.

SQL> select
  to_char(value(t).x) x,
  to_char(value(t).y) y
         ,0.5,3785))) t;

X                                                        Y
----------------------------------------         ----------------------------------------
-13026550.373647                             3864160.0406267

The 8307 geodetic projection utilizes the unit of degrees for the latitude/longitude coordinates, while the 3785 Mercator projection uses meters as the measure.  From the example above, you can see up to 7 decimal places for the coordinates – which was far greater than what we need for our mapping analysis and visualization needs.  You may wonder why we should bother about the numeric precision of spatial geometries.  The answer is performance and storage savings.  The larger the precision, the more storage it will take.  The more storage for your geometries, the more Oracle blocks needed to store your data.  The more data blocks that the database has to fetch to satisfy a query, the longer the query will take.  

To illustrate the amount of additional space that transformed geometries can take compared to the original geometries, I created 4 tables each consisting of 30,532 ZIP Code geometries.

Next I ran a query joining USER_SEGMENTS and USER_LOBS to get the total space consumption of the SDO_ORDINATES for each of the 4 tables.   For polygon geometries, Oracle will likely store the geometry outside the table in LOB segments.

  l.table_name, l.COLUMN_NAME, t.BYTES/(1024*1024) m_bytes
    user_segments t,
    user_lobs l
     t.segment_name = l.segment_name and
     l.column_name like '%GEOM%SDO_ORDINATES%';

TABLE_NAME                                   COLUMN_NAME                            M_BYTES
------------------------------                     ------------------------------                     ----------
ZIP_CODE_SRID8307                       "GEOM"."SDO_ORDINATES"        120.1875
ZIP_CODE_SRID3785                       "GEOM"."SDO_ORDINATES"        216.1875
ZIP_CODE_SRID3785_ROUND0     "GEOM"."SDO_ORDINATES"        120.1875
ZIP_CODE_SRID3785_ROUND1     "GEOM"."SDO_ORDINATES"        136.1875

The original ZIP Code SDO_ORDINATES consumed 120M.  But when we converted the same ZIP geometries to the Mercator projection, we ended up with 216M - that is an 80% increase in size.  Then, when we truncated the decimals for the Mercator projected coordinates in table ZIP_CODE_SRID3785_ROUND0 –  this brought the size back to 120M, but we ended with 41 invalid ZIP boundaries.  Rounding to 1 decimal place resulted in 136M of size and all valid geometries.  The goal is to round the coordinates to the least decimal places needed for your application.  In our case, we used the Mercator projection geometries only for visualization – so we were not very concerned about how valid the geometries were, and opted for truncating the decimal places, which worked out great for us.  In your case, you can play around with what precision works out best for you.

Here’s nice helper function that can be used to perform the coordinate transformation and then apply the required rounding all in one step.

create or replace function transformToSRID (
                           pGeometry    in sdo_geometry,                           
                           pTolerance   in number,
                           pToSRID      IN number,
                           pRoundPos    in integer )
return sdo_geometry
outGeometry  sdo_geometry;
outGeometry := sdo_cs.transform( geom => pGeometry,
                                 tolerance => pTolerance,
                                 to_srid => pToSrid ) ;
if outGeometry.sdo_point is not null then
  outGeometry.sdo_point.x := round( outGeometry.sdo_point.x, pRoundPos );
  outGeometry.sdo_point.y := round( outGeometry.sdo_point.y, pRoundPos );
end if;
if outGeometry.sdo_ordinates is not null then
  for i in outGeometry.sdo_ordinates.first .. outGeometry.sdo_ordinates.last loop
    outGeometry.sdo_ordinates(i) := round(outGeometry.sdo_ordinates(i),pRoundPos);
  end loop;
end if;
return outGeometry;

Quick usage example =>
SQL> select
  to_char(value(t).x) x,
  to_char(value(t).y) y
  table(sdo_util.GetVertices(transformToSRID( sdo_geometry(2001,8307,sdo_point_type(-117.019493,32.765053,null),null,null),0.5,3785,1)))

X                                                        Y
----------------------------------------         ----------------------------------------
-13026550.4                                       3864160

Here are a picture from Neustar’s ElementOne platform overlaying a site trade area over a terrain map.

Here’s another picture from Neustar’s ElementOne showing 10 and 15 minute drive time trade areas over an aerial map.

In conclusion, the amount of precision for geometry coordinates matters for performance and storage.  If you perform a lot of repetitive coordinate transformation to support your application needs, you may want to consider storing the projected geometries.  By default, the SDO_CS.TRANSFORM function may create geometries with coordinates containing more precision than required for your needs.  You should always check the amount of precision of your geometries and round to the minimum number of decimal places needed to support your application requirements.

Friday Jul 31, 2015

Submit your abstract for Oracle Spatial & BIWA Summit '16!

For the second year, Oracle Spatial Summit will once again be part of BIWA Summit at Oracle HQ in January 2016!

We want to hear your story -- and encourage you to submit your proposal soon. Proposals will be accepted through  November 2.   Don't wait, though -- we're accepting submissions on a rolling basis.  The sooner you submit, the better your chances.

We're looking for innovative Oracle Spatial and Graph, and MapViewer use cases in government, utilities, transportation, energy, telco,  retail and insurance, or other industries.  Large geospatial enterprise systems, and cloud, big data, BI deployments are of interest.

View more guidelines here, and view 2015 Summit sessions.

BIWA has a great event planned - with tracks for Advanced Analytics, Big Data, BI, Data Warehousing and Integration, and IoT, great keynote speakers, and Hands On Labs.

Submit your abstract here (

Good luck.  We hope you'll join us for Summit 2016!

Tuesday May 19, 2015

Dedicated blog for Oracle Big Data Spatial and Graph

With the launch of the new Oracle Big Data Spatial and Graph product we have also established a new blog dedicated to spatial analytics on the Hadoop platform and in-memory property graph processing (here).

Friday Mar 13, 2015

Using SDO_UTIL Functions to Construct Geodetic Shapes

Guest Post By: Nick Salem, Distinguished Engineer, Neustar

Note: Thanks to Nick Salem, Technical Chair of the Oracle Spatial SIG , for contributing another valuable tip and script example!  This is very useful for those who want to learn how to create grids for trade area/demographic analysis.

Oracle Spatial & Graph provides a feature rich toolkit for building and manipulating various types of geometries. There are a lot of great helper functions that you can utilize to construct a specific shape type. At Neustar, we leverage these functions to create square and hexagonal grids that we can overlay over a site’s trade area or some market. These grids can be then scored to show the variation of data across an area.

Below is a picture from Neustar’s ElementOne platform displaying a 0.5 mile grid layer over a 3 mile radius of a prospect bank site. The grids are scored by the number of households that have a bank savings account from high to low. Square grids are simple polygons that are easy to create in a planar surface since they have edges of equal length. However in a geodetic projection, you cannot simple use simple geometric calculations to construct these shapes or at least not as accurately as you may want especially if you are covering a larger area. Oracle provides the SDO_UTIL.CONVERT_UNIT and the SDO_UTIL.POINT_AT_BEARING functions that can be helpful in creating shapes on geodetic surfaces. You can use the CONVERT_UNIT function to convert units from degrees to radians and the POINT_AT_BEARING to place individual coordinates of a geometry.

Here’s a picture of a 1 mile hexagonal grid tessellation for the number of households with a home equity loan for the county of Anne Arundel in Maryland. Hexagonal grids have one advantage over square grids in that they are more circular in shape and so the distance from the center to the sides and edges is even.

Next I am going to show a simple PL/SQL script to create a simple trapezoid shape using the SDO_UTIL.CONVERT_UNIT and SDO_UNIT.POINT_AT_BEARING functions. In this example, I begin with a starting point located in San Diego county and then go in a counter clockwise order to plot the subsequent coordinates that will make up the trapezoid polygon. The angle passed into the SDO_UTIL.POINT_AT_BEARING function is measured clockwise from due north (i.e. bearing 0 is north, bearing 90 is east, bearing 180 is south, bearing 270 is west). I am creating a trapezoid polygon here as an example since a trapezoid can represent the top or bottom part of a hexagon.

Simple PL/SQL script example:

startPoint sdo_geometry;
nextPoint sdo_geometry;

coords sdo_ordinate_array :=sdo_ordinate_array();
shape sdo_geometry;


-- first starting point

startPoint := sdo_geometry(2001,4326,
coords(coords.count-1) := startPoint.sdo_point.x;
coords(coords.count) := startPoint.sdo_point.y;

-- place second coordinate 5,000 meters away using 330 (northwest) degree angle

nextPoint := sdo_util.point_at_bearing( start_point => startPoint,
                                         bearing => sdo_util.convert_unit(330,'degree','radian'),
                                         distance => 5000 );
coords(coords.count-1) := nextPoint.sdo_point.x;
coords(coords.count) := nextPoint.sdo_point.y;

-- place third coordinate 3,000 meters away using 270 (west) degrees angle

startPoint := nextPoint;
nextPoint := sdo_util.point_at_bearing( start_point => startPoint,
                                         bearing => sdo_util.convert_unit(270,'degree','radian'),
                                         distance => 3000 );
coords(coords.count-1) := nextPoint.sdo_point.x;
coords(coords.count) := nextPoint.sdo_point.y;

-- place fourth coordinate 5,000 meters away using 210 (southwest) degrees angle

startPoint := nextPoint;
nextPoint := sdo_util.point_at_bearing( start_point => startPoint,
                                         bearing => sdo_util.convert_unit(210,'degree','radian'),
                                         distance => 5000 );
coords(coords.count-1) := nextPoint.sdo_point.x;
coords(coords.count) := nextPoint.sdo_point.y;

-- add starting coordinate as 5th coordinate to close the polygon

coords(coords.count-1) := coords(1);
coords(coords.count) := coords(2);

-- construct shape geometry

shape :=sdo_geometry( 2003, 4326,null,
                           sdo_elem_info_array(1,1003,1), coords ) ;

Here’s the result of the shape geometry from script above displayed on a map.

In conclusion, Oracle Spatial & Graph provides us with some nice helper functions that we can use to construct shapes on a geodetic surface. In this post, we demonstrated how one can use the SDO_UTIL.POINT_AT_BEARING and SDO_UTIL.CONVERT_UNIT to plot coordinate points for a geometric polygon.

Monday Jan 19, 2015

The Importance of Organizing Spatial Data By Proximity

Guest Post By: Nick Salem, Distinguished Engineer, Neustar

Note: Thanks to Nick Salem, Technical Chair of the Oracle Spatial SIG , for contributing this valuable tuning and performance tip that will be useful to most Oracle Spatial users!

The goal of this post is to shed some light on a technique that I feel is many times overlooked by users working to tune large spatial datasets: to organize the rows in a table by their spatial proximity. Dan Geringer alluded to this in the post “Tips on tuning SDO_NN (nearest neighbor) queries” . What I want to highlight is that this technique is not only beneficial for SDO_NN queries, but also for basically all queries that use any of the spatial query operators like SDO_FILTER, SDO_RELATE, SDO_WITHIN_DISTANCE, as well as the SDO_RELATE helper operators such as SDO_ANYINTERACT, SDO_INSIDE and so on. The SDO_JOIN operator by itself may not benefit from this approach because it relies solely on the spatial index, but if you decide to join the resultset of the SDO_JOIN operation back to the input tables of the join operation, then you will most likely also benefit from having the data stored by proximity.

To understand the dynamics of this, one has to understand in general how Oracle queries work. When a user issues a spatial query, a spatial index is used to find the rowids for the rows that need to be returned. Oracle uses these rowids to retrieve the database blocks from disk into the buffer cache where it can process the data. Each database block can contain one or more rows of data. In order for Oracle to retrieve data for a query, it needs to retrieve all the blocks needed to satisfy a query. The more blocks that need to be scanned, the more I/O operations are performed and the longer the query will take. For example, a spatial query resulting in the scan of one or two adjacent blocks will return a lot faster than the same spatial query needing to scan a large number of disparate blocks to process the same results. Of course, this issue is not confined to just spatial queries. In fact, most Oracle DBAs are aware of this with the concept of the index clustering factor which describes the relationship between the order of an index and its corresponding table. For non-spatial data, achieving an optimal index clustering factor can be as easy as ordering the data in a table by a scalar data type column that you plan to index and then creating that index. For spatial data, this can be a little trickier because one cannot just simply order by the SDO_GEOMETRY column. In Dan’s post “Tips on tuning SDO_NN (nearest neighbor) queries”, he shows an example using the MD.HHENCODE_BYLEVEL function to return a value that you can use to sort your spatial data by.

In this post, I will show an example of a use case I tested that highlights the impact on performance when ordering spatial data by proximity. The example will include taking the US Business listing table and creating two copies: one that is not ordered by proximity, and the other ordered by proximity using the MD.HHENCODE_BYLEVEL function. Then, I will test running a simple SDO_WITHIN_DISTANCE query on both tables to retrieve the sum of employees for an arbitrary 5 mile radius and compare the results.

Example Pre-requisites

In this example, I am starting with a 16 million row table containing all the US business locations with approx 30 columns called BUSINESS1. This table is not ordered by spatial proximity. BUSINESS1 also has a spatial index created on column GEOM.

Step 1) Next I will create table BUSINESS2, which will be an exact copy of BUSINESS1, but will be ordered by proximity using the MD.HHENCODE_BY_LEVEL function as described in Dan’s post.

SELECT /*+ parallel(8) */
  business1 b
  row_number() over (order by md.hhencode_bylevel(
     b.geom.sdo_point.y,-90,90,26)) ;

* Note: the MD.HHENCODE_BYLEVEL function takes an x and y coordinate and so the example works well with the US Business points. If you are working with polygons instead of points, you will need to retrieve the centroid of the shape and then pass the X/Y point coordinates to function. For more info, please refer to Dan Geringer’s original post “Tips on tuning SDO_NN (nearest neighbor) queries” to see an example of how this is done.

* Note: only use PCTFREE 0 for read only tables. In this example, the BUSINESS2 is read only and using PCTFREE 0 allows more rows to be packed into a single database block.

Step 2) add diminfo and create spatial index


mdsys.sdo_meta.change_all_sdo_geom_metadata( USER, ‘BUSINESS2’, ‘GEOM’, mdsys.sdo_dim_array (
     mdsys.sdo_dim_element('X', -180, 180, .5),
     mdsys.sdo_dim_element('Y', -90, 90, .5) ),8307);

CREATE INDEX xsp_business2 ON business2(geom)

* Note: it is recommended to use a WORK_TABLESPACE that is a different tablespace than the one where the index will be created. Although optional, the use of a WORK_TABLESPACE can reduce the fragmentation of your spatial index which is important for performance.

* Note: only use SDO_RTR_PCTFREE=0 for read only data. In this example, the US BUSINESS listing table is pretty much a read only dataset and gets completely replaced every month.

Step 3) Perform query comparison test

In SQL*PLUS, set timing and flush the buffer cache


System altered.

* Note: you should not flush the buffer cache on a production system. But in a test environment, flushing the buffer cache can help with testing comparative performance by ensuring there are no blocks in the SGA from prior queries that could skew performance results.

Run the first query on the non-spatially ordered table

 business1 b
  sdo_within_distance( b.geom,
                       mdsys.sdo_point_type( -117.047071, 32.75521, null),null,null),
                       'distance=5 unit=mile' ) = 'TRUE';


Elapsed: 00:00:35.14

Run the query on the spatially ordered business table and compare results.

 business2 b
  sdo_within_distance( b.geom,
                       mdsys.sdo_point_type( -117.047071, 32.75521, null),null,null),
                       'distance=5 unit=mile' ) = 'TRUE';


Elapsed: 00:00:01.49


In this example, I was able to achieve more than 20X better performance just by taking the same table and ordering by a geographic extent. The queries in the example utilized the spatial operator SDO_WITHIN_DISTANCE to return the sum of employees with a 5 mile radius around a location. Since both tables (BUSINESS1 and BUSINESS2) are identical except in storage of the order of rows of data, the spatial index performance should be pretty much the same. The difference in performance that we are seeing is due to the amount of disk and memory I/O processing caused by the different number of blocks that needed to be accessed for each of the queries. This is a substantial improvement in performance and highlights the importance of the order of spatial data by geographic proximity. And as mentioned in the beginning of the post, ordering spatial data may boost any spatial query operations, whether you are performing within distance queries, sdo relate queries, nearest neighbor or performing map visualizations. Results can vary based on size of table, the speed of disk I/O and also the order of the original dataset. It is possible that the original dataset you are working with is already organized by some geographic extent such as county or ZIP Code so additional ordering using the method described in this post could result in some performance gains but nothing as significant as the 20X I have experienced. But if the order of the table is completely random and not tied to any geographic extent, then you can expect to see greater performance gains. All in all, I definitely recommend looking into organizing larger and more frequently queried spatial data by geographic proximity as a best practice technique for optimizing your spatial data.

Monday Oct 06, 2014

OOW 2014 Spatial SIG Meetup Discussion


At Oracle OpenWorld last week in San Francisco, the Oracle Spatial SIG invited users to join an informal meetup -- at the OTN Lounge in Moscone South.  Users from the US and Europe, from industries such as rail, local city government, national land management agencies, and defense, met with SIG Board and  Oracle Spatial product team members to discuss questions around Oracle Spatial and MapViewer implementations.

Topics included: 

-- challenges of and strategies for handling complex geometries

-- enterprise deployments with various GIS tools

-- MapViewer capabilities such as HTML5 / JSon support, editing, and new mobile/BI features

Read a full recap of the discussion topics here.

And check out more photos from the event. 

Monday Aug 11, 2014

Save the dates! Jan 27-29, 2015 -- Oracle Spatial Summit 2015

Oracle Corporation
header image

Oracle Spatial Summit 2015

Moving to California as part of the Oracle BIWA Summit 2015

divider line

Save the dates! Jan 27-29, 2015

Oracle Conference Center at Oracle HQ Campus, Redwood Shores, CA

  • Learn directly from the senior management and developers of Oracle’s industry-leading spatial technologies -- Dan Geringer, Siva Ravada, Xavier Lopez, Jayant Sharma and Jim Steiner.
  • Participate in Hands-On-Labs for Oracle Spatial and MapViewer.
  • Hear from leading practitioners and customers about their experiences with Oracle’s spatial technologies, Exadata, OBIEE and more.
  • Experience Oracle BIWA with expert and customer sessions on Business Intelligence, Data Warehouse, and Big Data technologies.


Hardware and Software, Engineered to Work Together
Copyright © 2014, Oracle and/or its affiliates.
All rights reserved.
Contact Us | Legal Notices and Terms of Use | Privacy Statement

Tuesday Jul 29, 2014

How much space do your SDO_GEOMETRY tables consume?

 By Karin Patenge, Principal Sales Consultant, Oracle Germany

I would like to briefly offer a simple function to calculate the memory allocated for SDO_GEOMETRY tables. This takes into account only tables, LOB and index segments (this is not applicable for partitioned tables).

create or replace function alloc_space_in_mbytes (tablename in varchar2)
return number 
table size number;
    sum (bytes) into table size
  from (
      USER_SEGMENTS - table segments             
      segment_name = tablename
    union all
      s.segment_name segment_name, 
      s.bytes bytes
      user_lobs l, - Lobsegmente
      l.table_name = tablename and
      s.segment_name = l.segment_name
    union all
      s.segment_name segment_name, 
      s.bytes bytes
      USER_INDEXES i, - index segments
      i.table_name = tablename and
      s.segment_name = i.index_name);

  table size: = tablesize/1024/1024; - Conversion in MB
  return table size;
The call to the function can then look like this:
  alloc_space_in_mbytes ('GEOM_TABLE_UNTRIMMED') untrimmed, 
  alloc_space_in_mbytes ('GEOM_TABLE_TRIMMED') trimmed
from dual
A test table with 218 237 polygons, a total of 60,754,462 bases and existing spatial index result in the following values:

SDO_ORDINATE_ARRAY values with predominantly 12 to 13 decimal places: 1643.5 MB
SDO_ORDINATE_ARRAY values truncated to 5 decimal places: 1033.5 MB

Friday May 02, 2014

Tips on tuning SDO_NN (nearest neighbor) queries

By:  Daniel Geringer, Senior Development Manager, Oracle Spatial and Graph 

Many times I come across customers who ask for help tuning their SDO_NN queries.

I generally group SDO_NN queries into two categories.
The category defines which SDO_NN parameter to use, SDO_NUM_RES or SDO_BATCH_SIZE.
Choose either SDO_NUM_RES or SDO_BATCH_SIZE, never both.

SDO_NUM_RES -    Find the nearest candidates only with respect to proximity to the window geometry.
                                     There are no additional conditions in the WHERE clause. 
SDO_BATCH_SIZE - Find the nearest candidates, and match additional conditions in the WHERE clause.
                                       For example, find the 5 nearest banks with name = 'CITIBANK'

For "SDO_NN with SDO_BATCH_SIZE", a template is recommended.
Optimizations have been developed when the template is used for "SDO_NN with SDO_BATCH_SIZE" queries.

The template must include the FIRST_ROWS hint and ORDER BY distance for the optimization to take effect.
The following is an example of the template.

--  Find 30 closest banks named CITIBANK to the window geometry no further than 8046.72 meters (or 5 miles) away.
--   This SDO_BATCH_SIZE query includes the first_rows hint, and also ORDER BY distance.
--   Specifying the cutoff distance is not required, but it can also really help performance.
--   For example, distance=8046.72 means stop searching once you reach 5 miles away from the window geometry.
SELECT bank_id, distance
FROM (SELECT /*+ first_rows */ a.bank_id, sdo_nn_distance(1) distance
             FROM banks a
             WHERE sdo_nn (geometry, window_geometry, 'SDO_BATCH_SIZE=10 distance=8046.72', 1)='TRUE'
                 AND a.bank_name = 'CITIBANK'
             ORDER BY distance)
WHERE rownum <=  30;

Not always necessary, but for SDO_NN queries against very dense data (for example, when searching customers in a city with a large population), major performance gains can be achieved by ordering the data in your table spatially by a linear key.

The idea is to order the table during load, where rows containing geometries close to each other
get placed in the same database block. That way, when nearest neighbor gets the next sdo_batch_size set of rows,
and checks additional conditions for a match, the data for the additional condition checks are likely to be in a
database block that is already cached in the SGA. 

If the table is not ordered spatially, potentially, every nearest neighbor candidate (obtained by the spatial index)
might have its additional attributes in database blocks spread all over the place.  This results in much more
disk reads vs in-memory hits to compare the additional conditions in the query.

SDO_NN with SDO_BATCH_SIZE can perform much faster when this is done.
Below are a polygon and point example of how to order a table spatially by a linear key:

-- Make a copy of the POLYGON table.  Call it POLYGON_ORDERED.
connect / as sysdba
grant execute on to some_user;

connect some_user/some_user
DROP TABLE polygon_ordered;
  part1 AS (SELECT /*+ parallel (8) */
                   sdo_geom.sdo_centroid(geom,.05) centroid
              FROM polygon_table)
SELECT /*+ parallel (8) */
       row_number() OVER (ORDER BY md.hhencode_bylevel (p1.centroid.sdo_point.x, -180, 180, 27,
                                                                                                     p1.centroid.sdo_point.y,  -90,  90, 26)) rn
FROM part1 p1;

-- Make a copy of the POINT table.  Call it POINT_ORDERED.
connect / as sysdba
grant execute on to some_user;

connect some_user/some_user
DROP TABLE point_ordered;
  part1 AS (SELECT /*+ parallel (8) */
              FROM point_table)
SELECT /*+ parallel (8) */
       row_number() OVER (ORDER BY md.hhencode_bylevel (p1.geom.sdo_point.x, -180, 180, 27,
                                                                                                     p1.geom.sdo_point.y,  -90,  90, 26)) rn
FROM part1 p1;

Tuesday Apr 01, 2014

Tips for using the Oracle Spatial and Graph geocoding engine to convert addresses directly in the Oracle database

By Karin Patenge, Principal Sales Consultant, Oracle Germany

Many OLTP systems and data warehouse implementations would benefit from having an address cleansing process included which at the same time associates geographic coordinates with each address for spatial analytics. Together with reference datasets from HERE/Nokia or TomTom the Oracle Geocoder can provide this functionality out-of-the-box.

The Geocoding engine in Oracle Spatial and Graph can be accessed using PL/SQL, via the SDO_GCDR package, which belongs to the user MDSYS. In addition to the the actual conversion of an address line into a point geometry (SDO_GEOMETRY with the geometry type 2001) using GEOCODE_AS_GEOMETRY, this package includes the GEOCODE function:

select SDO_GCDR.GEOCODE(user, SDO_KEYWORDARRAY('Schiffbauergasse 14', '14467 Potsdam'),'DE', 'DEFAULT') from dual;

This function provides the second important capability of the geocoder (in addition to the calculation of the geo-coordinates), namely to perform address verification. The return value is an object of type SDO_GEO_ADDR. Based on the address match against the reference dataset, this function returns information such as the quality of the match as well as the completed or corrected address from the reference dataset.

The command describe SDO_GEO_ADDR displays the structure of the object. The result for the query above can be found below, but without looking in the Oracle Spatial Developer's Guide only some parts are meaningful.


To understand the output more easily, here is a PL/SQL script that extracts the array values and interprets the ERRORMESSAGE, MATCHVECTOR and MATCHMODE strings.

create or replace procedure format_geo_addr (
  address SDO_GEO_ADDR
  type strings is table of varchar2(30);
  match_names strings := strings (
    '?            ',
    '0 (MATCHED)  ',
    '1 (ABSENT)   ',
    '2 (CORRECTED)',
    '3 (IGNORED)  ',
    '4 (SUPPLIED) '
  address_elements strings := strings (
    'X Address Point',
    'O POI Name',
    '# House or building number',
    'E Street prefix',
    'N Street base name',
    'U Street suffix',
    'T Street type',
    'S Secondary unit',
    'B Built-up area or city',
    '1 Region',
    'C Country',
    'P Postal code',
    'P Postal add-on code'
  element_match varchar2(128);
  element_match_code char(1);

  if address is not null then
    dbms_output.put_line ('- ID                  ' || address.ID);
    dbms_output.put_line ('- ADDRESSLINES');
    if address.addresslines is not null then
      for i in 1..address.addresslines.count() loop
        dbms_output.put_line ('- ADDRESSLINES['||i||']           ' || address.ADDRESSLINES(i));
      end loop;
    end if;
    dbms_output.put_line ('- PLACENAME           ' || address.PLACENAME);
    dbms_output.put_line ('- STREETNAME          ' || address.STREETNAME);
    dbms_output.put_line ('- INTERSECTSTREET     ' || address.INTERSECTSTREET);
    dbms_output.put_line ('- SECUNIT             ' || address.SECUNIT);
    dbms_output.put_line ('- SETTLEMENT          ' || address.SETTLEMENT);
    dbms_output.put_line ('- MUNICIPALITY        ' || address.MUNICIPALITY);
    dbms_output.put_line ('- REGION              ' || address.REGION);
    dbms_output.put_line ('- COUNTRY             ' || address.COUNTRY);
    dbms_output.put_line ('- POSTALCODE          ' || address.POSTALCODE);
    dbms_output.put_line ('- POSTALADDONCODE     ' || address.POSTALADDONCODE);
    dbms_output.put_line ('- FULLPOSTALCODE      ' || address.FULLPOSTALCODE);
    dbms_output.put_line ('- POBOX               ' || address.POBOX);
    dbms_output.put_line ('- HOUSENUMBER         ' || address.HOUSENUMBER);
    dbms_output.put_line ('- BASENAME            ' || address.BASENAME);
    dbms_output.put_line ('- STREETTYPE          ' || address.STREETTYPE);
    dbms_output.put_line ('- STREETTYPEBEFORE    ' || address.STREETTYPEBEFORE);
    dbms_output.put_line ('- STREETTYPEATTACHED  ' || address.STREETTYPEATTACHED);
    dbms_output.put_line ('- STREETPREFIX        ' || address.STREETPREFIX);
    dbms_output.put_line ('- STREETSUFFIX        ' || address.STREETSUFFIX);
    dbms_output.put_line ('- SIDE                ' || address.SIDE);
    dbms_output.put_line ('- PERCENT             ' || address.PERCENT);
    dbms_output.put_line ('- EDGEID              ' || address.EDGEID);
    dbms_output.put_line ('- ERRORMESSAGE        ' || address.ERRORMESSAGE);
    if address.MATCHVECTOR is not null then
      dbms_output.put_line ('- MATCHVECTOR         ' || address.MATCHVECTOR);
      for i in 1..length(address.MATCHVECTOR) loop
        if address_elements(i) is not null then
          if substr (address.matchvector,i,1) = '?' then
            element_match_code := 0;
            element_match_code := substr(address.matchvector,i,1) + 1;
          end if;
          dbms_output.put_line ('-   '|| substr(address.errormessage,i,1)  || ' ' ||
            match_names (element_match_code + 1) || ' ' ||
            address_elements (i)
        end if;
      end loop;
    end if;
    if address.MATCHVECTOR is not null then
      dbms_output.put_line ('- MATCHCODE           ' || address.MATCHCODE || ' = ' ||
        case address.MATCHCODE
          when  0 then 'Ambiguous'
          when  1 then 'Exact match'
          when  2 then 'Street type not matched'
          when  3 then 'House number not matched'
          when  4 then 'Street name not matched'
          when 10 then 'Postal code not matched'
          when 11 then 'City not matched'
    end if;
    dbms_output.put_line ('- MATCHMODE           ' || address.MATCHMODE);
    dbms_output.put_line ('- LONGITUDE           ' || address.LONGITUDE);
    dbms_output.put_line ('- LATITUDE            ' || address.LATITUDE);
    dbms_output.put_line ('**** NO MATCH ****');
  end if;
show errors

create or replace procedure format_addr_array (
  address_list SDO_ADDR_ARRAY

  if address_list is not null and address_list.count() > 0 then
    for i in 1..address_list.count() loop
      dbms_output.put_line ('ADDRESS['||i||']');
      format_geo_addr (address_list(i));
    end loop;
    dbms_output.put_line ('**** NO MATCH ****');
  end if;
show errors

Now the GEOCODE function can be called again, but this time in conjunction with the format function:

exec format_geo_addr (SDO_GCDR.GEOCODE(user, SDO_KEYWORDARRAY('Schiffbauergasse 14', '14467 Potsdam'), 'DE', 'DEFAULT'));

And the resulting output is now formatted in a manner that is much easier to understand.

- ID                  0
- PLACENAME          
- STREETNAME          Schiffbauergasse
- SECUNIT            
- SETTLEMENT         
- MUNICIPALITY        Potsdam
- COUNTRY             DE
- POSTALCODE          14467
- POBOX              
- HOUSENUMBER         14
- SIDE                R
- PERCENT             .75
- EDGEID              748777426
- ERRORMESSAGE        ????#ENUT?B281CP?
- MATCHVECTOR         ???10101010??400?
-   ? ?             X Address Point
-   ? 1 (ABSENT)    O POI Name
-   # 0 (MATCHED)   # House or building number
-   E 1 (ABSENT)    E Street prefix
-   N 0 (MATCHED)   N Street base name
-   U 1 (ABSENT)    U Street suffix
-   T 0 (MATCHED)   T Street type
-   ? 1 (ABSENT)    S Secondary unit
-   B 0 (MATCHED)   B Built-up area or city
-   1 4 (SUPPLIED)  1 Region
-   C 0 (MATCHED)   C Country
-   P 0 (MATCHED)   P Postal code
-   ? ?             P Postal add-on code
- MATCHCODE           1 = Exact match
- LONGITUDE           13.0745378367008
- LATITUDE            52.4041174822031

Note: The reference dataset used for this test with the Oracle Spatial and Graph geocoder was provided by HERE/Nokia.

Upcoming Webinars: MapViewer at City of Toronto for Public Safety, Customers Achieve 300x Performance Gains with Oracle Spatial and Graph

A note to share information about two upcoming Directions Media webinars on April 23 and May 6.

City of Toronto Enhances Public Safety Using Real-time Big Data and Map Rendering with Oracle and AGSI, Wed., April 23, 2:00PM US EDT

Learn how the City of Toronto Police Services can search, review and map social media traffic in real time to quickly identify and respond to incidents, improving public safety. See live demos of their system using Oracle MapViewer's HTML5 capabilities, Oracle Spatial, and a social media mapping platform from partner AGSI. Carol Palmer of Oracle will co-present this webinar with Mike Jander of AGSI, and City of Toronto, hosted by Directions Media.

Learn more and register for this free webinar -

Learn How Customers Are Experiencing 300x Performance Gains with Oracle Spatial and Graph, Tues., May 6, 2:00PM US EDT (Free Webinar)

Nick Salem of Neustar and Steve Pierce of Think Huddle will share their realized performance benchmarks using Oracle Spatial and Graph. With Oracle Spatial and Graph in Database 12c, customers can address the largest geospatial workloads and experience performance increases of 50 to 300 times for vector operations, with minimal configuration changes. Jim Steiner of Oracle will also discuss performance gains from parallel raster processing and Exadata.

Learn more and register for this free webinar -

Monday Feb 24, 2014

Performance Boost with Aggregating Geometries - with 12c Spatial Vector Performance Acceleration

Nick Salem of Neustar recently shared some impressive performance gains realized using Oracle Spatial 12c Vector Performance Acceleration, in a Google+ post.   He observed performance gains of 40X to 300X -- with a use case aggregating all ZIP Code geographies in California using a SDO_AGGR_UNION operations (plain and with group by/mod functions as described in 11g documented best practices). 

Read more details of his test case and results here.

Thanks for sharing, Nick!


The official blog for the spatial features of Oracle Spatial and Graph, an option of Oracle Database - brought to you by the product managers and developers. Get technical tips, product information, and the latest news here. Visit our official product website at


« May 2016