Oracle-spatial Spatial Database Foundation

Because of the love of various editions of Hello World, the two Space database oracle-spatial,mysql version of the Hello World to tidy up.

First, Introduction
Oracle spatial is the space database component of Oracle Corporation, which gives Oracle the ability to process spatial data. From the 9i to the spatial data provides a relatively complete support. Oracle Spatial manages spatial data primarily through metadata tables, spatial attribute fields (Sdo_geometry), and spatial indexes (R-tree and four-fork tree indexes), and provides a series of spatial query and spatial analysis functions on this basis.

Two, the above does not have the use, means:
1. Set up a table (more than one sdo_geometry type of field), insert data, insert a meta Data View,
2. The Space attribute field is the mark that the space table distinguishes from other tables, the field type Sdo_geometry (can customize)
3. The spatial index can be understood to evolve from the normal table's B-tree index, used to improve the query speed, Quadtree cable (principle I understand to cut tofu), but the spatial object distribution is uneven, will be due to uneven speed, so the main use of R-Tree index, for these two indexes are available to be collated.
4. Function, part of the dependent spatial index is mainly used to do the query, the part is not dependent on the index, so when the data is large, we must first use the function of relying on the index to quickly narrow the calculation range, and then use the Space analysis function to calculate the desired results (just start with oracle-spatial With the online copying and pasting the great God's method, the data volume has a problem, and later by the school professional book content inspired, think of a way to solve the query irregular polygon point speed problem.

The sdo_geometry types defined by Oracle spatial are:

The CREATE type Sdo_geometry as OBJECT (
                    sdo_gtype number   ,  ///Front string is the field name; The following string is the field type
                     sdo_srid number    .
                     Sdo_point    Sdo_point_type,
                     sdo_elem_info    sdo_elem_info_array,
                     sdo_ordinates    sdo_ordinate_array);

Where the Sdo_geometry as object identifies the type as an object type. At first we can understand that it is the Geometry object in Arcobjects (the object in the shape field of the element is geometry), not how he is organized. As for the type of Sdo_point_type, Sdo_elem_info_array, Sdo_ordinate_array is also the type of Oracle spatial customization and Sdo_geometry is the same.

   Now a simple introduction to each parameter in the Sdo_geometry type:
    1, Sdo_gtype: Represents the geometry type to be stored, such as the dotted line. It is expressed by number type,
    2, Sdo_srid: The Space reference coordinate system of geometry, the type is also number
    3, Sdo_point: If the geometric type point type, is the storage point coordinates, otherwise is empty. Oracle Custom Sdo_point_type Type
    4, Sdo_elem_info: Define how to understand the coordinate string in Sdo_ordinates,
    5, Sdo_ordinates: Store the actual coordinates, with X, Y and different points are separated by commas;

Four, learning time to see the Internet is not copy paste, is the translation of the official website example, the actual use is still very inconvenient, the following directly said the actual operation:
–0. Check to see if the oracle-spatial is installed

sql> desc sdo_georaster;

Element         Type               
rastertype      number             
spatialextent   mdsys. Sdo_geometry 
rasterdatatable VARCHAR2 ()       
rasterid number             
METADATA        SYS. XmlType  

This is a loaded, not to install the words to be installed manually.

The online examples are for inserting a few test data, the company actually uses the batch to insert,
There are different ways to import different data formats:

After MapInfo is connected to Oracle, MapInfo files can be imported directly.
ArcGIS's *.shp file, you need to use SHP2SDO conversion.

Text file data import two ways:

1. The procedure connects the database one inserts (Java-jdbc,python needs to install cx_oracle module);
2. Use Sqlldr batch interpolation (but each piece of data needs to be spelled into a special format);

Background:
Text file;
A data sample (three fields id| longitude | latitude, ' | ') Separation): 1|115.98346012774272|39.7059960734836;
Data volume 20 million;

Processing data in advance, stitching results:

–1. Building a table

CREATE TABLE wyp_point_test (
ID number,
geoloc sdo_geometry)

–2.SQLLDR Import data (This step sqlldr control file and normal table control file is not the same, to the SDO_GEOMETRY structure, the network does not, the following is the point of example, lines and polygons to modify the control file and data format)
Control file Point.ctl

