Data schema

[vgeorge]

Users

• id: string, PK, non-public
• username: string, required, unique, can use to login
• email: string, required, unique, can use to login
• password: string, hashed
• name: string
• isAdmin: boolean
• createdAt: date
• updatedAt: date

Traces

• id: string, PK, URL-friendly (see shortid)
• creatorId: string, FK
• label: string
• gpx: binary, will store the gpx zipped (see jszip)
• bbox: GEOMETRY(Polygon, 4326), required
• uploadedAt: date

Photos

• id: string, PK, URL-friendly (see shortid)
• creatorId: string, FK
• exif: json
• label: string
• filename: string
• blob: binary, contains the photo file
• osmObjects: array of strings, will contain osm ids like node/2932938, way/10203932, relation/102293, etc
• location: GEOMETRY(Point, 4326), required
• bearing: number, required
• createdAt: date, when the photo was taken
• uploadedAt: date

[vgeorge]

cc @geohacker @batpad @sethvincent

[batpad]

@vgeorge this looks like a good start. Some questions:

User Schema

How do we deal with / store the relationship to the OSM auth in the schema? This looks good to me for a simple auth mechanism, not sure if we need to think about the schema in relation to logging in with OSM username / linking to osm user data.

Traces

While storing the gpx zipped sounds okay, we would need to do things like filter gpx traces by location / bounding box. Shall we separately add a centroid Point field that we can run postgis queries on? cc @geohacker . I wonder if we also need a timestamp of the actual trace to filter by? We definitely do need either the whole Line String or a centroid to use in bounding box queries though.

Do we also need to store the timestamp of the actual trace separately? or like start_timestamp and end_timestamp ? Not sure if we'd want to / need to filter by timestamps of the traces.

Photos

Again, we will need some geo-data attached to the photos to be able to do things like "find all photos in this bounding box" - perhaps just a Point field to hold a lat / lng? cc @geohacker

The other question I have is around using blob to hold the photo. I generally prefer to store media on somewhere more file-system-like like s3, and just store paths in the database - to avoid the database becoming heavy with image data - and then in the API, just handle authentication and fetching from s3, etc. I'm a bit wary of storing the image blobs in the DB, but happy to discuss.

Overall, this looks great - my main feedback is just ensuring that things will be filterable by bounding box / other queries we need.

[LanesGood]

@vgeorge One note on username - I assume the account/database source for this needs to be configurable so that developers can implement the API for containerized versions of OSM.

[vgeorge]

Added three changes to the schema:

• isAdmin to handle permissions in a very simple way
• bbox of type Polygon to traces
• location of type Point to photos

@LanesGood I'm not sure if I understood correctly how this relates to the user schema, but the OAuth endpoint will be configurable, so it would be posible to use a custom OSM instance if it offers OAuth.

[vgeorge]

One outstanding question from me. Do you see the need of labels for traces and/or images?

[batpad]

@vgeorge sorry, one additional comment / request: can we have bearing as a separate field for photos? I'd be in favour of requiring the frontend to send this as a separate parameter. cc @geohacker

[vgeorge]

@batpad thanks for remembering this. I've added as a required number field.

[vgeorge]

Regarding the trace schema, OSM has a description field:

https://www.openstreetmap.org/user/vgeorge/traces/1227616

It has also a filename field, but don't see how it is useful. The API can use the trace id for that.

[vgeorge]

A first version of the data schema is implemented. It's a little bit different from the description at the first comment, but in line with our current understading of features and use cases. Let's discuss any further changes in specific issues.