PostgresStorageAdapter.js: postgis version aware use of distance sphere function (GeoPoint distance calculation)

[xeaon]

Issue Description

If you use postgresql as database and need to calculate distances of GeoPoint objects, you obviously need postgis. In postgis version > 2.2.0 the function ST_Distance_Sphere was renamed to ST_DistanceSphere which causes the Postgres Storage Adapter to fail here:

parse-server/src/Adapters/Storage/Postgres/PostgresStorageAdapter.js

Line 624 in 9d7da58

`ST_distance_sphere($${index}:name::geometry, POINT($${index +

parse-server/src/Adapters/Storage/Postgres/PostgresStorageAdapter.js

Line 628 in 9d7da58

`ST_distance_sphere($${index}:name::geometry, POINT($${index +

parse-server/src/Adapters/Storage/Postgres/PostgresStorageAdapter.js

Line 676 in 9d7da58

`ST_distance_sphere($${index}:name::geometry, POINT($${index +

It should be possible to build these queries based on the postgis version by actually quering the version. I'll try to implement a fix but if anyone else has an elegant way to solve it, please do it :)

Steps to reproduce

1. pull postgis/postgis or install postgis from source
2. use postgis instead of mongo
3. add a GeoPoint column to your class and try to query a near location

Expected Results

actual near locations

Actual Outcome

query fails with an internal error

Environment Setup

• Server

  • parse-server version (Be specific! Don't say 'latest'.) : 3.10.0 (parseplatform/parse-server
    image drom dockerhub)
  • Operating System: ubuntu
  • Hardware: docker container
  • Localhost or remote server? (AWS, Heroku, Azure, Digital Ocean, etc): localhost

• Database

  • MongoDB PostgreSQL version: POSTGIS="3.0.0 r17983" [EXTENSION] PGSQL="120" (postgis/postgis image from dockerhub)
  • Hardware: docker container
  • Localhost or remote server? (AWS, mLab, ObjectRocket, Digital Ocean, etc): localhost

Logs/Trace

error: Parse error: error: function st_distance_sphere(geometry, geometry) does not exist

{"code":1,"stack":"Error: error: function st_distance_sphere(geometry, geometry) does not exist\n    at /parse-server/lib/Controllers/DatabaseController.js:1194:21\n    at processTicksAndRejections (internal/process/task_queues.js:94:5)"}

[dplewis]

I believe the tests run against postgis 2.5 and postgres 11. I'm surprised this didn't catch.
https://github.com/parse-community/parse-server/blob/master/.travis.yml

We can make this change and require postgis 2.2.0 be the required minimum (currently I don't know what the minimum is).

[xeaon]

I've just double checked, if there was a backwards compatibility given in 2.5 but they've documented the new function name as a breaking change here: https://postgis.net/docs/PostGIS_Special_Functions_Index.html#ChangedFunctions_2_2

It also seems like, that there is no test which would cover this issue. I've forked parse-server, added a simple SQL query and it fails: xeaon@553f460

https://travis-ci.org/xeaon/parse-server/jobs/655267531

[dplewis]

@xeaon I use postgis 2.5.2 and I ran the example from the Postgres Docs here.

WARNING:  ST_Distance_Sphere signature was deprecated in 2.2.0. Please use ST_DistanceSphere

Perhaps 3.0 that you are using completely removed it instead of a warning.

I'm all for making the change to ST_DistanceSphere as long as we document it.

@acinader @davimacedo Thoughts

[davimacedo]

Yes. I agree. I think it would be better to only support the new way and document it in the next Parse Server release.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.