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:
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.)
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:
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:
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:
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
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
Comments
Post a Comment