JavaScript APIWeb Services APIGeocoder API
  1. Queries
  2. Queries - Quick Start
Queries

The expanded query API is capable of processing both traditional data queries and geo queries using a simple, yet powerful, SQL-like language. The ml.query function returns an instance of the ml.data.Query class providing the methods necessary to query large data sets quickly using a fluent interface.

Define and run an advanced query 
  1. <html>
  2.     <head>
  3.         <script type="text/javascript" src="/JS"></script>
  4.         <script type="text/javascript">
  5.             ml.onload(function () {
  6.  
  7.                 var q = ml.query().from('maplarge/Donors').where('PartyCode', 'Contains', 'r').groupby('State');
  8.                 q.select('State').select('Amount.sum').orderby('Amount');
  9.                 q.run(function (data, query) {
  10.                     var dataDiv = document.getElementById('dataDiv');
  11.                     dataDiv.innerHTML = ml.util.toJSON(data);
  12.                 });
  13.  
  14.             });
  15.         </script>
  16.     </head>
  17.     <body>
  18.         <div id="dataDiv"></div>
  19.     </body>
  20. </html>

The Return Object from the server is a JSON object which contains two Objects, data and totals. The data object contains all non-aggregate data from the query. Grouped aggregate data is contained in the totals data object following the naming convention columnName.aggregateName.

Filtering by column data includes Contains, Does Not Contain, Greater Than, Equal To, etc.
Filtering by geospatial shapes (geo queries) includes Polyline, Polygon, Freehand Polygon, Circle, Point, etc

NameDescription
Operators Although syntactically different than SQL, the language is similar using operators named select, from, where, groupby, orderby, take and start. Aggregate functions min, max, avg, sum and count are also supported.

To use an aggregate function, append the function to the field name using a dot (period). For example, the column namedStarRating would become StarRating.avg. All columns can be requested using an asterisk within a single select method, for example select(‘*’). Row counts can be calculated using select(‘*.count’). This naming convention provides a familiar feel to the language and combined with the fluent interface, insures the resulting queries are easy to read.
Data Output Data is returned within a JSON object via an AJAX callback, or as a downloaded CSV file. By default, the return value is an object literal containing two properties, data and totals. The data properties value is an object literal with one property for each column whose value is an array of values. The transposeQueryData method accepts the default return object data value and returns an array containing an array for each row of data, including the column names (see example below).
Examples

The first example is documented below and steps through the process of defining a query, from beginning through execution. All examples will use a table containing hotel data. The hotels are located around the world and each is represented visually by a point on a map.

First Example

The first example is separated by operator/keyword to show the progression of building the query and for ease of explanation; however, the same example could have been done in one single line or broken down in any way necessary to meet requirements.

Constructor: Create an instance of the ml.data.Query class using the ml.query helper function:
var q = ml.query();
From: Define the table to query:

q.from(‘hms/hotels’);
Select: the fields to include in the return dataset:
q.select(‘HotelName’).select(‘CityName’).select(‘StarRating’);
Take: the number of records to retrieve (if no limit is specified a default limit of 100 is applied):
q.take(3); Run: run this query and write the contents to the browser console:
q.run(function(results) {
         console.log(results);
         // Only output the data is there is data:
         if (results.data) {
                 makeTable(ml.$('#results'), results.data);
         } else {
                 console.log('Query failed.');
         }
});

Additional Examples

