Tuesday, October 11, 2016

OTN Appreciation Day : GeoJSON and SDO_GEOMETRY marriage in Oracle 12.2

The native JSON functionality in Oracle Database version 12.2 has evolved quite a bit since the JSON functions first appeared in Oracle Database version 12.1.0.2. Just one example is JSON_TABLE can be used as a bridge between the open standard GeoJSON format and the database internal SDO_GEOMETRY format, making it very simple to use externally available geocoding datasources within spatial applications in the database.

You have been able to do this with version 12.2, since it became available at OpenWorld 2016, at least if you have an Exadata Express account in the Oracle Cloud (if you want it on premise, a little more patience is needed ;-)

But even though only Exadata Express users can use this so far, I like when such features appear in the database. It would be possible to do this yourself, but natively supported it just becomes a lot easier and someone has already thought of boundary conditions and potential errors and so forth. As I like a "thick database approach" (search for tweets with hashtag #ThickDB) I welcome any feature that makes it easier to create as much of the application as possible (and reasonable) inside the database.


Today, October 11th 2016, is OTN Appreciation Day. Oracle Technology Network supports the community a lot, not just via the OTN website, but also by supporting other ways of spreading knowledge to all Oracle users. One example being the Oracle ACE program where OTN supports active members of the community in their efforts to help their peers through blogging, videos, presenting at conferences, and much more.

I am honoured to be part of the ACE program and dedicate this blog post to OTN Appreciation Day, where a lot of bloggers will blog on their favorite features (search for tweets with
hashtag #ThanksOTN )


So, on to the topic of GeoJSON. To demonstrate the functionality, I'll let the database call out to OpenStreetMap webservices, so I need to setup a Network Access Control List (ACL) as a DBA or other privileged user:

begin
   dbms_network_acl_admin.create_acl(
      acl         => 'geocoding.xml'
    , description => 'ACL for geocoding with openstreetmap'
    , principal   => 'SCOTT'
    , is_grant    => true
    , privilege   => 'connect'
   );
   dbms_network_acl_admin.assign_acl(
      acl  => 'geocoding.xml'
    , host => '*.openstreetmap.org'
   );
   commit;
end;
/

With the ACL in place (and perhaps a rule setup in my firewall in case it restricts outward bound traffic), I can use HttpUriType to query OpenStreetMap for Steuben County (30 years ago I lived a year in Wayland in Steuben County, New York.)

set define off
set long 10000
set linesize 80
set pagesize 1000

select httpuritype(utl_url.escape(
   'http://nominatim.openstreetmap.org/search?'
|| 'format=json&q=steuben county&polygon_geojson=1'
)).getclob() georesult
  from dual
/

GEORESULT
--------------------------------------------------------------------------------
[{"place_id":"159302604","licence":"Data A? OpenStreetMap contributors, ODbL 1.0
. http:\/\/www.openstreetmap.org\/copyright","osm_type":"relation","osm_id":"183
7996","boundingbox":["41.9987515","42.580413","-77.749722","-76.9653699"],"lat":
"42.2359045","lon":"-77.3750861","display_name":"Steuben County, New York, Unite
d States of America","class":"boundary","type":"administrative","importance":0.6
4614043673213,"icon":"http:\/\/nominatim.openstreetmap.org\/images\/mapicons\/po
i_boundary_administrative.p.20.png","geojson":{"type":"Polygon","coordinates":[[
[-77.749722,41.9987615],[-77.7433019,42.1538996],[-77.7421952,42.1784621],[-77.7
415482,42.1921254],[-77.7380125,42.2574154],[-77.7377043,42.2610839],[-77.736035
8,42.2974185],[-77.7354543,42.3058941],[-77.734575,42.3276019],[-77.7330843,42.3
51736],[-77.7322496,42.3749387],[-77.7309524,42.4039136],[-77.7260671,42.4037603
],[-77.7259289,42.405819],[-77.7229423,42.4709527],[-77.7206271,42.5350437],[-77
.6963102,42.5344038],[-77.6962745,42.5352055],[-77.6612963,42.5349797],[-77.6600
197,42.5804102],[-77.6598549,42.580409],[-77.6577899,42.580413],[-77.6577639,42.
580413],[-77.6543339,42.580395],[-77.6542619,42.580394],[-77.6537859,42.580392],
[-77.6525019,42.580385],[-77.6519599,42.580382],[-77.6508469,42.580369],[-77.643
3649,42.580326],[-77.6404889,42.5803],[-77.6398269,42.580302],[-77.6391549,42.58
0303],[-77.6387069,42.580295],[-77.6379022,42.5803017],[-77.6374999,42.580305],[
-77.6368039,42.580272],[-77.6364219,42.580272],[-77.6358519,42.580266],[-77.6353
679,42.580278],[-77.6351039,42.580297],[-77.6348229,42.580297],[-77.6344489,42.5
80278],[-77.6339389,42.580266],[-77.6335799,42.580267],[-77.6317769,42.580274],[
-77.6295699,42.580252],[-77.6277589,42.580211],[-77.6263869,42.580185],[-77.6247
899,42.580176],[-77.6245199,42.58016],[-77.6242689,42.580161],[-77.6227409,42.58
0138],[-77.6224239,42.580123],[-77.6153548,42.5800525],[-77.5704495,42.5790334],
[-77.4903155,42.577276],[-77.4580149,42.576942],[-77.4056849,42.576355],[-77.366
499,42.5766048],[-77.3421479,42.575905],[-77.2786249,42.575878],[-77.1437449,42.
576931],[-77.1441879,42.573669],[-77.1490939,42.557343],[-77.1490169,42.54126],[
-77.1461559,42.531265],[-77.1487349,42.517914],[-77.1499629,42.508575],[-77.1496
889,42.502048],[-77.1491849,42.501041],[-77.1491549,42.499866],[-77.1478649,42.4
98066],[-77.1440729,42.495384],[-77.1396869,42.49408],[-77.1360699,42.494328],[-
77.1292039,42.496319],[-77.1196059,42.502522],[-77.1146619,42.50782],[-77.107779
9,42.505737],[-77.1071169,42.485153],[-77.1071319,42.48494],[-77.1071009,42.4835
74],[-77.1070249,42.480396],[-77.1047359,42.408169],[-77.1013409,42.314602],[-77
.0997919,42.274181],[-77.0970252,42.2735443],[-77.0960459,42.273319],[-77.086142
9,42.273895],[-77.0744169,42.27631],[-76.9653699,42.280312],[-76.9657359,42.1208
53],[-76.9658286,42.0289881],[-76.9656968,42.0010749],[-76.9853847,42.000929],[-
77.0048699,42.0006575],[-77.024361,42.0004088],[-77.0310247,42.0002896],[-77.044
0988,42.0002493],[-77.0636124,42.0001285],[-77.0832199,42.000038],[-77.1027561,4
1.999818],[-77.1146377,41.9997701],[-77.122466,41.9997386],[-77.1416057,41.99968
8],[-77.1612727,41.9996402],[-77.1809655,41.9995549],[-77.200404,41.9997582],[-7
7.219619,41.999713],[-77.2395879,41.9996238],[-77.2595654,41.9996495],[-77.27956
4,41.9996753],[-77.2911738,41.9996902],[-77.2995839,41.9997059],[-77.3298807,41.
9997623],[-77.334753,41.9997808],[-77.3547514,41.9998567],[-77.3747755,41.999932
7],[-77.3945433,41.9999647],[-77.4142957,42.0000268],[-77.4333748,42.000024],[-7
7.4530075,42.0000324],[-77.4722475,42.0000172],[-77.4757498,42.0000154],[-77.492
6344,42.0000065],[-77.5120402,41.9999244],[-77.5316058,41.9998243],[-77.5513077,
41.9996895],[-77.5710783,41.9995109],[-77.5907829,41.9993327],[-77.6097262,41.99
93308],[-77.6288584,41.9994806],[-77.6483307,41.999542],[-77.6679382,41.9992737]
,[-77.688124,41.999086],[-77.7077599,41.998891],[-77.7274843,41.9987958],[-77.74
71514,41.9987515],[-77.749722,41.9987615]]]}},{"place_id":"159491135","licence":
"Data A? OpenStreetMap contributors, ODbL 1.0. http:\/\/www.openstreetmap.org\/c
opyright","osm_type":"relation","osm_id":"1802089","boundingbox":["41.527561","4
1.760216","-85.1974664","-84.8047289"],"lat":"41.6414101","lon":"-85.0023641","d
isplay_name":"Steuben County, Indiana, United States of America","class":"bounda
ry","type":"administrative","importance":0.593977856445,"icon":"http:\/\/nominat
im.openstreetmap.org\/images\/mapicons\/poi_boundary_administrative.p.20.png","g
eojson":{"type":"Polygon","coordinates":[[[-85.1974664,41.7597479],[-85.1722299,
41.759618],[-85.1231019,41.759743],[-85.1172669,41.7597],[-85.1061024,41.7597409
],[-85.0838077,41.7598225],[-85.0783399,41.7598425],[-85.0394359,41.759985],[-85
.0378169,41.759801],[-85.0198659,41.7596809],[-84.9969088,41.7595273],[-84.99627
87,41.7595231],[-84.9728029,41.759366],[-84.9715509,41.759527],[-84.9615619,41.7
59552],[-84.9608599,41.759438],[-84.9324839,41.759691],[-84.8602888,41.7598922],
[-84.8242406,41.7600269],[-84.8188729,41.760059],[-84.8058829,41.760216],[-84.80
61339,41.743115],[-84.8060739,41.737603],[-84.8060649,41.732909],[-84.8060419,41
.720544],[-84.8060179,41.707485],[-84.806045,41.7026524],[-84.805973,41.6961536]
,[-84.8062099,41.67455],[-84.8059327,41.6527609],[-84.8056729,41.632342],[-84.80
56959,41.631398],[-84.8057134,41.6286333],[-84.8057148,41.6284143],[-84.8057732,
41.6191619],[-84.8058119,41.61304],[-84.8054473,41.5851618],[-84.8048796,41.5417
547],[-84.8047289,41.530231],[-84.8554459,41.530018],[-84.9689639,41.528698],[-8
5.0818559,41.528179],[-85.1947329,41.527561],[-85.1955949,41.613369],[-85.196410
9,41.701908],[-85.1974664,41.7597479]]]}}]

