Example 1-7 Creating Longitude/Latitude Spatial Data Using SDO_GEOMETRY(longitude, latitude) Constructor

The example creates a table, inserts a row of longitude/latitude spatial data using the SDO_GEOMETRY(-73.45, 45.2) constructor, creates the spatial index, and then queries the inserted geometry.

–- Create a table
CREATE TABLE t1(i NUMBER, geom SDO_GEOMETRY);

–- Insert lon/lat spatial data using the following constructor
INSERT INTO t1 VALUES (1, SDO_GEOMETRY(-73.45, 45.2));

–- Create the spatial index
–- Required metadata automatically created when index is created
CREATE INDEX lon_lat_sidx ON t1(geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2;

–- Display the inserted geometry
SQL> SELECT geom FROM t1;

GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-73.45, 45.2, NULL), NULL, NULL)

Example 1-8 Creating and Indexing Polygonal Longitude/Latitude Data

This example creates a spatial table, inserts three rows of longitude/latitude spatial data, updates the metadata, creates the spatial index, and then performs some miscellaneous operations.

-- Create the table.

CREATE TABLE polygons_long_lat (
  geom_id NUMBER PRIMARY KEY,
  geom_name VARCHAR2(32),
  shape SDO_GEOMETRY);

-- The geometries are simple polgons using the 
-- WGS 84 (longitude/latitude) coordinate system.
-- The geometries are three simple polygons. The first and third have 4 sides; 
-- the second has 3 sides (triangle). These geeometries happen to
-- be in or around Concord in the US state of Massachusetts, but they
-- do not represent any actual identifiable places or areas of interest.

INSERT INTO polygons_long_lat VALUES(
  1,
  'geom_1',
  SDO_GEOMETRY(
    SDO_POLYGON2D,  -- two-dimensional polygon
    4326,  -- SRID for 'Longitude / Latitude (WGS 84)' coordinate system
    NULL,
    SDO_ELEM_INFO_ARRAY(1,1003,1), -- polygon
    SDO_ORDINATE_ARRAY(
                      -71.373742, 42.475827,
                      -71.369622, 42.455059,
                      -71.344903, 42.472788,
                      -71.357949, 42.480638,
                      -71.373742, 42.475827)
              )
);

INSERT INTO polygons_long_lat VALUES(
  2,
  'geom_2',
  SDO_GEOMETRY(
    SDO_POLYGON2D,  -- two-dimensional polygon
    4326,  -- SRID for 'Longitude / Latitude (WGS 84)' coordinate system
    NULL,
    SDO_ELEM_INFO_ARRAY(1,1003,1), -- polygon
    SDO_ORDINATE_ARRAY(
                      -71.358120, 42.464937,
                      -71.352971, 42.454046,
                      -71.357777, 42.475827,
                      -71.358120, 42.464937)
              )
);

INSERT INTO polygons_long_lat VALUES(
  3,
  'geom_3',
  SDO_GEOMETRY(
    SDO_POLYGON2D,  -- two-dimensional polygon
    4326,  -- SRID for 'Longitude / Latitude (WGS 84)' coordinate system
    NULL,
    SDO_ELEM_INFO_ARRAY(1,1003,1), -- polygon
    SDO_ORDINATE_ARRAY(
                      -71.394341, 42.435552,
                      -71.405671, 42.429977,
                      -71.390564, 42.428203,
                      -71.383698, 42.434285,
                      -71.394341, 42.435552)
              )
);


-- Optional Step: Update the USER_SDO_GEOM_METADATA view. 
–- By default, Oracle Spatial will automatically create the metadata in
-- USER_SDO_GEOM_METADATA view using a default tolerance value of 0.05.
–- Run this step only if you prefer a different tolerance value.
–- The following example uses a tolerance value of 10 meters.

