My White Whale - Views with Attachments

2184
7
10-31-2018 02:41 AM
MichaelMannion
New Contributor III

(This is a long tale, replete with arcane technical details. If you have a strong will, and an equally strong cup of coffee, I invite you to read and offer suggestions. Otherwise, turn back now. Thank you in advance to all who start - and especially to any who finish.)

 

TL;DR

I'm writing regarding an issue that's affected most of my projects at some point over the past few years. It has grown from an annoyance on the desktop, to a veritable showstopper as more workflows transition to ArcGIS Online / Portal (hereinafter collectively "portal").

 

I've spent copious time reading doc, talking with colleagues, and testing workarounds - with some success, but ultimately yielding no silver bullet. Therefore, I'm casting a wider net here on GeoNet, and asking the community for suggestions on the following:

How can one access geodatabase attachments from a web map, when using a database view of a feature class, rather than the feature class, itself?

Typical Environment

In the interest of brevity, the wording of that question omits many details. To summarize the basics, the typical environment has:

  • An enterprise geodatabse, with a normalized data model to facilitate editing
  • A need to restructure data in real time for reporting purposes, including joining tables, computing columns, filtering rows, etc.
  • A target audience of web / mobile app users, and a portal to support them
  • A business requirement to use configurable (not custom) apps

 

I think there was one more thing... oh, right, Collector. Collector being the driving force for using a portal, capturing photos being the driving force for using Collector, and geodatabase attachments being the mechanism for enabling photos.

 

Views, For The Win!

Logically, database views are a natural fit in these scenarios; all of those database 101 reasons to use views apply here.

 

Correspondingly, we're increasingly publishing feature services in pairs - one of the feature class for editing, and one (or more) of a view on the feature class for reporting. This generally works great - except for attachments.

 

Attachments, For The Block

Ostensibly, geodatabase attachments are nothing special; they're a related table with a BLOB and some metadata. You may have built / used something similar in pre-attachment days.

 

Practically speaking, geodatabase attachments are very special. ArcGIS apps can natively work with photos and other documents if there's a supporting 1) table and 2) relationship class with the right names and structures. You can't just point ArcGIS to your own table of BLOBs; you have to use the specific table and relationship class that get created when you enable attachments.

 

Why does this matter? Because:

  • If we have a feature class fc, with an attachments table fc__ATTACH and a relationship class fc__ATTACHREL, and;
  • We create a view of the feature class fc_view, then;
  • ArcGIS apps cannot access attachments through the view, because there's no fc_view__ATTACH table or fc_view__ATTACHREL relationship class (and probably some missing metadata in the GDB_% system tables)

 

The attachments are right there; you just can't use them through the view. At least I can't, so far.

 

So, I'm faced with a surfeit of unpalatable options, like: coding a custom Collector app (non-starter), coding custom reporting apps (non-starter), ditching geodatabase attachments (non-starter), ETL-ing production data into a warehouse on a schedule (non-starter), or compromising data integrity by denormalizing the editing model to accommodate reporting.

 

That last one is a non-starter for me, but it's sometimes up for debate, depending on who you talk to. In an effort to protect data integrity as low in the stack as possible - and to configure reporting summaries as low in the stack as possible - I'd really like to stick with this normalized tables + reporting views model, like so many other business systems do. This hinges on making attachments available through the views, though.

 

To that end, I've tried the following...

 

Attempted Workarounds

Reading The Doc

I started by reading parts of the Desktop and Enterprise documentation to see if using attachments with views was supported. I didn't find anything that addresses this topic. Similarly, I looked at GeoNet, StackExchange, general Google searches, etc. with nothing conclusive.

 

That said, I might have missed something. I'd be obliged if you could point me to an existing resource that describes how to use attachments with views, or to a credible statement that doing so isn't supported.

Enable Attachments on View

I tried enabling attachments on a view, with the intent of swapping out the new attachments table for a view of the real one. Understandably, enabling attachments isn't supported for views.

Joining to Attachments Table

In the interest of completeness, I tried various permutations of joining the view to the attachments table. In ArcMap, I could create both joins and relates, but ArcMap didn't interpret the related table as attachments, just arbitrary data.

