Optic Geospatial
Released in MarkLogic 11
Geospatial features in MarkLogic are not new. They have been around since the start of MarkLogic 9.0. However, they have been limited to the MarkLogic CTS related APIs. With the release of MarkLogic 11, the Optic API now supports geospatial features. This article will explore the use of the Optic API for geospatial features.
What did MarkLogic have for Geospatial features prior to MarkLogic 11?
The geospatial implementation started like some other new features - a new index type and appropriate tooling to support it. The geospatial index supports many types of spatial features such as complex polygons, points, lines, and circles. In MarkLogic prior to 11, like all indexes, they are scoped to a fragment. For most use-cases, this translates to a document. You could easily add many geospatial features into a single document. These will index fine and are searchable using various geospatial related search functions. However, to find out "which" feature in your document matched requires post-processing (filtering) in some manner or other. This also leads to double-querying (post-process to then re-match the correct version). This really limited the use-cases in which one might want or need more than one geospatial feature in a single document.
In Summary - Document Oriented:
- Multiple geospatial artifacts allowed and indexed
- But “Which one matched” takes work:
- Searchable expression(filtered) - or walk the document yourself looking for the right one
- Fragment root/parent
What's the difference in MarkLogic 11?
Instead of Document Oriented, TDE Driven
- As many records as possible from a fragment of a single document
- Indexed and aware of self - but also fragment(document)
To start off, all the power of MarkLogic is still there. You can still index many geospatial features in a single document. However, in normal MarkLogic practice, this foundation has been extended. When we dove into the details of the release, the feeling was that the title "Optic Geospatial" was a bit reserved in describing the power of the release. The update actually has multiple hidden gems:
The heart of the release is the first bullet point extract geospatial features embedded into a document as a row. This means that you can now query for a feature and get single results per feature.
- Indexes available via TDE (rows, triples)
- (point , box , circle , linestring , polygon , complexPolygon , and a generic region)
- GEO library available via Optic API (just like others such as ofn, oxdmp, oxs etc)
- SQL and SPARQL also support geospatial features extracted from the TDE templates
To further prove the point of how versatile the release is, the samples later on are mixed between SQL and Optic.
Optic API is available in both Javascript and xQuery
This review is in Javascript. However, of the current release, the Optic API Geospatial features are also available via xQuery.
Exploring the Optic API Geospatial Features
Llamaverse
With much of our testing, we try to stick with a single, familiar set of data. We then just extend the dataset as needed. For this purpose, I am using the Llamaverse geospatial data. This is familiar to those who have read the articles on GraphQL. Those examples answered questions based on relationships. We'll answer questions based on geospatial data.
{
type: "FeatureCollection",
creator: "CleverLlamas",
name: "geospatial map",
features: [
{
type: "Feature",
properties: {
id: 1,
name: "background",
type: "boundary",
},
geometry: {
type: "Polygon",
coordinates: [
[
[-121.977579, 37.377548],
[-121.977579, 37.375048],
[-121.972902, 37.375048],
[-121.972902, 37.377548],
[-121.977579, 37.377548],
],
],
},
},
{
type: "Feature",
properties: {
id: 1,
name: "Blackwater Creek",
type: "water",
},
geometry: {
coordinates: [
[
[-121.977579, 37.375813],
[-121.977579, 37.375738],
[-121.977563, 37.37574],
[-121.977522, 37.375744],
[-121.977484, 37.375746],
[-121.977456, 37.375747],
[-121.977426, 37.375748],
[-121.977394, 37.375747],
[-121.97736, 37.375745],
[-121.977324, 37.375742],
[-121.977287, 37.375738],
[-121.977249, 37.375732],
[-121.977209, 37.375725],
[-121.977169, 37.375715],
[-121.977128, 37.375704],
[-121.977086, 37.37569],
[-121.977065, 37.375683],
[-121.977038, 37.375672],
[-121.976987, 37.375651],
[-121.97694, 37.37563],
[-121.976897, 37.375608],
[-121.976856, 37.375587],
[-121.976817, 37.375565],
[-121.976764, 37.375533],
[-121.976696, 37.375491],
[-121.976648, 37.375462],
[-121.976616, 37.375444],
[-121.976583, 37.375427],
[-121.976549, 37.375412],
[-121.976514, 37.375397],
[-121.976478, 37.375385],
[-121.976459, 37.375379],
[-121.976448, 37.375376],
[-121.976424, 37.37537],
[-121.976397, 37.375365],
[-121.976367, 37.375361],
[-121.976318, 37.375354],
[-121.976242, 37.375348],
[-121.976201, 37.375345],
[-121.976157, 37.375342],
[-121.976063, 37.375338],
[-121.975963, 37.375336],
[-121.975858, 37.375337],
[-121.975804, 37.375338],
[-121.97575, 37.37534],
[-121.975667, 37.375343],
[-121.975612, 37.375347],
[-121.975556, 37.375351],
[-121.975501, 37.375355],
[-121.975446, 37.375361],
[-121.975392, 37.375367],
[-121.975339, 37.375375],
[-121.975286, 37.375383],
[-121.975234, 37.375392],
[-121.975184, 37.375402],
[-121.975136, 37.375414],
[-121.975089, 37.375426],
[-121.975043, 37.375439],
[-121.975, 37.375454],
[-121.974979, 37.375461],
[-121.974799, 37.375531],
[-121.974453, 37.375662],
[-121.974194, 37.375757],
[-121.974063, 37.375806],
[-121.973997, 37.375829],
[-121.973866, 37.375876],
[-121.973739, 37.37592],
[-121.973618, 37.375961],
[-121.973504, 37.375999],
[-121.9734, 37.376032],
[-121.973308, 37.376059],
[-121.973247, 37.376076],
[-121.973211, 37.376084],
[-121.973195, 37.376088],
[-121.973173, 37.376093],
[-121.973129, 37.376104],
[-121.973089, 37.376115],
[-121.97305, 37.376126],
[-121.973013, 37.376138],
[-121.972979, 37.37615],
[-121.972947, 37.376162],
[-121.972917, 37.376175],
[-121.972902, 37.376181],
[-121.972902, 37.376267],
[-121.972916, 37.376261],
[-121.972947, 37.376247],
[-121.97298, 37.376233],
[-121.973016, 37.376219],
[-121.973056, 37.376205],
[-121.973099, 37.376191],
[-121.973145, 37.376179],
[-121.973194, 37.376166],
[-121.97322, 37.37616],
[-121.973238, 37.376156],
[-121.973276, 37.376147],
[-121.97334, 37.376129],
[-121.973435, 37.376101],
[-121.973541, 37.376068],
[-121.973656, 37.37603],
[-121.973778, 37.375988],
[-121.973905, 37.375944],
[-121.974035, 37.375897],
[-121.974101, 37.375874],
[-121.974234, 37.375825],
[-121.974493, 37.375729],
[-121.974839, 37.375598],
[-121.97502, 37.375529],
[-121.975039, 37.375522],
[-121.975078, 37.375509],
[-121.975119, 37.375497],
[-121.975163, 37.375486],
[-121.975208, 37.375475],
[-121.975256, 37.375466],
[-121.975305, 37.375457],
[-121.975355, 37.375449],
[-121.975406, 37.375442],
[-121.975459, 37.375436],
[-121.975512, 37.37543],
[-121.975566, 37.375425],
[-121.975619, 37.375421],
[-121.975673, 37.375418],
[-121.975754, 37.375414],
[-121.975807, 37.375413],
[-121.97586, 37.375412],
[-121.975962, 37.375411],
[-121.97606, 37.375413],
[-121.976151, 37.375417],
[-121.976193, 37.375419],
[-121.976233, 37.375422],
[-121.976306, 37.375429],
[-121.976366, 37.375437],
[-121.976401, 37.375443],
[-121.97642, 37.375447],
[-121.976428, 37.375449],
[-121.976443, 37.375454],
[-121.976474, 37.375465],
[-121.976504, 37.375478],
[-121.976534, 37.375492],
[-121.976563, 37.375509],
[-121.976594, 37.375526],
[-121.976641, 37.375554],
[-121.976708, 37.375595],
[-121.976763, 37.375628],
[-121.976804, 37.37565],
[-121.976847, 37.375672],
[-121.976893, 37.375695],
[-121.976943, 37.375717],
[-121.976997, 37.37574],
[-121.977025, 37.37575],
[-121.977047, 37.375758],
[-121.97709, 37.375772],
[-121.977133, 37.375784],
[-121.977175, 37.375794],
[-121.977216, 37.375803],
[-121.977256, 37.375809],
[-121.977295, 37.375814],
[-121.977333, 37.375818],
[-121.977369, 37.37582],
[-121.977404, 37.375822],
[-121.977437, 37.375822],
[-121.977468, 37.375821],
[-121.97751, 37.37582],
[-121.977559, 37.375816],
[-121.977579, 37.375813],
[-121.977579, 37.375813],
],
],
type: "Polygon",
},
},
{
type: "Feature",
properties: {
id: 2,
name: "High Hill Field",
type: "pasture",
farm: 1,
},
geometry: {
coordinates: [
[
[-121.975305, 37.377548],
[-121.976849, 37.377548],
[-121.976869, 37.377513],
[-121.976919, 37.37743],
[-121.976962, 37.37736],
[-121.977011, 37.377287],
[-121.977049, 37.377232],
[-121.977075, 37.377197],
[-121.977101, 37.377163],
[-121.977126, 37.377133],
[-121.977151, 37.377105],
[-121.977174, 37.377081],
[-121.977186, 37.377071],
[-121.977195, 37.377063],
[-121.977217, 37.377047],
[-121.97724, 37.377032],
[-121.977264, 37.377016],
[-121.97729, 37.377001],
[-121.977317, 37.376987],
[-121.977358, 37.376966],
[-121.977414, 37.376941],
[-121.977468, 37.376918],
[-121.977518, 37.376899],
[-121.977561, 37.376883],
[-121.977579, 37.376876],
[-121.977579, 37.376176],
[-121.977534, 37.376159],
[-121.977391, 37.376106],
[-121.977261, 37.376059],
[-121.977174, 37.376029],
[-121.977095, 37.376003],
[-121.977045, 37.375988],
[-121.977017, 37.37598],
[-121.977005, 37.375977],
[-121.97698, 37.375972],
[-121.976934, 37.375963],
[-121.976893, 37.375958],
[-121.976859, 37.375955],
[-121.976831, 37.375953],
[-121.97681, 37.375953],
[-121.976791, 37.375953],
[-121.976787, 37.375954],
[-121.976756, 37.375956],
[-121.976677, 37.375962],
[-121.976578, 37.375971],
[-121.976464, 37.375982],
[-121.976338, 37.375996],
[-121.976203, 37.376012],
[-121.976063, 37.376031],
[-121.975921, 37.376051],
[-121.97578, 37.376073],
[-121.975678, 37.37609],
[-121.975612, 37.376102],
[-121.975548, 37.376114],
[-121.975487, 37.376127],
[-121.97543, 37.37614],
[-121.975376, 37.376153],
[-121.975326, 37.376166],
[-121.97528, 37.376179],
[-121.97524, 37.376193],
[-121.975205, 37.376207],
[-121.975182, 37.376217],
[-121.975169, 37.376224],
[-121.975157, 37.376231],
[-121.975147, 37.376238],
[-121.975139, 37.376245],
[-121.975133, 37.376252],
[-121.975128, 37.37626],
[-121.975125, 37.376267],
[-121.975124, 37.37627],
[-121.975124, 37.376278],
[-121.975123, 37.376293],
[-121.975124, 37.376309],
[-121.975126, 37.376325],
[-121.97513, 37.376341],
[-121.975134, 37.376357],
[-121.975143, 37.376381],
[-121.975157, 37.376415],
[-121.975175, 37.376449],
[-121.975205, 37.376502],
[-121.97525, 37.376575],
[-121.975285, 37.376632],
[-121.975307, 37.37667],
[-121.975327, 37.37671],
[-121.975345, 37.37675],
[-121.975357, 37.376781],
[-121.975364, 37.376801],
[-121.97537, 37.376822],
[-121.975375, 37.376843],
[-121.975377, 37.376854],
[-121.975385, 37.376894],
[-121.9754, 37.376971],
[-121.97541, 37.377026],
[-121.975415, 37.377062],
[-121.975418, 37.377099],
[-121.97542, 37.377137],
[-121.975419, 37.377176],
[-121.975415, 37.377216],
[-121.975409, 37.377258],
[-121.975399, 37.377303],
[-121.975386, 37.377351],
[-121.975368, 37.377403],
[-121.975347, 37.377458],
[-121.97532, 37.377517],
[-121.975305, 37.377548],
[-121.975305, 37.377548],
],
],
type: "Polygon",
},
},
{
type: "Feature",
properties: {
id: 3,
name: "Blackwater Pasture",
type: "pasture",
farm: 2,
},
geometry: {
coordinates: [
[
[-121.975423, 37.375048],
[-121.972902, 37.375048],
[-121.972902, 37.375849],
[-121.97291, 37.375861],
[-121.972923, 37.375879],
[-121.972933, 37.375891],
[-121.972944, 37.375903],
[-121.972956, 37.375915],
[-121.97297, 37.375926],
[-121.972984, 37.375937],
[-121.973, 37.375947],
[-121.973016, 37.375957],
[-121.973035, 37.375966],
[-121.973054, 37.375974],
[-121.973075, 37.375981],
[-121.973097, 37.375987],
[-121.973121, 37.375992],
[-121.973146, 37.375996],
[-121.97316, 37.375997],
[-121.973173, 37.375998],
[-121.973199, 37.375999],
[-121.973225, 37.375999],
[-121.973252, 37.375998],
[-121.97328, 37.375996],
[-121.973308, 37.375992],
[-121.973337, 37.375987],
[-121.973365, 37.375982],
[-121.973395, 37.375975],
[-121.973425, 37.375968],
[-121.973471, 37.375955],
[-121.973533, 37.375934],
[-121.973598, 37.375911],
[-121.973665, 37.375886],
[-121.973734, 37.375858],
[-121.973841, 37.375813],
[-121.973991, 37.375749],
[-121.974109, 37.375699],
[-121.974191, 37.375666],
[-121.974233, 37.375649],
[-121.974332, 37.37561],
[-121.974528, 37.375533],
[-121.974677, 37.375476],
[-121.974792, 37.375434],
[-121.974869, 37.375407],
[-121.974944, 37.375382],
[-121.975016, 37.375358],
[-121.975085, 37.375338],
[-121.975149, 37.375321],
[-121.975194, 37.37531],
[-121.975221, 37.375305],
[-121.975247, 37.3753],
[-121.975271, 37.375297],
[-121.975282, 37.375295],
[-121.975288, 37.375295],
[-121.9753, 37.375293],
[-121.975311, 37.37529],
[-121.975322, 37.375286],
[-121.975332, 37.375282],
[-121.975341, 37.375277],
[-121.97535, 37.375271],
[-121.975357, 37.375265],
[-121.975368, 37.375255],
[-121.975381, 37.37524],
[-121.975392, 37.375223],
[-121.9754, 37.375205],
[-121.975407, 37.375186],
[-121.975413, 37.375166],
[-121.975417, 37.375147],
[-121.97542, 37.375127],
[-121.975423, 37.375098],
[-121.975423, 37.375063],
[-121.975423, 37.375048],
[-121.975423, 37.375048],
],
],
type: "Polygon",
},
},
{
type: "Feature",
properties: {
id: 1,
name: "Valley Pasture",
type: "pasture",
farm: 3,
},
geometry: {
coordinates: [
[
[-121.977579, 37.375657],
[-121.977579, 37.375048],
[-121.975878, 37.375048],
[-121.975888, 37.37507],
[-121.975907, 37.375108],
[-121.975923, 37.375135],
[-121.975939, 37.375162],
[-121.975957, 37.375187],
[-121.975971, 37.375205],
[-121.975981, 37.375216],
[-121.975991, 37.375225],
[-121.976001, 37.375234],
[-121.976006, 37.375238],
[-121.97601, 37.37524],
[-121.976018, 37.375245],
[-121.976027, 37.375249],
[-121.976037, 37.375252],
[-121.976053, 37.375255],
[-121.976076, 37.375258],
[-121.976101, 37.375259],
[-121.976128, 37.375259],
[-121.976172, 37.375257],
[-121.976236, 37.375253],
[-121.976286, 37.375252],
[-121.976321, 37.375253],
[-121.976355, 37.375255],
[-121.97639, 37.375259],
[-121.976417, 37.375264],
[-121.976434, 37.375268],
[-121.976451, 37.375272],
[-121.976469, 37.375278],
[-121.976477, 37.375281],
[-121.976486, 37.375285],
[-121.976503, 37.375292],
[-121.97653, 37.375305],
[-121.976566, 37.375324],
[-121.976603, 37.375346],
[-121.976642, 37.375371],
[-121.97668, 37.375397],
[-121.976739, 37.375437],
[-121.976819, 37.375493],
[-121.976879, 37.375533],
[-121.976919, 37.375558],
[-121.976958, 37.375581],
[-121.976997, 37.375601],
[-121.977026, 37.375615],
[-121.977045, 37.375623],
[-121.977064, 37.37563],
[-121.977082, 37.375636],
[-121.977092, 37.375639],
[-121.977107, 37.375643],
[-121.97714, 37.375651],
[-121.977174, 37.375656],
[-121.977208, 37.375661],
[-121.977244, 37.375664],
[-121.97728, 37.375667],
[-121.977315, 37.375668],
[-121.97735, 37.375669],
[-121.977401, 37.375668],
[-121.977464, 37.375666],
[-121.977519, 37.375662],
[-121.977563, 37.375659],
[-121.977579, 37.375657],
[-121.977579, 37.375657],
],
],
type: "Polygon",
},
},
{
type: "Feature",
properties: {
id: 4,
name: "Northern Fields",
type: "pasture",
farm: 3,
},
geometry: {
coordinates: [
[
[-121.972902, 37.376415],
[-121.972902, 37.377548],
[-121.974586, 37.377548],
[-121.974598, 37.377519],
[-121.974628, 37.377445],
[-121.974652, 37.377378],
[-121.974669, 37.377329],
[-121.974684, 37.377278],
[-121.974698, 37.377226],
[-121.97471, 37.377174],
[-121.97472, 37.377121],
[-121.974725, 37.377082],
[-121.974728, 37.377056],
[-121.974729, 37.377031],
[-121.97473, 37.377007],
[-121.974729, 37.376983],
[-121.974727, 37.37696],
[-121.974724, 37.376938],
[-121.97472, 37.376917],
[-121.974714, 37.376897],
[-121.974707, 37.376878],
[-121.974698, 37.37686],
[-121.974688, 37.376844],
[-121.974682, 37.376836],
[-121.974674, 37.376827],
[-121.974659, 37.376811],
[-121.974643, 37.376796],
[-121.974627, 37.376783],
[-121.97461, 37.376771],
[-121.974592, 37.376761],
[-121.974573, 37.376752],
[-121.974555, 37.376744],
[-121.974535, 37.376737],
[-121.974516, 37.376731],
[-121.974496, 37.376726],
[-121.974475, 37.376721],
[-121.974444, 37.376716],
[-121.974402, 37.376711],
[-121.974337, 37.376706],
[-121.974273, 37.376701],
[-121.97423, 37.376697],
[-121.974188, 37.376692],
[-121.974158, 37.376686],
[-121.974137, 37.376682],
[-121.974118, 37.376676],
[-121.974098, 37.37667],
[-121.97408, 37.376663],
[-121.974061, 37.376654],
[-121.974052, 37.37665],
[-121.974044, 37.376645],
[-121.974029, 37.376634],
[-121.974017, 37.376623],
[-121.974007, 37.376612],
[-121.973999, 37.3766],
[-121.973994, 37.376587],
[-121.97399, 37.376574],
[-121.973987, 37.376561],
[-121.973985, 37.37654],
[-121.973986, 37.376512],
[-121.973987, 37.376483],
[-121.973987, 37.376455],
[-121.973986, 37.376434],
[-121.973984, 37.376421],
[-121.97398, 37.376407],
[-121.973974, 37.376395],
[-121.973967, 37.376382],
[-121.973958, 37.37637],
[-121.973946, 37.376359],
[-121.973931, 37.376348],
[-121.973914, 37.376338],
[-121.973893, 37.376329],
[-121.973869, 37.37632],
[-121.973842, 37.376313],
[-121.97381, 37.376306],
[-121.973774, 37.3763],
[-121.973734, 37.376296],
[-121.973689, 37.376292],
[-121.973665, 37.376291],
[-121.973629, 37.37629],
[-121.97356, 37.376289],
[-121.973494, 37.37629],
[-121.97343, 37.376293],
[-121.97337, 37.376298],
[-121.973312, 37.376305],
[-121.973258, 37.376313],
[-121.973207, 37.376322],
[-121.973159, 37.376332],
[-121.973114, 37.376343],
[-121.973073, 37.376354],
[-121.973034, 37.376366],
[-121.972999, 37.376377],
[-121.972967, 37.376389],
[-121.972926, 37.376405],
[-121.972902, 37.376415],
[-121.972902, 37.376415],
],
],
type: "Polygon",
},
},
{
type: "Feature",
properties: {
id: 2,
name: "Midfield Pond",
type: "water",
},
geometry: {
coordinates: [
[
[-121.976499, 37.377173],
[-121.976541, 37.377154],
[-121.976583, 37.377135],
[-121.976591, 37.377132],
[-121.976598, 37.377128],
[-121.976601, 37.377125],
[-121.976607, 37.37712],
[-121.976609, 37.377116],
[-121.97661, 37.377113],
[-121.976611, 37.37711],
[-121.976612, 37.377107],
[-121.976614, 37.377102],
[-121.976616, 37.3771],
[-121.976618, 37.377098],
[-121.976624, 37.377094],
[-121.976627, 37.377093],
[-121.976629, 37.377093],
[-121.97663, 37.377093],
[-121.976631, 37.377093],
[-121.976632, 37.377092],
[-121.976633, 37.377091],
[-121.976633, 37.37709],
[-121.976634, 37.377089],
[-121.976634, 37.377088],
[-121.976633, 37.377086],
[-121.976633, 37.377083],
[-121.976633, 37.377081],
[-121.976634, 37.377079],
[-121.976635, 37.377077],
[-121.976637, 37.377075],
[-121.976641, 37.377071],
[-121.976645, 37.377068],
[-121.976648, 37.377065],
[-121.97665, 37.377058],
[-121.976651, 37.377055],
[-121.976651, 37.377051],
[-121.976649, 37.377044],
[-121.976648, 37.377041],
[-121.976646, 37.377037],
[-121.976642, 37.37703],
[-121.976634, 37.37702],
[-121.976629, 37.377013],
[-121.976627, 37.377009],
[-121.976623, 37.377002],
[-121.976622, 37.376998],
[-121.976621, 37.376994],
[-121.976622, 37.376986],
[-121.976623, 37.376982],
[-121.976625, 37.376978],
[-121.976631, 37.37697],
[-121.976635, 37.376967],
[-121.976638, 37.376963],
[-121.97664, 37.376959],
[-121.976641, 37.376957],
[-121.976643, 37.376952],
[-121.976643, 37.37695],
[-121.976642, 37.376948],
[-121.976641, 37.376944],
[-121.976639, 37.376942],
[-121.976638, 37.376941],
[-121.976635, 37.376939],
[-121.976633, 37.376938],
[-121.976629, 37.376937],
[-121.976625, 37.376937],
[-121.976616, 37.376937],
[-121.976608, 37.376936],
[-121.9766, 37.376934],
[-121.976592, 37.376931],
[-121.976587, 37.37693],
[-121.976577, 37.37693],
[-121.976568, 37.376931],
[-121.976558, 37.376933],
[-121.976553, 37.376934],
[-121.976544, 37.376937],
[-121.976527, 37.376945],
[-121.976518, 37.376948],
[-121.976508, 37.376952],
[-121.976488, 37.376958],
[-121.976458, 37.376966],
[-121.976437, 37.37697],
[-121.976428, 37.376973],
[-121.97641, 37.376977],
[-121.976392, 37.376983],
[-121.976375, 37.37699],
[-121.976367, 37.376993],
[-121.976359, 37.376998],
[-121.976344, 37.377007],
[-121.976332, 37.377018],
[-121.976324, 37.377028],
[-121.97632, 37.377035],
[-121.976318, 37.377038],
[-121.976314, 37.377048],
[-121.976309, 37.377064],
[-121.976305, 37.377073],
[-121.976302, 37.377078],
[-121.976298, 37.377083],
[-121.97629, 37.377092],
[-121.976286, 37.377097],
[-121.976284, 37.377101],
[-121.976281, 37.377109],
[-121.976279, 37.377113],
[-121.976277, 37.377117],
[-121.976273, 37.377125],
[-121.976271, 37.377128],
[-121.976268, 37.377131],
[-121.976262, 37.377135],
[-121.976252, 37.377141],
[-121.976245, 37.377144],
[-121.976242, 37.377146],
[-121.976236, 37.377151],
[-121.976234, 37.377154],
[-121.976232, 37.377156],
[-121.976229, 37.377163],
[-121.976229, 37.377166],
[-121.97623, 37.377169],
[-121.976233, 37.377176],
[-121.976235, 37.377179],
[-121.976237, 37.377184],
[-121.97624, 37.37719],
[-121.976242, 37.377193],
[-121.976248, 37.377198],
[-121.976252, 37.377201],
[-121.976256, 37.377202],
[-121.976265, 37.377205],
[-121.97627, 37.377205],
[-121.976274, 37.377206],
[-121.976284, 37.377205],
[-121.976297, 37.377203],
[-121.976306, 37.377201],
[-121.976313, 37.3772],
[-121.976323, 37.377198],
[-121.97633, 37.377197],
[-121.976333, 37.377197],
[-121.976341, 37.377198],
[-121.976348, 37.377199],
[-121.976358, 37.3772],
[-121.976378, 37.377202],
[-121.976398, 37.377201],
[-121.976417, 37.377199],
[-121.976437, 37.377196],
[-121.976456, 37.377191],
[-121.976474, 37.377185],
[-121.976491, 37.377177],
[-121.976499, 37.377173],
[-121.976499, 37.377173],
],
],
type: "Polygon",
},
},
{
type: "Feature",
properties: {
id: 1,
name: "High Hill Farm",
type: "farm",
},
geometry: {
coordinates: [
[
[-121.975007, 37.377458],
[-121.975093, 37.377133],
[-121.974831, 37.377089],
[-121.974745, 37.377414],
[-121.975007, 37.377458],
],
],
type: "Polygon",
},
},
{
type: "Feature",
properties: {
id: 2,
name: "Blackwater Ranch",
type: "farm",
},
geometry: {
coordinates: [
[
[-121.97415, 37.376206],
[-121.974592, 37.376036],
[-121.974462, 37.375821],
[-121.97402, 37.37599],
[-121.97415, 37.376206],
],
],
type: "Polygon",
},
},
{
type: "Feature",
properties: {
id: 3,
name: "Llama Valley Estate",
type: "farm",
},
geometry: {
coordinates: [
[
[-121.975961, 37.375881],
[-121.976447, 37.375828],
[-121.976406, 37.375591],
[-121.97592, 37.375644],
[-121.975961, 37.375881],
],
],
type: "Polygon",
},
},
{
type: "Feature",
properties: {
id: 1,
name: "Blackwater Road",
type: "road",
},
geometry: {
coordinates: [
[
[-121.974872, 37.376236],
[-121.974906, 37.376223],
[-121.974972, 37.376199],
[-121.975038, 37.376177],
[-121.975101, 37.376158],
[-121.975162, 37.37614],
[-121.975221, 37.376124],
[-121.975277, 37.376111],
[-121.975329, 37.376099],
[-121.975399, 37.376084],
[-121.975476, 37.37607],
[-121.975549, 37.376059],
[-121.975564, 37.376057],
[-121.975565, 37.376057],
[-121.975566, 37.376057],
[-121.975596, 37.376052],
[-121.975736, 37.37603],
[-121.975878, 37.376008],
[-121.976044, 37.375984],
[-121.976225, 37.375959],
[-121.976364, 37.375941],
[-121.976455, 37.37593],
[-121.976543, 37.37592],
[-121.976627, 37.375912],
[-121.976667, 37.375909],
[-121.976687, 37.375907],
[-121.976726, 37.375906],
[-121.976766, 37.375905],
[-121.976806, 37.375906],
[-121.976847, 37.375909],
[-121.976887, 37.375912],
[-121.976928, 37.375917],
[-121.976969, 37.375923],
[-121.97701, 37.375929],
[-121.97705, 37.375937],
[-121.97709, 37.375945],
[-121.977129, 37.375954],
[-121.977187, 37.375968],
[-121.977261, 37.375988],
[-121.977297, 37.375998],
[-121.977321, 37.376005],
[-121.977367, 37.37602],
[-121.97743, 37.376042],
[-121.977502, 37.376068],
[-121.977558, 37.37609],
[-121.97758, 37.376099],
[-121.97758, 37.376057],
[-121.977557, 37.376048],
[-121.977501, 37.376026],
[-121.977434, 37.376002],
[-121.977356, 37.375976],
[-121.977313, 37.375963],
[-121.977277, 37.375952],
[-121.977201, 37.375932],
[-121.977142, 37.375917],
[-121.977102, 37.375908],
[-121.977061, 37.3759],
[-121.97702, 37.375892],
[-121.976978, 37.375886],
[-121.976936, 37.37588],
[-121.976893, 37.375875],
[-121.976851, 37.375871],
[-121.976809, 37.375869],
[-121.976766, 37.375868],
[-121.976725, 37.375868],
[-121.976683, 37.37587],
[-121.976663, 37.375871],
[-121.976617, 37.375875],
[-121.97652, 37.375885],
[-121.976419, 37.375896],
[-121.976315, 37.375909],
[-121.976159, 37.37593],
[-121.975959, 37.375958],
[-121.975867, 37.375971],
[-121.97582, 37.375979],
[-121.975631, 37.376008],
[-121.975557, 37.37602],
[-121.975536, 37.376023],
[-121.975449, 37.376036],
[-121.975383, 37.376049],
[-121.975333, 37.376059],
[-121.975278, 37.376071],
[-121.975219, 37.376086],
[-121.975156, 37.376102],
[-121.975089, 37.376121],
[-121.975021, 37.376142],
[-121.97495, 37.376166],
[-121.974878, 37.376193],
[-121.974804, 37.376222],
[-121.974767, 37.376238],
[-121.974754, 37.376243],
[-121.974728, 37.376253],
[-121.974701, 37.376261],
[-121.974673, 37.376269],
[-121.974645, 37.376275],
[-121.974616, 37.37628],
[-121.974588, 37.376285],
[-121.974559, 37.376288],
[-121.97453, 37.376291],
[-121.974501, 37.376292],
[-121.974457, 37.376293],
[-121.974401, 37.376292],
[-121.974346, 37.37629],
[-121.974294, 37.376285],
[-121.974246, 37.376279],
[-121.974202, 37.376273],
[-121.974163, 37.376266],
[-121.974117, 37.376257],
[-121.974082, 37.376249],
[-121.974077, 37.376248],
[-121.974064, 37.376245],
[-121.973998, 37.37623],
[-121.973946, 37.376221],
[-121.973905, 37.376215],
[-121.973858, 37.37621],
[-121.973807, 37.376205],
[-121.973751, 37.3762],
[-121.973691, 37.376197],
[-121.973627, 37.376196],
[-121.973558, 37.376196],
[-121.973486, 37.376198],
[-121.97341, 37.376202],
[-121.97337, 37.376206],
[-121.973351, 37.376208],
[-121.973313, 37.376212],
[-121.973275, 37.376218],
[-121.973239, 37.376224],
[-121.973203, 37.376231],
[-121.973169, 37.376239],
[-121.97312, 37.376251],
[-121.97306, 37.376269],
[-121.973006, 37.376288],
[-121.972959, 37.376305],
[-121.972919, 37.376322],
[-121.972902, 37.376329],
[-121.972902, 37.376372],
[-121.972915, 37.376366],
[-121.97295, 37.376351],
[-121.972994, 37.376333],
[-121.973047, 37.376314],
[-121.973093, 37.376299],
[-121.973125, 37.37629],
[-121.97316, 37.37628],
[-121.973196, 37.376272],
[-121.973233, 37.376264],
[-121.973272, 37.376256],
[-121.973313, 37.37625],
[-121.973354, 37.376245],
[-121.973376, 37.376243],
[-121.973414, 37.37624],
[-121.973489, 37.376235],
[-121.973559, 37.376233],
[-121.973626, 37.376233],
[-121.973689, 37.376235],
[-121.973747, 37.376237],
[-121.973802, 37.376242],
[-121.973851, 37.376247],
[-121.973896, 37.376252],
[-121.973936, 37.376258],
[-121.973986, 37.376267],
[-121.974049, 37.37628],
[-121.974062, 37.376283],
[-121.974062, 37.376284],
[-121.974062, 37.376284],
[-121.974067, 37.376285],
[-121.974103, 37.376294],
[-121.974151, 37.376303],
[-121.974191, 37.37631],
[-121.974236, 37.376317],
[-121.974287, 37.376323],
[-121.974341, 37.376328],
[-121.974398, 37.376331],
[-121.974442, 37.376332],
[-121.974472, 37.376332],
[-121.974503, 37.376331],
[-121.974534, 37.376329],
[-121.974565, 37.376326],
[-121.974596, 37.376322],
[-121.974626, 37.376318],
[-121.974657, 37.376312],
[-121.974687, 37.376305],
[-121.974717, 37.376297],
[-121.974747, 37.376287],
[-121.974776, 37.376276],
[-121.97479, 37.37627],
[-121.97481, 37.376262],
[-121.974829, 37.376254],
[-121.974872, 37.376236],
],
],
type: "Polygon",
},
},
{
type: "Feature",
properties: {
id: 2,
name: "Hill Road",
type: "road",
},
geometry: {
coordinates: [
[
[-121.975116, 37.377548],
[-121.975164, 37.377548],
[-121.975175, 37.377501],
[-121.975194, 37.377404],
[-121.975208, 37.377327],
[-121.975221, 37.377244],
[-121.975233, 37.37716],
[-121.975239, 37.377098],
[-121.975242, 37.377058],
[-121.975243, 37.377019],
[-121.975244, 37.376983],
[-121.975243, 37.376965],
[-121.975243, 37.376954],
[-121.97524, 37.376932],
[-121.975236, 37.376909],
[-121.97523, 37.376886],
[-121.975223, 37.376862],
[-121.975214, 37.376837],
[-121.975205, 37.376812],
[-121.975194, 37.376787],
[-121.975175, 37.376748],
[-121.975148, 37.376696],
[-121.975118, 37.376643],
[-121.975086, 37.37659],
[-121.97507, 37.376563],
[-121.975039, 37.376515],
[-121.974979, 37.376421],
[-121.974939, 37.376357],
[-121.974915, 37.376318],
[-121.974895, 37.376282],
[-121.97486689053964, 37.376231664268545],
[-121.974823326818, 37.376249306071124],
[-121.974854, 37.376303],
[-121.974875, 37.376339],
[-121.974899, 37.376379],
[-121.974939, 37.376442],
[-121.974998, 37.376533],
[-121.975027, 37.37658],
[-121.975043, 37.376606],
[-121.975075, 37.376659],
[-121.975104, 37.376711],
[-121.975131, 37.376763],
[-121.975154, 37.376813],
[-121.975169, 37.376849],
[-121.975177, 37.376872],
[-121.975184, 37.376894],
[-121.975189, 37.376916],
[-121.975193, 37.376937],
[-121.975196, 37.376957],
[-121.975196, 37.376966],
[-121.975197, 37.376983],
[-121.975196, 37.37702],
[-121.975194, 37.377058],
[-121.975191, 37.377099],
[-121.975185, 37.377161],
[-121.975173, 37.377247],
[-121.97516, 37.37733],
[-121.975145, 37.377408],
[-121.975126, 37.377504],
[-121.975117, 37.377548],
[-121.975116, 37.377548],
],
],
type: "Polygon",
},
},
{
type: "Feature",
properties: {
id: 3,
name: "Creek Crossings",
type: "road",
},
geometry: {
coordinates: [
[
[-121.97586959145089, 37.375974771410284],
[-121.975867, 37.375895],
[-121.975865, 37.375854],
[-121.975864, 37.375831],
[-121.975862, 37.375814],
[-121.975858, 37.375777],
[-121.975853, 37.37574],
[-121.975846, 37.375702],
[-121.975838, 37.375664],
[-121.975827, 37.375627],
[-121.975814, 37.37559],
[-121.975803, 37.375564],
[-121.975795, 37.375547],
[-121.97579, 37.375538],
[-121.975782, 37.375523],
[-121.975765, 37.375489],
[-121.975748, 37.375453],
[-121.975732, 37.375415],
[-121.975716, 37.375376],
[-121.975701, 37.375336],
[-121.975686, 37.375296],
[-121.975673, 37.375256],
[-121.975667, 37.375236],
[-121.975661, 37.375217],
[-121.97565, 37.37518],
[-121.975641, 37.375145],
[-121.975634, 37.375113],
[-121.975631, 37.375098],
[-121.975628, 37.375084],
[-121.975625, 37.375058],
[-121.975625, 37.375048],
[-121.975576, 37.375048],
[-121.975577, 37.37506],
[-121.975581, 37.375088],
[-121.975584, 37.375104],
[-121.975587, 37.375119],
[-121.975595, 37.375152],
[-121.975604, 37.375188],
[-121.975615, 37.375226],
[-121.975621, 37.375245],
[-121.975627, 37.375265],
[-121.975641, 37.375306],
[-121.975655, 37.375346],
[-121.975671, 37.375387],
[-121.975687, 37.375427],
[-121.975704, 37.375466],
[-121.975721, 37.375503],
[-121.975738, 37.375537],
[-121.975747, 37.375554],
[-121.975755, 37.375569],
[-121.975769, 37.375601],
[-121.975781, 37.375636],
[-121.975791, 37.375671],
[-121.975799, 37.375708],
[-121.975806, 37.375745],
[-121.975811, 37.375781],
[-121.975815, 37.375816],
[-121.975816, 37.375833],
[-121.975818, 37.375858],
[-121.97582, 37.375902],
[-121.97582198931369, 37.37598250672985],
[-121.97586959145089, 37.375974771410284],
],
],
type: "Polygon",
},
},
{
type: "Feature",
properties: {
id: 1,
name: "George",
type: "llama",
birthDate: "2007-06-01",
farm: 3,
pasture: 4,
},
geometry: {
coordinates: [-121.97416315360529, 37.37726523951736],
type: "Point",
},
},
{
type: "Feature",
properties: {
id: 2,
name: "Charlie",
type: "llama",
birthDate: "2002-06-10",
farm: 3,
pasture: 4,
},
geometry: {
coordinates: [-121.97395784953005, 37.37715161987822],
type: "Point",
},
},
{
type: "Feature",
properties: {
id: 3,
name: "Winnie",
type: "llama",
birthDate: "1998-11-22",
farm: 3,
pasture: 4,
},
geometry: {
coordinates: [-121.97345925391895, 37.37734098584815],
type: "Point",
},
},
{
type: "Feature",
properties: {
id: 4,
name: "Loretta",
type: "llama",
birthDate: "1996-11-14",
farm: 3,
pasture: 4,
},
geometry: {
coordinates: [-121.97352891065863, 37.37674375308745],
type: "Point",
},
},
{
type: "Feature",
properties: {
id: 5,
name: "Pablo",
type: "llama",
birthDate: "2003-11-11",
farm: 1,
pasture: 2,
},
geometry: {
coordinates: [-121.97599622570581, 37.377279806127106],
type: "Point",
},
},
{
type: "Feature",
properties: {
id: 6,
name: "Maria",
type: "llama",
birthDate: "2003-05-26",
farm: 1,
pasture: 2,
},
geometry: {
coordinates: [-121.97590090595644, 37.37722445300167],
type: "Point",
},
},
{
type: "Feature",
properties: {
id: 7,
name: "Jeff",
type: "llama",
birthDate: "2003-05-11",
farm: 1,
pasture: 2,
},
geometry: {
coordinates: [-121.97704474294699, 37.376936033423746],
type: "Point",
},
},
{
type: "Feature",
properties: {
id: 8,
name: "Tiff",
type: "llama",
birthDate: "2002-03-20",
farm: 1,
pasture: 2,
},
geometry: {
coordinates: [-121.97694209090955, 37.37683989331836],
type: "Point",
},
},
{
type: "Feature",
properties: {
id: 9,
name: "Larry",
type: "llama",
birthDate: "2005-10-02",
farm: 1,
pasture: 2,
},
geometry: {
coordinates: [-121.97705207523538, 37.37679036654879],
type: "Point",
},
},
{
type: "Feature",
properties: {
id: 10,
name: "Duke",
type: "llama",
birthDate: "2005-09-28",
farm: 2,
pasture: 3,
},
geometry: {
coordinates: [-121.97479373040784, 37.37533659563674],
type: "Point",
},
},
{
type: "Feature",
properties: {
id: 11,
name: "Elle",
type: "llama",
birthDate: "2005-03-24",
farm: 2,
pasture: 3,
},
geometry: {
coordinates: [-121.97382220219458, 37.375418170702574],
type: "Point",
},
},
{
type: "Feature",
properties: {
id: 12,
name: "Chip",
type: "llama",
birthDate: "1999-04-12",
farm: 2,
pasture: 3,
},
geometry: {
coordinates: [-121.97337859874628, 37.37568328905425],
type: "Point",
},
},
{
type: "Feature",
properties: {
id: 13,
name: "Beth",
type: "llama",
birthDate: "1999-10-19",
farm: 2,
pasture: 3,
},
geometry: {
coordinates: [-121.97371588401279, 37.375706596117254],
type: "Point",
},
},
{
type: "Feature",
properties: {
id: 14,
name: "Ruby",
type: "llama",
birthDate: "2000-06-04",
farm: 2,
pasture: 3,
},
geometry: {
coordinates: [-121.97337126645787, 37.37549391889877],
type: "Point",
},
},
{
type: "Feature",
properties: {
id: 15,
name: "James",
type: "llama",
birthDate: "2000-07-01",
farm: 3,
pasture: 1,
},
geometry: {
coordinates: [-121.97734903291558, 37.37544439124045],
type: "Point",
},
},
{
type: "Feature",
properties: {
id: 16,
name: "William",
type: "llama",
birthDate: "2002-08-15",
farm: 3,
pasture: 1,
},
geometry: {
coordinates: [-121.97714739498474, 37.37532785544573],
type: "Point",
},
},
{
type: "Feature",
properties: {
id: 17,
name: "Haley",
type: "llama",
birthDate: "2003-06-22",
farm: 3,
pasture: 4,
},
geometry: {
coordinates: [-121.97340426175577, 37.37689816006372],
type: "Point",
},
},
{
type: "Feature",
properties: {
id: 18,
name: "Ben",
type: "llama",
birthDate: "2004-01-22",
farm: 3,
pasture: 4,
},
geometry: {
coordinates: [-121.97329061128559, 37.37680201990919],
type: "Point",
},
},
{
type: "Feature",
properties: {
id: 19,
name: "Adam",
type: "llama",
birthDate: "2004-12-10",
farm: 1,
pasture: 2,
},
geometry: {
coordinates: [-121.97639583542347, 37.37706130670982],
type: "Point",
},
},
{
type: "Feature",
properties: {
id: 20,
name: "Jenny",
type: "llama",
birthDate: "2000-08-11",
farm: 1,
pasture: 2,
},
geometry: {
coordinates: [-121.97588624137967, 37.37635627758419],
type: "Point",
},
},
{
type: "Feature",
properties: {
id: 21,
name: "Mick",
type: "llama",
birthDate: "2005-02-06",
farm: 1,
pasture: 2,
},
geometry: {
coordinates: [-121.97574692790832, 37.37632714400462],
type: "Point",
},
},
{
type: "Feature",
properties: {
id: 22,
name: "Lizzy",
type: "llama",
birthDate: "2005-03-22",
farm: 1,
pasture: 2,
},
geometry: {
coordinates: [-121.97576504912091, 37.37653998535161],
type: "Point",
},
},
],
};
Prior to MarkLogic 11, we would need to store every feature in a separate document if we wanted to be able to query for each feature individually without the overhead of post-processing.
Tables vs JSON in results
You will notice a mix of results in tables and json. That has to do with the way the data is represented and subsequently displayed in Query Console.
- SQL results are always displayed in a table in Query Console
- xQuery Optic results are a sequence of maps and are displayed as a table in Query Console
- Javascript Optic results are displayed as a sequence of JSON documents if the results are generated as Javascript
- In code, they are a sequence of JSON objects
TDE Template to project features into their own rows.
Alter the content to match some TDE requirements.
One interesting item points out a limitation of the TDE template grammar and features. Although you can do some complex work in creating your values in the configuration of a TDE row or triple, you cannot import modules to assist you. Furthermore, MarkLogic does not import any libraries as part of this process. The documentation is, however, very clear on what you can do and what functions are available. What this means, is that we cannot, for example, make use of the geospatial toolbox. With the rapid growth of features and fixes around the OpticAPI, I am sure that this will be addressed in the future.
We represent the geospatial data as geojson. However, the underlying geospatial indexes do not readily understand this yet.
The TDE engine expects cts features (such as a polygon represented as a list of cts: points or in the format of WKT (Well Known Text)). Therefore, we need to convert our coordinates into a format that is accepted. This is not necessarily impossible to do at runtime - a splits and joins and replaces. However that is a bit messy and difficult to maintain. Instead, I chose to use the tools made for that purpose and add a new version of the polygons by using a transform on ingest.
The new element added is called ctsPoly2 and is just another property for each polygon. You will see that referenced in one of the TDE templates.
Pastures TDE Template
<template xmlns="http://marklogic.com/xdmp/tde">
<description>Pasture info</description>
<context>//features[./properties/type="pasture"]</context>
<rows>
<row>
<schema-name>pasture</schema-name>
<view-name>details</view-name>
<columns>
<column>
<name>ID</name>
<scalar-type>int</scalar-type>
<val>./properties/id</val>
</column>
<column>
<name>name</name>
<scalar-type>string</scalar-type>
<val>./properties/name</val>
</column>
<column>
<name>farmId</name>
<scalar-type>integer</scalar-type>
<val>./properties/farm</val>
</column>
<column>
<name>geometry</name>
<scalar-type>polygon</scalar-type>
<val>./ctsPoly2</val>
</column>
</columns>
</row>
</rows>
</template>
const op = require("/MarkLogic/optic");
op.fromView("pasture", "details", "pastureDetails").result();
We can now query the pastures directly since they have been projected into their own rows. 
Llamas TDE Template
Llamas may not be the most sprightly of creatures, but they are still able to move around. We can use the same technique to project the llamas into their own rows. For this, we will assume that there is a report of all llamas and their location at a given time. This is a common use-case for geospatial data. We can then use the Optic API to query for llamas in a given area at a given time.
{
"root": {
"reportTime": "2023-02-28T10:00:00",
"type": "llamaReport",
"data": [
{
"location": {
"coordinates": [-121.974163153605, 37.3772652395174],
"type": "Point"
},
"name": "George",
"id": 1
},
{
"location": {
"coordinates": [-121.97395784953, 37.3771516198782],
"type": "Point"
},
"name": "Charlie",
"id": 2
},
{
"location": {
"coordinates": [-121.973459253919, 37.3773409858482],
"type": "Point"
},
"name": "Winnie",
"id": 3
},
{
"location": {
"coordinates": [-121.973528910659, 37.3767437530875],
"type": "Point"
},
"name": "Loretta",
"id": 4
},
{
"location": {
"coordinates": [-121.975996225706, 37.3772798061271],
"type": "Point"
},
"name": "Pablo",
"id": 5
},
{
"location": {
"coordinates": [-121.975900905956, 37.3772244530017],
"type": "Point"
},
"name": "Maria",
"id": 6
},
{
"location": {
"coordinates": [-121.977044742947, 37.3769360334237],
"type": "Point"
},
"name": "Jeff",
"id": 7
},
{
"location": {
"coordinates": [-121.97694209091, 37.3768398933184],
"type": "Point"
},
"name": "Tiff",
"id": 8
},
{
"location": {
"coordinates": [-121.977052075235, 37.3767903665488],
"type": "Point"
},
"name": "Larry",
"id": 9
},
{
"location": {
"coordinates": [-121.974793730408, 37.3753365956367],
"type": "Point"
},
"name": "Duke",
"id": 10
},
{
"location": {
"coordinates": [-121.973822202195, 37.3754181707026],
"type": "Point"
},
"name": "Elle",
"id": 11
},
{
"location": {
"coordinates": [-121.973378598746, 37.3756832890543],
"type": "Point"
},
"name": "Chip",
"id": 12
},
{
"location": {
"coordinates": [-121.973715884013, 37.3757065961173],
"type": "Point"
},
"name": "Beth",
"id": 13
},
{
"location": {
"coordinates": [-121.973371266458, 37.3754939188988],
"type": "Point"
},
"name": "Ruby",
"id": 14
},
{
"location": {
"coordinates": [-121.977349032916, 37.3754443912405],
"type": "Point"
},
"name": "James",
"id": 15
},
{
"location": {
"coordinates": [-121.977147394985, 37.3753278554457],
"type": "Point"
},
"name": "William",
"id": 16
},
{
"location": {
"coordinates": [-121.973404261756, 37.3768981600637],
"type": "Point"
},
"name": "Haley",
"id": 17
},
{
"location": {
"coordinates": [-121.973290611286, 37.3768020199092],
"type": "Point"
},
"name": "Ben",
"id": 18
},
{
"location": {
"coordinates": [-121.976395835423, 37.3770613067098],
"type": "Point"
},
"name": "Adam",
"id": 19
},
{
"location": {
"coordinates": [-121.97588624138, 37.3763562775842],
"type": "Point"
},
"name": "Jenny",
"id": 20
},
{
"location": {
"coordinates": [-121.975746927908, 37.3763271440046],
"type": "Point"
},
"name": "Mick",
"id": 21
},
{
"location": {
"coordinates": [-121.975765049121, 37.3765399853516],
"type": "Point"
},
"name": "Lizzy",
"id": 22
}
]
}
}
<template xmlns="http://marklogic.com/xdmp/tde">
<description>Llama Report</description>
<context>//data</context>
<rows>
<row>
<schema-name>llama</schema-name>
<view-name>location</view-name>
<columns>
<column>
<name>reportTimestamp</name>
<scalar-type>dateTime</scalar-type>
<val>xs:dateTime(../../reportTime)</val>
</column>
<column>
<name>ID</name>
<scalar-type>int</scalar-type>
<val>./id</val>
</column>
<column>
<name>name</name>
<scalar-type>string</scalar-type>
<val>./name</val>
</column>
<column>
<name>location</name>
<scalar-type>point</scalar-type>
<val>cts:point(./location/coordinates[2], ./location/coordinates[1])</val>
</column>
</columns>
</row>
</rows>
</template>
Reporting the whereabouts of the llamas
Who is where at 10 o'clock?
To answer this question, we can use the SQL interface to the database. This is a very simple query that uses the geospatial functions to find all the llamas and their location at 10:00 o'clock. Two queries are included . They are slightly different.
select llama.location.name as llamaName, pasture.details.name as pastureName , llama.location.reportTimestamp
from llama.location
left join pasture.details
on ST_within(llama.location.location, pasture.details.geometry)
where reportTimestamp = '2023-02-28T10:00:00'
order by pastureName desc, llamaName desc
// This example shows who is on a pasture at 10:00. However, it does not show the ones that are not on a pasture. It is by design (joinInner). Other examples will show how to find who is missing from a pasture.
"use strict";
const op = require("/MarkLogic/optic");
let $llamaLocationPlan = op.fromView("llama", "location", "llamaLocation");
let $pastureDetailsPlan = op.fromView("pasture", "details", "pastureDetails");
var result = $llamaLocationPlan
.where(op.eq(op.col("reportTimestamp"), "2023-02-28T10:00:00"))
.joinInner(
$pastureDetailsPlan,
null,
op.geo.within(
$llamaLocationPlan.col("location"),
$pastureDetailsPlan.col("geometry")
)
)
.select(
[$pastureDetailsPlan.col("name"), $llamaLocationPlan.col("name")],
null
)
.result();
result;
Full Report of Locations
Of course, we may just want a report of all llamas and where they were at any time. If you look closely, you will notice one llama that is not in a pasture. We'll come back to that later.
// This reports all llamas and their location, even if they are not on a pasture. We have report times.
const op = require("/MarkLogic/optic");
let $llamaLocationPlan = op.fromView("llama", "location", "llamaLocation");
let $pastureDetailsPlan = op.fromView("pasture", "details", "pastureDetails");
var result = $llamaLocationPlan
.joinFullOuter(
$pastureDetailsPlan,
null,
op.geo.within(
$llamaLocationPlan.col("location"),
$pastureDetailsPlan.col("geometry")
)
)
.select(
[
$llamaLocationPlan.col("name"),
$pastureDetailsPlan.col("name"),
$llamaLocationPlan.col("reportTimestamp"),
],
null
)
.orderBy([
$llamaLocationPlan.col("name"),
$llamaLocationPlan.col("reportTimestamp"),
])
.result();
result;
SQL - Who's a naughty llama at 11 o' clock?
Someone reported a rogue llama this morning. Our report at 11 o'clock shows a llama that has switched location. We can simply find the llama that was not in a pasture at that time.
select llama.location.name as llamaName, pasture.details.name as pastureName , llama.location.reportTimestamp
from llama.location
left join pasture.details
on ST_within(llama.location.location, pasture.details.geometry)
where reportTimestamp = '2023-02-28T11:00:00'
and pasture.details.name is null
order by pastureName desc, llamaName desc
Hmm... It was Winnie..
Optic - Who's a naughty llama at 12 o' clock?
For this example. We decided to be a bit more fluid in the way we expressed the optic query. We define the plans as we need them, use op.col and op.view-col as appropriate. Furthermore, we chose to express this result in a notExistsJoin. There are other ways in which this could have been expressed. However, this is a good example of how flexible the Optic API is.
"use strict";
const op = require("/MarkLogic/optic");
op.fromView("llama", "location", "llamaLocation")
.where(op.eq(op.col("reportTimestamp"), "2023-02-28T12:00:00"))
.notExistsJoin(
op.fromView("pasture", "details", "pastureDetails"),
null,
op.geo.within(
op.viewCol("llamaLocation", "location"),
op.viewCol("pastureDetails", "geometry")
)
)
.select(
[op.viewCol("llamaLocation", "name"), op.col("reportTimestamp")],
"naughtyLlama"
)
.result();
Hmm... It was Adam..
Mix in Optic Power with GeoSpatial Power
So far, we have just done a few samples. To push things a bit further, we thought of a few more interesting questions that we could answer.
Who mixes with Whom?
As a quick analysis of which Llamas may have been in contact with each other, we can do a simple exercise. For this, We chose to do a bounding box based on all the locations sampled per llama and see where those bounding boxes overlap. This is a very simple way of doing this. In a more complete example, one would have to extend the query for more than overlap (covers, is covered by, touches, etc). In addition, a bounding box is a bit generous in size. Taking time to draw a polygon from the points or perhaps overlapping circles from each point could be more precise. But for this example we will stick with the bounding box. We rely heavily on grouping aggregates to prepare our data for us.
"use strict";
const op = require("/MarkLogic/optic");
var boxList = op
.fromView("llama", "location", "boxList")
.groupBy(
op.col("name"),
op.sequenceAggregate("locations", op.col("location"))
)
.bind(
op.as(
"boundingBox",
op.geo.boundingBoxes(
op.call(
"http://marklogic.com/cts",
"polygon",
op.col("locations")
)
)
)
)
.select(["name", "boundingBox"], "boxList");
var boxListAlias = boxList.select(["name", "boundingBox"], "boxListAlias");
boxList
.joinFullOuter(
boxListAlias,
null,
op.and(
op.geo.regionIntersects(
op.viewCol("boxList", "boundingBox"),
op.viewCol("boxListAlias", "boundingBox")
),
op.ne(
op.viewCol("boxList", "name"),
op.viewCol("boxListAlias", "name")
)
)
)
.where(op.ne(op.viewCol("boxList", "name"), ""))
.groupBy(op.viewCol("boxList", "name"), [
op.count(
op.col("countOverlapsWithOtherLlama"),
op.viewCol("boxListAlias", "name")
),
op.groupConcat(op.col("names"), op.viewCol("boxListAlias", "name"), {
separator: ", ",
}),
])
.orderBy(op.desc(op.col("countOverlapsWithOtherLlama")))
.result();
Who moved the most
As with the above example, there are other ways that one might choose to do this. As a quick example, our choice was just to find the distances between all points for each llama and find the max distance.
"use strict";
const op = require("/MarkLogic/optic");
op.fromView("llama", "location", "first")
.joinInner(
op.fromView("llama", "location", "second"),
op.on(op.viewCol("first", "name"), op.viewCol("second", "name")),
op.ne(
op.geo.toWkt(op.viewCol("first", "location")),
op.geo.toWkt(op.viewCol("second", "location"))
)
)
.bind(
op.as(
"distance",
op.geo.distance(
op.viewCol("first", "location"),
op.viewCol("second", "location"),
"units=km"
)
)
)
.groupBy(
op.viewCol("first", "name"),
op.max("maxDistanceInKM", op.col("distance"))
)
.select([op.viewCol("first", "name"), "maxDistanceInKM"], null)
.orderBy(op.desc("maxDistanceInKM"))
.result();
Which way did they go?
We already know that Winnie and Adam both left the reservation (literally). It also looks like the sneaky little llamas removed their trackers. Still, we should be able to figure out which way they were heading before they left. It is technically possible for me to have gotten the answer in a different way - grouping and using op.arrayAggregate to list the points as I did in other samples and then from there taken the first two using op.call() to access fn.subsequence() to grab the last two entries. However, I did this exercise to also push the work to the D-nodes as much as possible.
"use strict";
const op = require("/MarkLogic/optic");
const mostRecentTimestamp = op
.fromView("llama", "location", "mostRecentTimestamp")
.groupBy(
op.as(op.viewCol("timestamp", "name"), op.col("name")),
op.max(op.viewCol("timestamp", "timestamp"), op.col("reportTimestamp"))
);
const mostRecentRecord = mostRecentTimestamp
.joinInner(op.fromView("llama", "location", "mostRecentRecord"), [
op.on(
op.viewCol("timestamp", "name"),
op.viewCol("mostRecentRecord", "name")
),
op.on(
op.viewCol("timestamp", "timestamp"),
op.viewCol("mostRecentRecord", "reportTimestamp")
),
])
.select([
op.viewCol("mostRecentRecord", "name"),
op.viewCol("mostRecentRecord", "location"),
]);
const previousTimestamp = op
.fromView("llama", "location", "secondRecentTimestamp")
.notExistsJoin(mostRecentTimestamp, [
op.on(
op.viewCol("timestamp", "name"),
op.viewCol("secondRecentTimestamp", "name")
),
op.on(
op.viewCol("timestamp", "timestamp"),
op.viewCol("secondRecentTimestamp", "reportTimestamp")
),
])
.groupBy(
op.as(op.viewCol("timestamp", "name"), op.col("name")),
op.max(
op.viewCol("timestamp", "timestamp"),
op.viewCol("secondRecentTimestamp", "reportTimestamp")
)
);
const result = previousTimestamp
.joinInner(op.fromView("llama", "location", "secondRecentRecord"), [
op.on(
op.viewCol("timestamp", "name"),
op.viewCol("secondRecentRecord", "name")
),
op.on(
op.viewCol("timestamp", "timestamp"),
op.viewCol("secondRecentRecord", "reportTimestamp")
),
])
.select([
op.viewCol("secondRecentRecord", "name"),
op.viewCol("secondRecentRecord", "location"),
])
.joinLeftOuter(
mostRecentRecord,
op.on(
op.viewCol("secondRecentRecord", "name"),
op.viewCol("mostRecentRecord", "name")
)
)
.where(
op.gt(
op.geo.distance(
op.viewCol("secondRecentRecord", "location"),
op.viewCol("mostRecentRecord", "location"),
"units=km"
),
0.5
)
)
.bind(
op.as(
"bearing",
op.geo.bearing(
op.viewCol("secondRecentRecord", "location"),
op.viewCol("mostRecentRecord", "location")
)
)
)
.select([op.viewCol("secondRecentRecord", "name"), "bearing"], "result");
result.result();
Conclusion
Overall, the flexibility of using the MarkLogic Geospatial features against individual projected rows or sets of triples appears to be amazingly valuable and useful. It does what it claims to do - expose the already rich geospatial feature via SQL,SPARQL and OPTIC by way of hooking into existing items and adding new geospatial datatypes to the TDE template library. However, there are a few items to keep in mind as listed below.
TDE Template Limitations
As mentioned above, TDE template engine, by design, is limited by what tooling is used to process. This makes sense since template extraction at insert/runtime has a direct effect on processing time. In addition, there is no way to hook into other libraries. That means even though there may be a library available in MarkLogic that can help prepare your data to be in the format needed for indexing as a geospatial feature, it may be unreachable at index time. This leaves you with having to transform the data as a step before inserting. This is not new. However, we found that it became more apparent when we were trying to use the geospatial features. For many situations, this may never be encountered, depending on the source data used.
Performance
It is never appropriate to mention performance in these types of tests. They are done on small instances and in some cases, using preview versions of code that may have extra tracing etc. However, it is important to remember: MarkLogic is a distributed database. Items projected into rows are indexed in the same forest as the document. MarkLogic balances documents across forests and nodes. Therefore, (unless you have implemented some forest rules to keep things together), your rows are across the nodes. As much as possible, work is done on the D-Nodes (resolve, filter, sort). If you write queries (SQL, Sparql, Optic) etc. and have a constant on one side of the equation (where schema-a.col.foo = 12), then life is good. However, items like where schema-a.col.foo = schema-b.col.bar then there is an inevitable situation in which data has to be transmitted across nodes to resolve the filtering and joining and self-joins.
The above is not new. However, when writing queries, it is important to keep this in mind. Some of the ways that things were written above include essentially table aliases and left and right values from indexes that are no longer simple values (RegionA Overlaps Region B).
The samples above are used to articulate concepts only. Many of these samples go against the grain of what is recommended for performance(actually doing many of the items mentioned above). However, they are used to show the flexibility of the Optic API and the power of the Geospatial features. Breaking them into questions where you have a constant as part of a query would help - example: (1) Who is off the reservation right now(Wendy)? and (2) from that constant (Wendy), what are her last two known positions.
State of mind
Sometimes when writing queries like the above, they just flow out and work. Then I step back and look at what was written and wonder how I did that. I find Optic API to be a very powerful tool. When mixing Geospatial queries into it as well, it's important to be in the right frame of mind when deciding how to join, and most importantly, when and how to filter and group to get the results in an efficient manner.
This is fun and powerful. Overall, a pleasure to test.
- What did MarkLogic have for Geospatial features prior to MarkLogic 11?
- What's the difference in MarkLogic 11?
- Exploring the Optic API Geospatial Features
- Llamaverse
- TDE Template to project features into their own rows.
- Reporting the whereabouts of the llamas
- Who is where at 10 o'clock?
- Full Report of Locations
- SQL - Who's a naughty llama at 11 o' clock?
- Optic - Who's a naughty llama at 12 o' clock?
- Mix in Optic Power with GeoSpatial Power
- Who mixes with Whom?
- Who moved the most
- Which way did they go?
- Conclusion
- TDE Template Limitations
- Performance
- State of mind