Load data
infile "/data1/all_signal_dev/wyp/allrsl.txt"
append
Continueif Next (1:1) = ' # ' into
table China_subway_point
Fields terminated by "|"
Trailing nullcols (
ID,
geoloc Column Object
(Sdo_gtype,
Sdo_srid,
sdo_point Column object (
x,y)))

–3. Update meta data after importing data try to

Insert INTO User_sdo_geom_metadata (Table_name,column_name
, Diminfo,srid) VALUES (
' wyp_point_test ',
' Geoloc ',
mdsys.sdo_dim_array (
mdsys. Sdo_dim_element (' x ', 70.000000000,140.000000000,0.000000050),
Mdsys. Sdo_dim_element (' y ', 0.000000000,60.000000000,0.000000050)),
8307--srid
);

–4. Create an index

Create INDEX Wyp_point_test_idx on wyp_point_test (geoloc)
Indextype is Mdsys.spatial_index

1 always insert meta data for space layer before creating index (update metadata view)
2 If the index you created previously failed, you must delete it before you can create

Drop index WYP_POINT_TEST_IDX;

During index creation, Oracle checks that the Srid in the Sdo_srid and user_sdo_geom_metadata of the indexed columns match, and if they do not match, Oracle generates ORA-13365 errors.
Spatial index information is available to view user_sdo_index_metadata or simpler user_sdo_index_info views.
The spatial index table is stored in this sdo_index_table field and always begins with MDRT. You can't treat a spatial index table like a normal table--that is, you can't move it from one tablespace to another, or delete it, copy it, and so on. Otherwise, an invalid spatial index appears and a subsequent space query operator or spatial index rebuild fails.
(R-Tree index, quadtree indexing principle, and index parameters)

–5. Functions of spatial query and spatial analysis
function, part of the dependent spatial index is mainly used to do the query, the part is not dependent on the index is the space analysis function, in fact, the function to studious, a look will be, is to prepare the data part of the online said very little, so the main part of the data organization, the function simply said

–sdo_within_distance query for objects within a 500-metre range

SELECT ID
from wyp_point_test A 
WHERE sdo_within_distance (A.geoloc, 
                           Mdsys. Sdo_geometry (2001, 
                                              8307, 
                                              Mdsys. Sdo_point_type (116.4601216738,39.9534043499, 0), 
                                              null, 
                                              null), 
                            ' distance= ' | | 500 | | ' unit=m ') = ' TRUE '

–sdo_filter, Sdo_geom. RELATE Query the point in the polygon

SELECT ID from Wyp_point_test S WHERE sdo_filter (S.geoloc,mdsys. Sdo_geometry (2003,8307,null, Mdsys. Sdo_elem_info_array (1,1003,1), Mdsys. Sdo_ordinate_array ( 113.6578611,34.74956944,113.6539528,34.75609722,113.6560056,34.75886667,113.6608,34.76098889,113.6660139,34.75729722,113.6 65475,34.75417222,113.66205,34.75011111,113.6578611,34.74956944)), ' querytype = WI Ndow ') = ' TRUE ' and Sdo_geom. RELATE (Mdsys. Sdo_geometry (2003,8307,null, Mdsys. Sdo_elem_info_array (1,1003,1), Mdsys. Sdo_ordinate_array ( 113.6578611,34.74956944,113.6539528,34.75609722,113.6560056,34.75886667,113.6608,34.76098889,113.6660139,34.75729722,113.6
                                      65475,34.75417222,113.66205,34.75011111,113.6578611,34.74956944)), ' Anyinteract ',
                       S.geoloc,               0.005) = ' TRUE ' 

6. Key points:

