The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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
496142036
accept rate: 40%

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
2.6k22945
accept rate: 39%

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:

×186
×21
×15

question asked: 18 Nov '14, 15:48

question was seen: 695 times

last updated: 19 Nov '14, 12:41