SPATIAL

Example

CREATE TABLE geom (g GEOMETRY);
INSERT INTO geom (g) VALUES (ST_GeomFromText('POINT(2 2)'));
SELECT ST_AsText(g) FROM geom;

Output

+---------------+
| ST_AsText(g)  |
+---------------+
| POINT(2 2)    |
+---------------+

Explanation

A table named ‘geom’ is created and a GEOMETRY type column ‘g’ is added. A POINT geometry is inserted into the ‘g’ column using the ST_GeomFromText function. The ST_AsText function is then used to retrieve the POINT geometry as a string from the ‘g’ column.

Example

CREATE TABLE places (
  place text,
  geom geometry(Point)
);

INSERT INTO places
VALUES (
  'Tour Eiffel',
  ST_GeomFromText('POINT(2.2945 48.8584)', 4326)
);

SELECT place,
       ST_AsText(geometry) AS geometry
FROM   places;

Output

 place     | geometry
-----------+---------------------
 Tour Eiffel | POINT(2.2945 48.8584)

Explanation

The example creates a table called “places”, which includes a location text and a geom POINT data. The POINT data is the geo-spatial point for the place. We then inserted a row into a table, where the Tour Eiffel is at the geo-spatial point POINT(2.2945 48.8584).

Finally, with the SELECT statement, we returned both the name of the place and its geometry representation as text. The area of the Tour Eiffel was displayed along with its location point in the output.

Example

CREATE TABLE SpatialTable
    (id int IDENTITY (1,1),
    GeogCol1 geography,
    GeomCol1 geometry);
GO

INSERT INTO SpatialTable (GeogCol1, GeomCol1)
VALUES (geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326),
        geometry::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 0));

SELECT GeogCol1.ToString(), GeomCol1.ToString()
FROM SpatialTable;

Output

LINESTRING (-122.36 47.656, -122.343 47.656)
LINESTRING (-122.36 47.656, -122.343 47.656)

Explanation

This example creates a table named SpatialTable and inserts a row with a geography column GeogCol1 and a geometry column GeomCol1. Both columns contain the same line string representing a spatial entity (in this case, coordinates). The SELECT statement then retrieves and outputs the spatial data as character string using the built-in ToString() function.

Example

CREATE TABLE places(
   id NUMBER PRIMARY KEY,
   location SDO_GEOMETRY
);

INSERT INTO places(id, location)
VALUES(1, SDO_GEOMETRY(
  2001,
  8307,
  SDO_POINT_TYPE(37.77, -122.41, NULL),
  NULL,
  NULL));

SELECT id FROM places
WHERE SDO_RELATE(location, SDO_GEOMETRY(
  2003,
  8307,
  NULL,
  SDO_ELEM_INFO_ARRAY(1, 1003, 3),
  SDO_ORDINATE_ARRAY(37, -123, 38, -122)),
  'mask=anyinteract')= 'TRUE';

Output

ID
-----
1

Explanation

The SDO_GEOMETRY is a spatial data type in Oracle, it is used to store geometric and geographic data in two dimensions, three dimensions, and four dimensions. A Spatial index on the location column has been created and can be used by the SDO_RELATE function to quickly identify intersections, distances, and relationships between geometric shapes. In this case, it is used to identify places that intersect with a specified region.