Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Find a 3D Spatial point inside a 3D Polygon(rectangle)

mlazAug 6 2015 — edited Sep 22 2015

I have a table:

CREATE TABLE 3DPOINT

(

     Flight_Id       VARCHAR(15),

    Spatial_Point   SDO_GEOMETRY

);

I then run:

INSERT INTO USER_SDO_GEOM_METADATA VALUES (

  '3DPOINT',

  'Spatial_Point',

  SDO_DIM_ARRAY(

    SDO_DIM_ELEMENT('Longitude', -180, 180, 10),

    SDO_DIM_ELEMENT('Latitude',  -90,  90, 10),

    SDO_DIM_ELEMENT('Altitude', -1000,40000, 10)),

  4985);

CREATE INDEX SIDX_3DPOINT on 3DPOINT(Spatial_Point)

  INDEXTYPE IS mdsys.spatial_index

  PARAMETERS ('sdo_indx_dims=3');

I then try to find all the points Spatial_Point values within a 3D Polygon(rectangle):

SELECT Flight_Id

, sd.Spatial_Point.SDO_POINT.X AS Longitude

, sd.Spatial_Point.SDO_POINT.Y AS Latitude

, sd.Spatial_Point.SDO_POINT.Z AS Altitude

FROM 3DPOINT sd

WHERE SDO_FILTER(Spatial_Point,

                    sdo_geometry(3003, -- 3d polygon

                                4985, -- SRID

                                NULL,

                                   sdo_elem_info_array(1,1003,3), -- one rectangle (1003 = exterior)

                                   sdo_ordinate_array(-74.454346,40.168380,100,-73.685303,41.102121,1000)) -- define rectangle (lower left and upper right)

                   ) = 'TRUE';

What I get back are all the rows that have a Longitude and Latitude within that triangle regardless of Altitude. Anyone see what I am doing wrong?

Thanks in advance.

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 20 2015
Added on Aug 6 2015
6 comments
1,290 views