6 Replies Latest reply: May 13, 2013 2:07 PM by user154136

# Identifying Coordinate Positions in a Polygon

I know I can use the built in sdo_geom functions to get the min and max coordinates of a rectangle. I was wondering if similar functionality exists for non rectangular polygons like parallelograms. Issue I have is that users can input 4 sets of coordinates in any order to build a geometry object. My logic requires that I know the top left and top right of the geometry object. I can write a function to return this, but I wanted to verify that this functionality does not already exist.

Thanks
Robert
• ###### 1. Re: Identifying Coordinate Positions in a Polygon
Robert,

Yes, that functionality already exists. You can write a SELECT statement using these function to get, for example, the bottom-left and upper-right.

[ SDO_GEOM.SDO_MIN_MBR_ORDINATE | http://docs.oracle.com/cd/B19306_01/appdev.102/b14255/sdo_objgeom.htm#i866250 ]
[ SDO_GEOM.SDO_MAX_MBR_ORDINATE | http://docs.oracle.com/cd/B19306_01/appdev.102/b14255/sdo_objgeom.htm#i866249 ]
``````WITH SampleGeometry AS (
SELECT
SDO_GEOMETRY(
'POLYGON ((6091829.946 2271465.64, 6092444.797 2271460.591, 6092445.067 2271797.663, 6091825.777 2271802.748, 6091829.946 2271465.64))',
40986
) geometry
FROM dual
)
SELECT
SDO_GEOM.SDO_MIN_MBR_ORDINATE(geometry,1) As left,
SDO_GEOM.SDO_MIN_MBR_ORDINATE(geometry,2) As bottom,
SDO_GEOM.SDO_MAX_MBR_ORDINATE(geometry,1) As right,
SDO_GEOM.SDO_MAX_MBR_ORDINATE(geometry,2) As upper
FROM SampleGeometry;

-- "Results"
LEFT     BOTTOM      RIGHT      UPPER
---------- ---------- ---------- ----------
6091825.78 2271460.59 6092445.07 2271802.75``````
Regards,
Noel
• ###### 2. Re: Identifying Coordinate Positions in a Polygon
Thanks Noel, I am aware of these functions for MBRs. In my case the geometry is not a rectangle, so this will not work. This will give me the coordinates of the bounding rectangle instead of the coordinates of the polygon itself. This can be quite different values depending on the shape of the polygon.

Thanks
Robert
• ###### 3. Re: Identifying Coordinate Positions in a Polygon
Dup Post

Edited by: user12191713 on May 13, 2013 10:11 AM
• ###### 4. Re: Identifying Coordinate Positions in a Polygon
Robert,

The question did seem familiar. Anyhow, I understand the problem a little better.
``````-- "MBR's points are Xs"

.|      O    X
|     * *
|    *   *
|   *     *
|  *       *
| O         O
|  *       *
|   *     *
|    *   *
|     * *
---X----O-------
|``````
If you want to find the O's, you could search for the intersection between your polygon and lines-defining-the-MBR. The following example demonstrates how you can identify the left- and right- most points of your polygon (not the enclosing MBR).
``````WITH SampleGeometry AS (
SELECT
SDO_GEOMETRY(
'POLYGON ((6091829.946 2271465.64, 6092444.797 2271460.591, 6092445.067 2271797.663, 6091825.777 2271802.748, 6091829.946 2271465.64))',
40986
) geometry
FROM dual
), MbrEdges AS (
SELECT
SDO_GEOMETRY('LINESTRING ('||
SDO_GEOM.SDO_MIN_MBR_ORDINATE(geometry,1)||' '||
SDO_GEOM.SDO_MIN_MBR_ORDINATE(geometry,2)||','||
SDO_GEOM.SDO_MIN_MBR_ORDINATE(geometry,1)||' '||
SDO_GEOM.SDO_MAX_MBR_ORDINATE(geometry,2)||')',
40986
) LeftEdge,
SDO_GEOMETRY('LINESTRING ('||
SDO_GEOM.SDO_MAX_MBR_ORDINATE(geometry,1)||' '||
SDO_GEOM.SDO_MIN_MBR_ORDINATE(geometry,2)||','||
SDO_GEOM.SDO_MAX_MBR_ORDINATE(geometry,1)||' '||
SDO_GEOM.SDO_MAX_MBR_ORDINATE(geometry,2)||')',
40986
) RightEdge
FROM SampleGeometry
)
SELECT
SDO_GEOM.SDO_INTERSECTION(SampleGeometry.geometry, MbrEdges.LeftEdge, 0.001) LeftMostPoint,
SDO_GEOM.SDO_INTERSECTION(SampleGeometry.geometry, MbrEdges.RightEdge, 0.001) RightMostPoint
FROM SampleGeometry, MbrEdges;

-- "Result"
SDO_GEOMETRY(2001, 40986, NULL, SDO_ELEM_INFO_ARRAY(1, 1, 1), SDO_ORDINATE_ARRAY(6091825.78, 2271802.75)) --"LeftMost"
SDO_GEOMETRY(2001, 40986, NULL, SDO_ELEM_INFO_ARRAY(1, 1, 1), SDO_ORDINATE_ARRAY(6092445.07, 2271797.66)) -- "RightMost"``````
Here's an alternative that doesn't generate intermediate lines:
``````WITH SampleGeometry AS (
SELECT
SDO_GEOMETRY(
'POLYGON ((6091829.946 2271465.64, 6092444.797 2271460.591, 6092445.067 2271797.663, 6091825.777 2271802.748, 6091829.946 2271465.64))',
40986
) geometry
FROM dual
), LeftMbrEdge AS (
SELECT MIN(t.x) x
FROM SampleGeometry, TABLE(SDO_UTIL.GETVERTICES(geometry)) t
), RightMbrEdge As (
SELECT MAX(t.x) x
FROM SampleGeometry, TABLE(SDO_UTIL.GETVERTICES(geometry)) t
), LeftMostPoint As (
SELECT t.x, t.y
FROM SampleGeometry, LeftMbrEdge, TABLE(SDO_UTIL.GETVERTICES(geometry)) t
WHERE t.x = LeftMbrEdge.x
), RightMostPoint As (
SELECT t.x, t.y
FROM SampleGeometry, RightMbrEdge, TABLE(SDO_UTIL.GETVERTICES(geometry)) t
WHERE t.x = RightMbrEdge.x
)
SELECT LeftMostPoint.*, RightMostPoint.*
FROM LeftMostPoint, RightMostPoint;

-- "Result "
6091825.78 2271802.75 6092445.07 2271797.66``````
These examples only searched for polygon points that touched the left or right edges of the MBR. You could easily modify them to search for polygon points that touch the upper or lower edges of the MBR.

Regards,
Noel
• ###### 5. Re: Identifying Coordinate Positions in a Polygon
Hi Robert,

No, such a function does not exist in the delivered functionality.

That said, it's easy enough to create a function that get's you the min and max X and Y from the SDO_ORDINATES. You could even do it with just SQL :-)

Be aware though that if you do not compare properly you might end up with the MBR anyway, since that is essentially what and MBR is.

Out of curiosity, what do you need this for? And, if your user defines a diamond, how do you define top left and top right (or top left and bottom right)?

HTH,
Stefan

Noel must've posted when I was typing, did not see that last night, only noticed it this morning :-). But there's your solution.

Edited by: Stefan Jager on May 14, 2013 9:45 AM
• ###### 6. Re: Identifying Coordinate Positions in a Polygon
Thanks Noel, I will look at your examples and apply them to my issue. Stefan, I need this for a custom tiling procedure I am working on where I have to divide a polygon into multiple polygons. i was using the sdo_sam tiling package but it was creating some oddly shaped tiles in some cases. Now, I am using the sdo_util tilt and bearing functions to divide the polygon up and i need to know what the top left and top right positions so that I can get the bearing and distance. I have a sdo_geometry object, so i can use this to determine the coordinate order since it will always be counter clockwise.

Thanks
Robert