Create new DB table to store 'new' and 'bad' geoms

[spwoodcock]

Is your feature request related to a problem? Please describe.

• When a new geometry is mapped (submission made), its not displayed on the map.
• When a geom is marked bad, its hidden within the task. You have to click the task to see bad geom.

Describe the solution you'd like
A clear and concise description of what you want to happen.

• Database table recording new and bad geoms.
• Synced to user via electric.
• Bad geoms displayed flashing on top of the map.
• New geoms displayed either on top, or when you click a task area only?

Describe alternatives you've considered

• Ideally we could add new geoms to the entity list. Does this work currently?
• Will there be issues if its a different geometry type to the other geoms (collecting a point in a building entity list)?
• However this would mean the user has to refresh the page to view new geoms, plus we don't have a nice way to do this currently anyway. Loading geoms is based on the flatgeobuf, then entity properties are mapped to the geometries by id.
• Using a new db table opens the possibility for real time sync of new geoms.

[NSUWAL123]

@spwoodcock This approach of storing bad geometries on a new table would also ease the effort on the frontend in pulsing the bad entities right #1975?

Currently, on PR #2018, I have added another flatgeobuf layer passing the project extent and then filtering the features based on the entity status. This approach might be resource-extensive for low-end devices and makes the UI a bit laggy (especially management frontend) due to the constant fetch of the fgb URL.
It would be very easy if we have an API that returns a feature collection of bad entities.

[spwoodcock]

The approach you took is nice & more calls to the fgb file isn't a huge problem.

This db table will be required for the newly added geoms though, as we can't update the fgb file every time.

So if we have the table there anyway, it makes sense too also add the 'bad' geoms to it too.

Then we can display as a layer on top of all the others, ignoring the task area filtering

[spwoodcock]

Plus also note the database table allows for the realtime update / loading bad geoms in the mapper frontend to be re-mapped immediately by the mapper still in the field.

(the fgb would require refresh via an API call, likely a page refresh)

[NSUWAL123]

The where clause in electric SQL doesn't support columns of the enum type. So I think we need to convert the type of column. I looked on electric SQL docs and couldn't find any solution.
https://electric-sql.com/docs/guides/shapes#where-clause
electric-sql/electric#1709

CC: @spwoodcock , @Sujanadh

[spwoodcock]

Damn! That's useful info (electric has a few limitations for SQL currently).

Hopefully its fine to load all geoms without a where clause, as the table should only have new and bad geoms. They could then be filtered to separate the 'new' geoms from the 'bad' geoms