Today we are going to focus on the Spatial and Graphics option of the Oracle Database. Most business information has a location component, such as customer addresses, sales territories and physical assets. Businesses can take advantage of their geographic information by incorporating location analysis and intelligence into their information systems. The geospatial data features of Oracle Spatial and Graph option support complex geographic information systems (GIS) applications, enterprise applications and location services applications. Oracle Spatial and Graph option extends the spatial query and
analysis features included in every edition of Oracle Database with the Oracle Locator feature, and provides a robust foundation for applications that require advanced spatial analysis and processing in the Oracle Database. It supports all major spatial data types and models, addressing challenging business-critical requirements from various industries, including transportation, utilities, energy, public sector, defense and commercial location intelligence.
The Spatial home page is a good starting point to learn more about the technology. Books that cover this topic are
Note that most of these books are three years old or older. Spatial has not changed much between 11g and 12c so the older books are still relevant. The key to the Spatial component is being able to define objects using geospatial tags. To achieve this, Oracle extended the database with the SDO_GEOMETRY data type. This is used just like an INTEGER or CHAR declaration for a variable but it contains a latitude and longitude element to define where something is located. Some sample code that we can lift from the Pro Oracle Spatial book looks like
SQL> CREATE TABLE us_restaurants_new
location SDO_GEOMETRY -- New column to store locations
This creates a table that defines an entry that helps us find where the restaurant is located. We can populate this entry with
SQL> INSERT INTO us_restaurants_new VALUES
2001, -- SDO_GTYPE attribute: "2" in 2001 specifies dimensionality is 2.
NULL, -- other fields are set to NULL.
SDO_POINT_TYPE -- Specifies the coordinates of the point
-87, -- first ordinate, i.e., value in longitude dimension
38, -- second ordinate, i.e., value in latitude dimension
NULL -- third ordinate, if any
This inserts and entry for restaurant number 1, labeled PIZZA_HUT, and the location is defined by a point located at -87, 38. Note that these are relative locations defined in relation to a map. We use the SDO_GTYPE to define what type of mapping that we are using and how we are describing the location for this store.
The key benefit to this is that we can define restaurants and things like interstates. We can query the database by asking for any reference that is half a mile from the interstate. This is done with the following query
SQL> SELECT poi_name
SDO_GEOM.SDO_DISTANCE(P.location, I.geom, 0.5) distance
FROM us_interstates I, us_restaurants P
WHERE I.interstate = 'I795'
ORDER BY distance
WHERE ROWNUM <= 5;
BLAIR MANSION INN DINNER THEATER
The select statement does a distance calculation looking at the distance between the interstate labeled I795 and any restaurant in the database. Note that we could have selected an address on the interstate and found something that is less than a specified distance. This is typically how something like Google Maps works. It uses your current location which is read from your phone as a latitude, longitude, and elevation and shows you the search term close to you. This allows you to easily find banks, places to eat, places to get gas, or an address that you are trying to get to.
We can not only look for distances relative to a point or a line (as we did with the interstate) but we can draw shapes around an object and look for things that fall into or out of the shape. For example, if we get the GSP points for a park, we can draw a shape that defines the park using latitude and longitude points. We can then look for related objects inside the park, outside the park, or within a few feet of the park. This helps police look for crimes that happen surrounding a park and react appropriately. In the database we define an object with spatial data and draw a shape around the object. A simple way of doing this is a simple box. The code to do this would look like
SQL> INSERT INTO USER_SDO_GEOM_METADATA VALUES
'CUSTOMERS', -- TABLE_NAME
'LOCATION', -- COLUMN_NAME
SDO_DIM_ARRAY -- DIMINFO attribute for storing dimension bounds, tolerance
'LONGITUDE', -- DIMENSION NAME for first dimension
-180, -- SDO_LB for the dimension
180, -- SDO_UB for the dimension
0.5 -- Tolerance of 0.5 meters
'LATITUDE', -- DIMENSION NAME for second dimension
-90, -- SDO_LB for the dimension
90, -- SDO_UB for the dimension
0.5 -- Tolerance of 0.5 meters
8307 -- SRID value for specifying a geodetic coordinate system
You can define a data type as
- Line string
- Polygon with a hole
- Collection (a combination of all of the above)
- Compound line string
- Compound polygon
- 3d Composite surface
- 3d Simple solid
- 3d Compound solid
- 3d Collection (a combination of all 3d objects)
When you define a spatial object it uses the SDO_GEOMETRY structure. This structure contains an SDO_GTYPE that defines if the object is 2d or 3d as well as the data type (0 = Uninterpreted type, 1 = Point, 5 = Multipoint, 2 = Line, 6 = Multiline, 3 = Polygon/surface, 7 = Multipolygon/multisurface, 4 = Collection, 8 = Solid, 9 = Multisolid). An entry of 2001 would be a 2d object, designated by the 2, that is a single points, designated by the 1. If this entry were 2002 it would be a 2d object that is a series of points to create a line, designated by the second 2. The SDO_SRID defines specifies the spatial reference system, or coordinate system, for the geometry. We can have a relative coordinate system or use latitude and longitude for coordinates. The SDO_POINT attribute specifies the location of a point geometry, such as the location of a customer. This gives us a reference point to work from and the rest of the data is the relative information based on the SDO_SRID. For example, we can draw a polygon defining a park starting at the northwest corner of the park. The SDO_POINT will provide the northwest corner of the park. The SDO_ELEM_INFO and SDO_ORDINATES attributes describe the polygon around the park. For more detailed examples, look at Chapter 3 of Pro Oracle Spatial for Oracle Database 11g
We are not going to go into deep detail on how to program Spatial. It is recommended that you look at
It is important to note that Spatial is an optional package that runs on the Enterprise Edition database. If you are going to run this in the cloud you need to use the High Performance Edition or Extreme Performance Edition. If you are going to run this on IaaS you need to purchase the option on top of your database license and the processor metrics need to match. For example, if you run on a 2 virtual core system in the cloud, you need a 2 virtual core license for Spatial as well. You can not run Spatial on Amazon RDS because they disable this feature.
In summary, Spatial and Graphics are optional packages that help you do locational queries against a database. Spatial is not unique to Oracle but the structures and select statements typically do not cross database types but does work with products like Golden Gate to replicate data to other database spatial structures and queries. Spatial is a very powerful package that simplifies select statements that would be very complex otherwise. Finding distance between objects or distances from a line (highway for example) or distances from a polygon (park for example). If your application needs Spatial you need to select the High Performance or Extreme Performance editions.