MySQL support for GIS & Oracle Spatial Database description

I had nothing to worry about this afternoon. I accidentally reviewed the MySQL manual. I used to check the content of the manual very well, the focus is on some basic details of SQL statements, MySQL performance parameters, and storage control management. However, today I accidentally discovered an important feature of MySQL, that is, MySQL also has the storage function for spatial data related to GIS, which suddenly aroused my interest, so I also focused on the relevant content of the manual. From the manual perspective, MySQL provided support for GIS objects very early, but I did not find it.

PostgreSQL is widely used in open-source GIS because of postgis extension. As another giant in open-source databases, MySQL has never abandoned the important database application field of GIS. MySQL has introduced a series of spatial extensions since mysql4.1, enabling it to have certain spatial processing capabilities.
MySQL complies with the OGC OpenGIS geometry model and supports the following spatial data objects:
Geometry (non-instantiable)

Point (instantiable)
Curve (non-instantiable)
Linestring (instantiable)
Line
Linearring
Surface (non-instantiable)
Polygon (instantiable)
Geometrycollection (instantiable)

Multipoint (instantiable)
Multicurve (non-instantiable)
Multilinestring (instantiable)
MultiSurface (non-instantiable)
Multipolygon (instantiable)

Wtk is the main way to import spatial data into MySQL spatial data tables. Wtb is a binary form of wtk and can also be identified by MySQL.

Create a MySQL space data table
Currently, only MyISAM engine data tables support the storage of geospatial data. Therefore, you must declare the data tables when creating them.

Create Database Geodatabase;
Use Geodatabase;
Create Table Test (
Id int primary key auto_increment,
Name varchar (128) not null,
PNT point,
Line linestring,
PGN Polygon
) Engine = MyISAM;

Insert a piece of space data using the following SQL

Insert into 'test' values (
Null,
'A test string ',
Pointfromtext ('point (15 20 )'),
Linestringfromtext ('linestring (0 0, 10 10, 20 25, 50 60 )'),
Polygonfromtext ('polygon (0 0, 10, 10, 0 10, 0 0), (5, 7, 7, 7, 5, 5 ))')
);

Here, you can also use the geomfromtext function to convert WKT to the internal geometric format of the database. The geomfromwkb function is used to convert WKB.

Use the following SQL statement to obtain spatial data from a data table:

Select ID, name, astext (PNT), astext (line), astext (PGN) from 'test ';

The functions of the astext function are the opposite of those of the geomfromtext function, that is, to convert data from an internal format to WKT; the corresponding asbinary function can be converted to WKB.

The built-in GIS functions of MySQL provide powerful spatial analysis capabilities for MySQL databases. As the fastest open source database, MySQL can be widely used in the GIS field.

I personally think that it is very meaningful to fully explore the features of MySQL in GIS space data storage through PHP scripts or C # program development with distinctive storage methods, if I have time, I will try again and write any progress on my blog.

 

 

I recently obtained some spatial data, so I found some knowledge about Oracle spatial databases. The following is a summary:

Oracle Spatial is a combination of object data types, type methods, operations, functions, and processes. A geographical object is saved in a field of the table as an sdo_geometry object. Spatial indexes are created and maintained by common DDL and DML statements.
Create a table:
Create Table cola_markets (
Mkt_id number primary key,
Name varchar2 (32 ),
Shape sdo_geometry );

Insert data:
Insert into cola_markets values (
1,
'Cola _ ',
Sdo_geometry (
2003, -- two-dimen1_polygon
Null,
Null,
Sdo_elem_info_array (1003, 3), -- one rectangle (= exterior)
Sdo_ordinate_array (1, 1, 5, 7) -- only 2 points needed
-- Define rectangle (lower left and upper right)
-- Cartesian-coordinate data
)
);
Insert into cola_markets values (
2,
'Cola _ B ',
Sdo_geometry (
2003, -- two-dimen1_polygon
Null,
Null,
Sdo_elem_info_array (, 1), -- one polygon (exterior polygon ring)
Sdo_ordinate_array (5, 1, 8, 1, 8, 6, 5, 5, 1)
)
);

Insert into cola_markets values (
3,
'Cola _ C ',
Sdo_geometry (
2003, -- two-dimen1_polygon
Null,
Null,
Sdo_elem_info_array (, 1), -- one polygon (exterior polygon ring)
Sdo_ordinate_array (3,3, 6,3, 6,5, 4,5, 3,3)
)
);

Insert into cola_markets values (
4,
'Cola _ d ',
Sdo_geometry (
2003, -- two-dimen1_polygon
Null,
Null,
Sdo_elem_info_array (1,1003, 4), -- one circle
Sdo_ordinate_array (8, 7, 10, 9, 8, 11)
)
);

