{"id":1745,"date":"2017-09-20T15:02:34","date_gmt":"2017-09-20T13:02:34","guid":{"rendered":"http:\/\/62.131.51.129\/?p=1745"},"modified":"2017-09-20T15:02:34","modified_gmt":"2017-09-20T13:02:34","slug":"some-spatial-functions-in-oracle","status":"publish","type":"post","link":"http:\/\/archief.van-maanen.com\/?p=1745","title":{"rendered":"Some spatial functions in Oracle"},"content":{"rendered":"<p>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.<br \/>\nThese points can be shown in SQL Developer in the map viewer. We have a set of locations in Dutch speaking areas. <\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-1750\" src=\"http:\/\/62.131.51.129\/wp-content\/uploads\/2017\/09\/Untitled-4.png\" alt=\"\" width=\"498\" height=\"494\" \/><\/p>\n<p>This is already an interesting result. We are able to store and show the data in the database.<\/p>\n<p>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.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/62.131.51.129\/wp-content\/uploads\/2017\/09\/Untitled-5.png\" alt=\"\" width=\"481\" height=\"475\" class=\"alignnone size-full wp-image-1753\" \/><\/p>\n<p>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:<\/p>\n<pre>\nSELECT b.name, a.gemeentena\nFROM \"TOPGRENZEN_GEM_ACTUEEL\" a, COUNTRIES b\nWHERE sdo_filter (a.geometry, b.geometry) = 'TRUE';\n<\/pre>\n<p>In SQL Developer, this looks like:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/62.131.51.129\/wp-content\/uploads\/2017\/09\/Untitled-6.png\" alt=\"\" width=\"674\" height=\"279\" class=\"alignnone size-full wp-image-1756\" \/><\/p>\n<p>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: <\/p>\n<p>update  topgrenzen_prov_actueel a<br \/>\nset  a.geosimpel =  SDO_UTIL.SIMPLIFY(geometry,  1000,   100  );<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-1745","post","type-post","status-publish","format-standard","hentry","category-allgemein"],"_links":{"self":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/1745","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1745"}],"version-history":[{"count":0,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/1745\/revisions"}],"wp:attachment":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1745"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1745"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1745"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}