Well, I got a result but even though that JSON is readable, it is not that easy for the human eye to pick out the interesting parts.

JSON_TABLE to the rescue:

select geo.*
  from json_table(
          httpuritype(utl_url.escape(
             'http://nominatim.openstreetmap.org/search?'
          || 'format=json&q=steuben county&polygon_geojson=1'
          )).getclob() format json
        , '$[*]'
          columns (
             display_name varchar2(80) path '$.display_name'
           , class        varchar2(20) path '$.class'
           , type         varchar2(20) path '$.type'
           , lat          number       path '$.lat'
           , lon          number       path '$.lon'
          )
       ) geo
/

DISPLAY_NAME
--------------------------------------------------------------------------------
CLASS                TYPE                        LAT        LON
-------------------- -------------------- ---------- ----------
Steuben County, New York, United States of America
boundary             administrative       42.2359045 -77.375086

Steuben County, Indiana, United States of America
boundary             administrative       41.6414101 -85.002364

That's more like it, now I can actually read the results and discover that not only New York state but also Indiana has a Steuben County. And I've got latitude and longitude where the two counties are.

But there is nothing new in this. So far I've only used functionality that was available in version 12.1.0.2. Now let us add one line of brand new 12.2 functionality:

select geo.*
  from json_table(
          httpuritype(utl_url.escape(
             'http://nominatim.openstreetmap.org/search?'
          || 'format=json&q=steuben county&polygon_geojson=1'
          )).getclob() format json
        , '$[*]'
          columns (
             display_name varchar2(80) path '$.display_name'
           , class        varchar2(20) path '$.class'
           , type         varchar2(20) path '$.type'
           , lat          number       path '$.lat'
           , lon          number       path '$.lon'
           , geojson      sdo_geometry path '$.geojson'
          )
       ) geo