Update view: user_sdo_geom_metadata
Insert into user_sdo_geom_metadata
(Table_name,
Column_name,
Diminfo,
SRID)
Values (
'Cola _ markets ',
'Shape ',
Sdo_dim_array (-- 20x20 Grid
Sdo_dim_element ('x', 0, 20, 0.005 ),
Sdo_dim_element ('y', 0, 20, 0.005)
),
Null -- SRID
);

Create a spatial index:
Create index cola_spatial_idx
On cola_markets (SHAPE)
Indextype is MDSYS. spatial_index;
-- Preceding statement created an R-tree index.

In this way, you can preview the spatial data information under mapguide.

Here are some of the most critical objects:

(Sdo_geometry object type)
In spatial, the description of a geographical object is placed in a separate field of Type sdo_geometry. For any table with this field, at least one other primary key field must be defined.

The sdo_geometry type defined by Oracle Spatial is:
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 );

Of course, spatial also defines sdo_point_type, sdo_elem_info_array, and
Sdo_ordinate_array type:
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;

Because the maximum sdo_ordinate_array value is 1048576, the number of vertex numbers of the sdo_geometry object depends on its dimension. The two-dimensional value is 524288, the three-dimensional value is 349525, And the four-dimensional value is only 262144 vertices.

Note:

For a given layer (with the same field), all geographical objects must be in the same dimension, and 2D and 3D data cannot be placed in one layer.

If you use four-digit sdo_etype, you also need to use four-digit sdo_gtype.

)

Sdo_geometry object type
2.1 sdo_gtype dltt
D: dimension
L: Linear referencing system (LRS)
TT: geometry type
00 unknown_geometry
01 point
02 line or curve
03 Polygon
04 collection
05 multipoint
06 multiline or multicurve
07 multipolygon
2.2 sdo_srid
Confirm the coordinate system. The value is the srid value in the sdo_coord_ref_sys table. This value is also inserted into the user_sdo_geom_metadata view.
2.3 sdo_point
(1) sdo_elem_info and sdo_ordinates are both null
(2) sdo_point attribute is non-null
Conclusion: coordinates are stored.
2.4 sdo_elem_info
It is used to explain the coordinate information stored in the sdo_ordinates attribute.
Sdo_starting_offset: the offset min in sdo_ordinates is 1.
Sdo_etype: 1, 2, 1003, and 2003 simple elements; 3 polygon ring; 4, 1005, and 2005 compound elements
Sdo_interpretation
2.5 sdo_ordinates
Long array, storing space object coordinates
2.6 usage considerations
Sdo_geom.validate_geometry_with_context is used to check the consistency of geometric objects.

1.1 sdo_geometry field details
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:

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 );
The object consists of five parts. The meanings of each part are shown in the following table:
Field name
Type
Description
 
Sdo_gtype
Number
Geometric object type
 
Sdo_srid
Number
Coordinate System of geometric objects
 
Sdo_point
Sdo_point_type
A geometric object that represents a point of geometric type.
 
 

Sdo_elem_info

Sdo_elem_info_array
It is a variable-length array. Each three numbers are used as an element unit to explain how coordinates are stored in sdo_ordinates.
 
 

Sdo_ordinates

Sdo_ordinate_array
Is a variable-length array used to store the real coordinates of geometric objects. The array type is number.
 

Table 1.1.1 meaning of each component of sdo_geometry
① 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. The following table describes the types of geometric objects supported by Oracle spatia1.
Numerical geometric type description
Dl00 is used to store custom type geometric objects
The dl01 point geometric object contains a point.
Dl02 linear or curve geometric objects consist of straight lines or curve segments
The dl03 polygon ry object contains a polygon, which can contain holes.
Dl04 composite shape set point, line, polygon superset, can contain all types
The dl05 composite point geometric object consists of one or more points.
Dl06 composite line or curve geometric object consists of one line or multiple lines
The dl07 composite polygon ry object can contain multiple outer rings and multiple non-Intersecting Polygon
Dl08-99 Oracle Spatial temporarily retained
Table 1.1.2 geometric object types supported by Oracle spatia1

② 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. The relationship between sdo_etype and sdo_interpretation is as follows:

Sdo_etype sdo_interpretation description
Any value of 0 is used for the custom type, which is not supported by Oracle spatial.

1 point type

1 n> 1 point set with N points

2 1 A line string composed of Line Segments

2 2 A line string composed of an arc segment. An arc segment consists of any point on the starting point, the arc, and the end point. The contacts of the adjacent two arc segments only need to be stored once.

1003
2003 1 a polygon composed of line segments. The start and end points must be the same

