Normally I wrap my persistence layer using a repository pattern, which I then unit-test using SQLite.
Now I am processing spatial data using NetTopologySuite. The repository method GetUsersInPerimeterAsync(requestorLoc, perimeter) should return all users closeby.
So I have GPS (WGS84, SRID 4326) coordinates I want to compute distance from. Using Haversine formula, this is not a problem, but of course this should be done on the database side.
Now, as SRID 4326 is using degrees as units, NTS's .Distance method also returns degrees which is completely meaningless.
I've read that using data type "geography" instead of "geometry" would return metres on Postgres and MSSQL, but is not available on SQLite.
So now, I am projecting to a cartesian system before persisting. Like 4839 (Germany). Like this, database-side distance resolving works, but I am now completely bound to a specific spatial reference.
On the other side, using a generic reference like pseude web-mercator will give me huge errors again...
Is there a way around this? Do I have to choose between locking to a specific database or locking to a specific spatial reference?
Why does haversine work "good enough" all over the world whereas SRID projections differ greatly?
