Question Re: Spatial operation error using ST_Intersection() with ST_Transform() in Technology Q&A

Spatial operation error using ST_Intersection() with ST_Transform()

Former Member — Tue, 18 Nov 2014 15:48:14 GMT

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

Re: Spatial operation error using ST_Intersection() with ST_Transform()

VolkerBarth — Wed, 19 Nov 2014 03:52:25 GMT

I'm no spatial expert at all but I (wildly!) guess the error may result from the fact that the spatial transformation seems to turn the closed polygons into a not really closed one.

If you test for intersection with the original 4269 SRID you do not get that error (though I can't tell whether the result is correct):

SELECT
   gp.GEOMETRY
   ,zp.GEOMETRY
   ,gp.GEOMETRY.ST_Intersection(zp.GEOMETRY) AS INTERSECTION
FROM Test_GeometryPart gp, Test_GeometryPart zp
WHERE gp.GEO_ID = 43055
   AND zp.GEO_ID = 1485884;

Another wild guess: When simply viewing the original geometries in the Spatial Viewer with SRID 3857, the second row seems to return an error, at least with 16.0.0.1915 (SQLCODE -1486 "Invalid polygon: ring has zero area (near '%1')").

Re: Spatial operation error using ST_Intersection() with ST_Transform()

ian_mchardy — Wed, 19 Nov 2014 10:20:03 GMT

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.

Re: Spatial operation error using ST_Intersection() with ST_Transform()

Former Member — Wed, 19 Nov 2014 10:21:13 GMT

Clearly, I'm not a spatial expert either, and your suggestion is just what I needed. Thanks a lot.

Re: Spatial operation error using ST_Intersection() with ST_Transform()

Former Member — Wed, 19 Nov 2014 12:34:32 GMT

It looks to me as if, when calculating ZP_3857 the ST_Transform() function breaks the polygon. That is, zp.GEOMETRY is valid but ZP_3857 is not?

Re: Spatial operation error using ST_Intersection() with ST_Transform()

ian_mchardy — Wed, 19 Nov 2014 12:41:10 GMT

Agreed that before the ST_Transform the geometry is reported as valid and after the ST_Transform it is not valid. We are looking further.