1 / 23

Introduction to Oracle Spatial

Introduction to Oracle Spatial. SELECT SDO_GEOM.SDO_INTERSECTION(b.geometry, a.geometry, 0.000000005) as geometry FROM state_hwy_all_ahtd a, counties_tig2000 b WHERE b.ID = 72 and (MDSYS.SDO_RELATE(a.GEOMETRY, b.GEOMETRY, 'mask=ANYINTERACT querytype = WINDOW') = 'TRUE');

alucus
Download Presentation

Introduction to Oracle Spatial

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Introduction to Oracle Spatial SELECT SDO_GEOM.SDO_INTERSECTION(b.geometry, a.geometry, 0.000000005) as geometry FROM state_hwy_all_ahtd a, counties_tig2000 b WHERE b.ID = 72 and (MDSYS.SDO_RELATE(a.GEOMETRY, b.GEOMETRY, 'mask=ANYINTERACT querytype = WINDOW') = 'TRUE'); SELECT geometry FROM counties_tig2000 WHERE id = 72; Spatial Database

  2. Oracle Spatial Oracle Spatial consists of a set of functions and procedures, built into the database software, that enables spatial data to be stored, accessed, and analyzed. • Oracle Spatial uses the Oracle object-relational model for representing geometries. • The geometry of a spatial element is stored as an object, in a single row, in a single column of type SDO_GEOMETRY. A single table will hold all of the geometries and attributes of a layer. • The SDO_GEOMETRY object supports many geometry types, including points, line strings, polygons, arc line strings, arc polygons, compound polygons, compound line strings, circles, and rectangles Spatial database table “Counties_tig2000” id county fips geometry Benton county

  3. SDO_GEOMETRY The object type SDO_GEOMETRY as: CREATE TYPE sdo_geometry AS OBJECT ( SDO_GTYPE NUMBER, SDO_SRID NUMBER, SDO_POINT SDO_POINT_TYPE, SDO_ELEM_INFO SDO_ELEM_INFO_ARRAY, SDO_ORDINATES SDO_ORDINATE_ARRAY ); CREATE TYPE sdo_point_type AS OBJECT ( X NUMBER, Y NUMBER, Z NUMBER ); CREATE TYPE sdo_elem_info_array AS VARRAY (1048576) OF NUMBER; CREATE TYPE sdo_ordinate_array AS VARRAY (1048576) OF NUMBER; SDO_GTYPE: type of geometry in format DLTT Dimensions (2,3 or 4) Linear Reference measure dimension (which dimension (3 or 4) contains the measure value) TTgeometry type (01 – point, 02 – line, 03 – polygon) Example: 2003 indicates a two-dimensional polygon SDO_SRID: Identity of the spatial coordinate system Example: 8265 indicates Longitude / Latitude (NAD 83) SDO_POINT: X Y and Z values of a point SDO_ELEM_INFO: Lets you know how to interpret the ordinates stored in the SDO_ORDINATES object. Contains one or more sets of triplets. Each triplet set is interpreted as: SDO_STARTING_OFFSET: Offset in the sdo_ordinate array SDO_ETYPE: Type of element SDO_INTERRETATION: Defines either the number of triplet values that are part of a compound element or how the sequence of ordinates for the non-compound element is interpreted SDO_ORDINATES: The coordinate values that make up the boundary of a spatial object

  4. SDO_GEOMETRY Example Counties_tig2000 table Id county fips geometry Benton county SDO_GEOMETRY Object • SDO_GTYPE 2003 2 dimensional geometry with one polygon • SDO_SRID 8265 identify a coordinate system -- long / Lat (NAD 83) • SDO_POINT NULL X, Y, and Z of point geometry • SDO_ELEM_INFO • SDO_STARTING_OFFSET 1 starting point of ordinates • SDO_ETYPE 1003 exterior simple polygon ring • SDO_INTERPRETATION 1 polygon boundary made up of a sequence of connecting straight line segments • SDO_ORDINATES -94.33516, 36.170734, … coordinate boundaries of the geometry (1429)

  5. SDO_GEOMETRY Example ----Create a simple table with a spatial column— --------------------------------------------------------------- DROP TABLE TEMPBOX; CREATE TABLE TEMPBOX ( ID NUMBER, GEOMETRY SDO_GEOMETRY); --Insert a simple polygon data type into the table-- INSERT INTO TEMPBOX VALUES ( 1, MDSYS.SDO_GEOMETRY(2003, 8265, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-94.38,36.26, -94.15,36.26, -94.15, 36.14, -94.38,36.14, -94.38,36.26))); ----------------------------------------------------------------- --Insert table dimensional information---------------- DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME = 'TEMPBOX'; INSERT INTO USER_SDO_GEOM_METADATA VALUES ('TEMPBOX', 'GEOMETRY', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',-180,180,0.0000005), MDSYS.SDO_DIM_ELEMENT('Y',-90,90,0.0000005)),8265); ---------------------------------------------------------------- --Create a spatial index on the geometry column-- DROP INDEX IS_TEMPBOX; CREATE INDEX IS_TEMPBOX ON TEMPBOX(GEOMETRY) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

  6. Small Candidate Set Secondary Filter Exact Result Set Primary Filter Large Input Data Set The Query Model Oracle Spatial uses a two-tier query model to resolve spatial queries and spatial joins. The two-tiered model uses two distinct operations, the primary and secondary filter. • The Primary filter compares geometry approximations (Spatial Indexes) then passes that along to the secondary filter. • The secondary filter applies exact computations to the geometries that pass from the primary filter.

  7. Spatial Operators Spatial operators are used to perform proximity analysis between geometry objects. Like relational operators (<, >, =) spatial operators can be used in the “WHERE” clause of a regular SQL statement. Operators: SDO_FILTER: Specifies which geometries may interact with a given geometry. This Operator performs only the primary filter operation. SDO_RELATE:Determines whether, or not, two geometries interact in a specific way. This operator performs both primary and secondary filter operations. SDO_NN:Determines the nearest neighbor geometries to a geometry. SDO_NN_DISTANCE:Returns the distance of an object returned by the SDO_NN operator. (Ancillary operator of SDO_NN) SDO_WITHIN_DISTANCE:Determines if two geometries are within a specified distance from one another.

  8. Spatial Operator Examples SDO_FILTER:Specifies which geometries may interact with a given geometry. This Operator performs only the primary filter operation. Format SDO_FILTER(table_geometry, query_geometry) Parameters table_geometry: sdo_geometry column in a table (must be spatially indexed) query_geometry: sdo_geometry column in a table or transient instance of a geometry Returns ‘TRUE’ if geometry objects Minimum Bounding Rectangle are non-disjoint False Positive Find all of the schools that match the minimum bounding rectangle of Washington county SELECT a.geometry FROM public_schools_k12_src a, counties_tig2000 b WHERE b.abname = 'Washington County' AND SDO_FILTER(a.geometry,b.geometry) = 'TRUE'

  9. Spatial Operator Examples SDO_RELATE:Determines whether, or not, two geometries interact in a specific way. This operator performs both primary and secondary filter operations. Format SDO_RELATE(table_geometry, query_geometry, parameters) Parameters table_geometry: sdo_geometry column in a table (must be spatially indexed) query_geometry: sdo_geometry column in a table or transient instance of a geometry parameters: mask keyword to specify the topological relationship Valid mask: TOUCH, OVERLAPBDYDISJOINT, OVERLAPBDYINTERSECT, EQUAL, INSIDE, COVEREDBY, CONTAINS, COVERS, ANYINTERACT, ON Returns ‘TRUE’ if geometry objects have the topological relationship specified by the mask keyword SDO_RELATE:Find all of the schools in Washington county that match the county geometry exactly. SELECT a.geometry FROM public_schools_k12_src a, counties_tig2000 b WHERE b.abname = 'Washington County' ANDSDO_RELATE(a.geometry,b.geometry,'mask=ANYINTERACT') = 'TRUE'

  10. Spatial Operator Examples SDO_NN:Determines the nearest neighbor geometries to a geometry. Format SDO_NN(table_geometry, query_geometry, parameters[, number]) Parameters table_geometry: sdo_geometry column in a table (must be spatially indexed) query_geometry: sdo_geometry column in a table or transient instance of a geometry parameters: keyword that determines the behavior of the operator Valid keywords: sdo_batch_size: The number of rows to be evaluated at a time when the SDO_NN expression may need to be evaluated multiple times to satisfy the WHERE clause sdo_num_res: specifies the number of results to be returned unit: used with SDO_NN_DISTANCE operator as a tag to bind the two operators Returns Object from the table_geometry that are nearest to the query_geometry SDO__NN:Find the 5 closest schools to Bob’s house. SELECT a.geometry FROM public_schools_k12_src a WHERE SDO_NN(a.geometry, MDSYS.SDO_GEOMETRY(2001, 8265, MDSYS.SDO_POINT_TYPE(- 94.2465, 35.9785, NULL), NULL,NULL), 'SDO_NUM_RES=5') = 'TRUE'

  11. Spatial Operator Examples SDO_NN_DISTANCE:Returns the distance of an object returned by the SDO_NN operator. Format SDO_NN_DISTANCE(number) Parameters number: used with SDO_NN operator as a tag to bind the two operators Returns The distance (in meters for geodetic data) of an object returned by SDO_NN SDO_NN_DISTANCE:What is the distance to the 5 closest schools to Bob’s house? SELECT a.NAME,SDO_NN_DISTANCE(1) FROM public_schools_k12_src a WHERE SDO_NN(A.GEOMETRY, MDSYS.SDO_GEOMETRY(2001,8265, MDSYS.SDO_POINT_TYPE(-94.2465, 35.9785, NULL) ,NULL,NULL), 'SDO_NUM_RES=5', 1) = 'TRUE' ORDER BY 2; SCHOOL Distance PRAIRIE GROVE HIGH 6411.59039 PRAIRIE GROVE JUNIOR HIGH 6411.59039 PRAIRIE GROVE LOWER ELEM.6649.8313 GREENLAND ELEM SCHOOL 6757.65032 GREENLAND HIGH SCHOOL 6757.65032

  12. Spatial Operator Examples SDO_WITHIN_DISTANCE:Determines if two geometries are within a specified distance from one another. Format SDO_WITHIN_DISTANCE(table_geometry, query_geometry, parameters) Parameters table_geometry: sdo_geometry column in a table (must be spatially indexed) query_geometry: sdo_geometry column in a table or transient instance of a geometry parameters: keyword that determines the behavior of the operator Valid keywords: distance: Distance value in meters for geodetic data or as defined in the unit parameter may need to be evaluated multiple times to satisfy the WHERE clause unit: unit of measurement default is meters querytype: ‘FILTER’ keyword for only primary filter operation Returns Object from the table_geometry that is within specified distance of the object in the query_geometry SDO_WITHIN_DISTANCE:What school district boundaries are within 10 miles of Bob’s house? Public School Districts Lincoln Prairie Grove West Fork Springdale Farmington Fayetteville Greenland Winslow Greenland Elkins SELECT a.name "Public School Districts" FROM public_school_districts_src a WHERE SDO_WITHIN_DISTANCE(a.geometry, (MDSYS.SDO_GEOMETRY(2001,8265, MDSYS.SDO_POINT_TYPE(-94.2465,35.9785,NULL), NULL,NULL)), 'DISTANCE = 10 UNIT = MILE') = 'TRUE';

  13. Geometric Analysis Functions Geometry functions in Oracle Spatial can be grouped into the following categories: Single-Object operations: SDO_AREA:Computes the area of a two dimensional polygon. SDO_LENGTH:Computes the length or perimeter of a geometry. SDO_BUFFER:Generates a buffer polygon around a geometry. SDO_CENTROID:Returns the centroid (center of mass or gravity) of a polygon. SDO_POINTONSURFACE:Returns a point that is guaranteed to be on the surface of a polygon. SDO_MBR:Returns the minimum bounding rectangle of a geometry. SDO_MIN_MBR_ORDINATE:Returns the minimum value of the specified ordinate of the minimum bounding rectangle of a geometry (min(X) or min(Y)). SDO_MAX_MBR_ORDINATE:Returns the maximum value of the specified ordinate of the minimum bounding rectangle of a geometry (max(X) or max(Y)). SDO_CONVEXHULL:Returns a polygon object that represents the convex hull of a geometry object (Shrink-wrap the geometry). SDO_ARC_DENSITY:Returns a geometry in which each arc or circle in the input geometry is changed into an approximation of an arc or circle consisting of straight line segments (NOTE: Some GIS software only supports simple geometry features (points, lines and polygons).

  14. A B A B A B A B Geometry Functions Two-Object Operations: SDO_INTERSECTION:Returns a geometry object that is the topological intersection (AND operation) of two geometry objects. SDO_UNION:Returns a geometry object that is the topological union (OR operation) of two geometry objects. SDO_XOR:Returns a geometry object that is the topological symmetric difference (XOR operation) of two geometry objects. SDO_DIFFERENCE:Returns a geometry object that is the topological difference (MINUS operation) of two geometry objects. SDO_DISTANCE:Computes the distance between two geometry objects. Validation Operations: SDO_VALIDATE_LAYER:Determines if all geometries stored in a column are valid (table level check). SDO_VALIDATE_GEOMETRY:Determines if a geometry is valid (row level check).

  15. Geometry Functions Spatial Aggregate Functions: SDO_AGGR_CENTROID:Returns the geometry object that is the center-of-gravity of multiple geometry object. SDO_AGGR_CONVEXHULL:Returns the geometry object that is the convex hull of multiple geometry object. SDO_AGGR_MBR:Returns the minimum bounding rectangle of multiple geometry object. SDO_AGGR_UNION:Returns the geometry object that is the topological union of multiple geometry object.

  16. Geometric Analysis Examples SDO_GEOM.SDO_AREA:Computes the area of a two dimensional polygon. Format SDO_GEOM.SDO_AREA(geometry, tolerance, unit_params) Parameters geometry: The geometry object to be analyzed tolerance: Tolerance used in analyzsis unit_params: units of the return value Returns Area of a geometry object (polygon) Size NAME -------------- ------------------- 151.642321 Cedarville 101.280977 Elkins 32.9321684 Farmington 113.541157 Fayetteville 86.7219756 Gentry 135.643985 Greenland 74.1132046 Greenland 740.479142 Huntsville 488.251756 Huntsville 146.224995 Lincoln 196.57418 Mountainburg 304.955565 Ozark 329.734769 Ozark 105.52943 Prairie Grove 259.682501 Rogers 144.4364 Siloam Springs 184.059404 Springdale 252.21372 St. Paul 131.281055 West Fork 61.529816 Winslow SDO_GEOM.SDO_AREA:What are the sizes of the school districts in Washington County. SELECT SDO_GEOM.SDO_AREA(a.geometry, 0.0005, 'UNITS = SQ_MILE') "Size", a.name FROM public_school_districts_src a, counties_tig2000 b WHERE b.abname = 'Washington County' and SDO_FILTER(a.geometry,b.geometry) = 'TRUE' ORDER by 2;

  17. Geometric Analysis Examples SDO_GEOM.SDO_LENGTH:Computes the length or perimeter of a geometry. Format SDO_GEOM.SDO_LENGTH(geometry, tolerance, unit_params) Parameters geometry: The geometry object to be analyzed tolerance: Tolerance used in analyzsis unit_params: units of the return value Returns Length of a line geometry object or perimeter of a polygon SDO_GEOM.SDO_LENGTH:How many miles of State highways are in Washington County? SELECT SUM(SDO_GEOM.SDO_LENGTH(a.geometry, 0.0005, 'UNITS = MILE')) "State Hwy in Washington Co" FROM state_hwy_all_ahtd a, counties_tig2000 b WHERE b.abname = 'Washington County' and SDO_RELATE(a.geometry,b.geometry,'MASK=ANYINTERACT') = 'TRUE'; State Hwy in Washington Co -------------------------- 219.587073

  18. Geometric Analysis Examples SDO_GEOM.SDO_BUFFER:Generates a buffer polygon around or inside a geometry object. Format SDO_GEOM.SDO_BUFFER(geometry, tolerance, distance[, parameters]) Parameters geometry: The geometry object to be analyzed tolerance: Tolerance used in analysis distance: units of the return value parameters: units and arc_tolerance keywords Returns Buffer geometry SDO_GEOM.SDO_BUFFER:What are the two mile buffer zones around Prairie Grove and Farmington. SELECT SDO_GEOM.SDO_BUFFER(a.geometry, 2.0, 0.0005, 'UNIT=MILE' ) FROM city_limits2003_ahtd a WHERE a.city_name = 'Prairie Grove' or a.city_name = 'Farmington'

  19. Geometric Analysis Examples SDO_GEOM.SDO_CENTROID:Returns the centroid (center of mass or gravity) of a polygon. SDO_GEOM.SDO_POINTONSURFACE:Returns a point that is guaranteed to be on the surface of a polygon. Format SDO_GEOM.SDO_CENTROID(geometry, tolerance) SDO_GEOM.SDO_POINTONSURFACE(geometry, tolerance) Parameters geometry: The geometry object to be analyzed tolerance: Tolerance used in analyzsis Returns geometry SDO_GEOM.SDO_CENTROID:Where do I put the label? SELECT SDO_GEOM.SDO_CENTROID(a.geometry, 0.0005) FROM city_limits2003_ahtd a WHERE a.city_name = 'Prairie Grove' NOTE: SDO_CENTROID does note always put the center of mass inside the polygon. A shape like a cresant moon would have the centroid placed outside the polygon. SDO_POINTONSURFACE the geometry is always on the polygon.

  20. Geometric Analysis Examples SDO_GEOM.SDO_MBR:Returns the minimum bounding rectangle of a geometry. SDO_GEOM.SDO_MIN_MBR_ORDINATE:Returns the minimum value of the specified ordinate of the minimum bounding rectangle of a geometry (min(X) or min(Y)). SDO_GEOM.SDO_MAX_MBR_ORDINATE:Returns the maximum value of the specified ordinate of the minimum bounding rectangle of a geometry (max(X) or max(Y)). Format SDO_GEOM.SDO_MBR(geometry[, dim]) SDO_GEOM.SDO_MIN_MBR_ORDINATE(geometry,ordinate_pos) SDO_GEOM.SDO_MIN_MBR_ORDINATE(geometry,ordinate_pos) Parameters geometry: The geometry object to be analyzed Dim: Dimensional information ordinate_pos: Y = 1, X = 2 Returns geometry number number SELECT SDO_GEOM.SDO_MBR(a.geometry) FROM city_limits2003_ahtd a WHERE a.city_name = 'Prairie Grove'

  21. Geometric Analysis Examples SDO_CONVEXHULL:Returns a polygon object that represents the convex hull of a geometry object (Shrink-wrap the geometry). Often used to simplify complex geometries. Format SDO_GEOM.SDO_CONVEXHULL(geometry, tolerance) Parameters geometry: The geometry object to be analyzed tolerance: Tolerance used in analysis Returns geometry SDO_GEOM.SDO_CONVEXHULL:Produce simplified geometry of Fayetteville city limits. SELECT SDO_GEOM.SDO_CONVEXHULL(a.geometry,0.0005) FROM city_limits2003_ahtd a WHERE a.city_name = 'Fayetteville'

  22. A B Geometry Function Example Two-Object Operations SDO_INTERSECTION:Returns a geometry object that is the topological intersection (AND operation) of two geometry objects. Format SDO_GEOM.SDO_INTERSECTION(geometry1, geometry2, tolerance) Parameters geometry1: geometry object geometry2: geometry object tolerance: Returns geometry shared by the two geometries What is the area of overlap between two polygon geometries? SELECT sdo_geom.sdo_intersection(b.geometry, a.geometry, 0.00005) FROM counties_tig2000 a, tempbox b WHERE a.abname = 'Washington Counties' and b.id = 1 What is the area of overlap between a polygon geometry and a line geometry? SELECT sdo_geom.sdo_intersection(b.geometry, a.geometry, 0.00005) FROM adt2000_ahtd a, tempbox b WHERE b.ID = 1 and (MDSYS.SDO_RELATE(a.GEOMETRY, b.GEOMETRY, 'mask=ANYINTERACT') = 'TRUE')

  23. Geometry Function Example Two-Object Operations SDO_DISTANCE:Computes the distance between two geometry objects. Format SDO_GEOM.SDO_INTERSECTION(geometry1, geometry2, tolerance[, units]) Parameters geometry1: geometry object geometry2: geometry object tolerance: units: Unit of measure Returns Distance between geometries based on the closest points of the two objects What is the minimum distance between the city limits of Farmington and Prairie Grove? DISTANCE ---------- 3.35874221 SELECT SDO_GEOM.SDO_DISTANCE(a.geometry,b.geometry,0.005,'UNIT=MILE') as Distance FROM city_limits2003_ahtd a, city_limits2003_ahtd b WHERE a.city_name = 'Prairie Grove' and b.city_name = 'Farmington' What are the cemeteries that are within 3 miles of the city limits of Prairie Grove? SELECT b.name, SDO_GEOM.SDO_DISTANCE(a.geometry,b.geometry,0.005,'UNIT=MILE') as Distance FROM city_limits2003_ahtd a, cemeteries_gnis b WHERE a.city_name = 'Prairie Grove' and SDO_GEOM.SDO_DISTANCE(b.geometry,a.geometry,0.005,'UNIT=MILE') < 3.0 NAME DISTANCE ------------------------------ -------- Illinois Chapel Cemetery 1.27144539 Rutherford Cemetery 2.56814328 Sharp Cemetery 1.9561893

More Related