Some spatial functions in Oracle

Oracle has a wonderful spatial module. The spatial data are stored in a database as so-called SDO_GEOMETRY data type. The data in such field look like: (MDSYS.SDO_GEOMETRY(2001,90112,MDSYS.SDO_POINT_TYPE(121235,484926,NULL),NULL,NULL)). In this structure, we see the so-called SRID. This SRID is here 90112. This indicates how a geographical location can be translated in coordinates. In this case we need two coordinates to store one point. In this case, it is (121235,484926) that we need.
These points can be shown in SQL Developer in the map viewer. We have a set of locations in Dutch speaking areas.

This is already an interesting result. We are able to store and show the data in the database.

For areas, we have a so-called polygon. This is a set of points that show a polygon on a map. The start of such polygon may look like: MDSYS.SDO_GEOMETRY(2003,28992,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(100455.11,505440.83,100409.798,505244.882. This can also be shown in next diagramme.

We can also make calculations on spatial data. Let me provide two examples. One example is a comparison where it is stated if one spatial object is located in another object. The question is whether objects in one table (TOPGRENZEN_GEM_ACTUEEL) is stored in objects in another table (COUNTRIES). Let me provide an example:

SELECT, a.gemeentena
WHERE sdo_filter (a.geometry, b.geometry) = 'TRUE';

In SQL Developer, this looks like:

A final statement that might be useful. In some cases, we have polygons that are far too large: too many points. A solution is to simplify the polygon with:

update topgrenzen_prov_actueel a
set a.geosimpel = SDO_UTIL.SIMPLIFY(geometry, 1000, 100 );