topic Re: Best practices for using nonspatial tables (SQL Views) as event layers? in Data Management Questions

Best practices for using nonspatial tables (SQL Views) as event layers?

SusanZwillinger — Wed, 04 Feb 2015 16:30:22 GMT

To all of the ArcSDE experts,

What are your best practices for using nonspatial tables and/or SQL Server views as input for event layers? The Help docs for 9.3 note that because ArcGIS cannot add an ObjectID field to a view, you cannot register a view with the geodatabase. (I'm using 10.2, but I don't think this has changed.) If a view is not registered, it means that the table (even if it has a field called ObjectID) will not work properly as input for an event layer. Sure, the event layer can be created, but if you try to query the data with a "Select by Attributes", you get very strange results. In addition, you are not able to select features on the map with the Select tool. This is a severe limitation when your data is changing frequently and you want users to be able to see those changes dynamically. With large data files, the process to convert the event layer to a feature class is painfully slow. So, any suggestions on best practices? Would it help to create an indexed view in SQL Server? (Indexed views can provide better performance but this has to be weighed against the increased maintenance requirements of the database. Would ArcGIS "recognize" an indexed view?)

Are we better off duplicating a view as a regular, indexed table and then registering that for the event layer? This would require some kind of automatic trigger to re-create the table after the underlying tables were changed by users and seems like a very bad idea when you have multiple people editing the data.

I'm also wondering whether tables that are registered have limited functionality for ArcGIS Desktop Basic users versus ArcGIS Desktop Standard users. Most of the users only have a Basic license and it would be costly to upgrade everyone to a Standard license. Currently, the underlying SQL tables can be edited through a custom form in ArcGIS regardless of the license level being used. When the data is refreshed, the event layers show the edits made by the users.

Regards,

-Susan-

Re: Best practices for using nonspatial tables (SQL Views) as event layers?

JakeSkinner — Wed, 04 Feb 2015 18:04:08 GMT

Hi Susan,

How you add a non-spatial table has changed, I believe starting at 10.0. When you attempt to add a non-spatial table to ArcMap, it will be added a Query Layer. You will be prompted to choose which field represents the Unique Identifier:

After selecting the Unique Identifier Field, you will be able to create an Event Layer and successfully query/select features within this layer.

Re: Best practices for using nonspatial tables (SQL Views) as event layers?

JoshuaBixby — Wed, 04 Feb 2015 19:12:54 GMT

Is there a reason you are looking at 9.3 Help even though you are using 10.2? A lot has changed in nearly 7 years.

Re: Best practices for using nonspatial tables (SQL Views) as event layers?

SusanZwillinger — Thu, 05 Feb 2015 18:52:56 GMT

Typically, I don't look at the old help files, but in this particular case I couldn't find the corresponding help topic when I looked in the 10.2 help file, so I thought that it may still be a limitation that you can't register a view with the geodatabase. Have you ever tried to register a SQL Server View in 10.2 as opposed to registering SQL Server tables (which I know we can do)?

Re: Best practices for using nonspatial tables (SQL Views) as event layers?

SusanZwillinger — Thu, 05 Feb 2015 18:56:07 GMT

Thanks for your helpful information, Jake. I have used the Make Query Layer tool in ModelBuilder, and this does address some of the issues with event layers, but it doesn't seem to address the underlying performance issues. I'm wondering if having an indexed view would help? In addition, I'm wondering whether it is possible to register an indexed view with the geodatabase and whether the combination of the indexed view and the registered view would improve performance.

Re: Best practices for using nonspatial tables (SQL Views) as event layers?

JoshuaBixby — Thu, 12 Feb 2015 18:06:15 GMT

You can see database views in ArcGIS Desktop just by connecting to a database, what functionality are you hoping to gain by registering a database view? Is it not functionality but performance related?

Re: Best practices for using nonspatial tables (SQL Views) as event layers?

SusanZwillinger — Thu, 12 Feb 2015 18:24:06 GMT

This is not a question of functionality; it's about performance and best practices. I can see the database tables and views and I can create event layers in ArcGIS. To be more clear about what I am asking, I have numbered my questions below:

1) What are the best practices for using SQL Server tables, views, or indexed views?

2) Does ArcGIS work with an indexed view (I know it works with tables and regular views) and is there any benefit to an indexed view being used with ArcGIS?

