topic Re: Database spatial enabled view from NonSpatial Database in Geodatabase Questions

Database spatial enabled view from NonSpatial Database

User35489 — Sun, 07 Feb 2016 11:43:50 GMT

Greetings All,

My Environment: ArcSDE 10.1, Linux RHEL 6, Oracle 11g2

In our environment we have a two corporate databases Database1(non-spatial) and Database 2(geodatabase). Database 1(non-spatial) has a table TAB1 with geometry column and it gets continiously updated. I want to create a spatially enabled view in DATABASE2(geodatabase) of this table to get updates simultaneously to our GIS users using Database2(geodatabase).
Please help me with the best way to do this.

My Homework: In DATABASE2 .. I created oracle view .. registered it as layer, but it failed to draw geography in ArcMap with message"Spatial Index needed". It once again failed when i tried to create spatial index on the view.

Thanks
-AS

Re: Database spatial enabled view from NonSpatial Database

Anonymous User — Mon, 08 Feb 2016 09:21:39 GMT

Hi Abdullah

You can try with materialized views both with ESRI st_geometry or Oracle sdo_geometry. I think you can build a spatial index over a materialized view.

Let me know.

Regards

Jesus de Diego

Re: Database spatial enabled view from NonSpatial Database

VinceAngelo — Mon, 08 Feb 2016 14:23:24 GMT

In all RDBMSes except Oracle (11g and below), different "databases" are actually stored in the same instance. The fact that this is not true in Oracle means you can't use the same techniques as recommended for other RDBMS software.

It is not possible to create an index on a view. Views always use the index on the base table, unless the view is really an Oracle database link, in which case it often won't use the index even then.

What you need to do is make a decision. You can:

Establish a connection protocol that allows GIS users to access the spatial table in database1 via Query Layers
Establish a process to replicate the database1 table in database2 (and then another process to maintain synchronization when changes occur in database1)

Since I'm a data manipulation geek, I'm usually asked to do option 2, but it's often more complex and subject to periodic re-synchronization than most project managers realize.

- V

Re: Database spatial enabled view from NonSpatial Database

User35489 — Mon, 08 Feb 2016 15:55:29 GMT

Hi Alarcon,

I have a setup to synchronize data between two tables with help of materialized views. Its working perfectly. What i was trying to do is to look for a different method. Your suggestion is one of the successful setups for synchronization.

Thanks once again.

Re: Database spatial enabled view from NonSpatial Database

Anonymous User — Mon, 08 Feb 2016 16:01:44 GMT

You can create a new table/layer on DATABASE2 and create triggers for updating this table/layer each time table in DATABASE1 is modified...

Regards

Jesus de Diego Alarcon

Re: Database spatial enabled view from NonSpatial Database

User35489 — Mon, 08 Feb 2016 16:07:35 GMT

Thanks vince,

Query layer process: I can view the table in database1 through ArcCatalog/ArcMap 10.3 as spatial table 'iconed', but when i switch to 'Geography' in ArcCatalog or When i drag it to ArcMap the fetures are not drawn, only table content is view able. What is the issue i don't know(Please suggest)

Replication: Recently i implemented this process, writing a package(oracle procedure) and scheduled it to run on specific intervals(job scheduler) which syncs data.

Re: Database spatial enabled view from NonSpatial Database

VinceAngelo — Mon, 08 Feb 2016 16:17:07 GMT

There could be any number of problems, but insufficient information to determine which one.

- V