SAP SQL Anywhere

Question

I'm getting an error with the ST_Intersection() function that makes no sense to me. The error is:

There was an error reading the results of the SQL statement. The displayed results may be incorrect or incomplete. Invalid polygon: ring is not closed (near 'CompoundCurve ((-8960237.9725 5357495.7308, -8960233.9554 5357492.7047, -8960232.568 5357491.5778, -8960231.9835 5357491.1286, ') SQLCODE=-1485, ODBC 3 State="HY000"

If you look at the example below, there is a Polygon and a MultiPolygon and I am checking whether or not they intersect. I believe all of the polygons involved are closed (i.e., start==end), but the error implies otherwise.

-------------------------------------------------------------------------------------------------
DROP TABLE IF EXISTS Test_GeometryPart;
CREATE TABLE Test_GeometryPart (
    "GEO_ID" BIGINT NOT NULL,
    "GEOMETRY" ST_Geometry(SRID=4269) NOT NULL,
) IN "system";

INSERT INTO "DBA"."Test_GeometryPart" ("GEO_ID","GEOMETRY") VALUES(43055,'Polygon ((-80.490979899999999 43.490733200000001, -80.491207399999993 43.490490100000002, -80.491187199999999 43.490479700000002, -80.491133399999995 43.490449599999998, -80.491082000000006 43.4904175, -80.490839100000002 43.490647500000001, -80.490960900000005 43.490721600000001, -80.490979899999999 43.490733200000001))');
INSERT INTO "DBA"."Test_GeometryPart" ("GEO_ID","GEOMETRY") VALUES(1485884,'MultiPolygon (((-80.491187230078907 43.490479716207119, -80.49115111388835 43.490459908120179, -80.491116014830851 43.490439150960938, -80.491081981067708 43.490417473797557, -80.49083911081506 43.490647468049133, -80.490960867340746 43.490721593241055, -80.491187230078907 43.490479716207119)), ((-80.491187231297985 43.490479717105607, -80.491187230086396 43.490479715314088, -80.491187230078907 43.490479716207119, -80.491187231297985 43.490479717105607)))');

COMMIT;

SELECT
gp.*
,gp.GEOMETRY.ST_Transform(3857) AS GP_3857
,zp.*
,zp.GEOMETRY.ST_Transform(3857) AS ZP_3857
,GP_3857.ST_Intersection(ZP_3857) AS INTERSECTION
FROM Test_GeometryPart gp, Test_GeometryPart zp
WHERE gp.GEO_ID = 43055
AND zp.GEO_ID = 1485884;
-------------------------------------------------------------------------------------------------

I'm running SQL Anywhere 16.0.0.1948 Developer Edition under Windows 7 (I believe it is the latest version since 'Check-for-Updates' reports no updates). Is this a bug? Has anyone else seen this?

Terry

Accepted Answer

One possible cause of this type of error is that the input geometries are invalid. Checking for invalid geometries is not performed when a geometry is loaded/created.

From

SELECT
gp.*
,gp.GEOMETRY.ST_IsValid()
,gp.GEOMETRY.ST_Transform(3857) AS GP_3857
,GP_3857.ST_IsValid()
,zp.*
,zp.GEOMETRY.ST_IsValid()
,zp.GEOMETRY.ST_Transform(3857) AS ZP_3857
,zp_3857.ST_IsValid()
//,GP_3857.ST_Intersection(ZP_3857) AS INTERSECTION
FROM Test_GeometryPart gp, Test_GeometryPart zp
WHERE gp.GEO_ID = 43055
AND zp.GEO_ID = 1485884;

Note the zp_3857 is not valid. From:

call st_geometry_dump( 
(SELECT
zp.GEOMETRY.ST_Transform(3857) AS ZP_3857
FROM Test_GeometryPart zp
WHERE zp.GEO_ID = 1485884 ),
'validate=full' )

We can see that:

Polygon ((-8960237.976 5357495.7335, -8960237.9758 5357495.7332, 
-8960237.9758 5357495.7333, -8960237.976 5357495.7335))

has the error: Curve 0 of polygon is not a ring (near "Polygon ((-8960237.976 5357495.7335, -8960237.9758 5357495.7332, -8960237.9758 5357495.7333, -8960237.976 5357495.7335))")

Hmm... This ring looks closed to me (start point is exactly end point). I will look further.

SAP SQL Anywhere

Spatial operation error using ST_Intersection() with ST_Transform()

Follow this question

Related questions