AnsweredAssumed Answered

My White Whale - Views with Attachments

Question asked by mannion on Oct 31, 2018
Latest reply on Jan 7, 2019 by mannion

(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.

Outcomes