1.32 Getting Started with Longitude/Latitude Spatial Data
Get started on creating spatial data using the WGS 84 (longitude/latitude) coordinate system.
Starting with Oracle Database 23ai, you can easily create longitude/latitude
spatial data using the SDO_GEOMETRY(-73.45, 45.2) constructor as shown in
the following example.
Example 1-7 Creating Longitude/Latitude Spatial Data
Using SDO_GEOMETRY(-73.45, 45.2) 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)The following example refers to concepts that are explained in SDO_GEOMETRY Object Type and Coordinate Systems (Spatial Reference Systems).
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(
2003, -- two-dimensional polygon
8307, -- 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(
2003, -- two-dimensional polygon
8307, -- 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(
2003, -- two-dimensional polygon
8307, -- 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
),
8307 -- 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>
Parent topic: Spatial Concepts