topic Re: Create Spatial View with ArcSDE feature and external tables in Data Management Questions

Create Spatial View with ArcSDE feature and external tables

WalterDziuba1 — Tue, 31 Jan 2017 15:53:50 GMT

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

Re: Create Spatial View with ArcSDE feature and external tables

AdamZiegler1 — Tue, 31 Jan 2017 16:03:35 GMT

Hi Walter - what flavor database are you using?

-Adam Z

Re: Create Spatial View with ArcSDE feature and external tables

AdamZiegler1 — Tue, 31 Jan 2017 16:19:55 GMT

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

Re: Create Spatial View with ArcSDE feature and external tables

WalterDziuba1 — Tue, 31 Jan 2017 16:22:24 GMT

Adam,

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

Walter

Re: Create Spatial View with ArcSDE feature and external tables

WalterDziuba1 — Tue, 31 Jan 2017 16:25:49 GMT

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

Re: Create Spatial View with ArcSDE feature and external tables

AdamZiegler1 — Tue, 31 Jan 2017 16:35:31 GMT

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

Re: Create Spatial View with ArcSDE feature and external tables

WalterDziuba1 — Tue, 31 Jan 2017 20:32:18 GMT

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

Re: Create Spatial View with ArcSDE feature and external tables

WalterDziuba1 — Mon, 06 Feb 2017 15:56:23 GMT

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

Re: Create Spatial View with ArcSDE feature and external tables

AdamZiegler1 — Mon, 06 Feb 2017 16:29:13 GMT

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

Re: Create Spatial View with ArcSDE feature and external tables

WalterDziuba1 — Mon, 06 Feb 2017 17:25:09 GMT

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

Re: Create Spatial View with ArcSDE feature and external tables

MichaelVolz — Mon, 06 Feb 2017 17:55:27 GMT

Walter:

Were you able to create spatial views in older versions of ArcGIS for Desktop and publish mapservices with this data without getting the high alert error that you mentioned above?

Re: Create Spatial View with ArcSDE feature and external tables

AdamZiegler1 — Mon, 06 Feb 2017 17:59:29 GMT

Walter - Do you have this patch installed?

ArcGIS for Server Publishing Patch

-Adam

Re: Create Spatial View with ArcSDE feature and external tables

WalterDziuba1 — Mon, 06 Feb 2017 18:04:38 GMT

Gentlemen,

This is the first time we're trying to use spatial views for our project. I was attempting to publish last week, but since the layer data source was not registered, it was trying to copy the entire database over to ArcGIS server. But initially, it was letting me publish, without the high alert. Now it won't even let me publish.

Adam, am not sure if we've installed the server publishing patch. But I will inquire.

Walter