By jeanihm-Oracle on Jan 06, 2016
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.
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
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 )
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 );
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);
Quick usage example =>
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.