Basic Select/From/Take 
  1. <html>
  2. 	<head>
  3. 		<script type="text/javascript" src="/JS"></script>
  4. 		<script type="text/javascript">
  5. 			ml.onload(function() {
  6.  
  7.                 // create our query object
  8.                 var q = ml.query();
  9.  
  10.                 // select data from the 'hms/hotels' table
  11.                 q.select('HotelName').select('CityName').select('StarRating');
  12.                 q.from('hms/hotels');
  13.  
  14.                 // narrow down our example using Where Clause
  15.                 q.where('CityName', 'Contains', 'Ber');
  16.  
  17.                 // execute our query, capture results with the callback function
  18.                 q.run(function(results) {
  19.  
  20.                     // let's hand off the results for display....
  21.                     makeTable(ml.$('#results'), results.data );
  22.                 });
  23.  
  24.                 /******************************************
  25.                 	Helper Function to Layout the Data
  26.                 ******************************************/
  27.                 function makeTable(container, data) {
  28.                     // let's transpose our data in rows
  29.                     var rows = ml.data.query.Query.transposeQueryData(data);
  30.  
  31.                     // get our column headings
  32.                     var head = [];
  33.                     ml.each(rows[0], function(k,v) {
  34.                         head.push(k);
  35.                     });
  36.                     rows.unshift(head);
  37.                     data=rows;
  38.                     var table = ml.$("<table/>").addClass('CSSTableGenerator');
  39.                     ml.$.each(data, function(rowIndex, r) {
  40.                         var row = ml.$("<tr/>");
  41.                         ml.$.each(r, function(colIndex, c) { 
  42.                             var content = "<p>"+c+"</p>";
  43.                             row.append(ml.$("<t"+(rowIndex == 0 ?  "h" : "d")+"/>").html(content));
  44.                         });
  45.                         table.append(row);
  46.                     });
  47.                     return container.append(table);
  48.                 }
  49. 			});
  50. 		</script>
  51.    	</head>
  52. 	<body>
  53. 		<div id="results" style="width:100%;height:100%;"></div>
  54. 	</body>
  55. </html>
Take 
  1. <html>
  2. 	<head>
  3. 		<script type="text/javascript" src="/JS"></script>
  4. 		<script type="text/javascript">
  5. 			ml.onload(function() {
  6.  
  7.                 // create our query object
  8.                 var q = ml.query();
  9.  
  10.                 // select data from the 'hms/hotels' table
  11.                 q.select('HotelName').select('CityName').select('StarRating');
  12.                 q.from('hms/hotels');
  13.  
  14.                 // narrow down our example using Where Clause
  15.                 q.where('CityName', 'Contains', 'Ber');
  16.  
  17.                 // we only need 10 records
  18.                 q.take(10);
  19.  
  20.                 // execute our query, capture results with the callback function
  21.                 q.run(function(results) {
  22.  
  23.                     // let's hand off the results for display....
  24.                     makeTable(ml.$('#results'), results.data );
  25.  
  26.                 });
  27.                 /******************************************
  28.                 	Helper Function to Layout the Data
  29.                 ******************************************/
  30.                 function makeTable(container, data) {
  31.                     // let's transpose our data in rows
  32.                     var rows = ml.data.query.Query.transposeQueryData(data);
  33.  
  34.                     // get our column headings
  35.                     var head = [];
  36.                     ml.each(rows[0], function(k,v) {
  37.                         head.push(k);
  38.                     });
  39.                     rows.unshift(head);
  40.                     data=rows;
  41.                     var table = ml.$("<table/>").addClass('CSSTableGenerator');
  42.                     ml.$.each(data, function(rowIndex, r) {
  43.                         var row = ml.$("<tr/>");
  44.                         ml.$.each(r, function(colIndex, c) { 
  45.                             var content = "<p>"+c+"</p>";
  46.                             row.append(ml.$("<t"+(rowIndex == 0 ?  "h" : "d")+"/>").html(content));
  47.                         });
  48.                         table.append(row);
  49.                     });
  50.                     return container.append(table);
  51.                 }
  52. 			});
  53. 		</script>
  54. 	</head>
  55. 	<body>
  56. 		<div id="results" style="width:100%;height:100%;"></div>
  57. 	</body>
  58. </html>