INSERT INTO user_sdo_geom_metadata
    (TABLE_NAME,
     COLUMN_NAME,
     DIMINFO,
     SRID)
  VALUES (
  'polygons_long_lat',
  'shape',
  SDO_DIM_ARRAY(
    SDO_DIM_ELEMENT('Longitude', -180, 180, 10),  -- 10 meters tolerance
    SDO_DIM_ELEMENT('Latitude', -90, 90, 10)  -- 10 meters tolerance
     ),
  4326   -- SRID for 'Longitude / Latitude (WGS 84)' coordinate system
);

-- Create the spatial index

CREATE INDEX polygons_long_lat_spatial_idx
   ON polygons_long_lat(shape)
   INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2;

-----------------
-- Miscellaneous other operations

-- Is a specific geometry (geom_3) valid?
SELECT geom_name, SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(shape, 10)
   FROM polygons_long_lat WHERE geom_name = 'geom_3';

-- Is a layer valid? (First, create the results table.)
CREATE TABLE val_results (sdo_rowid ROWID, result VARCHAR2(2000));
CALL SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT('POLYGONS_LONG_LAT', 'SHAPE', 
  'VAL_RESULTS');
-- Next SELECT should process 3 rows and return null (no errors).
SELECT * from val_results;

-- Do two geometries (geom_1 and geom_2) have any spatial relationship?
SELECT SDO_GEOM.RELATE(p_a.shape, 'anyinteract', p_b.shape, 10)
  FROM polygons_long_lat p_a, polygons_long_lat p_b
  WHERE p_a.geom_name = 'geom_1' AND p_b.geom_name = 'geom_2';

-- Return the areas of all geometries.
SELECT geom_name, SDO_GEOM.SDO_AREA(shape, 10) FROM polygons_long_lat;

(For an example of bulk loading of longitude/latitude data, see Bulk Loading Point-Only Data in SDO_GEOMETRY Objects.)

Example 1-9 Output of SELECT Statements in Longitude/Latitude Data Example

This example shows the output of the SELECT statements in the preceding example.

SQL> -- Miscellaneous other operations
SQL> 
SQL> -- Is a specific geometry (geom_3) valid?
SQL> SELECT geom_name, SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(shape, 10)
  2  	FROM polygons_long_lat WHERE geom_name = 'geom_3';

GEOM_NAME                                                                       
--------------------------------                                                
SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(SHAPE,10)                               
--------------------------------------------------------------------------------
geom_3                                                                          
TRUE                                                                            
                                                                                

SQL> 
SQL> -- Is a layer valid? (First, create the results table.)
SQL> CREATE TABLE val_results (sdo_rowid ROWID, result VARCHAR2(2000));

Table created.

SQL> CALL SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT('POLYGONS_LONG_LAT', 'SHAPE',
  2    'VAL_RESULTS');

Call completed.

SQL> -- Next SELECT should process 3 rows and return null (no errors).
SQL> SELECT * from val_results;

SDO_ROWID                                                                       
------------------                                                              
RESULT                                                                          
--------------------------------------------------------------------------------
                                                                                
Rows Processed <3>                                                              
                                                                                

SQL> 
SQL> -- Do two geometries (geom_1 and geom_2) have any spatial relationship?
SQL> SELECT SDO_GEOM.RELATE(p_a.shape, 'anyinteract', p_b.shape, 10)
  2    FROM polygons_long_lat p_a, polygons_long_lat p_b
  3    WHERE p_a.geom_name = 'geom_1' AND p_b.geom_name = 'geom_2';

SDO_GEOM.RELATE(P_A.SHAPE,'ANYINTERACT',P_B.SHAPE,10)                           
--------------------------------------------------------------------------------
TRUE                                                                            

SQL> 
SQL> -- Return the areas of all geometries.
SQL> SELECT geom_name, SDO_GEOM.SDO_AREA(shape, 10) FROM polygons_long_lat;

GEOM_NAME                        SDO_GEOM.SDO_AREA(SHAPE,10)                    
-------------------------------- ---------------------------                    
geom_1                                            3531176.58                    
geom_2                                            273244.085                    
geom_3                                            812379.389                    

SQL>