(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.)
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?
In the interest of brevity, the wording of that question omits many details. To summarize the basics, the typical environment has:
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.
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.
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:
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...
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.
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.
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.
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.)
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:
You can cram a text version of the binary data (in base64 format) right into the tag:
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:
SELECT '<A href="data:' || content_type || ';base64,' || ENCODE(data, 'base64')...
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.
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.
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.
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:
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.
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.
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.
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.
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).
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?