Skip to main content
Hello,

 

 

I want to create a shapefile from a view in Oracle Spatial. View is created by another application by querying number of table. So schema of the view changes every time. I am able to create shapefile first time but once schema changes I am not able to create. What should I need to do to handle this dynamic schema.

 

 

Cheers
HI,

 

You gave the answer yoursefl 🙂 use the dynamic mode.

 

Tha way the writer will read the schema and apply it while running the worksoace.

 

 
MaterialisedView u can use a schemareader or a schemamapper.

 

 

 

If it is not a MV then  u can extract  the SQLquery of a certain view by

 

 

SELECT text

 

FROM all_views

 

WHERE view_name = 'DGDTW_VW_FUG'

 

 

(replace 'DGDTW_VW_FUG' with view you want to read of course)

 

 

1 Then parse the txt record using stringsearcher. For example regexp=" \\(select (.*) from .*"

 

2 Then Use attributesplitter to put result in a list; splitcharacter = ",".

 

 Explode list using listexploder.

 

3 etc. if and when nescessary 

 

   Stringsearcher or tester to filter out things like unions etc.

 

 

It depends on how complex the View is actualy.

 

If it is a simple select from then steps 1 and 2 will do.

 

If it is a very complex view, u need to finetune stringsearchering and regularexpressions.

 

 

 

 
Hi ETLS-Itay,

 

 

I tried using dynamic mode but not able to create output.

 

 

Cheers
Mind you a shapefile has its limitations, for example not all atrribute names will map correctly if they are longer that 10 chars
Yes I am aware of that even if I change output format from shape to MITAB it's failing.

 

Cheers
I read from your post that the views are created in ORA Spatial by another application.

 

I assume you are not tallking about materialised views.

 

So to get its schema, you can not use a schemareader, like you can with a materialised view or normal table.

 

The method above can read the schema.

 

 

 

 

Third column are the attributes.

 

 

Once you have the schema, you can use it to create a schemamap and then use this map to dynamicaly map it to the shape objects.

 

(if you're interested i can post the workbench i just made to create above table)
Thanks Gio.

 

Yes view is created by other application depending on user selection on attributes. Now I am able to get the schema as suggested but still struggling to bind this schema with shape objects. Any pointers in this direction?

 

Cheers
If you have extracted the schema you then should be able to use dynamic writer, a shapefilewriter in dynamic mode.

 

 

On writer's general  tab you can choose Dynamic properties and point to the schema resource.

 

 

Of course you have to write the extracted shema to a file first.

 

And of course you need the objects form the view...

 

 

With

 

SELECT text

 

FROM all_views

 

WHERE view_name = 'DGDTW_VW_FUG'

 

 

Connect a SQL executor and enter the "text' as sql. It should run and get the geoobjects.

 

 

Write them out to shape using the schema you created..

 

 

(of course take care running sql you dont know...lol)

 

 
Thanks Gio,

 

If I understand corrrectly, this means I need 2 workbenches first to create schema resource and second main workbench. Is this correct or am I missing something.

 

 

Cheers.
Yes,

 

Schema resource must be create first. (because view is unknown as well as its attributes)

 

 

You can have the one in wich you create the schema call the main trough a workspacecaller.

 

 

btw i tested these suggestions on some views here, and came across a view wich was larger then the maximum characters fo a text field...means it could not run the sql as it didnt fit. (most of the views fit though..)

 

You may want to keep an eye on this.
Thanks Gio. I learned lot of things which I might have been difficult to learn by going through documentation.

Reply