1003
2003 2 a polygon composed of arc segments. The start and end points must be the same. An arc segment consists of any point on the starting point, the arc, and the end point. The contacts of two adjacent arc segments only need to be stored once.

1003
2003 3 rectangle: the rectangle is determined by two points in the lower left corner and the upper right corner.

1003
2003 4 circle: composed of three points on the circumference

4 n> 1 A composite line composed of a straight line segment and an arc segment. N indicates the number of adjacent sub-elements of the composite line. The sdo_etype of the sub-element must be 2, the last point of a child element is the first point of the next child element, which cannot be repeated.

1005
2005 n> 1 A composite polygon composed of a straight line segment and an arc segment. N indicates the number of adjacent sub-elements of the composite line. The sdo_etype of the sub-element must be 2, the last point of a child element is the first point of the next child element, which cannot be repeated. The start and end points of a polygon must be the same.

Table 1.1.3 sdo_etype and sdo_interpretation

⑤ 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 ,...} .
Instance description
The following example further describes how to use the sdo_geometry object.

① A polygon with Holes

• Sdo_gtype = 2003, indicating that the geometric object is a two-dimensional polygon.
• Sdo_srid = NULL. Linear reference values are not required in two-dimensional scenarios.
• Sdo_point = NULL, indicating that the geometric object is not of the point type.
• Sdo_elem_info = (, 1, 1), which has two triple elements (, 1) and (, 1) "1" indicates that the child element starts from 1st values in the sdo_ordinates array, "1003" indicates that the element is a polygon outer ring, and "1" indicates that the polygon is composed of straight lines; in (15th, 2003, 1), "19" indicates that the starting point of the child element is values in the sdo_ordinates array, and "" indicates that the element is the inner ring of the polygon, "1" indicates that the polygon is composed of straight lines.
• Sdo_ordinates =,
7, 5, 7, 10, 10, 10, 5, 7, 5 ). The outer ring coordinates are arranged counterclockwise, and the inner ring coordinates are arranged clockwise.

② A compound line string composed of a straight line segment and an Arc Segment

• Sdo_gtype = 2002, indicating that the geometric object is a two-dimensional linestring.
• Sdo_srid = NULL. Linear reference values are not required in two-dimensional scenarios.
• Sdo_point = NULL, indicating that the geometric object is not of the point type.
• Sdo_elem_info = (, 2, 1, 2), which has three triplet elements (, 2) (, 1) and (, 2 ), in the order of order, (, 2) indicates that the line string is a composite line string, which consists of the child elements described by the following three tuples; in (, 1, "1" indicates that the child element starts from 1st values in the sdo_ordinates array, "2" indicates that the element is a line, and "1" indicates that the line is composed of a line. In (3, 2, 2), "3" indicates that the starting point of the child element is the 3rd values in the sdo_ordinates array, and "2" indicates that the element is a line, "2" indicates that this line is composed of arc segments.
• Sdo_ordinates = (10, 10, 10, 14, 6, 10,), coordinate (10, 14) is the connection point between the line segment and the Arc Segment, without repeated storage.

③ Compound polygon composed of a straight line segment and an Arc Segment
• Sdo_gtype = 2003, indicating that the geometric object is a two-dimensional polygon.
• Sdo_srid = NULL. Linear reference values are not required in two-dimensional scenarios.
• Sdo_point = NULL, indicating that the geometric object is not of the point type.
• Sdo_elem_info = (, 2, 1, 2), which has three triple elements (, 2) (, 1) and (, 2 ), in the order of order, (, 2) indicates that the polygon is a composite polygon, which consists of the child elements described by the following three tuples. In (, 1, "1" indicates that the child element starts from 1st values in the sdo_ordinates array, "2" indicates that the element is a line, and "1" indicates that the line is composed of a line. In (, 2, 2), "5" indicates that the starting point of the child element is the 5th values in the sdo_ordinates array, and "2" indicates that the element is a line, "2" indicates that this line is composed of arc segments.
• Sdo_ordinates = (6, 10, 1, 14, 10, 10, 14, 6, 10), coordinate () is the connection point between the line segment and the Arc Segment, without repeated storage.
In Oracle Spatial, you can use SQL statements to perform various operations on geometric data, such:
Create an oralce database named data1:
Create Table data1 (mktid integer, // target number
Name char (20), // target name
Shape sdo_geometry // spatial data of the target
);
Insert the composite polygon in the preceding example into the database data1:
Insert into datal values (1, // No.
'Composite polyg', // name
MDSYS. sdo_geome1ry (2003, null, null, // Spatial Data
MDSYS. sdo_elem _ info_array (1, 1005, 2, 1, 2, 1, 5, 2, 2 ),
MDSYS. sdo_ordinates_array (6, 10, 10, 1, 14, 10, 14, 6, 10)
);