Trefle DB repalcement

[astappiev]

Hi,

I have finished the project I mentioned earlier, I downloaded the complete db from Trefle using their API.
It's stored in relational DB, which greatly simplifies how we can use it. Also includes 149462 main images of species.

Preamble
I tried to deploy Trefle, but failed.
Also their public dump (available here) is outdated (not a big deal as since then their DB has only slightly grown) and a single CSV with lots of data omitted (I thought so).

Methods:
For simplicity and fun, I used golang to make ~500k requests to their API (single thread and some throttle), in particular, I used pockebase as a backend.

Results:
~1,1GB SQLite db of 489813 species + some other tables
~29,0 GB of 149462 image files (not necessary, there is still image_url which points to some random cloud used by Trefle)

You only need to download the files, unpack them and put them into the same folder as pocketbase binary (or minimal go source file with framework init). Run ./pocketbase serve and REST API is ready to be used + GUI Admin panel if needed.
I'm planning to share the files with anyone interested, I just don't know a good way to do that.

Alternative solution:
While searching for alternatives to Trefle, I found https://plantbook.io/, which could be a direct replacement for Trefle.
However, it's not open source and I found their website very slow (at least from EU, maybe it's deployed in US), I spent 10 minutes creating the API key because each page loads a minute. API works slightly better, but still not compared to Trefle.

The first mention of this project comes from HomeAssistant forum where it was introduced as integration with plant-card. Reading the forum, I concluded that it was created as a result of a similar crawl to what I made, but from Chinese MiFlora API (a companion App for MiFlora Bluetooth devices).

Following this, I found MiFloraDB, which also consists of CSV file and images.
As a bonus, I put it into a separate SQLite table, with all the images together, so it can serve as Local API (this one is only ~10MB in size + ~500MB of images). And contains the most common household plants with nice pictures.

Conclusion:
I think MiFloraDB is more suitable for the app as it contains "simplified" information about plants. It's also smaller in size, and can be easily packed as a Docker image to be distributed to each installation of the app. Maybe we could create a rival to Trefle: a collaborative platform to gather plant knowledge, but with aim to gardeners instead of biologists.

[MDeLuise]

Hi @astappiev, thank you so much for the extensive work you've done, you've uncovered some really interesting and valuable resources.

Next Steps and Suggestions

I'm currently exploring the best path forward and your findings are incredibly timely. Here’s what I’m thinking:

1. Temporary Replacement for Trefle:

   • For the next few months, we could use floracodex, as their APIs are quite similar to Trefle. I’ve already started testing the integration.

2. New Project Development:

   • Concurrently, we should start developing a new service. Once it reaches a stable version, we can transition Plant-it to this new platform.

Your suggestions and feedback are welcome since these are just my initial thoughts. I’m particularly looking for suggestions on how best to proceed with these plans, feedback on their feasibility, or if there might be other directions we could consider. Here are some key characteristics I envision for the new service:

• Open Source and Open Data: Ensuring the platform is transparent and community-driven.
• Self-Hostable: While a SaaS option would be beneficial, the ability to self-host is crucial.
• User Contributions: Enabling users to suggest updates to the plant dataset.
• Cost Considerations: The service should ideally be free, perhaps with some rate limiting. I think this might raise some issues, as Trefle’s shutdown was likely due to infrastructure costs.

Technical Feasibility and Initial Tests

I’ve conducted some preliminary tests with the Trefle dump, and your more comprehensive dump will be useful for this. Here's an outline of the current project setup:

• Elasticsearch: For storing the dataset.
• Spring Boot: As the backend framework.
• GraphQL: For querying the data.

This project is still in its infancy and primarily a feasibility study at this stage. If needed, I can share the project with the community to gather more input and collaboration.

Looking forward to your thoughts and any additional suggestions you might have.

[astappiev]

Yes, using floracodex is an easy replacement, if I had seen it a week ago, I would not jump into this topic at all.
But maybe having a community-driven solution would be good anyway.

This project can be split into two, or even three subprojects:

Collabarative database:
I had a few ideas in mind on how we can create a collaborative database. But of course, there are cons and pros:

1. Github repository with YAML files. One file for each plant, collaborate via PR. Unfortunately, this approach can survive only for a small database, I can't imagine 400,000 species from Trefle in a single repository. But, do we need all of them?
2. Using something like Airtables, in particular this open-source alternative https://github.com/nocodb/nocodb. I still have no idea how good will it be, but we need to try.
3. Developing our own software for that, basically, Trefle from scratch.

PlantDB with REST API:
Honestly, we don't need Elasticsearch here, sqllite with fulltext index returns results in under a second over the whole database of 500,000 species. If we need a simple solution that can be downloaded to each installation of PlantIT, we need to keep it simple. That's why I thought that pocketbase could be really good for that. We can create a docker image with 10,000 most used species and REST API in under 50Mb (of which ~7Mb db + alpine Linux + pocketbase). The whole database would be around 1 GB container. Plant images of course should be stored separately, perhaps we can use S3 storage for that, this should be cheap enough, with public dump somewhere.

But of course, if we have a collaborative db, we would like to have a SaaS API solution as well. I would still suggest using Apache Solr instead of Elasticsearch, or at least OpenSearch. I also prefer Quarkus over Spring Boot, but this is personal :)

Plant monitoring
I like that in Open PlantBook we can push sensor data to have it attached to a plant. I have some b-parasite sensors sticking around. But this should be better integrated directly into PlantIT backend.

[astappiev]

Hi @MDeLuise,

I have deployed the database, you can access it as
https://plantdb.astappiev.me/api/collections/mifloradb/records
and
https://plantdb.astappiev.me/api/collections/species/records

There are some parameters supported, you can take a look at documentation here
https://pocketbase.io/docs/api-records/ filter and expand are most useful for us

E.g. https://plantdb.astappiev.me/api/collections/species/records?filter=(common_name~'Jade')&expand=genus_id,family_id,distributions_native

Images can be accessed following https://pocketbase.io/docs/files-handling/#file-url
e.g. https://plantdb.astappiev.me/api/files/mifloradb/hw3dgn38b0qi5t8/hw3dgn38b0qi5t8.jpg

Of course, I don't want to use it in the app right now. It's for testing only.
But we can think about how we can ship it in the future.

[MDeLuise]

Hi @astappiev,

Thank you so much for the effort you’ve put into this issue. I really appreciate all the work you’ve done and the resources you’ve uncovered.

I need to finish a few other tasks first, but I plan to start working on this project in the coming weeks. I had a chance to play around with Pocketbase, and I’m really enjoying it.

Do you prefer we create a temporary repository for this, or should we continue our discussions here?

[FracKenA]

I love where this is leading. The only request I have is that whatever the end result is, it can be self hosted as well. I have made a focus in all my daily use apps, that there is no dependency on 3rd party services.

I am just coming into plant it. My wife is needing something for her gardening, and if helping develop something like this supports her, I am in. Has the discussion moved else where?

[KalyaSc]

Hi :)

I'm not a dev so sorry If I add unnecessary noise and I do not know if this is something related or useful or even if this could fit into plant-it.

But I leave this here: https://github.com/plantnet

It has an API so I thought this could be something of interest for plant-it. Maybe I'm wrong, If so just delete my comment :).