This discussion is archived
9 Replies Latest reply: Jun 25, 2012 11:52 PM by Simon Greener RSS

is it a good idea to use oracle database to process the geometry data?

940155 Newbie
Currently Being Moderated
I am working on a map related project. Here are the stops I need to do.

1. load geometry information from a table with spatial filter and other conditions (where clause)
2. get the points from the geometry column (sdo_geometry)
3. transform the coordinates
4. encoding the coordinates
5. output

We have a third party applicaiton that can do both #2 and #3. However the process is slow. Some quick tests show that all the steps from #1 to #4 can be done within oracle and the speed is faster. My concern is if it is a good approach to use oracle, a database server, to do all the data processing that can be done and normally shoudl be done on a web server.

Any adivce please?

(BTW, we use oracle 10g on windows 2008R2 64bit, and we may upgrate to 11g in about a year.)
  • 1. Re: is it a good idea to use oracle database to process the geometry data?
    Simon Greener Journeyer
    Currently Being Moderated
    James,

    I am quite sure there is a database-based solution for all your steps.

    *1. load geometry information from a table with spatial filter and other conditions (where clause)*

    I assume you mean something like:
    CREATE TABLE load_table AS SELECT ID, GEOM FROM base_table where SDO_INSIDE(....) = 'TRUE';
    *2. get the points from the geometry column (sdo_geometry)*

    I assume you mean something like:
    SELECT ID, v.x, v.y FROM load_table l, TABLE(sdo_util.getVertices(l.geom)) t;
    (See last comment about data.)

    The problem is that we need a lot more information on what it is that you want to do in steps 3 - 5.

    *3. Transform the coordinates.*

    What sort of transformation? Rotation, Shift, Scale, Coordinate rounding?

    *4. Encoding the coordinates*

    What do you mean by this? Write them out as WKT/KML/GML?

    *5. Output*

    Again, output to what, where? A new table? A shapefile? A text file?

    Also, if you can provide a single sdo_geometry object + data and some example SQL it would help as well.

    Here is an example where I take a geometry, rotate it (cf 3. Transform), extract vertices (2. get points), encode it (4. Encoding), and then output to screen (5. Output).
    WITH myGeom As (
      SELECT 1 as id,
             MDSYS.SDO_GEOMETRY(2006, NULL, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1,5,2,1), MDSYS.SDO_ORDINATE_ARRAY(355600.52, 5407396.19, 361365.32, 5408106.36, 356488.27, 5409242.37, 357437.46, 5406457.67)) as geom 
        FROM DUAL
    )
    SELECT g.id || ',' || t.x || ',' || t.y as csv
      FROM myGeom g,
           TABLE (sdo_util.getVertices(
                    GEOM.Rotate(p_geometry  => g.geom,
                                p_tolerance => 0.005,
                                p_rotatePt  => MDSYS.SDO_Point_Type(357437.46,5406457.67,NULL),
                                p_rotation  => 45 ))) t;
    
    -- Results
    --
    CSV
    ----------------------
    1,355474.91,5405822.39
    1,359049.08,5410400.89
    1,354797.2,5407755.57
    1,357437.46,5406457.67
    Now show us what it is you want to do!!

    regards
    Simon
  • 2. Re: is it a good idea to use oracle database to process the geometry data?
    940155 Newbie
    Currently Being Moderated
    Hi Simon,

    Thanks for your help.

    Yes all the steps can be done within the database side (Oracle). However I could do the same (extracting the coordinates from the sdo_geometry column, transforming the coordinates and encoding the coordinates) in .net code (web server side) by making use of the new verison of the odp.net. So I have to make a decision now to choose which approach to go, relying on Oracle server to do all the data processing or .net code. I am pretty sure Oracle can process a single request fairly quick, but if tens or even more concurrent requests for the process, I have concerns over the performance or reliability of the database server, especially the server is (a kind of ) beyond of our control (in control of the midware, IS and network team). Somebody said database is just a database and for data storing and retrieving, but may not for processing the data.

    Here is more information regarding what we are doing. We use Google Map API to disply parcels. Each time the map is loaded, panned, zoomed, or refreshed (and so on), we need to query the database with spatial filter and some other criteria. The result will be tens or up to handreds of parcels containing hundreds to thousands of coordinates that are extracted from the parcel table that has a sdo_geometry column. We are expecting tens to hundreds of these requests every minute.

    Now I am trying to chain every step together and do some tests using both appoachs in order to see which way is more effient and promising. I am guessing there won't be too much difference in terms of the performance for some simple testing unless I run some load testing, or if somebody like you guys can help me out.

    1. load geometry information from a table with spatial filter and other conditions (where clause)


    Regarding the issues you asked:
    ====================================
    I assume you mean something like: Yes


    CREATE TABLE load_table AS SELECT ID, GEOM FROM base_table where SDO_INSIDE(....) = 'TRUE';



    2. get the points from the geometry column (sdo_geometry)

    I assume you mean something like: Yes


    SELECT ID, v.x, v.y FROM load_table l, TABLE(sdo_util.getVertices(l.geom)) t;



    (See last comment about data.)

    The problem is that we need a lot more information on what it is that you want to do in steps 3 - 5.

    3. Transform the coordinates.

    What sort of transformation? Rotation, Shift, Scale, Coordinate rounding? Transform from UTM to LatLng

    4. Encoding the coordinates

    What do you mean by this? Write them out as WKT/KML/GML? Google map encoded polylines

    5. Output

    Again, output to what, where? A new table? A shapefile? A text file? JSONP formatted data

    Also, if you can provide a single sdo_geometry object + data and some example SQL it would help as well.

    Here is an example where I take a geometry, rotate it (cf 3. Transform), extract vertices (2. get points), encode it (4. Encoding), and then output to screen (5. Output).


    WITH myGeom As (
    SELECT 1 as id,
    MDSYS.SDO_GEOMETRY(2006, NULL, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1,5,2,1), MDSYS.SDO_ORDINATE_ARRAY(355600.52, 5407396.19, 361365.32, 5408106.36, 356488.27, 5409242.37, 357437.46, 5406457.67)) as geom
    FROM DUAL
    )
    SELECT g.id || ',' || t.x || ',' || t.y as csv
    FROM myGeom g,
    TABLE (sdo_util.getVertices(
    GEOM.Rotate(p_geometry => g.geom,
    p_tolerance => 0.005,
    p_rotatePt => MDSYS.SDO_Point_Type(357437.46,5406457.67,NULL),
    p_rotation => 45 ))) t;

    -- Results
    --
    CSV
    ----------------------
    1,355474.91,5405822.39
    1,359049.08,5410400.89
    1,354797.2,5407755.57
    1,357437.46,5406457.67



    Now show us what it is you want to do!!

    Edited by: James Dong on May 31, 2012 11:41 AM
  • 3. Re: is it a good idea to use oracle database to process the geometry data?
    ToM2 Newbie
    Currently Being Moderated
    Hi,
    It's good idea, to do it on DB side.
    1. No context switching
    2. Permission/authentication
    3. very good parallelization
    4. geometry validation
    5. maitenance(no problem with new version, patches, patchsets)
    6. if You need more power in PLSQL code, you can use native compilation(in math operation speed over x10)
    Regards,
    Tomek
  • 4. Re: is it a good idea to use oracle database to process the geometry data?
    Simon Greener Journeyer
    Currently Being Moderated
    James,
    Yes all the steps can be done within the database side (Oracle). However I could do the same (extracting the coordinates from the sdo_geometry column, transforming the coordinates and encoding the coordinates) in .net code (web server side) by making use of the new verison of the odp.net. So I have to make a decision now to choose which approach to go, relying on Oracle server to do all the data processing or .net code. I am pretty sure Oracle can process a single request fairly quick, but if tens or even more concurrent requests for the process, I have concerns over the performance or reliability of the database server, especially the server is (a kind of ) beyond of our control (in control of the midware, IS and network team). Somebody said database is just a database and for data storing and retrieving, but may not for processing the data.
    The decision as to how to deploy functionality is not an either/or binary process that is easily resolved by saying a "database is just a database and for data storing and retrieving, by [but?] may not [be used?] for processing the data".
    Before you jump into spending your company's money on employing you to write .NET code that is so scalable and mutlithreaded that it is capable of scaling out of the box, I would suggest you look into the database a lot more.
    Here's some pointers.

    *1. Transform from UTM to LatLng*
    Now, it is true that sdo_cs.TRANSFORM is slow when applied across multiple rows dynamically in a query. However, this can be conceived of being a data-level operation such that one could create a materialized view (fast refresh or otherwise) that is constructed to hold either/or/both a UTM SDO_GEOMETRY object and a LATLONG SDO_GEOMETRY column. SDO_CS.TRANSFORM_LAYER is far faster and could be used when constructing the MV or a table over which an MV is created. Triggers could ensure that the UTM and LATLONG SDO_GEOMETRY columns are kept synchronised (if a table is used). Doing this at the data level relieves the .NET application of a costly transformation operation. The database pays a one off cost and a minor refresh/update cost which it can then leverage many, many times.

    Database 1 .NET App 0

    *2. Google map encoded polylines*

    I will assume you are talking about KML. Now, in 10gR2 there is no SDO_UTIL.TO_KMLGEOMETRY (11g). However, you could either upgrade or deploy some Java into the database JVM or KML (see my free KML PL/SQL package for some ideas - http://www.spatialdbadvisor.com/source_code/218/kml-package-documentation), and then take the same approach as 1. That is, create another column on your table to hold the KML and keep them synchronised via Triggers or use a Materialized View (with whatever refresh option you want). Then in you application first search (SDO_FILTER etc) on the SDO_GEOMETRY but map/use the KML. Doing this at the data level relieves the .NET application of a costly KML transformation operation. The database pays a one off cost and a minor refresh/update cost which it can then leverage many, many times.

    Database 1 .NET App 0.2

    3. JSONP formatted data

    This is the same as the KML problem. I have written an example GeoJSON PL/SQL function for Oracle that might give you some ideas.
    http://www.spatialdbadvisor.com/oracle_spatial_tips_tricks/206/sdo2geojson
    Or you could write some Java and deploy a SDO_GEOMETRY to GeoJSON function into the JVM (straightforward).

    Database 1 .NET App 0.2

    To be honest, all that you are doing is data representation change. All this can be done, and I would say, should be done in the database, in such a way that you can leverage its well known speed and scalability (even across mutliple applications). Use what someone (Oracle) else has already done, and done well, before trying to create something yourself.

    regards
    Simon

    Edited by: Simon Greener on Jun 1, 2012 3:56 PM
  • 5. Re: is it a good idea to use oracle database to process the geometry data?
    940155 Newbie
    Currently Being Moderated
    I'd like to give my thanks to Tomek and Simon for providing very helpful advices.

    I am sorry to come back to the post so late. I did try the approaches I mentioned and it turned out that the asp.net + odp.net won out. Some quick tests at the beginning showed the .net app is about ten times faster than the complete Oracle side solution. Here are some numbers.

    130 Parcels(polygon), over 1200 points, takes about 1.4~1.5 Sec. if process done on Oracle, but less than 100ms taken using .net code

    There could be a lot of reasons why Oracle process is slow and, as Simon pointed out, the Oracle TRANSFORM function is one of them. I did not try the SDO_CS.TRANSFORM_LAYER function, or any other possible ways to improve the transform performance as it involves more efforts and more unknown factors.

    To summarize, I just did my project and by using the pure .net code to deal with the geometry data seems to satisfy our applicaiton.
  • 6. Re: is it a good idea to use oracle database to process the geometry data?
    Simon Greener Journeyer
    Currently Being Moderated
    James,

    It is very, very, very hard to compare apples with oranges when one is not privy to the final Oracle and .NET code.

    But let's take what you assert as correct. You are right: the .NET code wins. But only if SPEED is the measure that determines the best outcome.

    It is sad that you have had to program up yourself what Oracle has made available via the license fee. One therefore wonders at the value of anything other than straight SDO_GEOMETRY storage and search to your company and your project. Are you licensed for Spatial or just Locator?

    Then all I can say is that you MUST lodge a ER with Oracle providing them with a USE CASE (data+code) they can use to improve their performance as they claim that this is the only way they respond to performance and other issues (not like the PostGIS and other product forums where the dev team will respond to clearly demonstrated problems).

    This request is not just about you and your problem but lots of us have problems with the speed of some spatial functions and look for Oracle to improve them.

    For example, they MIGHT be able to improve the performance of SDO_CS.TRANFORM as other database solutions etc run far faster indicating some sort of algorithmic/implementation issue. Also, SDO_UTIL.TO_GMLxxxx are all coded in Java and NOT C and thus suffer a context switch everytime SQL calls Java via PL/SQL slowing execution time. Since PostGIS's transformation routines are all written in C it can't be that hard for the Oracle team to re-code to get the sort of performance we expect.

    Perhaps some of us should look at building an EXTPROC based on the C PostGIS projection and GEOS processing libraries!

    regards
    Simon
  • 7. Re: is it a good idea to use oracle database to process the geometry data?
    John O'Toole Journeyer
    Currently Being Moderated
    For example, they MIGHT be able to improve the performance of SDO_CS.TRANFORM as other database solutions etc run far faster indicating some sort of algorithmic/implementation issue.
    James mentioned that he is on 10g and may upgrade to 11g in a year.

    Note that the performance of SDO_CS.TRANFORM has been improved in 11gR2. There are some notes about this on slide 22 of http://download.oracle.com/otndocs/products/spatial/pdf/oow_2011/oow2011_sp_betrbizapps.pdf
    Fast Coordinate System Transformations
    • SDO_CS.Transform is upto 10 times faster in 11gR2
    – This speedup is seen if 1000s of transformations are done in a session
    – This is typical for mapping applications where themes are transformed at run time to match the base map SRID
    • In the prior releases, the transformation context was created for each transform() call
    – now we use that context between transform() calls with the same source and traget SRIDs
  • 8. Re: is it a good idea to use oracle database to process the geometry data?
    Simon Greener Journeyer
    Currently Being Moderated
    John,

    Very good, I missed him being on 10g.

    Also, it would help James if you could performance profile the Oracle SQL/Code and tell us where, exactly, is the bottleneck. We all think it is TRANFORM but....

    regards
    Simon
  • 9. Re: is it a good idea to use oracle database to process the geometry data?
    Simon Greener Journeyer
    Currently Being Moderated
    John,

    Last time I worked with sdo_cs.Transform performance tuning a slow data loading process was with an Oracle Database 11g Release 11.1.0.7.0 – Production database. Which probably ran about the same as 10gR2.

    regards
    Simon

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points