Similarly, in ArcCatalog, I tried creating a relationship class between the view and the attachments table, but relationship classes aren't supported on views.

Attributes Containing HTML

With the more obvious attempts proving unsuccessful, I started looking for other ways to access document-type data from ArcGIS. HTML popups seemed promising, in that they allowed for links, embedded images, and fancier things like XSL and Arcade expressions that I wasn't familiar with.

 

I was happy to find that I could build attribute values in the view that were marked up with certain HTML tags (namely <A> and <IMG>) and the HTML popup dialogs in ArcMap and portal web apps would recognize them, either automatically or with a little fiddling.

 

I learned a few ways for building the markup within the popup configurators, too. But, I preferred doing that work in the view, because I only had to do it once on behalf of all clients, rather than individually within their respective popups.

The problem, of course, was what to use as the href or src of the HTML tags? The attachment "files" live in a BLOB in a database table, and I don't know of a way to fetch a row from a table by URL (without writing a web service). I've explored this most on Postgres, and there doesn't appear to be a built-in solution. I haven't looked as closely at Oracle and SQL Server yet. I'd be interested if anyone has ideas. (More on web services at the end.)

Data URIs

I wondered if there was some way to just include the attachment data right inside the HTML, and was happy to discover data URIs. In short, instead of referencing some file:

<A href="http://foo.com/image.jpg">image.jpg</A>

You can cram a text version of the binary data (in base64 format) right into the tag:

<A href="data:image/jpeg;base64,4AAQSkZ....">image.jpg</A>

And it works!

 

I tried this in ArcMap and the ArcGIS Online Map Viewer, with <A>, <IMG>, and <EMBED> tags, and they worked to varying degrees. A few notes:

  • You can build those data URI strings in the view. You'll need to join to the attachment table, then concatenate some literal text and some attachment attributes, like:
SELECT '<A href="data:' || content_type || ';base64,' || ENCODE(data, 'base64')...
  • ArcGIS stores the attachment data as binary, but HTML wants it as text. So, you need to use something like the ENCODE() function (Postgres) in the prior example to translate to base64 on-the-fly.
  • You also need to plan for multiple attachments. I used the STRING_AGG() function (again, Postgres) to combine multiple attachment rows into a single value, yielding something like:
<A...>image1.jpg</A><BR><A...>image2.jpg</A><BR>...
  • You can beautify the results by including the download attribute in your tags, so the links show real file names when users click them.

 

Performance Considerations

 

The data URI trick worked fine on my tiny test feature class. I was worried, however, that clients (apps or humans) would end up fetching too much data in a real production scenario.

 

For example, when you're connected directly to a geodatabase, ArcMap is smart enough to only ask for the subset of attributes it needs for any given operation. If you draw the map in one color, it only asks for the shape. If you use the HTML popup, it asks for everything - but only for that one feature you clicked, so it's OK if the attributes are big. If the user opens the attribute table, though, they could be in for a long wait.

 

On the portal side, I wasn't as lucky. Map Viewer seems always to fetch entire features - geometry with full attributes - for every feature it adds to the map. Even if you remove an attribute from the popup (and, hence, from the attribute table viewer) it still fetches every attribute. So, if you have 1000 features with a mere 1 MB inline attachment each, adding that layer to your web map will download a GB of data up-front. That's not going to work.

Map (not Feature) Service

I re-tested the previous workaround, but using the REST endpoint for the map service instead of the feature service. My goal was to fetch a small PNG quickly for mapping, and the larger inline attachment attributes only on-demand via the HTML popup.

 

This worked in ArcMap, but not in any of the portal apps I tried. I don't know why, but in the latter case, I wasn't able to get a response by clicking features in the map. I'd be interested to know if this is intended behavior, or if I was doing something wrong.

Data URIs + Related Table

I still liked this data URI technique, but wanted to move those big attributes off to a related table so we weren't fetching them all the time (not coincidentally, like geodatabase attachments). I thought I might be able to combine the earlier work with joins with this new work on data URIs.

 

Unfortunately, you can't publish ArcMap joins or relates to a feature service. (Ironically, the help page for the resulting error 24040 suggests to "Create a view" as a workaround.)

 