3) Are we better off duplicating a view as a regular, indexed table? Would that be essentially the same as creating an indexed view?

4) Do tables that are "registered" have limited functionality for ArcGIS Desktop Basic users versus ArcGIS Desktop Standard users?

5) Can indexed views be "registered" and is there any benefit to doing that?

Regards,

-Susan-

Re: Best practices for using nonspatial tables (SQL Views) as event layers?

JoshuaBixby — Thu, 12 Feb 2015 18:39:49 GMT

OK, now we are getting down to business. Hopefully Vince Angelo‌ can find some time to chime in, he always has good information to share on these types of question. I will have to take some time to think them over.

Re: Best practices for using nonspatial tables (SQL Views) as event layers?

VinceAngelo — Fri, 13 Feb 2015 14:28:46 GMT

Best practice (and best performance) calls for actual geometries in your event tables (this allows for a spatial index to be used on draw, vice having to execute a full table scan query). Given native geometry support on all platforms (which allows trigger-based point creation on insert and update), there really isn't any reason to use an Event Theme on a large database table.

It is not possible to register a view with a geodatabase (right-click 'Register' from Desktop). Spatial views can be registered with ArcSDE (at 10.2.2 and earlier) using 'sdelayer -o register', but this is not the same as registering with the geodatabase (views must be simple feature classes, may only contain one topology type [Point/Line/Polygon], and must contain a unique non-zero positive rowid column [register with USER-set rowid]).

There is no difference in queries from a Desktop Basic client (vice Standard or Advanced); the difference is in administrative and data creation activities and in the size of the toolboox.

- V

Re: Best practices for using nonspatial tables (SQL Views) as event layers?

SusanZwillinger — Fri, 13 Feb 2015 16:06:14 GMT

This is very helpful. It sounds like we should be creating a spatial view in SQL Server 2008 R2 (the client is using ArcSDE 10.2.1) and then setting up a trigger for when the lat/long data is updated for the points rather than using the Make Query Table option to create an event layer. I'm assuming that the term "spatial view" is equivalent to an "indexed view" in SQL Server terms. Can you point me to any good resources for further reading?

Since the client wants to use Desktop Basic (and not Standard or Advanced), is there any problem with updating or adding new points in this scenario? Several years ago, I remember a different client having ArcSDE and the only way they could edit or update feature classes was to have an ArcEditor license. For cost reasons, it is obvious that the current client would want to avoid having to use the Standard license when updating or adding data to the map layer from the spatial view being stored in SQL Server.

Re: Best practices for using nonspatial tables (SQL Views) as event layers?

MarcoBoeringa — Fri, 13 Feb 2015 16:20:44 GMT

Susan Zwillinger schreef:
I'm assuming that the term "spatial view" is equivalent to an "indexed view" in SQL Server terms.

No, a spatial view is a view containing a spatial column, containing actual geometries, and most likely a spatial index for these geometries to enhance display performance.This is not the same as an indexed view (indexes could just be attribute indexes, not refering to any geometry data).

Simple columns with lat/long data are just attribute columns with numeric data from a GIS perspective, they are NOT equivalent to a geometry column.

Susan Zwillinger schreef:
Since the client wants to use Desktop Basic (and not Standard or Advanced), is there any problem with updating or adding new points in this scenario?

This would either require custom development in ArcGIS, or your client could simply access the base table (not the spatial view) and edit the numerical lat/long data from within ArcGIS. If you have then setup triggers like Vince suggested, the spatial column should be updated as well, and the updated data visible in the spatial view.

Re: Best practices for using nonspatial tables (SQL Views) as event layers?

EricKriener — Wed, 24 Jun 2015 15:12:21 GMT

why the heck would you do this? not all data has a uid.

Re: Best practices for using nonspatial tables (SQL Views) as event layers?

VinceAngelo — Wed, 24 Jun 2015 15:23:14 GMT

Only data which does have a unique identifier can be used as a layer with ArcGIS. The issue is the need to link the attribute table with the graphics plane, so that a click on the table can flash the graphic, and a click near the graphic can flash the attribute row. If the rowid column isn't a UNIQUE, NON-ZERO, POSITIVE, 32-bit Integer, which returns the same value for the same feature, regardless of WHERE clause, then you could experience unexpected behavior. Feature classes have an SDE-set rowid column by default. Views (more specifically, 1:1 views) can use the registered rowid of the feature class, but 1:M and M:1 cardinality requires careful engineering of rowid designation.

