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 [message #578015] |
Fri, 22 February 2013 13:59  |
gentleman777us
Messages: 122
Registered: 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.
Please reply.
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: 9100
Registered: 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: 122
Registered: 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: 122
Registered: 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)
from shippers a, user_sdo_geom_metadata m
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: 9100
Registered: 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.
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Dec 03 12:12:58 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