Apparently, you can only publish related data using a relationship class. And, as we know from above, you can't create a relationship class on a view. I understand that relationship classes are intended for editing, and that (most) views aren't editable, but there doesn't seem to be a read-only equivalent for reporting purposes.

 

If anyone knows how to publish related data as a feature service without a relationship class, or to create a relationship in a web map / configurable app / dashboard, I'd love to hear from you.

Attributes Containing HTML + Web Service

My last idea was to keep the view column with the HTML markup, but to remove the inline payload and replace it with a reference to a web service that would fetch the attachment when the use clicked the link. I know I already tried and failed at that before, so this was really just trying again - but harder.

 

I don't think this is going to work for me, for several reasons:

  • It doesn't seem that Postgres can natively accept a URL and return a value. Again, I'll check the other databases as I return to this topic on other projects.
  • I can publish the attachments table as a feature service and craft a URL to fetch from it. But, I get extra stuff in the response that I don't want, like JSON or HTML. I really need just the "clean" attachment data.
  • I thought that maybe a geoprocessing service would give me more control over the response than a feature service. Those, too, appear to respond with a bunch of extra stuff around the file payload. (Note that I've only read about this, and haven't tried it first-hand yet.)
  • Writing a custom web service isn't an option on at least one project, both for business and technical reasons. If we could configure / code Postgres or ArcGIS Server to do what we want, that would be acceptable. If the service had to run on a web server, though, that wouldn't fly in this environment.

 

I'm not a web developer, so I could be wrong about this part. If you see a potentially viable alternative for this type of approach, I'd be interested in hearing your thoughts.

 

Coda

Please accept my sincere thanks for your valuable time and consideration. If you are able to fill in the missing piece of this puzzle, you're going to make a lot of people happy - especially me.

7 Replies
George_Thompson
Esri Frequent Contributor

This is an interesting read and unfortunately do not have anything to add in terms of "next steps". I see that you have done a lot of testing to get where you are now.

I know that in the past attachments have been difficult outside of the default ArcGIS client due to the need for the conversion from a BLOB to actually file. I can see how using a view for that would complicate things.

I will be following this thread to see what happens.

ArcGIS Enterprise

--- George T.
JulioGaspar1
New Contributor III

I have the same problem too. I use a lot of views to summary my data or join from different sources.

Do you know if somebody create something about this in arcgis ideas?

0 Kudos
MichaelMannion
New Contributor III

Speaking only for myself, I haven't checked for, nor posted anything, to the Ideas site yet. I'm still at the stage of trying to determine what is/isn't possible with the current software. Hopefully, there's a feature, or a viable workaround, that we haven't discovered yet.

If not, it might be worth posting something to the Ideas site. Realistically, there are several integration/view related issues worth fixing; I don't know if it'd be more productive to post a comprehensive list of related requests, or to post a separate request for each. Do you have an opinion on that?

In either case, a back-office topic like this only directly affects a small number of users (even though the downstream affects touch many more). Consequently, it'll never get as many votes as ideas for improving something like ArcGIS Pro or Online, which more people use first-hand. (For context, after two months, this post doesn't even have 100 views). It's my understanding that the Ideas site is essentially a popularity contest; if that's the case, it's likely not an effective forum for motivating changes at the database tier. If I'm wrong, though, please correct me.

0 Kudos
MichaelVolz
Esteemed Contributor

At my org, we had a 3rd party application that utilized GIS services and this 3rd party software put attachments into a SQL Server database BLOB field that was inaccessible to ESRI ArcGIS.  Ultimately, the 3rd party application was upgraded and attachments were saved into files (seems to be more of the industry standard - please correct me if I am wrong) which can now be used in ESRI ArcGIS.  As such, my opinion would be for attachments not to be saved into a BLOB field in the first place, as it is more difficult to use in an enterprise environment and storage space these days is relatively cheap (If that was the impetus for saving attachments in a BLOB field in the first place).

0 Kudos
MichaelMannion
New Contributor III

I've looked at exporting attachments to files on a couple of projects. While the export operation is relatively straightforward I haven't found a generic way to reference the resulting files.