OrderBy Ascending 
  1. <html>
  2. 	<head>
  3. 		<script type="text/javascript" src="/JS"></script>
  4. 		<script type="text/javascript">
  5. 			ml.onload(function() {
  6.  
  7.                 // create our query object
  8.                 var q = ml.query();
  9.  
  10.                 // select data from the 'hms/hotels' table
  11.                 q.select('HotelName').select('CityName').select('StarRating');
  12.                 q.from('hms/hotels');
  13.  
  14.                 // narrow down our example using Where Clause
  15.                 q.where('CityName', 'Contains', 'Ber');
  16.  
  17.                 // we only need 10 records
  18.                 q.take(10);
  19.  
  20.                 // let's control the order, ASCENDING by default
  21.                 q.orderby('CityName');
  22.  
  23.                 // execute our query, capture results with the callback function
  24.                 q.run(function(results) {
  25.  
  26.                     // let's hand off the results for display....
  27.                     makeTable(ml.$('#results'), results.data );
  28.                 });
  29.  
  30.                 /******************************************
  31.                 	Helper Function to Layout the Data
  32.                 ******************************************/
  33.                 function makeTable(container, data) {
  34.                     // let's transpose our data in rows
  35.                     var rows = ml.data.query.Query.transposeQueryData(data);
  36.  
  37.                     // get our column headings
  38.                     var head = [];
  39.                     ml.each(rows[0], function(k,v) {
  40.                         head.push(k);
  41.                     });
  42.                     rows.unshift(head);
  43.                     data=rows;
  44.                     var table = ml.$("<table/>").addClass('CSSTableGenerator');
  45.                     ml.$.each(data, function(rowIndex, r) {
  46.                         var row = ml.$("<tr/>");
  47.                         ml.$.each(r, function(colIndex, c) { 
  48.                             var content = "<p>"+c+"</p>";
  49.                             row.append(ml.$("<t"+(rowIndex == 0 ?  "h" : "d")+"/>").html(content));
  50.                         });
  51.                         table.append(row);
  52.                     });
  53.                     return container.append(table);
  54.                 }
  55. 			});
  56. 		</script>
  57. 	</head>
  58. 	<body>
  59. 		<div id="results" style="width:100%;height:100%;"></div>
  60. 	</body>
  61. </html>
OrderBy Descending 
  1. <html>
  2. 	<head>
  3. 		<script type="text/javascript" src="/JS"></script>
  4. 		<script type="text/javascript">
  5. 			ml.onload(function() {
  6.  
  7.                 // create our query object
  8.                 var q = ml.query();
  9.  
  10.                 // select data from the 'hms/hotels' table
  11.                 q.select('HotelName').select('CityName').select('StarRating');
  12.                 q.from('hms/hotels');
  13.  
  14.                 // narrow down our example using Where Clause
  15.                 q.where('CityName', 'Contains', 'Ber');
  16.  
  17.                 // we only need 10 records
  18.                 q.take(10);
  19.  
  20.                 // let's control the order, DESCENDING by appending '.desc' to the column name
  21.                 q.orderby('CityName.desc');
  22.  
  23.                 // execute our query, capture results with the callback function
  24.                 q.run(function(results) {
  25.  
  26.                     // let's hand off the results for display....
  27.                     makeTable(ml.$('#results'), results.data );
  28.                 });
  29.  
  30.                 /******************************************
  31.                 	Helper Function to Layout the Data
  32.                 ******************************************/
  33.                 function makeTable(container, data) {
  34.                     // let's transpose our data in rows
  35.                     var rows = ml.data.query.Query.transposeQueryData(data);
  36.  
  37.                     // get our column headings
  38.                     var head = [];
  39.                     ml.each(rows[0], function(k,v) {
  40.                         head.push(k);
  41.                     });
  42.                     rows.unshift(head);
  43.                     data=rows;
  44.                     var table = ml.$("<table/>").addClass('CSSTableGenerator');
  45.                     ml.$.each(data, function(rowIndex, r) {
  46.                         var row = ml.$("<tr/>");
  47.                         ml.$.each(r, function(colIndex, c) { 
  48.                             var content = "<p>"+c+"</p>";
  49.                             row.append(ml.$("<t"+(rowIndex == 0 ?  "h" : "d")+"/>").html(content));
  50.                         });
  51.                         table.append(row);
  52.                     });
  53.                     return container.append(table);
  54.                 }
  55. 			});
  56. 		</script>
  57. 	</head>
  58. 	<body>
  59. 		<div id="results" style="width:100%;height:100%;"></div>
  60. 	</body>
  61. </html>
