saving a polygon in oracle database

Question

I have captured four points(coordinate) of a plot using a gps device.

Point 1:- lat- 27.54798833 long- 80.16397166
Point 2:- lat 27.547766, long- 80.16450166
point 3:- lat 27.548131, long- 80.164701
point 4:- ---

now I want to save these coordinate in oracle database which save it as an polygon.

Thanks

Answer 1

If you're intending to use Oracle Spatial for storage or processing of polygons, then you'll need to store the data as an SDO_GEOMETRY object. Here's a quick example:

CREATE TABLE my_polygons (
  id INTEGER
, polygon sdo_geometry
)
/

INSERT INTO my_polygons (
  id
, polygon
)
VALUES (
  1
, sdo_geometry (
    2003 -- 2D Polygon
  , 4326 -- WGS84, the typical GPS coordinate system
  , NULL -- sdo_point_type, should be NULL if sdo_ordinate_array specified
  , sdo_elem_info_array(
      1    -- First ordinate position within ordinate array
    , 1003 -- Exterior polygon
    , 1    -- All polygon points are specified in the ordinate array
    )
  , sdo_ordinate_array(
      80.16397166, 27.54798833,
    , 80.16450166, 27.547766,
    , 80.164701, 27.548131,
    , 80.16397166, 27.54798833
    )
  )
)
/

There's far more information about the different flags on the object type here: http://docs.oracle.com/cd/B19306_01/appdev.102/b14255/sdo_objrelschema.htm

Key things to note:

1. What is your source coordinate system? You state GPS - is it WGS84 (Oracle SRID = 4326)? Your GPS device will tell you. You can look up the Oracle SRID for this in the table MDSYS.SDO_COORD_REF_SYS
2. Make sure your coordinates complete a full polygon (i.e. loop back around to the starting point).
3. Your coordinates for a polygon's external boundary should be ordered anticlockwise.
4. You can call the method st_isvalid() on a geometry object to quickly test whether it is valid or not. You should ensure geometries are valid before presenting them to any other software.