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

• 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

[NSUWAL123]

The geom is being returned in wkb format. Sujan and I looked possibilities if we can convert geojson from the wkb format. But didn' t found the solution.

Any suggestions @spwoodcock ?

[Sujanadh]

I think you mentioned about using pglite to sync database with our main db, if we could sync db/electric migrate then we could use postgis function to get geojson directly from sql. Something like this?

const db = initDb;
async function getGeoJSON() {
     const query = ` SELECT id, ST_AsGeoJSON(ST_GeomFromWKB(wkb_column)) AS geojson FROM geometrylog; `; 
     const results = await db.query(query); 
     return results.rows; // Each row will have id and geojson 
    }

[spwoodcock]

Unfortunately those PostGIS won't be available in PGLite yet 😅 (maybe in time)

The only solutions are:

1. Convert in the db (assuming not possible due to use of electric.
2. Store geom in a JSONB instead (probably the best solution for now).
3. Convert wkb on the frontend (openlayers can do this I believe, but its far from ideal).

[spwoodcock]

I think the field types for the geometrylog table should be updated with a migration:

id --> UUID
geom --> jsonb

[NSUWAL123]

@spwoodcock we want to show the entity pulse effect on the management frontend as well. So since we don't have a get API for retrieving the bad geoms, what shall be the next step?

• A get API to show bad geoms on the react frontend, OR
• Implementing electric SQL on react frontend as well?

[spwoodcock]

An API endpoint 👍

We want to avoid adding electric to the React frontend probably (for now)