0

The sql below is intended to create a point from a know X,Y. It should then buffer the point with a known distance. Last, the sql should intersect the buffer with an underlying polygon layer and return the name field from the polygon the buffer intersects. Seems straightforward.

SELECT name
FROM polygons t1
WHERE sde.st_intersects (sde.st_buffer(sde.st_point(x, y), buffer), t1.SHAPE) = 1

When I run the sql, I get the following:

Error at line 3
ORA-20003: Geometry type "" is not a valid shape type.
ORA-06512: at "SDE.ST_GEOM_UTIL", line 275
ORA-06512: at "SDE.ST_POINT", line 21

What am I doing wrong and how do I fix it? The X,Y I'm using are definitely in the correct area so buffering and finding an intersection should not be a problem.

I'm using st_geometry on Oracle from Arc 10.1

Improve this question

1 Answer 1

Reset to default
1

You need to add a SRID to ST_POINT.

SELECT name
FROM polygons t1
WHERE sde.st_intersects (sde.st_buffer(sde.st_point(x, y, SRID), buffer), t1.SHAPE) = 1
Improve this answer
3
  • 1
    You'll get better performance if you place the t1.SHAPE parameter first in the argument list (utilizing the index on the polygons table)
    – Vince
    Commented Oct 12, 2015 at 17:41
  • @Vince Nice tip! A SELECT query we've been using went from 500ms to 115ms. How did you know to make that suggestion? I know geometry is indexed but how do you know switching the parameters would help?
    – DenaliHardtail
    Commented Oct 12, 2015 at 17:49
  • The spatial index of the first parameter is used. If the first parameter is a constant, then the spatial query is a full table scan on the table of the second parameter. The real fun comes with a spatial join of two tables; when the spatial test may be different if you change the order (e.g., ST_Contains vs. ST_Within)
    – Vince
    Commented Oct 12, 2015 at 18:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.