Create Spatial View with ArcSDE feature and external tables

4424
12
01-31-2017 07:53 AM
WalterDziuba1
New Contributor III

Hello,

I need to create a spatial view in an ArcSDE enterprise database that links a table from a feature class and 3x other tables from an external database.  This view needs to be registered with SDE. 

We are running ArcSDE 10.4.1

From what I've been able to gather, is that I need to create a python script creating and registering the view.

Does anyone have any python samples that I can look at?

Is this the proper way of achieving this goal?

Help, is badly needed.

Walter

0 Kudos
12 Replies
AdamZiegler1
Esri Contributor

Hi Walter - what flavor database are you using?

-Adam Z

0 Kudos
AdamZiegler1
Esri Contributor

I have implemented spatial views in the past. My rule of thumb is if it gets complicated I create the view natively in the database and then register it with the Enterprise geodatabase. The registration is completed in via your connection to the database, right-clicking on the layer, manage and register with geodatabase.

-Adam Z

0 Kudos
WalterDziuba1
New Contributor III

Adam,

We're using ArcGIS server 10.4.1. So does that mean that the SDE is of the same version?

Walter

0 Kudos
WalterDziuba1
New Contributor III

Adam,

So by accessing the ArcSDE database through Catalog, I can use the create view function to create this spatial view? So, under the view definition, I create a sql statement, similar to that what I was trying in SQL server to accomplish this task?

Any sample sql statement would be much appreciated.

Walter

0 Kudos
AdamZiegler1
Esri Contributor

You'll need an Enterprise Geodatabase in order to facilitate spatial views. Are you running an Enterprise geodatabase, i.d. Sql Server or Oracle?

EDIT: Essentially, yes. You could use the create view in ArcCatalog to insert your sql statement. Where there are more than two tables involved I prefer to create the view in the database and then register it. Helping with the statement is difficult unless we know where your SQL Server databases (right, SQL Server?) are positioned and how the tables relate to one another or to the base feature class.

-Adam Z

WalterDziuba1
New Contributor III

Adam,

Thank you for your assistance today. It was much appreciated. And yes, I was able to create the spatial view from ArcCatalog and registered it with the database using the GP tools. 

The vies is responding as it should.

Thank again.....Walter

0 Kudos
WalterDziuba1
New Contributor III

Adam,

I need your assistance again. Rather than using ArcCatalog to create the spatial  view, I initially used SQL Server Management studio to create the view inside our SDE database. This, I was told, can corrupt the database. Rather, I should have used the Create > View in ArcCatalog and only supply the correct sql statement. But when I attempt to register the  view with the SDE database, I get the 001050 error: Either registered with geodatabase already or cannot open the dataset.

After additional research, it seems that only feature classes or tables can only be registered with the geodatabase once and only feature classes or tables are supported?

Walter

0 Kudos
AdamZiegler1
Esri Contributor

Hi Walter - Did you receiving the error while trying to register the view you created in SQL Server Management Studio or the one you created in ArcCatalog. When you use the ArcCatalog tools, it will be registered with the GDB automatically, which would validate the error (in this case it's more of a warning that the view has already been registered).

Making views in the database is not going to corrupt the database. You have to be careful with views that have been registered with the GDB because there are items written to SDE tables. Here's a quote from help: "You can use the Create Database View tool or the database context menu in ArcGIS Desktop to create a database view, or use the native SQL of your database management system (DBMS)."

Views in an enterprise geodatabase—Help | ArcGIS Desktop 

-Adam Z

0 Kudos
WalterDziuba1
New Contributor III

Adam,

After my view is created in the SDE, I then alter the data source in my mxd to use the newly created view as it's data source. But then when I attempt to create a map service out of this, during the analyze stage, I get a high error alert which then tells me that I need to register the layer with the database and it will not allow me to publish.

Walter

0 Kudos