Answers to: Spatial operation error using ST_Intersection() with ST_Transform()https://sqlanywhere-forum.sap.com/questions/23477/spatial-operation-error-using-st_intersection-with-st_transform<p>I'm getting an error with the ST_Intersection() function that makes no sense to me. The error is:
</p><pre><p></p>
<hr>
<p>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"</p>
<hr>
<p></p></pre>
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.<p></p>
<pre>-------------------------------------------------------------------------------------------------
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;
-------------------------------------------------------------------------------------------------
</pre>
<p>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?</p>
<p>Terry</p>enWed, 19 Nov 2014 12:41:10 -0500Comment by Ian McHardy on Terry Wilkinson's questionhttps://sqlanywhere-forum.sap.com/questions/23477/spatial-operation-error-using-st_intersection-with-st_transform#23489<p>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.</p>Ian McHardyWed, 19 Nov 2014 12:41:10 -0500https://sqlanywhere-forum.sap.com/questions/23477/spatial-operation-error-using-st_intersection-with-st_transform#23489Comment by Terry Wilkinson on Ian McHardy's answerhttps://sqlanywhere-forum.sap.com/questions/23477/spatial-operation-error-using-st_intersection-with-st_transform#23488<p>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?</p>Terry WilkinsonWed, 19 Nov 2014 12:34:32 -0500https://sqlanywhere-forum.sap.com/questions/23477/spatial-operation-error-using-st_intersection-with-st_transform#23488Comment by Terry Wilkinson on Terry Wilkinson's questionhttps://sqlanywhere-forum.sap.com/questions/23477/spatial-operation-error-using-st_intersection-with-st_transform#23485<p>Clearly, I'm not a spatial expert either, and your suggestion is just what I needed. Thanks a lot.</p>Terry WilkinsonWed, 19 Nov 2014 10:21:13 -0500https://sqlanywhere-forum.sap.com/questions/23477/spatial-operation-error-using-st_intersection-with-st_transform#23485Answer by Ian McHardyhttps://sqlanywhere-forum.sap.com/questions/23477/spatial-operation-error-using-st_intersection-with-st_transform/23484<p>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.</p>
<p>From</p>
<pre class="codehilite"><code>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;</code></pre>
<p>Note the zp_3857 is not valid. From:</p>
<pre class="codehilite"><code>call st_geometry_dump(
(SELECT
zp.GEOMETRY.ST_Transform(3857) AS ZP_3857
FROM Test_GeometryPart zp
WHERE zp.GEO_ID = 1485884 ),
'validate=full' )</code></pre>
<p>We can see that:</p>
<pre class="codehilite"><code>Polygon ((-8960237.976 5357495.7335, -8960237.9758 5357495.7332,
-8960237.9758 5357495.7333, -8960237.976 5357495.7335))</code></pre>
<p>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))")</p>
<p>Hmm... This ring looks closed to me (start point is exactly end point). I will look further.</p>Ian McHardyWed, 19 Nov 2014 10:20:02 -0500https://sqlanywhere-forum.sap.com/questions/23477/spatial-operation-error-using-st_intersection-with-st_transform/23484Comment by Volker Barth on Terry Wilkinson's questionhttps://sqlanywhere-forum.sap.com/questions/23477/spatial-operation-error-using-st_intersection-with-st_transform#23478<p>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.</p>
<p>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):</p>
<pre>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;
</pre>
<hr>
<p>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')").</p>Volker BarthWed, 19 Nov 2014 03:52:24 -0500https://sqlanywhere-forum.sap.com/questions/23477/spatial-operation-error-using-st_intersection-with-st_transform#23478