Skip to main content
  • EN
Hi,

 

 

I have list of IDs (of polygons) in csv and I would like to extract those polygons from Oracle Spatial.Number of polygons may run in tens of thousands. After extracting would like to aggregate them and put it back in database or create a shape file.

 

 

What's tha best and efficient way to do it.

 

 

Cheers,

 

John
Hi,

 

 

one way would be to read the list of IDs using the CSV reader, then send each ID to a FeatureReader to extract the polygons from Oracle. After processing, either write them back to Oracle using a writer in Update-mode, or write it out to a shape file.

 

 

David
Hallo,

 

 

Another option would be to aggregate the ID's and use it in the FeatureReader or SQLExecutor using a IN syntax in your where clause.

 

Itay
...each ID to the reader??? Is'nt that a tad slow?

 

....Aggregate maybe (tens of) thousands of id's into a string..

 

 

Might as well use inlinequerier then.

 

 

When you have (tens of) thousand of polygons and maybe thaousands of ID's i think it is wise to create an external reference tabel.

 

 

 

I think its more efficient use ODBC

 

Save it to a Excel and make a datalink to it.

 

(connect to Excel and Access by using Oracle Heterogeneous Service...which has been available since Oracle 8i.)

 

 

https://docs.oracle.com/cd/E11882_01/owb.112/e10582/loading_ms_data.htm

 

 

 

 
hitting the db for each feature is less efficient than aggregating it, but it all depends on the db settings.

 

Then again you can just do it all in Oracle, no need to extract the data and write it back.

 

 
I would have to test to see if linking to a excel in a Oracle SQL is not faster then having to do an 'In" on a string with (ten)thousands of elements.... try it out.
the IN clause has a limitation of a 1000 features, but you overcome this by building it in your workspace.

Reply


Community Stats

30,108
Posts
114,295
Replies
37,744
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.