Push down of PostGIS fucntions

[jatorre]

Hi,

We are wondering if you have considered adding support for push down of postgis functions. We did something like this, obviously much more easy on the postgres_fdw (https://carto.com/blog/postgres-fdw/). We generalized it for Postgresql here https://www.postgresql.org/message-id/CACowWR0EY8f6tA16Qb6zLRV69efTmiDN_-Z61QSRn%2BbG%2BZkVwg%40mail.gmail.com

Most interesting to us is pushing down at least bounding box queries, but trying to get as much functions push down will be great and we will be willing to contribute with resources and matching of operations.

[laurenz]

I have no objections.

My first idea would be to hard-code which functions can get pushed down. To test if a function is a PostGIS function, it is probably enough to verify that GEOMETRYOID is valid and that the function belongs to the same schema. I'd say that we can assume Oracle Locator functions to be there, but I wouldn't feel too good if we assumed that Oracle Spatial is installed, because it probably isn't in most cases.

The new code would be added to deparseExpr.

I am happy to help with the oracle_fdw side of things if you provide the code with the knowledge what can be translated to what.

One design principle for oracle_fdw is that functions are only pushed down if the results are guaranteed to be the same as for local execution. For example, I don't push down inequality operators on strings, because the result depends on the collation. So I will resist translating functions to something that is only an approximation.

[jatorre]

Thanks for the response.

Would it make sense to have undet the server options an attribute to indicate if oracle spatial is installed on the remote server to decide if passing by those functions, and assume oracle locator is always there?

We will provide on this issue a first pass of 3 or 4 functions with the equivalence between Oracle and PostGIS, possibly 1 or 2 in Oracle Locator and another 1 or 2 in Oracle Spatial.

There is a common standard that PostGIS and Oracle follow on OGC SQL so there should not be many semantic differences, but we will see...

Thanks!

[laurenz]

Sure, a server option like that would be fine.

[borja-munoz]

Hi,

I work with @jatorre. Regarding spatial function translation/mapping, one example would be the ST_DWithin PostGIS function that can be translated to the SDO_WITHIN_DISTANCE Oracle function.

If we have a table with adjacent polygon geometries like census blocks or municipalities and we want to find the elements within 1000 metres of a given element, these would be the corresponding queries:

• PostGIS SELECT p2.name FROM polygon_table p1, polygon_table p2
  WHERE p1.name = 'name' AND ST_DWithin(p1.geom, p2.geom, 1000);

• Oracle SELECT p2.name FROM polygon_table p1, polygon_table p2 WHERE p1.name = 'name' AND SDO_WITHIN_DISTANCE(p1.geom, p2.geom, 'distance=1000') = 'TRUE' ;

Please let me know if this was the type of example that you were expecting. For other functions, the mapping is not so straightforward. I'll add more examples.

Thanks!

[laurenz]

That looks good from the semantics.

But I see a certain problem with this example: The Oracle documentation writes:

The operator must always be used in a WHERE clause and the condition that includes the operator should be an expression of the form:

SDO_WITHIN_DISTANCE(arg1, arg2, 'distance = <some_dist_val>') = 'TRUE'

(The expression must not equate to any value other than 'TRUE'.)

The geometry column must have a spatial index built on it. If the data is geodetic, the spatial index must be an R-tree index.

SDO_WITHIN_DISTANCE is not supported for spatial joins. See Within-Distance Operator for a discussion on how to perform a spatial join within-distance operation.

So, as often, Oracle was kind of sloppy and implemented it only halfways.

Now this means that the pushed down query will fail if the Oracle table does not have a spatial index. So a query that would work perfectly fine today will suddenly cause an error, just because a condition is pushed down.

And then there is this:

SELECT *
FROM table1
   LEFT JOIN table2 ON st_dwithin(table1.g, table2.g, 42):

According to the documentation, that will fail if the join is pushed down to Oracle.

So you'd have to change deparseExpr to know if it is called in the context of a join condition or not.

So you see the kind of problems you are up against. I am not saying it cannot be done, but you have to watch out for the corner cases.

Another sentence from the Oracle documentation, talking about the first function argument:

Specifies a geometry column in a table.

Does that mean that the function will actually fail if the first argument is not a column reference? Yet another case to consider.

[ThinkJ001]

Yes, and also patched the testcase

---

发件人: Laurenz Albe [email protected] 发送时间: Saturday, March 14, 2020 5:18:56 AM 收件人: laurenz/oracle_fdw [email protected] 抄送: Subscribed [email protected] 主题: Re: [laurenz/oracle_fdw] Push down of PostGIS fucntions (#379)

This is an attempt to implement #250https://github.com/laurenz/oracle_fdw/issues/250. Did you also patch the documentation?

― You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHubhttps://github.com/laurenz/oracle_fdw/issues/379#issuecomment-598915746, or unsubscribehttps://github.com/notifications/unsubscribe-auth/ABVPHU3PIWJPJOZU6AJWFLLRHKPMBANCNFSM4K4IQI2A.

[pauldzy]

Just a note for PostGIS enhancements, Oracle Spatial is now free https://blogs.oracle.com/oraclespatial/spatial-now-free-with-all-editions-of-oracle-database

Meaning hopefully we can start to see folks just installing by default and thus more of a chance of Spatial being there to match up with PostGIS functions.

[jatorre]

The biggest issue remains what @laurenz points out on the difference on the semantics and the issues with no consistency on the returns for push down queries.

@laurenz Would it make sense to set a config param on the server set up to indicate the push down of spatial functions and have somewhere documented how these behaves. Not perfect, but considering that there is a lot of corner case scenarios, might make sense to let the user be explicit on activating this feature at their risk. If they dont it will still work through regular FDW system with no push down.

I am trying to find a way to enable the most common use cases where push downs make a lot of sense, and not let the corner cases destroy all the fun. And I agree it feels almost impossible to ensure we are going to be consistent.

[laurenz]

I am currently on vacations, will look at it after Aug 9.

[laurenz]

Sorry for letting this rot...

It is not pretty, but as a last resort a foreign server option like unsafe_postgis_pushdown would be acceptable. That would explicitly allow for wrong results, but we should document exactly what could happen. And I'd draw the line at errors. A query that is valid when executed in PostgreSQL should never error out.

[laurenz]

I'll close this, since it has been inactive for over two years, and I see no consensus about how this can be implemented safely.