Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I have 200 .xyz files containing 1500000 points each. Is this possible to import this dataset to oracle?

It makes a total of 300.000.000 points!!!

Background is I want to improve a FME script that cuts out a desired region of elevation data. Today it uses a .shp grid to find the .xyz files intersected. This is a done with two linked fme workspaces, very complicated procedure. If I can get it into oracle, an oracle reader with bounds would do the same job more straightforward..

Any input regarding elevation data in databases or FME retrival of elevation data is much appreciated.

Best regards!

share|improve this question
    
Is this Lidar data? –  Kirk Kuykendall Mar 15 '13 at 13:32
    
300 million points is manageable with oracle spatial 11g R2 –  Mapperz Mar 15 '13 at 14:01
    
Recommend the FeatureReader with the Spatial Interaction setup evangelism.safe.com/fmeevangelist78 will read only the area of interest much faster. –  Mapperz Mar 15 '13 at 14:06
    
Kirk - yes initially it was Lidar now its just lots of rows like this "x y z": 105869.084 6213011.402 49.940 –  giskis Mar 15 '13 at 14:15
    
Mapperz - good, the 300 million was mostly what I was worried about. And yes, I think thats the reading method I had in mind! Do you think there will be a problem inserting all the points at once, any risk for crash etc? –  giskis Mar 15 '13 at 14:20
add comment

2 Answers

I think you're correct that Oracle will do a quicker job in clipping the data, because then the data will be (presumably) indexed. FME is working on the basic xyz files that are un-indexed. So naturally it will be slower.

If the data is unchanging then you could convert it from xyz to a spatially indexed format that FME could use - for example POD - and the speed would be drastically improved. But still, indexed data stored in Oracle and being processed by Oracle should always have the better performance.

As to FME methodology, I wouldn't necessarily agree with saying it would be more straightforward with Oracle. I just don't see why your FME solution should be so complex (unless there are other details I don't know about).

The ideal solution is a single workspace with a reader to read the Shape dataset. You feed that into a FeatureReader to read the xyz, using the Shape feature as a clip boundary. Then you write the data out with a writer. It should be very simple. I certainly don't see a need to have two linked FME workspaces.

One other FME tip. Be sure to use the XYZ format reader. That way FME will create a single, efficient point-cloud feature. If you use the CSV format and a transformer to convert it to points, then you'll be working with 300m individual point features and the process will be much, much slower.

share|improve this answer
add comment

Handle data as CSV.

In Postgresql i would do something like

Copy xyz from '/path/to/csv/xyz.txt' DELIMITERS ',' CSV; copy command is fastest way to import data into database.

If you run that stuff thru FME it will be slow. Fastest way with FME would be PATHreader->SQLExecutor (execute copy here) ->Results, if you use xyz reader it will be slower. And It would be faster if you read xyz file as point cloud vs points.

followin links seems to have howto do it in Oracle http://www.orafaq.com/wiki/SQL*Loader_FAQ

share|improve this answer
    
Thanks alot! Will check up next week when I insert it. To bad my FME licence doesnt come with the oracle spatial object writer stuff :) –  giskis Mar 15 '13 at 14:23
    
i have same problem with MsSQL spatial. i use stringconcentrator and 'insert into table .... '@'var1 , '@'var2' to text file to go around that limit –  simplexio Mar 18 '13 at 7:51
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.