Home » Server Options » Spatial » How to compute Min Max lat and lon values from polygon (Oracle 11g)
How to compute Min Max lat and lon values from polygon Fri, 22 February 2013 13:59
 gentleman777us Messages: 122Registered: April 2005 Senior Member
Hi

I have a polygon data in a table. I have a function which takes lat and lon values as input and it returns me the row. I use the following function to get it.

select id from shippers shp
where SDO_RELATE(shp.geom, sdo_geometry(2001, 8307, sdo_point_type(i_lon,i_lat,null),null,null),'mask=anyinteract')= 'TRUE';

This will return the rowid associated which satisfies this condition.

My objective now is to return the minimum and maximum value of latitude and also minimum and maximum value of longitude values associated with this record.

what sdo function I should use to get it.

Thanks
Re: How to compute Min Max lat and lon values from polygon [message #578016 is a reply to message #578015] Fri, 22 February 2013 14:07
 Barbara Boehmer Messages: 8737Registered: November 2002 Location: California, USA Senior Member
Re: How to compute Min Max lat and lon values from polygon [message #578017 is a reply to message #578016] Fri, 22 February 2013 15:13
 gentleman777us Messages: 122Registered: April 2005 Senior Member
Hi Barbara,

Does SDO_GEOM.SDO_MIN_MBR_ORDINATE will give me MINIMUM value of lat or lon?

Also does SDO_GEOM.SDO_MAX_MBR_ORDINATE will give me MAXIMUM value of LAT or LON?

But I need MAXIMUM and MINIMUM value both LATITUDE and LONGITUDE.

Please email me how I can achive this.

Thanks
Re: How to compute Min Max lat and lon values from polygon [message #578019 is a reply to message #578017] Fri, 22 February 2013 17:01
 Barbara Boehmer Messages: 8737Registered: November 2002 Location: California, USA Senior Member
I don't usually respond via email, since that would defeat the purpose of the forums, which is making it so that everyone can read and respond and contribute and learn and also avoid duplicate responses, so I will respond here as usual.

Apparently those functions return the minimum and maximum X and Y (latitude and longitude) coordinates of the minimum bounding rectangle, as demonstrated below, using some sample data from the Oracle online documentation. Since the shapes for names cola_a and cola_b and cola_c are rectangles, this works. However, the results are different for cola_d. So, whether or not this works for you may depend on what type of data you have and what results you want. Given the data for cola_d, do you want 6,7,10,11 or 8,7,10,11?

```SCOTT@orcl_11gR2> SELECT c.name, c.shape.sdo_ordinates,
2  	    SDO_GEOM.SDO_MBR(c.shape, m.diminfo).sdo_ordinates min_bounding_rectangle,
3  	    SDO_GEOM.SDO_MIN_MBR_ORDINATE(c.shape, m.diminfo, 1) min_x,
4  	    SDO_GEOM.SDO_MIN_MBR_ORDINATE(c.shape, m.diminfo, 2) min_y,
5  	    SDO_GEOM.SDO_MAX_MBR_ORDINATE(c.shape, m.diminfo, 1) max_x,
6  	    SDO_GEOM.SDO_MAX_MBR_ORDINATE(c.shape, m.diminfo, 2) max_y
7    FROM cola_markets c, user_sdo_geom_metadata m
8   WHERE m.table_name = 'COLA_MARKETS' AND m.column_name = 'SHAPE'
9  /

NAME
--------------------------------
SHAPE.SDO_ORDINATES
--------------------------------------------------------------------------------
MIN_BOUNDING_RECTANGLE
--------------------------------------------------------------------------------
MIN_X      MIN_Y      MAX_X      MAX_Y
---------- ---------- ---------- ----------
cola_a
SDO_ORDINATE_ARRAY(1, 1, 5, 7)
SDO_ORDINATE_ARRAY(1, 1, 5, 7)
1          1          5          7

cola_b
SDO_ORDINATE_ARRAY(5, 1, 8, 1, 8, 6, 5, 7, 5, 1)
SDO_ORDINATE_ARRAY(5, 1, 8, 7)
5          1          8          7

cola_c
SDO_ORDINATE_ARRAY(3, 3, 6, 3, 6, 5, 4, 5, 3, 3)
SDO_ORDINATE_ARRAY(3, 3, 6, 5)
3          3          6          5

cola_d
SDO_ORDINATE_ARRAY(8, 7, 10, 9, 8, 11)
SDO_ORDINATE_ARRAY(6, 7, 10, 11)
6          7         10         11

4 rows selected.
```

[Updated on: Fri, 22 February 2013 17:03]

Report message to a moderator

Re: How to compute Min Max lat and lon values from polygon [message #578021 is a reply to message #578019] Fri, 22 February 2013 18:00
 gentleman777us Messages: 122Registered: April 2005 Senior Member
Barbara,

Thank you very much for providing insight. I think this helps me to compute. This evening I ran into some issues with the data we have received. They provided us data with 3 dimensions. It gives errors when I scroll through results of SDO_MAX_MBR_ORDINATE. I need to address that first.

I think What you have provided is enough to solve my issue. I will update later.

Thanks and have a great weekend.
Re: How to compute Min Max lat and lon values from polygon [message #578220 is a reply to message #578021] Mon, 25 February 2013 15:16
 gentleman777us Messages: 122Registered: April 2005 Senior Member
Hi Barbara,

Ex: one of the recordI id = 5960) in my shape column is as follows:

MDSYS.SDO_GEOMETRY(2003,4326,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(-76.1461679399999,38.820674378,-76.145 951339,38.8071596180001,-76.128678857,38.807327764,-76.128892196,38.8208426050001,-76.1461679399999,38.820674378))

when I run the following query

select sdo_geom.sdo_min_mbr_ordinate(a.shape, m.diminfo,1)
where id = '5960'

I got the following error:

ora-13364 layer dimensionality does not match the geometry dimensions

The spatial layer has a geometry with a different dimesions than the dimensions specified in the layer.

Action: Make sure that all geometrics in a layer have the same dimensions and that they match the dimensions in the SDO_DIM_ARRAY object for the layer in the USER_SDO_GEOM_METADATA view.

How can I fix this geometrics mis match ?

Thanks
Re: How to compute Min Max lat and lon values from polygon [message #578223 is a reply to message #578220] Mon, 25 February 2013 17:43
 Barbara Boehmer Messages: 8737Registered: November 2002 Location: California, USA Senior Member
You need to insert the proper values in user_sdo_geom_metadata before creating the index, as demonstrated below. You can read in the online documentation how to determine what values you should use. The values that I used below consisted of minimum and maximum latitude and longitude for the planet, minimum tolerance, and the srid from your data.

```SCOTT@orcl_11gR2> CREATE TABLE shippers(
2    id     NUMBER PRIMARY KEY,
3    shape  SDO_GEOMETRY)
4  /

Table created.

SCOTT@orcl_11gR2> INSERT INTO shippers VALUES(
2    5960,
3    SDO_GEOMETRY(
4  	 2003,
5  	 4326,
6  	 NULL,
7  	 SDO_ELEM_INFO_ARRAY(1,1003,1),
8  	 SDO_ORDINATE_ARRAY
9  	   (-76.1461679399999,38.820674378,
10  	    -76.145951339,    38.8071596180001,
11  	    -76.128678857,    38.807327764,
12  	    -76.128892196,    38.8208426050001,
13  	    -76.1461679399999,38.820674378)))
14  /

1 row created.

SCOTT@orcl_11gR2> INSERT INTO USER_SDO_GEOM_METADATA VALUES (
2    'SHIPPERS',
3    'SHAPE',
4    SDO_DIM_ARRAY(
5  	 SDO_DIM_ELEMENT('X', -90, 90, 0.05),
6  	 SDO_DIM_ELEMENT('Y', -180, 180, 0.05)
7  	  ),
8    4326
9  )
10  /

1 row created.

SCOTT@orcl_11gR2> CREATE INDEX shippers_spatial_idx
2  	ON shippers(shape)
3  	INDEXTYPE IS MDSYS.SPATIAL_INDEX
4  /

Index created.

SCOTT@orcl_11gR2> SELECT c.id, c.shape.sdo_ordinates,
2  	    SDO_GEOM.SDO_MBR(c.shape, m.diminfo).sdo_ordinates min_bounding_rectangle,
3  	    SDO_GEOM.SDO_MIN_MBR_ORDINATE(c.shape, m.diminfo, 1) min_x,
4  	    SDO_GEOM.SDO_MIN_MBR_ORDINATE(c.shape, m.diminfo, 2) min_y,
5  	    SDO_GEOM.SDO_MAX_MBR_ORDINATE(c.shape, m.diminfo, 1) max_x,
6  	    SDO_GEOM.SDO_MAX_MBR_ORDINATE(c.shape, m.diminfo, 2) max_y
7    FROM shippers c, user_sdo_geom_metadata m
8   WHERE m.table_name = 'SHIPPERS' AND m.column_name = 'SHAPE'
9  /

ID
----------
SHAPE.SDO_ORDINATES
--------------------------------------------------------------------------------
MIN_BOUNDING_RECTANGLE
--------------------------------------------------------------------------------
MIN_X      MIN_Y      MAX_X      MAX_Y
---------- ---------- ---------- ----------
5960
SDO_ORDINATE_ARRAY(-76.146168, 38.8206744, -76.145951, 38.8071596, -76.128679, 3
8.8073278, -76.128892, 38.8208426, -76.146168, 38.8206744)
SDO_ORDINATE_ARRAY(-76.146168, 38.8071596, -76.128679, 38.8208426)
-76.146168 38.8071596 -76.128679 38.8208426

1 row selected.
```

Re: How to compute Min Max lat and lon values from polygon [message #578305 is a reply to message #578223] Tue, 26 February 2013 13:17
 gentleman777us Messages: 122Registered: April 2005 Senior Member
Thanks! Barbara, it worked now. There was an issue with my meta data.
 Previous Topic: Syntax for Creating Local spatial index on Partioned table. Next Topic: Area of Country
Goto Forum:

Current Time: Fri Aug 18 18:39:38 CDT 2017

Total time taken to generate the page: 0.25046 seconds