Oracle Create Sdo_geometry Table

Oracle Spatial is composed of a lump of object data types, type methods, operators, functions and processes. A geographic object is stored as a Sdo_geometry object in a field in the table. Spatial indexes are established and maintained by Common DDL and DML statements.
This chapter focuses on some examples of how to create, query, and index spatial data.

Simple Insert, index and query spatial data examples
This section demonstrates a very simple process of creating spatial tables, inserting, indexing, and querying data.
The scene is a soft-drink company that uses geographic information to represent the situation of their products (COLA) in various regions. These can be: market share, competitive pressure, growth potential and so on. The region can be a neighboring city, state, province, or country.
What we are going to do is:
1. Create a table (Cola_markets) to save spatial data
2. Insert data from four (cola_a, Cola_b, Cola_c, Cola_d) regions
3. Upgrade the User_sdo_geom_metadata view to the dimension information for these areas anyway
4. Building a spatial index (COLA_SPATIAL_IDX)
5. Make some spatial queries

[SQL]View PlainCopy

1. CREATE TABLE cola_markets (
2. mkt_id number PRIMARY KEY,
3. name VARCHAR2 (+),
4. Shape sdo_geometry);

[SQL]View PlainCopy

2. INSERT into cola_markets VALUES (
4. 1,
6. ' cola_a ',
8. Sdo_geometry (
10. 2003, --two-dimensional polygon
12. NULL,
14. NULL,
16. Sdo_elem_info_array (1,1003,3), --one rectangle (1003 = exterior)
18. Sdo_ordinate_array (5,7) --only 2 points needed to
20. --Define rectangle (lower left and upper right) with
22. --Cartesian-coordinate data
24. )
26. );
28. --The next and the INSERT statements create areas of interest for
30. --Cola B and Cola C. These areas is simple polygons (and not
32. --rectangles).
34. INSERT into cola_markets VALUES (
36. 2,
38. ' Cola_b ',
40. Sdo_geometry (
42. 2003, --two-dimensional polygon
44. NULL,
46. NULL,
48. Sdo_elem_info_array (1,1003,1), --one polygon (exterior polygon ring)
50. Sdo_ordinate_array (5,1, 8, 1, 8, 6, 5, 7, 5,1)
52. )
54. );
56. INSERT into cola_markets VALUES (
58. 3,
60. ' Cola_c ',
62. Sdo_geometry (
64. 2003, --two-dimensional polygon
66. NULL,
68. NULL,
70. Sdo_elem_info_array (1,1003,1), --one polygon (exterior polygon ring)
72. Sdo_ordinate_array (3,3, 6, 3, 6, 5, 4, 5, 3,3)
74. )
76. );
78. --now insert a area of interest for Cola D. This is a
80. --circle with a radius of 2. It is completely outside the
82. --first three areas of interest.
84. INSERT into cola_markets VALUES (
86. 4,
88. ' Cola_d ',
90. Sdo_geometry (
92. 2003, --two-dimensional polygon
94. NULL,
96. NULL,
98. Sdo_elem_info_array (1,1003,4), - -One circle
100. Sdo_ordinate_array (8,7, 10, 9, 8,11)
102. )
104. );

You need to write information about the spatial layer of the established table into a user_sdo_geom_metadata

[SQL]View PlainCopy

2. INSERT into user_sdo_geom_metadata
4. (TABLE_NAME,
6. COLUMN_NAME,
8. Diminfo,
10. SRID)
12. VALUES (
14. ' Cola_markets ',
16. ' shape ',
18. Sdo_dim_array ( --20X20 grid
20. Sdo_dim_element (' X ', 0, 0.005),
22. Sdo_dim_element (' Y ', 0, 0.005)
24. ),
26. NULL --SRID
28. );

[SQL]View PlainCopy

1. --SRID

User_sdo_geom_metadata after landing PL/SQL, users→mdsys→objects→views→user_sdo_geom_metadata

Open to view (I have inserted 2 records of the spatial table)

You can see this inserted record, click on any one of the red squares, and it will appear:

[SQL]View PlainCopy

1. Sdo_dim_array ( --20X20 grid
2. Sdo_dim_element (' X ', 0, 0.005),
3. Sdo_dim_element (' Y ', 0, 0.005)
4. ),

This is the meaning of SQL, where Sdo_dimname is a two-dimensional maximum and minimum value of the description, SDO_LB is the minimum value above x, Sdo_ub is the maximum value above X, sdo_tolerance refers to the size of the error, This means that data in 0.005 will default to one data.

The fourth step is to create a spatial index (COLA_SPATIAL_IDX)

[SQL]View PlainCopy

1. CREATE INDEX Cola_spatial_idx
2. On cola_markets (shape)
3. Indextype is Mdsys.  Spatial_index;

The purpose of indexing is to increase the query speed (in theory, you can not set up the index to query, but the actual basic cannot query out)

When the index is established, the following table is generated in tables:

This is an index table:

Can be found in the system's Index table.

The above is created to complete the Sdo_geometry table, you can start the spatial analysis.

Oracle Create Sdo_geometry Table