Forum Stats

  • 3,851,536 Users
  • 2,263,995 Discussions
  • 7,904,771 Comments

Discussions

Please Ignore

WhiteScars
WhiteScars Member Posts: 4
edited Dec 30, 2009 3:00AM in SQL & PL/SQL
Hello guys,
First of all, i am not sure if i post this message right place. if i did so, please accept my apologizes.
I am new to Oracle Spatial Database concepts. I really appriciate if you can help me.

By the way, this is what i have done

I have created a table with the following DDL:

CREATE TABLE shapes(
shape_id number(8) NOT NULL,
shape_name varchar2(100) NOT NULL,
shape SDO_GEOMETRY
);

Defined metadata as follows:

INSERT INTO user_sdo_geom_metadata
(TABLE_NAME,
COLUMN_NAME,
DIMINFO,
SRID)
VALUES (
'shapes',
'shape',
SDO_DIM_ARRAY( -- 600X800 grid
SDO_DIM_ELEMENT('X', 0, 600, 0.005),
SDO_DIM_ELEMENT('Y', 0, 800, 0.005)
),
NULL -- SRID
);

Created Spatial Index like this:
CREATE INDEX shape_spatial_idx ON SHAPES(shape) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

Inserted following data:
INSERT INTO shapes VALUES (1, 'Rectangle', SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3), SDO_ORDINATE_ARRAY(340,178,544,56)));
INSERT INTO shapes VALUES (2, 'Polygon', SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,1), SDO_ORDINATE_ARRAY(306,193,130,441,489,653,88,183,442,354)));
INSERT INTO shapes VALUES (3, 'Circle', SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,4), SDO_ORDINATE_ARRAY(344,324,244,284,304,284)));

Then Run the following query:

SELECT * FROM shapes s1
WHERE SDO_CONTAINS(s1.shape, SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE (324, 224, NULL), NULL, NULL)
) = 'TRUE';

SQL should return Polygon shape as a result of the query, because the point (324, 224) is inside of the the polygon. However the query doesnt fetch any data :)

To understand whats going on, i have written a small java application that fetches a specified shape in the table and draws on a Panel. The result is shown on the image
below.

http://img63.imageshack.us/img63/6936/36977188.png

When i try to query a point that is inside the red area, i cannot fetch any row. But the red area is also a part of the polygon (,^^)
Then i try to query another point that is inside the green area, i can fetch polygon shape.

I have also checked the query in java code. it returns true.
if (s instanceof GeneralPath){
GeneralPath path = (GeneralPath)s;
PathIterator pi = path.getPathIterator(new AffineTransform());
Polygon p1 = new Polygon();
while (!pi.isDone())
{
float[] point = new float[2];
int type = pi.currentSegment(point);
if (type == PathIterator.SEG_LINETO)
p1.addPoint((int)point[0], (int)point[1]);
else if (type == PathIterator.SEG_MOVETO)
{
p1 = new Polygon();
p1.addPoint((int)point[0], (int)point[1]);
}
pi.next();
}
System.out.println(p1.contains(new Point(324, 224)));
drawing.setCustomShape(p1);
drawing.repaint();
}

Is this some kind of bug, or am i doing something wrong?

Edited by: user4472000 on Dec 29, 2009 3:20 PM

Edited by: user4472000 on Dec 29, 2009 3:22 PM

Edited by: user4472000 on Dec 29, 2009 3:23 PM

Edited by: user4472000 on Dec 29, 2009 3:24 PM

Edited by: user4472000 on Dec 29, 2009 11:47 PM
Tagged:

Answers

This discussion has been closed.