Validate Prisma ORM

[vgeorge]

We discussed that Prisma ORM could be a great fit for this project because it provides modern tooling (migrations, preview studio) and type safety between API routes and frontend components.

However, it lacks out-of-the-box support for geospatial features using PostGIS. The documentation page on Custom & type-safe queries provides an example of implementing a basic PostGIS schema and query. We should investigate how we can implement the features needed to build the visualization.

Current changes:

• Docker compose file for the development database
• Migration to create the PostGIS extension
• Basic schema for areas (currently just national boundaries)

Proposed Exercise:

• Create a schema for areas with PostGIS geometry fields
• Add a script for ingesting the existing GeoJSON in this repository
• Create an API route for serving MVT tiles
• Replace the existing Deck.gl GeoJSON layer with MVTLayer for area boundaries

This should cover the most critical aspects of integrating Prisma ORM with PostGIS for our project.

@willemarcel @oliverroick please let me know if you have comments.

cc @wrynearson

[vercel[bot]]

The latest updates on your projects. Learn more about Vercel for Git ↗︎

Name	Status	Preview	Comments	Updated (UTC)
foodtwin-global-app	✅ Ready (Inspect)	Visit Preview	💬 Add feedback	Jun 24, 2024 5:49pm

[vgeorge]

Changes added:

• Ingest script
• API route serving MVT
• New sandbox page displaying a flat map loading vector tiles

Steps to provide feedback:

• Follow README instructions to set up and populate the development database
• Access /sandbox/mvt-test and confirm the map is displaying data

Known issues: It seems there is some projection or clipping issue in the MVT route. If you switch the data URL in lines 16-17 of the sandbox/mvt-test files to use a remote URL, the tiles are rendered correctly.

@oliverroick can you please do a first review?

[vgeorge]

The projection issue was fixed in the last commit, tiles should be rendered correctly.

[vgeorge]

@oliverroick thanks for the review. I believe the migration issue is fixed. I introduced other changes:

• Created a "Web App Seed Data" folder in the Earth Genome drive to host the seed data used in the application
• Updated the seed process to use those files by running pnpm seed
• Updated the dependencies list to include GDAL. This was necessary to use ogr2ogr to ingest files, which is much more efficient than the current approach
• Added a 2D/3D toggle to the MVT example
• Updated the README

Could you do another review? If you are able to ingest the data and browse the MVT map, this should be good to go. There is some odd behavior when rendering MVT in the globe view, but we can discuss this in another ticket.