This discussion is archived
5 Replies Latest reply: Sep 12, 2013 12:45 AM by Ivan Bush RSS

Converting large amounts of points - 76 million lat/lon's to spatial object...

JohnL Newbie
Currently Being Moderated

Hello, I need help.

Platform - Oracle 11g 64bit on Windows Enterprise server 2008 64bit.  64 GB of ram with 2 CPUs totalling 24 cores

Does any one know of a fast way to convert large amounts of points to a spatial object?  I need to convert 76 million lat/lon's to ESRI st_geometry or Oracle sdo_geometry.

Currently, I have setup code using pipelined parallel functions and multiple jobs that run concurrently.  It still takes over 2.5 hours to process all of the points.

Any pointers would be GREATLY appreciated!

 

Thanks

 

John

  • 1. Re: Converting large amounts of points - 76 million lat/lon's to spatial object...
    John O'Toole Journeyer
    Currently Being Moderated

    Hi,

     

    Where is the lat/lon data at the moment?  In an external text file or in an existing database table as number attributes?

     

    If they're in an external text file, then I'd probably use an external table to load them in as quickly as possible.

    If they're in an existing database table, then you can just update the sdo_geometry column using:

    update <table> set <geometry column> = sdo_geometry(2001, <your srid>, sdo_point_type(<lon column>, <lat column>, null), null, null)
    where <lon column> is not null
    and <lat column> is not null;


    That should run very quick for you.  If you want to avoid the overhead of creating redo, you could use "create table .... as select...".  This example of creating 1,000,000 points runs in 9 seconds for me.

     

    create table sample_points (geometry) nologging as
      (select sdo_geometry(2001, null, 
      sdo_point_type(
      trunc(100000 * dbms_random.value()), 
      trunc(100000 * dbms_random.value()),
      null), null, null)
      from dual connect by level <= 1000000);

     

    I have setup code using pipelined parallel functions and multiple jobs that run concurrently

    You shouldn't need to use pl/sql for this task.  If you find you do, then provide some sample code and we'll take a look.


    Regards,

    John O'Toole

  • 2. Re: Converting large amounts of points - 76 million lat/lon's to spatial object...
    JohnL Newbie
    Currently Being Moderated

    You are absolutely correct. Using the Oracle sdo_geometry, all 76 millions points were converted in 85 secs with out the use of parallel queries or parallel processes.  The problem I am having, is that we are forced to use the ESRI st_geometry object, which is determined by future analysis.  Following is the code I use to create the spatial object:

     

    create table sample_points (
       RECORDID,
       SHAPE) nologging as
       (select  /*+ NO_PARALLEL_INDEX(jl) */
               tab_row_id,
     sde.st_geometry (longitude, latitude, null, null,0)
       from jl_dano_all jl);

     

    The process was still running after 11 hours, with only 4% of the blocks read from the table.

     

    Thanks for your input!

     

    John

  • 3. Re: Converting large amounts of points - 76 million lat/lon's to spatial object...
    B Hall Explorer
    Currently Being Moderated

    Forced by who? For what "future analysis" - moving to SQL Server?

     

     

    Our ESRI users always went to the kool-aid conference and for years told me that we should not use sdo_geometry, but instead ESRI's binary format because it "was better". Better for what? ESRI's data lock in? This from the people that generally treat the database as a bit bucket?

     

     

    No, the role of the DBA/database developer is to tell the app user community the best way to store and manage data for any given tool/requirement. That was never their binary format, and is certainly not ESRI's special new st_geometry format. Neither are native to Oracle, and neither can operate with the optimizer to efficiently return data. They could have choose to support Oracle's implementation of the st_geometry type if they really wanted that format - but they did not.

     

     

    So if I was in your shoes, I'd tell them to change the default storage format to sdo_geometry for best performance. If they insist on st_geometry, tell them you cannot help them when their screen refreshes are dog slow as they are not using a database vendor supported format.They won't like it - but you should never let application people tell you what works best in the database - they are not database people. Results are what matter - not vendor hoopla.

     

     

    Bryan

  • 4. Re: Converting large amounts of points - 76 million lat/lon's to spatial object...
    John O'Toole Journeyer
    Currently Being Moderated

    I'd have to agree with Byran.  In my opinion sde.st_geometry isn't a great choice for storing data in Oracle.  Fight your corner and fight the "future analysis" - sdo_geometry is the way to go for storing spatial data in Oracle.

  • 5. Re: Converting large amounts of points - 76 million lat/lon's to spatial object...
    Ivan Bush Journeyer
    Currently Being Moderated

    JohnL,

     

    I also agree with Bryan and John. There are plenty of developments which have turned into disasters because the spatial data is stored in the wrong format. You should not have to fight very hard because you can demonstrate the performance issue with the data conversion. This will be repeated in any application trying to access it.

     

    The most important people here are the users. They will not expect to have to wait for the screen to render. It is ultimately the users that will have to accept the whole system and my experience, over the many years involvement, is that they will not sign this off.

     

    Best of luck.

     

    Ivan

Legend

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