- V

Re: Best practices for using nonspatial tables (SQL Views) as event layers?

EricKriener — Wed, 24 Jun 2015 15:29:34 GMT

then how do I geocode my SQL view (which can't be registered) and doesn't have (or need, frankly) uids?

Re: Best practices for using nonspatial tables (SQL Views) as event layers?

VinceAngelo — Wed, 24 Jun 2015 15:37:55 GMT

You would ask a new question, to start. Be sure to include as much information about the data and software in use as possible. Good questions are more likely to result in good answers. See my blog post for tips.

- V

Re: Best practices for using nonspatial tables (SQL Views) as event layers?

AdamBakiera — Wed, 04 Sep 2019 14:45:05 GMT

Is it still not possible to register views in the geodatabase?

Re: Best practices for using nonspatial tables (SQL Views) as event layers?

JoshuaBixby — Wed, 04 Sep 2019 14:47:45 GMT

It is possible from 10.5.x onward: What's new in ArcMap 10.5.x—ArcGIS Help | ArcGIS Desktop

Re: Best practices for using nonspatial tables (SQL Views) as event layers?

Bud — Mon, 02 May 2022 15:03:58 GMT

Assorted thoughts (although I use Oracle, not SQL Server):

Occasionally, I've noticed strange behavior when selecting features from spatial views that are registered with the GDB. If I export the view as an FC, then the selection issues don't happen with that FC. The selection issues don't happen in non-registered views either. So, I try to avoid registering views with the GDB, unless it's actually needed for publishing a map service to Portal, etc.

Registered view gets buggy when database link added [closed]

Unexpected query layer results: Why does unioning with DUAL fix it?

When using Oracle queries/views in ArcMap, I've found I need to cast the ObjectID to an integer: cast(objectid as number(38,0)) as objectid. Otherwise, ArcMap sometimes doesn't recognize the ObjectID as a valid unique identifier. I'm not sure if something similar is needed in SQL Server or not.

For cases where we don't have an ObjectID, using Oracle's ROWNUM column (a pseudocolum) seems to work pretty well too: cast(rownum as number(38,0)) as rownum_. Note: the column can't be called "rownum"; it needs to be called something else like "rownum_".
Some people say using ROWNUM as the unique identifier will produce unexpected results. But I haven't found it to be a problem. The key thing is: the unique identifier definitely needs to be unique. Otherwise, unexpected results do occur -- especially in the attribute table.
Does SQL Server have an equivalent column to Oracle's ROWNUM pseudocolumn?

Unique identifier fields

Integer representation of textual value (as unique ID for query layer)

For what it's worth, I noticed that the SDE.ST_GEOMETRY datatype was particularly slow when used in some spatial views (data source: dblink/an external database). I found that SDO_GEOMEMTRY in a spatial view, or simply using an XY Event Layer (based on numeric XY columns), was much faster than SDE.ST_GEOMETRY. I talk about it here: Are XY event layers optimized/indexed on-the-fly?

I don't have any experience with indexed views in SQL server. But I believe Oracle's similar functionality, materialized views, does work in ArcMap. Although materialized views are essentially tables, they're not just an index, so I suppose the two concepts aren't really the same/can't be directly compared.
Note: Registering materialized views with the GDB is a bad idea -- I think it converts the materialized view to a regular table, which breaks the sync. I don't know if the same thing applies to indexed views in SQL Server or not.

Materialized view breaks when registered with the GDB

SDO_GEOM materialized view: Why is static field slower than dynamic field? (answer: spatial index was missing)

Materialized view with FAST refresh on remote table: How to include a SHAPE column?

Question: Do indexed views in SQL Server require a spatial index for fast performance? Is that even possible, since I assume an indexed view is still just a regular view?

Have you considered converting the non-spatial table to a feature class, and then automatically populating the geometry using calculation attribute rules or a db trigger? Or if that won't work, could you create a parallel/helper FC that has a geometry, and populate that FC using attribute rules or db triggers?

I know this post is old. And a lot of my points pertain to Oracle, not SQL Server. But I thought I'd share my thoughts in case they're helpful to anyone or trigger some ideas...