1. The above two examples are all used a temporary object, can also insert another table.
2.Oracle is limited to the length of the SQL, for example, the latitude and longitude of a polygon has more than 500 pairs, temporary object method is not, I think of the solution is, first the polygon into another space table, so that there is no limit on the length of SQL, there is a problem, the table can not be used insert into needs to use SQLLDR, because insert is also SQL ...
3.sdo_geom. Relate function is not dependent on the index, so the number of thousands of data after the traversal is very slow, plagued for a long time, I dream of the idea is, first through the index query, polygon minimum rectangle within the point, and then more deformation calculation, need to use the relate calculation of the amount of data.
4. Using sdo_within_distance can also query the surrounding and interior points of polygons, distance set to 0, is not the interior of the point, more than 0 is the surrounding and internal, but there is a detail, so it is best to query out the results of the point and polygon distance, because the actual use of the time found , using this method of polygon latitude and longitude, clockwise, the data out is wrong, the difference is 108,000.
52,000 points, the query speed is a second level, the usual use should be no problem.

7. Personal summary of the empty see query to improve performance methods (for reference only)

1. When indexing, indicate the object type (dot-line) in the spatial index
CREATE index cell_adj_all_idx on dim_m_cell_adj_all_spatial (geoloc)
Indextype is Mdsys.spatial_index
PARAMETERS (' layer_gtype=point ')  
index_name is the spatial index name, TABLE_NAME is the table name, Row_name is the column name
, Layer_gtype value In addition to point, but also for Line,polygon and so on.

2. Table partitioning for high performance
when the amount of data that needs to be stored and retrieved is large, in order to be easy to manage and improve the efficiency of spatial data retrieval, you can create partitions in this large data table and
then create spatial indexes on these partitioned tables, so that not only makes the tables manageable, Reduce the number of queries per query.  

3. Select a reasonable sequence of parameters for the space operator    
when executing an SQL statement that contains a space operator, Oracle chooses different execution plans according to the order of the parameters.
Therefore, a reasonable parameter order should be chosen according to the characteristics of the query object, so that Oracle spatial can choose better execution plan and
improve the efficiency of spatial query. However, not changing the order of each space operator can improve the efficiency of spatial query,
only changing the spatial operators that are sensitive to parameter order, such as sod_within_distance, can improve the efficiency of spatial query significantly.
The reasonable parameter order of space operators can improve the efficiency of spatial query, mainly lies in the spatial query mechanism of Oracle spatial.
Oracle Spatial uses a dual query mechanism for spatial queries, which is used for the main filtering and secondary filtering processes.
adjusting the parameter order of the space operator can make the winner's filtering stage more full use of spatial index, so as to improve the efficiency of spatial query and shorten the space query

Briefly introduce Sdo_geom.relate.

Sdo_geom.relate (Sdo_geometry1, ' MASK ', Sod_geometry2, tolerance):
used to determine the relationship between a geometry and another geometry, we use to determine whether the current point is in a certain plane (province surface, County market, township level) on. 
parameter Description: 
Sdo_geometry1,sdo_geometry2 is the geometrical object corresponding to spatial data. 
tolerance: allowable range of precision;    Mask parameter: The 
anyinteract:sdo_geometry2 falls on the sdo_geometry1 surface and is included on the edge. The 
Contains:sdo_geometry2 is completely contained in the Sdo_geometry1 geometry object, and the edges of the two geometric objects are not crossed. The 
Coveredby:sdo_geometry1 is fully contained in the Sdo_geometry2, and the sides of the two geometric objects have one or more points overlapping each other. The 
Covers:sdo_geometry2 is fully contained in the Sdo_geometry1, and the sides of the two geometric objects have one or more points overlapping each other. 
disjoint: Two geometries have no overlapping intersections and no common side. 
Equal: Two geometry is equal. The 
Inside:sdo_geometry1 is completely contained in the Sdo_geometry2 geometry object, and the edges of the two geometric objects are not crossed. The 
edges of the on:sdo_geometry1 and the inner lines are entirely on the sdo_geometry2. 
Overlapbdydisjoint: Two overlapping geometric objects, but no edges crossed. 
Overlapbdyintersect: Two geometric objects overlap, and the edges are partially crossed. Touch 
: Two geometric objects have a common edge, but no crossover.　　

MySQL recently used the space database, speed is also very fast, and constantly updated, after the opportunity to clean up the MySQL, PostGIS these spatial databases, as well as the complex polygon data structure, there are four-fork tree, R-tree index principle.