Use of proj's sqlite database

[willcohen]

Moving discussion here from #12.

With a broader goal of starting to more closely track changes with proj over the last year or two, proj4j would need to eventually start getting from the .sql files that are now provided in proj upstream to something ingestible here.

One option, as mentioned in #12, would be to start embedding the sqlite database in proj4j as well. The benefit here is to minimize the amount of transformation needed from what proj uses, and the downsides would be to use a heavier dependency that requires a fair bit of native code. It makes sense that the native code is less of an issue for proj, since it's already living in the world of native code.

A second option would to be to more systematically move from what's in the sqlite to a pure-java database. We could think about ingesting the .sql files into an h2 database for proj4j, which would remove the need for native code but would still be a new dependency. The projection and CRS definitions do reference between a bunch of tables now, and I suspect that now that transformations don't always go through WGS84 that once the new functionalities are implemented it'd be hard to write out all the permutations without doing these lookups on the fly.

The last option would be to write out the tables generated by the .sql files into some plain text csvs similar to the existing epsg file and do the lookups on those files instead of a db file, to minimize change from the current methodology. I suspect, though, that this would start to be reinventing a wheel that h2 will do more performantly, which leads me to think that h2 might be the path forward here.

[dr-jts]

If a database is required for power or ease of porting, then H2 sounds like a good way to go.

Is a hybrid approach feasible? Keep the file-based approach for simple transformations (equivalent to current), and allow optional use of the DB for more complex things?

[willcohen]

Since EPSG is about to change their model, upstream proj will have to change accordingly to reflect that, and there's nothing wrong with existing functionality here, I'd like to wait until their new database format settles before trying to figure out the best path for considering integrating it into proj4j.

In addition to trying to make sure that any database dependency is able to stay in the world of pure java, another use case I want to make sure a potential switch addresses is making sure that interaction with an internal db file works with graal native. I know that h2 has some issues, so I'll also try to look at alternatives like derby or hsqldb to figure out what minimizes side effects for graal so it's not precluded.