Spatial Query : st_contains() related

[Former Member]

SQL Anywhere version : 12.0.1 build # 3769 OS : Windows

My objective is to return list of names where associated geometry contains the specified Point. Currently I inserted some dummy data with name and associated polygon.

When I try to fire following query which I was expecting would return at least one record, started giving me following error.

"select a.name from locationdb a where a.location.ST_CONTAINS(new ST_POINT(0.5206163, 0.90219253)) =1;"

I get error

There was an error reading the results of the SQL statement. The displayed results may be incorrect or incomplete. A geometry with SRID=4326 is not supported for method ST_Contains(ST_Geometry). SQLCODE=-1435, ODBC 3 State="HY000"

---

Why is SRID 4326 is not supported? How do I get list of SRIDs supported by ST_CONTAINS() method?

I referred to documentation link : http://infocenter.sybase.com/help/topic/com.sybase.help.sqlanywhere.12.0.1/dbspatial/pg-api-spatial-...

but did not help much.

Thanks, Ketan

[Former Member · Accepted solution]

There are actually two different problems with the example query:

1. The predicate is being applied between two geometries of differing SRIDs.
2. ST_CONTAINS is not supported on a round-earth SRS (SRID 4326 in this case)

The first problem arises because a.location has SRID=4326 (as evidenced by the error message), but the ST_POINT in the query has SRID=0 (default because no SRID was specified in the constructor). To fix it you need to specify the SRID in the ST_POINT constructor. See documentation page http://dcx.sybase.com/index.html#1201/en/dbspatial/pg-api-spatial-st-point-constructor.html for ST_POINT constructor templates.

The second problem is what you asked about: ST_CONTAINS is not supported on SRID 4326. To see a list of which methods are [not] supported under round-earth reference systems, see the following documentation pages: http://dcx.sybase.com/index.html#1201/en/dbspatial/list-of-spatial-methods.html or http://dcx.sybase.com/index.html#1201/en/dbspatial/list-of-spatial-predicates.html (for predicates only).

The precise reason why certain methods are not currently supported under round-earth has to do with implementation-specific details, but the problem is roughly that the strategy we currently use for indexing round-earth data does not distinguish a geometry's boundary from its interior (which is why we don't currently support ST_BOUNDARY under round-earth).

The definition of ST_Contains is as follows (see http://dcx.sybase.com/index.html#1201/en/dbspatial/pg-api-spatial-st-geometry-type-st-contains-metho... 😞

The ST_Contains method tests if the geometry-expression completely contains geo2 and there is one or more interior points of geo2 that lies in the interior of the geometry-expression.

See http://dcx.sybase.com/index.html#1201/en/dbspatial/addl-info-stcontains.html for additional explanation.

Under round-earth reference systems the second part of that definition is problematic since it requires distinguishing boundary from interior, which is why it is currently unsupported. However, we do support the (vendor extension) predicate ST_COVERS, which is essentially ST_CONTAINS without the interior intersection requirement. In other words, the only case where ST_CONTAINS and ST_COVERS diverge is when the smaller object occurs entirely on the boundary of the larger object. In this case, ST_CONTAINS returns false, whereas ST_COVERS returns true. The predicates ST_WITHIN and ST_COVEREDBY are analogous.

So, this leaves you two options for your query:

1. Use ST_COVERS/ST_COVEREDBY instead of ST_CONTAINS/ST_WITHIN. Recommended if your application can tolerate the minor change of semantics.
2. Project your round-earth data into a planar SRS before applying the predicate. To improve performance for large tables, you could define the projected geometry as a computed column and then define a spatial index over it.

[Former Member · Accepted solution]

Hello David,

Thanks very much for the answer and explanation.

The following query seems to be working for me.

select a.app_name from app_location a where a.location.ST_Covers(new ST_POINT(0.5206163, 0.90219253, 4326)) =1;

Best Regards, Ketan

[ian_mchardy]

There are a number of spatial operations that are not supported on round earth. Those that are supported on round earth are marked with an X in http://dcx.sybase.com/index.html#1201/en/dbspatial/list-of-spatial-methods.html.

In your case, you can use ST_Covers instead of ST_Contains. ST_Covers is supported for round earth and the only difference between ST_Covers and ST_Contains is if the point is exactly on the boundary of the polygon.

You can determine which spatial reference systems are round earth with:

select * from ST_SPATIAL_REFERENCE_SYSTEMS where round_earth = 'Y'

Note that you may want to install a large number of predefined spatial reference systems with sa_install_feature( 'st_geometry_predefined_srs' ).

[Former Member]

Hello Ian,

Thanks very much for the answer and explanation.

ST_COVERS is working for me with additional SRID as parameter.

Best Regards, Ketan