GroupBy 
  1. <html>
  2.     <head>
  3.         <script type="text/javascript" src="/JS"></script>
  4.         <script type="text/javascript">
  5.             ml.onload(function() {
  6.  
  7.                 // create our query object
  8.                 var q = ml.query();
  9.  
  10.                 // select data from the 'hms/hotels' table
  11.                 q.select('CityName');
  12.                 q.from('hms/hotels');
  13.  
  14.                 // narrow down our example using Where Clause
  15.                 q.where('CityName', 'Contains', 'Ber');
  16.  
  17.                 // we only need 10 records
  18.                 q.take(10);
  19.  
  20.                 // let's group our data by CityName
  21.                 q.groupby('CityName');
  22.                 // select a field with an aggregate applied
  23.                 q.select('StarRating.sum');
  24.  
  25.                 // let's control the order using our aggregated field
  26.                 q.orderby('StarRating.sum.desc');
  27.  
  28.                 // execute our query, capture results with the callback function
  29.                 q.run(function(results) {
  30.                     // let's hand off the results for display....
  31.                     makeTable(ml.$('#results'), results.data );
  32.                 });
  33.                 /******************************************
  34.                 	Helper Function to Layout the Data
  35.                 ******************************************/
  36.                 function makeTable(container, data) {
  37.                     // let's transpose our data in rows
  38.                     var rows = ml.data.query.Query.transposeQueryData(data);
  39.  
  40.                     // get our column headings
  41.                     var head = [];
  42.                     ml.each(rows[0], function(k,v) {
  43.                         head.push(k);
  44.                     });
  45.                     rows.unshift(head);
  46.                     data=rows;
  47.                     var table = ml.$("<table/>").addClass('CSSTableGenerator');
  48.                     ml.$.each(data, function(rowIndex, r) {
  49.                         var row = ml.$("<tr/>");
  50.                         ml.$.each(r, function(colIndex, c) { 
  51.                             var content = "<p>"+c+"</p>";
  52.                             row.append(ml.$("<t"+(rowIndex == 0 ?  "h" : "d")+"/>").html(content));
  53.                         });
  54.                         table.append(row);
  55.                     });
  56.                     return container.append(table);
  57.                 }
  58.             });
  59.         </script>
  60.     </head>
  61.     <body>
  62.         <div id="results" style="width:100%;height:100%;"></div>
  63.     </body>
  64. </html>
Start, Take, & Paging 
  1. <html>
  2. 	<head>
  3. 		<script type="text/javascript" src="/JS"></script>
  4. 		<script type="text/javascript">
  5. 			ml.onload(function() {
  6.  
  7.                 // initialize variables for paging
  8.                 var currentPage = 1,
  9.                     startPosition = 0,
  10.                     recordsPerRequest = 10;
  11.  
  12.                 // use jQuery for button clicks
  13.                 ml.$('#previousPage').click(function(e) {
  14.                    if (currentPage > 1) {
  15.                        currentPage = currentPage - 1;
  16.                        startPosition = startPosition + recordsPerRequest;
  17.  
  18.                        // process the query
  19.                        executeQuery();
  20.  
  21.                        // update our page number
  22.                        ml.$('#pageNumber').text(currentPage);
  23.                    }
  24.                 });
  25.                 ml.$('#nextPage').click(function(e) {
  26.                     currentPage = currentPage + 1;
  27.                     startPosition = startPosition + recordsPerRequest;
  28.  
  29.                     // process the query
  30.                     executeQuery();
  31.  
  32.                     // update our page number
  33.                     ml.$('#pageNumber').text(currentPage);
  34.                 });
  35.  
  36.                 // Execute our query to get the first page of data
  37.                 executeQuery();
  38.  
  39.                 // create a function to encapsulate the query
  40.                 function executeQuery() {
  41.                     // create our query object
  42.                     var q = ml.query();
  43.  
  44.                     // select data from the 'hms/hotels' table
  45.                     q.select('CityName').select('HotelName').select('StarRating');
  46.                     q.from('hms/hotels');
  47.  
  48.                     // how many records per request
  49.                     q.take(recordsPerRequest);
  50.  
  51.                     // set our starting record
  52.                     q.start(startPosition);
  53.  
  54.                     // let's control the order using our aggregated field
  55.                     q.orderby('CityName');
  56.  
  57.                     // execute our query, capture results with the callback function
  58.                     q.run(function(results) {
  59.  
  60.                         // let's hand off the results for display....
  61.                         makeTable(ml.$('#results'), results.data );
  62.  
  63.                     });
  64.                 }
  65.  
  66.                 /******************************************
  67.                 	Helper Function to Layout the Data
  68.                 ******************************************/
  69.                 function makeTable(container, data) {
  70.                     // let's transpose our data in rows
  71.                     var rows = ml.data.query.Query.transposeQueryData(data);
  72.  
  73.                     // get our column headings
  74.                     var head = [];
  75.                     ml.each(rows[0], function(k,v) {
  76.                         head.push(k);
  77.                     });
  78.                     rows.unshift(head);
  79.                     data=rows;
  80.                     var table = ml.$("<table/>").addClass('CSSTableGenerator');
  81.                     ml.$.each(data, function(rowIndex, r) {
  82.                         var row = ml.$("<tr/>");
  83.                         ml.$.each(r, function(colIndex, c) { 
  84.                             var content = "<p>"+c+"</p>";
  85.                             row.append(ml.$("<t"+(rowIndex == 0 ?  "h" : "d")+"/>").html(content));
  86.                         });
  87.                         table.append(row);
  88.                     });
  89.                     return container.html(table);
  90.                 }
  91. 			});
  92. 		</script>
  93. 	</head>
  94. 	<body>
  95. 		<div id="results" style="width:100%;"></div>
  96.         <div style="width:25%;margin:15px auto;">
  97.             <button id="previousPage" style="float:left;margin-right:20px">
  98.                 Previous
  99.             </button>
  100.             <div style="display:inline-block">
  101.                 <strong>Page <span id="pageNumber">1</span></strong>
  102.             </div>
  103.             <button id="nextPage" style="float:right">
  104.             	Next
  105.             </button>
  106.         </div>
  107. 	</body>
  108. </html>
