PostgresStorageAdapter.js: postgis version aware use of distance sphere function (GeoPoint distance calculation) #6441
Comments
|
I believe the tests run against postgis 2.5 and postgres 11. I'm surprised this didn't catch. We can make this change and require postgis 2.2.0 be the required minimum (currently I don't know what the minimum is). |
|
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 |
|
@xeaon I use postgis 2.5.2 and I ran the example from the Postgres Docs here.
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 |
|
Yes. I agree. I think it would be better to only support the new way and document it in the next Parse Server release. |
dplewis
pushed a commit
that referenced
this issue
Apr 3, 2020
…ostgres (#6506) * Update .travis.yml testing error to see what happens... * Update .travis.yml Attempting to resolve postgres in CL by installing postgis via sudo instead of through apt/packages * Update .travis.yml * Update .travis.yml * Update .travis.yml Removed extra lines of postgres that were under "services" and "addons". I believe the "postgresql" line under "services" was installing the default of 9.6 and "addons" was installing postgres 11. My guess is the fail was occurring due to 9.6 being called sometimes and it never had postgis installed. If this is true, the solution is to only install one version of postgres, which is version 11 with postgis 2.5. * Adding test case for caseInsensitive Adding test case for verifying indexing for caseInsensitive * Implementing ensureIndex * Updated PostgresStorageAdapter calls to ST_DistanceSphere. Note this has a minimum requirement of postgis 2.2. Documented the change in the readme. This is address #6441 * updated postgres sections of contributions with newer postgres info. Also switched postgis image it points to as the other one hasn't been updated in over a year. * more info about postgres * added necessary password for postgres docker * updated wording in contributions * removed reference to MacJr environment var when starting postgres in contributions. The official image automatically creates a user named 'postgres', but it does require a password, which the command sets to 'postgres' * added more time to docker sleep/wait to enter postgis commands. This will always take a few seconds because the db is installing from scratch everytime. If postgres/postgis images aren't already downloaded locally, it will take even longer. Worst case, if the command times out on first run. Stop and remove the parse-postgres container and run the command again, 20 seconds should be enough wait time then * latest changes * initial fix, need to test * fixed lint * Adding test case for caseInsensitive Adding test case for verifying indexing for caseInsensitive * Implementing ensureIndex * Updated PostgresStorageAdapter calls to ST_DistanceSphere. Note this has a minimum requirement of postgis 2.2. Documented the change in the readme. This is address #6441 * updated postgres sections of contributions with newer postgres info. Also switched postgis image it points to as the other one hasn't been updated in over a year. * more info about postgres * added necessary password for postgres docker * updated wording in contributions * removed reference to MacJr environment var when starting postgres in contributions. The official image automatically creates a user named 'postgres', but it does require a password, which the command sets to 'postgres' * added more time to docker sleep/wait to enter postgis commands. This will always take a few seconds because the db is installing from scratch everytime. If postgres/postgis images aren't already downloaded locally, it will take even longer. Worst case, if the command times out on first run. Stop and remove the parse-postgres container and run the command again, 20 seconds should be enough wait time then * latest changes * initial fix, need to test * fixed lint * Adds caseInsensitive constraints to database, but doesn't pass regular tests. I believe this is because ensureIndex in the Postgres adapter is returning wrong. Also, some issues with the caseInsensitive test case * this version addes the indexes, but something still wrong with the ensureIndex method in adapter * removed code from suggestions * fixed lint * fixed PostgresAdapter test case * small bug in test case * reverted back to main branch package.json and lock file * fixed docker command in Contribute file * added ability to explain the find method * triggering another build * added ability to choose to 'analyze' a query which actually executes (this can be bad when looking at a query plan for Insert, Delete, etc.) the query or to just setup the query plan (default, previous versions defaulted to 'analyze'). Alse added some comparsons on sequential vs index searches for postgres * made sure to check that search actually returns 1 result. Removed prep time comparison between searches as this seemed to be variable * added test cases using find and case insensitivity on fields other than username and password. Also added explain to aggregate method * fixing issue where query in aggregate replaced the map method incorrectly * reverted back to mapping for aggregate method to make sure it's the issue * switched back to caseInsensitive check for email and username as it was causing issues * fixed aggregate method using explain * made query plain results more flexible/reusable. Got rid of droptables as 'beforeEach' already handles this * updated CONTRIBUTING doc to use netrecon as default username for postgres (similar to old style). Note that the official postgres docker image for postgres requires POSTGRES_PASSWORD to be set in order to use the image * left postgis at 2.5 in the contributing document as this is the last version to be backwards compatibile with older versions of parse server * updating docker command for postgres Co-authored-by: Arthur Cinader <700572+acinader@users.noreply.github.com>
|
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. |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Issue Description
If you use postgresql as database and need to calculate distances of
GeoPointobjects, you obviously need postgis. In postgis version > 2.2.0 the functionST_Distance_Spherewas renamed toST_DistanceSpherewhich causes the Postgres Storage Adapter to fail here:parse-server/src/Adapters/Storage/Postgres/PostgresStorageAdapter.js
Line 624 in 9d7da58
parse-server/src/Adapters/Storage/Postgres/PostgresStorageAdapter.js
Line 628 in 9d7da58
parse-server/src/Adapters/Storage/Postgres/PostgresStorageAdapter.js
Line 676 in 9d7da58
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
Expected Results
actual near locations
Actual Outcome
query fails with an internal error
Environment Setup
Server
parseplatform/parse-serverimage drom dockerhub)
Database
MongoDBPostgreSQL version: POSTGIS="3.0.0 r17983" [EXTENSION] PGSQL="120" (postgis/postgisimage from dockerhub)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)"}The text was updated successfully, but these errors were encountered: