4

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!

Improve this question
5
  • Is this Lidar data?
    – Kirk Kuykendall
    Commented Mar 15, 2013 at 13:32
  • 300 million points is manageable with oracle spatial 11g R2
    – Mapperz ♦
    Commented Mar 15, 2013 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 ♦
    Commented Mar 15, 2013 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
    Commented Mar 15, 2013 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
    Commented Mar 15, 2013 at 14:20

2 Answers 2

Reset to default
2

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.

Improve this answer
1

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

Improve this answer
2
  • 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
    Commented Mar 15, 2013 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
    – simpleuser001
    Commented Mar 18, 2013 at 7:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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