GeoQuery 
  1. <html>
  2. 	<head>
  3. 		<script type="text/javascript" src="/JS"></script>
  4. 		<script type="text/javascript">
  5. 			ml.onload(function() {
  6.  
  7.                 // create our query object
  8.                 var q = ml.query();
  9.  
  10.                 // select data from the 'hms/hotels' table
  11.                 q.select('HotelName').select('CityName').select('StarRating');
  12.                 q.from('hms/hotels');
  13.  
  14.                 // narrow down our example using GeoQuery - this Circle is defined by its center point and radius
  15.                 q.where('XY', 'Contains', 'WKT(CIRCLE(-91.45019397139549 44.87144275016589,285283)),COL(XY)');
  16.  
  17.                 // execute our query, capture results with the callback function
  18.                 q.run(function(results) {
  19.  
  20.                     // let's hand off the results for display....
  21.                     makeTable(ml.$('#results'), results.data );
  22.  
  23.                 });
  24.  
  25.                 /******************************************
  26.                 	Helper Function to Layout the Data
  27.                 ******************************************/
  28.                 function makeTable(container, data) {
  29.                     // let's transpose our data in rows
  30.                     var rows = ml.data.query.Query.transposeQueryData(data);
  31.  
  32.                     // get our column headings
  33.                     var head = [];
  34.                     ml.each(rows[0], function(k,v) {
  35.                         head.push(k);
  36.                     });
  37.                     rows.unshift(head);
  38.                     data=rows;
  39.                     var table = ml.$("<table/>").addClass('CSSTableGenerator');
  40.                     ml.$.each(data, function(rowIndex, r) {
  41.                         var row = ml.$("<tr/>");
  42.                         ml.$.each(r, function(colIndex, c) { 
  43.                             var content = "<p>"+c+"</p>";
  44.                             row.append(ml.$("<t"+(rowIndex == 0 ?  "h" : "d")+"/>").html(content));
  45.                         });
  46.                         table.append(row);
  47.                     });
  48.                     return container.append(table);
  49.                 }
  50.  
  51. 			});
  52. 		</script>
  53. 	</head>
  54. 	<body>
  55. 		<div id="results" style="width:100%;height:100%;"></div>
  56. 	</body>
  57. </html>
