Basic Oracle Spatial operations

Basic Oracle Spatial operations

Oracle Spatial mainly usesMetadata table and Spatial Data Field(Sdo_geometry field) and spatial index to manage spatial data, and provide a seriesSpatial Query and Spatial AnalysisOfProgramPackage, allowing users to develop more in-depth GIS applications. Oracle Spatial uses the space field sdo_geometry to store space data and uses metadata tables to manage spatial data tables with sdo_geometry fields, the R-tree and quad-tree indexes are used to speed up spatial queries and spatial analysis.

1. Metadata table description

Oracle spatial metadata table stores Spatial DataData Table Name, Spatial Field name, spatial data coordinate range, coordinate systemAndCoordinate Dimension description. You must use the metadata table to check whether Oracle Spatial spatial data exists in the Oracle database. PassMetadata View(User_sdo_geom_metadata. The basic definition of the metadata view is:

2. Spatial Field Parsing

The spatial data of Oracle Spatial is stored in the space field sdo_geometry. Understanding sdo_geometry is the key to compiling the Oracle Spatial program. Sdo_geometry is an object defined according to Open GIS specifications. Its original creation method is as follows.

① Sdo_gtype

Is a number-type value that defines the type of the stored geometric object. Sdo_gtype is an integer of four numbers in the format of dltt. D indicates the dimension of the geometric object, and l indicates the linear reference value in the three-dimensional linear reference system, this value must be set when D is 3 or 4 dimensions, which is generally null. Tt is the type of geometric object. Oracle Spatial defines seven types of geometric types. Currently, TT uses 00 to 07, where 08 to 99 is the number reserved by Oracle Spatial for future geometric object extension.

② Sdo_srid

Sdo_srid is also a number value used to identify the spatial coordinate system related to the geometric object. If sdo_srid is null, it indicates that no coordinate system is related to the geometric object. If this value is not null, it must be MDSYS. A value of the srid field in the cs_srs table. When creating a table containing a geometric object, this value must be added to the srid field in the user_sdo_geom_metadata view that describes the metadata of the spatial data table. Oracle Spatial specifies that all geometric objects in a geometric field must be of the same sdo_srid value for longpolling/latitude (8307), which we usually use international standards.

③ Sdo_point

Sdo_point is an object that contains 3D coordinate X, Y, and Z values. It is used to represent geometric objects with points of geometric type. If the sdo_elem_info and sdo_ordinates arrays are empty, X, Y, and Z in sdo_point are the coordinate values of the vertex object. Otherwise, the value of sdo_point is ignored (expressed as null ). Oracle Spatial strongly requires that the sdo_point storage space entity be the point type space data, which can greatly optimize the storage performance and query efficiency of Oracle spatial.

④ Sdo_elem_info

Sdo_elem_info is a variable-length array. Each three numbers are used as an element unit to indicate how coordinates are stored in the sdo_ordinates array. In this article, the three numbers that constitute an element are called 3 tuples. A triple contains the following three parts:

◇ Sdo_starting_offset

Sdo_starting_offset indicates the storage location of the first coordinate of each geometric element in the sdo_ordinates array. Its value starts from 1 and increases gradually.

◇ Sdo_etype

Sdo_etype represents the geometric type of each component element in a geometric object. When its values are 1, 2, 1003, and 2003, it indicates that this geometric element is a simple element. If sdo_etype is 1003, it indicates that the polygon is an outer ring (the first number is 1, indicating the outer ring), and the coordinate value is stored counterclockwise. If sdo_etype is 2003, it indicates that the polygon is the inner ring (the first number is 2, indicating the inner ring), and the coordinate value is stored clockwise. When sdo_etype is 4, 1005, and 2005, it indicates that this geometric element is a complex element. It contains at least one triple to show how many simple geometric elements the complex element has. Similarly, 1005 indicates that the polygon is the outer ring, and the coordinate value is stored counterclockwise. 2005 indicates that the polygon is the inner ring, and the coordinate value is stored clockwise.

◇ Sdo_interpretation

Sdo_interpretation has two meanings. The specific function is determined by whether sdo_etype is a complex element. If sdo_etype is a complex element (4, 1005, and 2005), sdo_interpretation indicates that several sub-3 tuples following it belong to this complex element. If sdo_etype is a simple element (1, 2, 1003, and 2003), sdo_interpretation indicates how the coordinates of the element are arranged in sdo_ordinates.

It should be noted that for a complex element, the child elements that constitute it are continuous, and the last vertex of a child element is the starting point of the next child element. The last coordinate of the last child element is either the coordinate corresponding to the value of sdo_starting_offset minus 1 of the next element, or the last coordinate of the entire sdo_ordinates array.

⑤ Sdo_ordinates

Sdo_ordinates is a variable-length array used to store the actual coordinates of geometric objects. It is an array with the maximum length of 1048576 and the type of number.

Sdo_ordinates must be used with the sdo_elem_info array for practical purposes. The coordinate storage method of sdo_ordinates is determined by the dimension of the geometric object. If the geometric object is two-dimensional, the coordinates of sdo_ordinates are {x1, Y1, X2, Y2 ,...} Ordered. If the geometric object is three-dimensional, the coordinates of sdo_ordinates are {x1, Y1, Z1, X2, Y2, Z2 ,...} .

3. Spatial Indexing Technology:

Oracle Spatial provides two indexing mechanisms: the r tree index and the quad tree index to speed up spatial queries and spatial analysis. Users need to create different indexes based on different spatial data types. When spatial data types are complex, improper selection of index types will slow Oracle spatial index creation.

3. Convert longitude and latitude into place names

At present, all types of location services lbs eventually return the longitude and latitude within the permitted range, such as GPS on-board terminals, Mobile Phone Positioning and other mobile devices. The system uses certain technologiesAlgorithmIt can be converted into a specific place name or nearby landmark. You can also return the information of the surrounding areas that the user cares about at the current location, such as hospitals, hotels, gas stations, and bus stops.

1. Import spatial data to Oracle Spatial

The current topic space database creation process includes technical design, data preparation, data acquisition, and data warehouse receiving. Data acquisition can often be achieved using existing GIS software such as geostar, MAPGIS, Supermap, ArcGIS, etc. The obtained data is uploaded to the Oracle database through a Spatial Data Engine (such as easyloader, oracle Spatial is used to store and manage spatial data. The mapora engine is a method for uploading spatial data to Oracle Spatial by programming the explicit format of MAPGIS.

2. Locating the entire service process

3. packages used for Oracle spatial association:

◆ Sdo_geom.relate (sdo_geometry1, 'mask', sod_geometry2, tolerance): used to judge the relationship between a ry and another ry, we are used to determine whether the current vertex is on a certain surface (province, county, county, and township.

◇ Sdo_geometry1 and sdo_geometry2 are geometric objects corresponding to spatial data.

◇ Tolerance: allowable precision range;

◇ Mask = anyinteract/contains/coveredby/covers/disjoint/

○ Anyinteract: sdo_geometry2 falls on the edge of sdo_geometry1.

○ Contains: sdo_geometry2 is completely included in the sdo_geometry1 geometric object, and the edges of the two geometric objects do not overlap.

○ Coveredby: sdo_geometry1 is completely contained in sdo_geometry2, and the edges of these two geometric objects overlap with one or more vertices.

○ Covers: sdo_geometry2 is completely contained in sdo_geometry1, and the edges of these two geometric objects overlap with each other.

○ Disjoint: The two geometries have no overlapping intersections or common edges.

○ Equal: The two geometries are equal.

○ Inside: sdo_geometry1 is completely contained in the sdo_geometry2 geometric object, and the edges of the two geometric objects do not overlap.

○ On: The sdo_geometry1 side and the internal line are completely on sdo_geometry2.

○ Overlapbdydisjoint: Two geometric objects overlap, but the edges do not overlap.

○ Overlapbdyintersect: Two geometric objects overlap, and some edges overlap.

○ Touch: Two geometric objects have common edges, but there is no cross.

◆ Sdo_nn (sdo_geometry1, sdo_geometry2, 'sdo _ num_res ', tolerance): returns other ry sets near the sdo_geometry2 of the ry.

◇ Sdo_geometry1 and sdo_geometry2 are geometric objects corresponding to spatial data.

◇ Tolerance: allowable precision range;

◇ Sdo_num_res = N: returns n ry, = 1 returns only one.

◆ Sdo_geom.within_distance (sdo_geometry1, distance, sdo_geometry2, tolerance, 'unit ') is used to determine what other ry sets exist near the specified distance of sdo_geometry2 to distance.

◇ Sdo_geometry1 and sdo_geometry2 are geometric objects corresponding to spatial data.

◇ Tolerance: allowable precision range;

◇ Distance: the specified distance;

◇ Unit: The unit used to indicate the distance. It may be the unit of unit = m/unit = km, but it must be one of the units listed in the sdo_dist_units table.

4. returned information:

The returned information can return all the information associated with the spatial information based on the business needs, and then combine the information into a paragraph in the form of a field theory, and return the information to the mobile terminal through SMS or other methods.

◆ It can be the location information of the current location, such as the place name, City Landmark, custom landmark, and road name;

◆ It can be the surrounding information of the current location: hospitals, hotels, gas stations, bus stations, etc;

Query: Select C. Geometry. sdo_gtype, C. Geometry. sdo_ordinates,
C. Geometry. sdo_elem_info, C. Geometry. sdo_srid, C. Geometry. sdo_point.x,
C. Geometry. sdo_point.y, C. Geometry. sdo_point.z from xz_chengshi C