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!
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.
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!
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.
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.