/

DISPLAY_NAME
--------------------------------------------------------------------------------
CLASS                TYPE                        LAT        LON
-------------------- -------------------- ---------- ----------
GEOJSON(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
Steuben County, New York, United States of America
boundary             administrative       42.2359045 -77.375086
SDO_GEOMETRY(2003, 4326, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(-77.749722, 41.9987615, -77.743302, 42.1538996, -77.742195, 42.1784621, -77.7
41548, 42.1921254, -77.738013, 42.2574154, -77.737704, 42.2610839, -77.736036, 4
2.2974185, -77.735454, 42.3058941, -77.734575, 42.3276019, -77.733084, 42.351736
, -77.73225, 42.3749387, -77.730952, 42.4039136, -77.726067, 42.4037603, -77.725
929, 42.405819, -77.722942, 42.4709527, -77.720627, 42.5350437, -77.69631, 42.53
44038, -77.696275, 42.5352055, -77.661296, 42.5349797, -77.66002, 42.5804102, -7
7.659855, 42.580409, -77.65779, 42.580413, -77.657764, 42.580413, -77.654334, 42
.580395, -77.654262, 42.580394, -77.653786, 42.580392, -77.652502, 42.580385, -7
7.65196, 42.580382, -77.650847, 42.580369, -77.643365, 42.580326, -77.640489, 42
.5803, -77.639827, 42.580302, -77.639155, 42.580303, -77.638707, 42.580295, -77.
637902, 42.5803017, -77.6375, 42.580305, -77.636804, 42.580272, -77.636422, 42.5
80272, -77.635852, 42.580266, -77.635368, 42.580278, -77.635104, 42.580297, -77.
634823, 42.580297, -77.634449, 42.580278, -77.633939, 42.580266, -77.63358, 42.5
80267, -77.631777, 42.580274, -77.62957, 42.580252, -77.627759, 42.580211, -77.6
26387, 42.580185, -77.62479, 42.580176, -77.62452, 42.58016, -77.624269, 42.5801
61, -77.622741, 42.580138, -77.622424, 42.580123, -77.615355, 42.5800525, -77.57
045, 42.5790334, -77.490316, 42.577276, -77.458015, 42.576942, -77.405685, 42.57
6355, -77.366499, 42.5766048, -77.342148, 42.575905, -77.278625, 42.575878, -77.
143745, 42.576931, -77.144188, 42.573669, -77.149094, 42.557343, -77.149017, 42.
54126, -77.146156, 42.531265, -77.148735, 42.517914, -77.149963, 42.508575, -77.
149689, 42.502048, -77.149185, 42.501041, -77.149155, 42.499866, -77.147865, 42.
498066, -77.144073, 42.495384, -77.139687, 42.49408, -77.13607, 42.494328, -77.1
29204, 42.496319, -77.119606, 42.502522, -77.114662, 42.50782, -77.10778, 42.505
737, -77.107117, 42.485153, -77.107132, 42.48494, -77.107101, 42.483574, -77.107
025, 42.480396, -77.104736, 42.408169, -77.101341, 42.314602, -77.099792, 42.274
181, -77.097025, 42.2735443, -77.096046, 42.273319, -77.086143, 42.273895, -77.0
74417, 42.27631, -76.96537, 42.280312, -76.965736, 42.120853, -76.965829, 42.028
9881, -76.965697, 42.0010749, -76.985385, 42.000929, -77.00487, 42.0006575, -77.
024361, 42.0004088, -77.031025, 42.0002896, -77.044099, 42.0002493, -77.063612,
42.0001285, -77.08322, 42.000038, -77.102756, 41.999818, -77.114638, 41.9997701,
 -77.122466, 41.9997386, -77.141606, 41.999688, -77.161273, 41.9996402, -77.1809
66, 41.9995549, -77.200404, 41.9997582, -77.219619, 41.999713, -77.239588, 41.99
96238, -77.259565, 41.9996495, -77.279564, 41.9996753, -77.291174, 41.9996902, -
77.299584, 41.9997059, -77.329881, 41.9997623, -77.334753, 41.9997808, -77.35475
1, 41.9998567, -77.374776, 41.9999327, -77.394543, 41.9999647, -77.414296, 42.00
00268, -77.433375, 42.000024, -77.453008, 42.0000324, -77.472248, 42.0000172, -7
7.47575, 42.0000154, -77.492634, 42.0000065, -77.51204, 41.9999244, -77.531606,
41.9998243, -77.551308, 41.9996895, -77.571078, 41.9995109, -77.590783, 41.99933
27, -77.609726, 41.9993308, -77.628858, 41.9994806, -77.648331, 41.999542, -77.6
67938, 41.9992737, -77.688124, 41.999086, -77.70776, 41.998891, -77.727484, 41.9
987958, -77.747151, 41.9987515, -77.749722, 41.9987615))

Steuben County, Indiana, United States of America
boundary             administrative       41.6414101 -85.002364
SDO_GEOMETRY(2003, 4326, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(-85.197466, 41.7597479, -85.17223, 41.759618, -85.123102, 41.759743, -85.1172
67, 41.7597, -85.106102, 41.7597409, -85.083808, 41.7598225, -85.07834, 41.75984
25, -85.039436, 41.759985, -85.037817, 41.759801, -85.019866, 41.7596809, -84.99
6909, 41.7595273, -84.996279, 41.7595231, -84.972803, 41.759366, -84.971551, 41.
759527, -84.961562, 41.759552, -84.96086, 41.759438, -84.932484, 41.759691, -84.
860289, 41.7598922, -84.824241, 41.7600269, -84.818873, 41.760059, -84.805883, 4
1.760216, -84.806134, 41.743115, -84.806074, 41.737603, -84.806065, 41.732909, -
84.806042, 41.720544, -84.806018, 41.707485, -84.806045, 41.7026524, -84.805973,
 41.6961536, -84.80621, 41.67455, -84.805933, 41.6527609, -84.805673, 41.632342,
 -84.805696, 41.631398, -84.805713, 41.6286333, -84.805715, 41.6284143, -84.8057
73, 41.6191619, -84.805812, 41.61304, -84.805447, 41.5851618, -84.80488, 41.5417
547, -84.804729, 41.530231, -84.855446, 41.530018, -84.968964, 41.528698, -85.08
1856, 41.528179, -85.194733, 41.527561, -85.195595, 41.613369, -85.196411, 41.70
1908, -85.197466, 41.7597479))

In the call to OpenStreetMap parameter polygon_geojson=1 tells the webservice I'd like a GeoJSON object returned as part of the JSON call result. In 12.2 such a GeoJSON object can be natively read as a database SDO_GEOMETRY datatype.

Just viewing the raw SDO_GEOMETRY objects like in the above output is maybe not that useful, but since it is now an SDO_GEOMETRY type rather than simply JSON text, I can use all the spatial functionality available. For example calculate area:

select geo.display_name
     , geo.class
     , geo.lat
     , geo.lon
     , sdo_geom.sdo_area(geo.geojson, 0.005, 'units=SQ_KM') sq_km
  from json_table(
          httpuritype(utl_url.escape(
             'http://nominatim.openstreetmap.org/search?'
          || 'format=json&q=steuben county&polygon_geojson=1'
          )).getclob() format json
        , '$[*]'
          columns (
             display_name varchar2(80) path '$.display_name'
           , class        varchar2(20) path '$.class'
           , lat          number       path '$.lat'
           , lon          number       path '$.lon'
           , geojson      sdo_geometry path '$.geojson'
          )
       ) geo
/

DISPLAY_NAME
--------------------------------------------------------------------------------
CLASS                       LAT        LON      SQ_KM
-------------------- ---------- ---------- ----------
Steuben County, New York, United States of America
boundary             42.2359045 -77.375086 3638.36625

Steuben County, Indiana, United States of America
boundary             41.6414101 -85.002364 834.001286

Ah, nice - "my" Steuben County is more than 4 times larger than the one in Indiana ;-)

This feature may be small in itself, but it is an example of how a relatively small thing suddenly can open up for a lot of rich functionality. The spatial capabilities of the database are numerous (some can be used within the basic licence, some you need to buy an extra option - check license documentation if in doubt) and this little feature opens up for using all those capabilities on any geo data that supports the GeoJSON open standard. You can use the spatial calculations inside the database not just for area like shown, but also for querying with spatial indexes and a lot more. Or you can use that APEX applications also support spatial datatypes.

So this was a single example, but it represents the "feature" I like most about the database - that it has so much built-in, that even relatively small feature enhancements often end up offering expansions to what is already built-in and thus become useful in much wider perspectives.

Happy OTN Appreciation Day. #ThanksOTN

No comments:

Post a Comment