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

asked 18 Nov '14, 15:48

Terry%20Wilkinson's gravatar image

Terry Wilkinson
746303548
accept rate: 25%

edited 18 Nov '14, 17:49

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')").

(19 Nov '14, 03:52) Volker Barth

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

(19 Nov '14, 10:21) Terry Wilkinson

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.

permanent link

answered 19 Nov '14, 10:20

Ian%20McHardy's gravatar image

Ian McHardy
3.4k23557
accept rate: 40%

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?

(19 Nov '14, 12:34) Terry Wilkinson
Replies hidden

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.

(19 Nov '14, 12:41) Ian McHardy
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×260
×24
×15

question asked: 18 Nov '14, 15:48

question was seen: 2,572 times

last updated: 19 Nov '14, 12:41