To elaborate, let's say my geodatabase is on an internal server (i.e. corporate LAN). Undoubtedly, there's also some file server that both the database and the clients can see. So, if I export an attachment BLOB to a file like \\myserver\attachments\file1.jpg, users in the office can resolve that pathname and view the file.

The problem has been with external users. Internet-based clients can typically access the internal database via some web service (e.g. internal ArcGIS Server instance + Web Adaptor to get through firewall). They usually cannot, however, see the file server. Correspondingly, they cannot resolve the file paths.

There's probably some workaround where I could replace the UNC path with ftp:// or http://, such that internal and external clients could resolve the same reference. It's been my experience, though, that there's usually not an obvious filesystem where the database can write and the web server can read. I'm not a web/network/security admin, so I can't explain further; it just seems to be that internet-accessible geodatabase content often resides on an internal server, whereas file-based content seems to live in the DMZ - and that it's considered bad to arbitrarily write files across that boundary.

Before the holidays, I started looking into exporting BLOBs to files on S3. That seemed to work OK for data with no access restrictions. But, there wasn't an obvious way for me to control access to the ArcGIS Server web services and the S3 files in a coordinated fashion. Again, security isn't my strong suit, so maybe I'm overlooking something. One way or another, though, I'd need the exported attachment files to be subject to the same access control as the original BLOBs from whence they came.

Does that make sense? If so, do you have any suggestions?

0 Kudos
MelanieHammond
New Contributor II

Hi Michael. I know this is long after you posted this but I was also trying to solve this issue as we use database views quite extensively. I believe I found a workaround that you can try.

It comes down to joining up to the Attachments table based on what fields the relationship is based on (mine below is using GlobalID and REL_GLOBALID) and then using the url to the service that contains the actual Feature Class (and thus the Attachments) to create a new field. I only have one attachment per feature in my Feature Class so this might need to be revised if you have multiple so that you grab all (or only one).

I hope this helps. Once I published this out I was able to have the image be available in my pop up either as an image or as a new field formatted as a hyperlink.

SELECT s.*,
CASE
WHEN a.REL_GLOBALID is not NULL then 'Your_Server_Name_Here/server/rest/services/Testing/Storage_Containers/FeatureServer/0/'+CAST(s.OBJECTID AS varchar)+'/attachments/'+CAST(a.ATTACHMENTID AS varchar)

ELSE ''
END AS PHOTO_URL
FROM Feature_Class s
LEFT OUTER JOIN Feature_Class__ATTACH_evw a ON s.GlobalID = a.REL_GLOBALID

MichaelMannion
New Contributor III

That's genius - use another feature service!

I just read your post (I got unsubscribed somehow; sorry for taking a year to get back to you) and haven't had a chance to test this yet, but it makes sense. This solution gives us a URL for fetching the attachments, and allows us to authenticate to that attachments service using the existing credentials we're already using to access the view service. This is a great insight - thank you for taking the time to post it.

Thinking out loud, there are two things I need to test now:

  1. There will be a dependency between the view service and the attachments service such that, once things are working, we have to take care not to change the latter in a way that affects the former - e.g. republishing with a new service name / layer IDs, removing service capabilities, changing permissions. There's probably no way to enforce that at the software level, thus requiring a modicum of change control / governance maturity to ensure stability. I've got to think / test to gain an intuition for this, and see if we can protect the dependency without relying on us fallible humans.
  2. Ideally, the solution should accommodate multiple attachments. I think the STRING_AGG() approach from the original post should work here, too. I'll try it when I have some time and post my results once I understand what's happening.

Regarding #2, coincidentally, I just read about the new(ish) Upgrade Attachments option that adds additional metadata to the attachments table. My immediate use case is for Survey123, for which this capability appears to have been designed, but we might be able to leverage the new KEYWORDS property to parse out multiple attachments into different columns in the view based on some sort of category (e.g. photo vs. document). I'm sure we could also do this by file extension or something, but I like the idea of having a tag-type metadata system for attachments to use with this view solution.

I'm going to leave this thread as-is until I have a chance to test, which might be a while. But, if things work as you've described, as I expect they will from my understanding of your post, I'll mark your answer as the solution.

Thank you again for your help. I'm looking forward to trying this.

0 Kudos