GeoQuery UI 
  1. <html>
  2. 	<head>
  3. 		<script type="text/javascript" src="/JS"></script>
  4. 		<script type="text/javascript">
  5. 			ml.onload(function() {
  6.  
  7.                 // create our map and hold a reference to it
  8.                 var map=ml.map(document.getElementById('mapdiv'));
  9.  
  10.                 // define our initial query to send in for GeoQuery processing
  11.                 var query=ml.query().select('HotelName').select('CityName').select('StarRating');
  12.                 query.from('hms/hotels').take(1000);
  13.  
  14.                 // RegionSelect options object
  15.                 // (map) [REQUIRED] reference to map
  16.                 // (callback) [REQUIRED]
  17.                 // (drawingType) [OPTIONAL default circle] either rectangle or circle
  18.                 // (query) [OPTIONAL] If provided it will return an ml.query data set to the callback
  19.                 //   (table) [OPTIONAL] table to query using the drawing
  20.                 //   (take) [OPTIONAL default 1,000] number of rows to return from table query
  21.                 //   (query) [OPTIONAL] reference to a configured ml.query
  22.                 var RegionSelectOptions = {
  23.                     map: map,
  24.                     drawingType: 'circle',
  25.                     query: {
  26.                         query: query
  27.                     },
  28.                     callback: function(data) { // data returned will be  Data JSON result from ml.query
  29.                         // empty our map container and destroy our map
  30.                         ml.$('#mapdiv').empty();
  31. 						map = null;
  32.                         // remove our button
  33.                         ml.$('button').remove();
  34.  
  35.                         // display our geoQuery results.
  36.                         makeTable(ml.$('#mapdiv'), data.data)
  37.                     }
  38.                 };
  39.  
  40.                 // ml.ui.map.RegionSelect takes one parameter, its Option object  
  41.                 var drawCircleForWkt = new ml.ui.map.RegionSelect(RegionSelectOptions);
  42.  
  43.                 // the beginDrawing() function triggers the drawing capabilities on the map.
  44.                 // this is reusable and can be called as many times as needed.
  45.                 ml.$('.ml-btn').on('click', function(e) {
  46.                   drawCircleForWkt.beginDrawing();
  47.                 });               
  48.  
  49.                 /******************************************
  50.                 	Helper Function to Layout the Data
  51.                 ******************************************/
  52.                 function makeTable(container, data) {
  53.                     // let's transpose our data in rows
  54.                     var rows = ml.data.query.Query.transposeQueryData(data);
  55.  
  56.                     // get our column headings
  57.                     var head = [];
  58.                     ml.each(rows[0], function(k,v) {
  59.                         head.push(k);
  60.                     });
  61.                     rows.unshift(head);
  62.                     data=rows;
  63.                     var table = ml.$("<table/>").addClass('CSSTableGenerator');
  64.                     ml.$.each(data, function(rowIndex, r) {
  65.                         var row = ml.$("<tr/>");
  66.                         ml.$.each(r, function(colIndex, c) { 
  67.                             var content = "<p>"+c+"</p>";
  68.                             row.append(ml.$("<t"+(rowIndex == 0 ?  "h" : "d")+"/>").html(content));
  69.                         });
  70.                         table.append(row);
  71.                     });
  72.                     return container.append(table);
  73.                 }
  74.  
  75. 			});
  76. 		</script>
  77. 		<style>
  78.             .wkt-response {
  79.                 display:none;
  80.                 background-color:white;
  81.                 padding:15px;
  82.                 border:1px solid lightgrey;
  83.                 border-radius:12px;  
  84.                 position:absolute;
  85.                 top:10%;
  86.                 left:10%;
  87.                 width:50%;
  88.             }
  89.             .mapdiv {
  90.                 width:100%;
  91.                 height:100%;
  92.             }
  93.             .ml-btn {
  94.                 position:absolute;
  95.                 top:10px;
  96.                 left:45px;
  97.             }
  98.         </style>
  99. 	</head>
  100. 	<body>
  101. 		<div id='mapdiv' class='mapdiv'></div>
  102.         <button class="ml-btn ml-btn-primary" id="region-select-trigger">
  103.           Select a Region
  104.         </button>
  105. 		<div class='wkt-response'></div>
  106. 	</body>
  107. </html>
MAPPING PLATFORM FOR BIG DATA VISUALIZATION, ANALYTICS & PUBLISHING
Request More Info