Editing - Upload Attachments

[LieGEOruad]

Who requested this new feature?

Liechtenstein, @LieGeo

Is your feature request related to a problem?

No

Describe the solution you'd like

edit function - Upload Attachments (Photos and documents)

Additional information or points to watch out for

For geoportal editing, it would be very useful if photos and documents could be uploaded directly to the PostGIS database via the editable attributes. It should be possible to upload several photos or documents.

[LieGEOruad]

We still have the following hint:
For the 1st stage it would be sufficient if only one document/photo could be uploaded.
In the medium term, several documents (1:n) should be uploaded and displayed.

[sdietmar]

@LieGEOruad i am looking for something maybe similar. Some questions to clarify:

1. how would you want to store the data? this can be done as blob / bytea with the postgres large object extensions. from my research it is efficient and simplifies the design vs having to manage files directly on the harddrive. i assume in the postgres database.

2. where would you store it?
   a) If it is directly in the same table as the rest of the information, then this could be done as standard WFS-T, the frontend would only need to take care of converting the file to blob/bytea. but there would be no check on file size (js script in the frontend could easily be tricked), and users could upload gigabytes of data.
   b) it might be useful to have a photo layer and show the images/documents on the map. In this case it would be better to store the image/file in separate point tables and just link to them. This would require more modifications: Either to the frontend edit function, to store into different tables and wait for response and store link, or a custom backend performing that step.

3. How about image/file attributes and EXIF information like rotation, gps position, viewing direction, datetime, resolution. would this just be in the blob and the frontend/reader would need to extract each time?
   a) i checked about 4000 images from construction sites taken from different people with different cameras/phones. GPS info was missing on 10% (likely quick access without unlocking android). It might be useful that the frontend checks and warns the user of missing GPS info.
   b) i also found one phone/user that was very prone to storing wrong exif image rotation info. i think as minimum there would need to be a button to rotate image 90deg at a time - and store the info with the image.

[LieGEOruad]

@sdietmar
thank you for your questions, here are our answers

1. yes, we would like to save the attachments as a blob.

2. we see the storage in a separate table without geometry. The attachments are then attached to the features with a 1:n relationship. This allows any number of attachments to be stored to a feature.

3. The file size thing is important, it would be ideal if the dimensions of the image files could be automatically reduced to when uploading. In the Esri apps, for example, the desired size can be set. For the 1st sprint, however, we still see this as a "nice to have".
   The image/file attributes are also "nice to have" but not mandatory for many applications. The Esri apps also read out the GNSS quality attributes.

[sdietmar]

@LieGEOruad
thank you for your quick response! Follow up questions and comments:

2.a. "separate table without geometry": i understand you do not plan on showing just the file on the map, because there is no geom.
2.b. having a 1:n in postgres proved challenging, because arrays can not be foreign keys. so the database does not know about the connection. you could delete the entry linking to the image or delete the image without the other table knowing about it. we created a lookup table for the1:n case. also see pg large_object module for triggers and the vauumlo: https://www.postgresql.org/docs/current/lo.html. Maybe turning the connection on its head would work easier: the image links to ONE parent.

The attachments are then attached to the features

4. should this "adding files" feature be available for every layer, or only specific ones?
5. should this function be part of the existing frontend EDIT menu, or a new menu entry?
6. would there be a way to add already database stored images (could be problematic with suggestion of image linking to one parent), or only fresh images

[LieGEOruad]

@sdietmar
2.a. Yes, it is not about the photo on the map but the photo is attached to the geoobject.
2.b. Yes, I consider the relationship of the table with the attachments to the geoobject to be purposeful. Each attachment then has the foreign key link to exactly one geoobject.
4. "adding files" should be available only for specific ones
5. function should be part of the existing frontend EDIT menu - see attached Screen as example
6. no