XML Public Data Genericization Where Clause Tutorial

XML Public Data Genericization Where Clause Tutorial

If you’re like me, regarding online data, you work it in your mind to work in with SQL statements, in a relational database sense.

In that sense, the first two of the three ways (while the third reminding us more of the DDL side of things) we come at improvements to our Public XML Data Google Chart Display web application have SQL corollaries for the XML XPath methodologies we’re deploying here …

• we start catering for XPath’s equivalent to SQL’s “WHERE” clause … our Javascript addfieldname function bearing the brunt of more intense coding complications via new dropdown options where user “WHERE” clauses can be asked about …
  
function addfieldname(osel) {
var wc='', little=osel.value.replace(/\=$/g, ''), andw=' and ', dotdot='';
if (osel.value != '') {
if (osel.value.slice(-1) == '=') {
if (document.getElementById('xpath').value.indexOf(']/') == -1) {
wc=prompt('Optionally enter a "where" type of clause (adding to our start for you) ... ' + String.fromCharCode(10) + ' eg. ' + little + '="lookfor"' + String.fromCharCode(10) + ' eg. ' + little + '!="notlookfor"' + String.fromCharCode(10) + ' eg. ' + little + '<998', little);
} else {
little=' and ' + osel.value.replace(/\=$/g, '');
wc=prompt('Optionally add an "and" subclause to your existant "where" type of clause (adding to our subclause start for you) ... ' + String.fromCharCode(10) + ' eg. ' + little + '="lookfor"' + String.fromCharCode(10) + ' eg. ' + little + '!="notlookfor"' + String.fromCharCode(10) + ' eg. ' + little + '<998', little);
}
if (wc == null) { wc=''; }
if (wc.trim() != '' && wc != little) {
if (document.getElementById('xpath').value.indexOf(']/') == -1) {
document.getElementById('xpath').value=document.getElementById('xpath').value.replace('/' + document.getElementById('xpath').value.split('/')[eval(-1 + document.getElementById('xpath').value.split('/').length)], '[]/' + document.getElementById('xpath').value.split('/')[eval(-1 + document.getElementById('xpath').value.split('/').length)]);
document.getElementById('xpath').value=document.getElementById('xpath').value.replace(']/', wc.replace(/\]$/g,'').replace(/^\[/g,'') + ']/');
document.getElementById('myfldselopt').innerText=('Optionally display selected field name(s) value(s)').replace('Optionally display selected field name(s) value(s)','Optionally display selected field name(s) [' + document.getElementById('xpath').value.split('[')[1].split(']')[0] + '] value(s)') + ' ...' + document.getElementById('myfldselopt').innerText.split(' ...')[1]
} else {
if (wc.toLowerCase().replace(/^\ or\ /g, ' and ').indexOf(' and ') == 0) {
andw='';
} else {
document.getElementById('xpath').value=document.getElementById('xpath').value.replace(']/' + document.getElementById('xpath').value.split('/')[eval(-1 + document.getElementById('xpath').value.split('/').length)], andw + ']/' + document.getElementById('xpath').value.split('/')[eval(-1 + document.getElementById('xpath').value.split('/').length)]);
}
document.getElementById('xpath').value=document.getElementById('xpath').value.replace(']/', wc.replace(/\]$/g,'').replace(/^\[/g,'') + ']/');
document.getElementById('myfldselopt').innerText=('Optionally display selected field name(s) value(s)').replace('Optionally display selected field name(s) value(s)','Optionally display selected field name(s) [' + document.getElementById('xpath').value.split('[')[1].split(']')[0] + '] value(s)') + ' ...' + document.getElementById('myfldselopt').innerText.split(' ...')[1]
}
dotdot=document.getElementById('xtitle').value.replace(document.getElementById('xtitle').value.split(' .. ')[0], '');
//alert(dotdot + ' ... ' + document.getElementById('xtitle').value);
document.getElementById('xtitle').value=document.getElementById('xtitle').value.split(' -- ')[0].split((' .... ' + (document.getElementById('myfldselopt').innerText + '[]').split('[')[1].split(']')[0] + ' .. ').replace(/^\ \.\.\.\ \ \.\.\ /g, ' .. '))[0] + (' .... ' + (document.getElementById('myfldselopt').innerText + '[]').split('[')[1].split(']')[0] + ' .. ').replace(/^\ \.\.\.\ \ \.\.\ /g, ' .. ') + dotdot;
}
osel.value='';
} else {
if (document.getElementById('xpath').value.indexOf('/*[self::') == -1) {
document.getElementById('sask').innerHTML=saskih;
document.getElementById('xpath').value=document.getElementById('xpath').value.replace('/' + document.getElementById('xpath').value.split('/')[eval(-1 + document.getElementById('xpath').value.split('/').length)], '/*[self::' + osel.value + ']');
document.getElementById('myfldselopt').innerText=document.getElementById('myfldselopt').innerText.split(' ...')[0] + ' ... ' + osel.value;
document.getElementById('xtitle').value=document.getElementById('xtitle').value.split(' -- ')[0].split((' .... ' + (document.getElementById('myfldselopt').innerText + '[]').split('[')[1].split(']')[0] + ' .. ').replace(/^\ \.\.\.\ \ \.\.\ /g, ' .. '))[0] + (' .... ' + (document.getElementById('myfldselopt').innerText + '[]').split('[')[1].split(']')[0] + ' .. ').replace(/^\ \.\.\.\ \ \.\.\ /g, ' .. ') + osel.value;
} else {
document.getElementById('xpath').value=document.getElementById('xpath').value.replace(/\]$/g, ' or self::' + osel.value + ']');
document.getElementById('myfldselopt').innerText+=',' + osel.value;
dotdot=document.getElementById('xtitle').value.replace(document.getElementById('xtitle').value.split(' .. ')[0], '');
document.getElementById('xtitle').value=document.getElementById('xtitle').value.split(' -- ')[0].split((' .... ' + (document.getElementById('myfldselopt').innerText + '[]').split('[')[1].split(']')[0] + ' .. ').replace(/^\ \.\.\.\ \ \.\.\ /g, ' .. '))[0] + (' .... ' + (document.getElementById('myfldselopt').innerText + '[]').split('[')[1].split(']')[0] + ' .. ').replace(/^\ \.\.\.\ \ \.\.\ /g, ' .. ') + dotdot + ',' + osel.value;
}
osel.value='';
}
}
}

• we “dip our toes” into catering for sorted data which is akin to SQL’s “ORDER BY” clause … our version of “dip our toes” looking for cases of date format YYYY-MM-DDThh:mm:ss style data we are always interested in sorting …
  
var numtabcols=[], recsare=[], kin=0, okayhere=true, swaponeandzero=false;

numtabcols=datasofar.split('~')[1].split('~')[0].split(String.fromCharCode(32));
if (eval('' + numtabcols.length) >= 2) {
if (eval('' + numtabcols.length) > 2) {
for (kin=2; kin<eval('' + numtabcols.length); kin++) {
if (numtabcols[kin].trim() == '') {
okayhere=false;
} else if (numtabcols[kin].trim().replace(/\./g,'').replace(/\-/g,'').replace(/0/g,'').replace(/1/g,'').replace(/2/g,'').replace(/3/g,'').replace(/4/g,'').replace(/5/g,'').replace(/6/g,'').replace(/7/g,'').replace(/8/g,'').replace(/9/g,'') != '') {
okayhere=false;
}
}
}
console.log('okayhere=' + okayhere + ' and numtabs.length=' + numtabcols.length);
if (numtabcols[1].trim() != '' && okayhere) {
if (numtabcols[1].indexOf('T') == 10) { swaponeandzero=true; }
console.log('Okayhere=' + okayhere + ' and numtabs.length=' + numtabcols.length + ' +++ ' + datasofar.split('~')[1].split('~')[0] + ' ... ' + numtabcols[1]);
if (swaponeandzero || numtabcols[1].trim().replace(/\./g,'').replace(/\-/g,'').replace(/0/g,'').replace(/1/g,'').replace(/2/g,'').replace(/3/g,'').replace(/4/g,'').replace(/5/g,'').replace(/6/g,'').replace(/7/g,'').replace(/8/g,'').replace(/9/g,'') == '') {
console.log('OkayHere=' + okayhere + ' and numtabs.length=' + numtabcols.length);
console.log(datasofar);
// [~2011-12-01T00:00:00 22.88~,2]
var inpts=datasofar.split('[');
var origst='', newst='', arrorig=[];
for (var jinpts=1; jinpts<inpts.length; jinpts++) {
origst='[' + inpts[jinpts].split(']')[0] + ']';
arrorig=origst.split(' ');
if (eval('' + arrorig.length) >= 2) {
//newst=arrorig[0] + '~,' + arrorig[1].split('~')[0].replace(/\ /g, ',') + ']';
if (swaponeandzero) {
newst='[~' + arrorig[1] + '~,' + arrorig[0].replace('[~','') + ',' + origst.split(arrorig[1] + ' ')[1].split('~')[0].replace(/\ /g, ',') + ']';
} else {
newst=arrorig[0] + '~,' + origst.replace(arrorig[0] + ' ','').split('~')[0].replace(/\ /g, ',') + ']';
}
datasofar=datasofar.replace(origst, newst);
document.getElementById('xlvalue').value='Measure';
document.getElementById('xbvalue').value='Measure';
if (eval('' + arrorig.length) > 2) {
for (kin=2; kin<=eval('' + arrorig.length); kin++) {
document.getElementById('xlvalue').value+=',Measure';
document.getElementById('xbvalue').value+=',Measure';
}
}
}
}
recsare=datasofar.split(',[');
//alert(datasofar.substring(0,40).replace(/\ /g,'x') + ' ... ' + recsare.length);
recsare.sort();
datasofar='';
for (kin=1; kin<recsare.length; kin++) {
datasofar+=',[' + recsare[kin].trim() + ' ';
}
//alert(datasofar);
console.log('sort=' + datasofar);
}
}
}


if (datasofar.split('~')[1].split('~')[0].indexOf('T') == 10 && eval('' + recsare.length) == 0) {
recsare=datasofar.split(',[');
//alert(datasofar.substring(0,40).replace(/\ /g,'x') + ' ... ' + recsare.length);
recsare.sort();
datasofar='';
for (kin=1; kin<recsare.length; kin++) {
datasofar+=',[' + recsare[kin].trim() + ' ';
}
console.log('sort=' + datasofar);
}

• we add a field name “data type” into the mix

And so, further to yesterday’s XML Public Data Genericization Field Names Tutorial we have an “Air Quality in New York USA” …


/response/row/row[name='Nitrogen dioxide (NO2)' and geo_place_name='Bedford Stuyvesant - Crown Heights']/*[self::start_date or self::data_value]


… XML XPath idea presented with some (of what we’ll have trouble not thinking of as) “WHERE” clauses, as well as being a small enough dataset, as to suit more more devices trying this in the changed histogram_pie.html web application for you to try.

Previous relevant XML Public Data Genericization Field Names Tutorial is shown below.

XML Public Data Genericization Field Names Tutorial

We come at improvements to the functionality of yesterday’s XML Public Data Genericization XPath Tutorial‘s XML Public Data Google Charts Display web application from two angles …

• we allow for an “early days” Geocoding data Google Chart Geo Chart display option … “early days” because we took advantage of some obvious conditions (ie. within data a POINT (longitude latitude) data format was being used) in the data we tested with, but “data being data” am sure we need to test more XML data here … and …
• we add in an XML data field name dropdown the user can use to tailor what fields comprise what they are interested in (using Ajax laced Javascript code below) …
  
function stateChanged() {
if (myxhr.readyState == 4) {
if (myxhr.status == 200) {
var fns=myxhr.responseText.split('"fieldName" : "');
for (var ifns=1; ifns<fns.length; ifns++) {
if (ifns == 1) {
arrflds=[];
fieldsel='  or  <select onchange=addfieldname(this); id=myfldsel><option id=myfldselopt value="">Optionally display selected field name(s) value(s) ...</option></select>';
}
if (fns[ifns].indexOf(':@') != 0 && fns[ifns].trim() != '') {
arrflds.push(fns[ifns].split('"')[0]);
fieldsel=fieldsel.replace('</select>', '<option value="' + fns[ifns].split('"')[0] + '">' + fns[ifns].split('"')[0] + '</option></select>');
}
}
if (document.getElementById('spanflds')) {
document.getElementById('spanflds').innerHTML=fieldsel;
}
}
}
}

var xurlis=location.search.split('xurl=')[1] ? decodeURIComponent(location.search.split('xurl=')[1].split('&')[0]).replace(/\+/g,' ') : "//data.ny.gov/api/views/d6yy-54nr/rows.xml";

var arrflds=[];
var myxhr = new XMLHttpRequest();
myxhr.open('GET', xurlis.replace('/' + xurlis.split('/')[eval(-1 + xurlis.split('/').length)], '/'), true);
myxhr.responseType = "text";
myxhr.onreadystatechange=stateChanged;
myxhr.send(null);

  … to pass onto the Google Chart displays

… in histogram_pie.html with this link there for you to try it out for yourself.

Previous relevant XML Public Data Genericization XPath Tutorial is shown below.

XML Public Data Genericization XPath Tutorial

You might have gathered from our current blog posting thread that XML XPath usage can be a bit of an art form. As such, we’ve had a day of XPath tweaking, such that an XML XPath path like …

… ideas may interest some people reading this blog posting, we’re hoping.

This also got us to consider using Google Chart …

• Column Chart
• Bar Chart

… to output findings when the user is deciding to deploy some of these ideas, extending the work of yesterday’s XML Public Data Genericization Sharing Tutorial with respect to the changed new version of our histogram_pie.html arrangement with this link.

Previous relevant XML Public Data Genericization Sharing Tutorial is shown below.

XML Public Data Genericization Sharing Tutorial

It can be some job to decouple hardcopy “particulars” of a web application “starting scenario” to “the ultimate genericization solution”, and, of course, often it is futile to try. But, further to yesterday’s Powerball Number Pick XML Genericization Tutorial start on this, the “two elephants in the room” remaining beyond their welcome, would have to be …

• twinning Google Chart Histograms and Pie Charts … as the only output means … and …
• XML … as the only input data format

… since …

• there are many other chart types … and …
• there are many other data formats … as a peruse of USA’s data.gov will undoubtedly show you

That second “XML” decoupling, of course, to many a programmer (but maybe so ho hum to non-programmers) “turns their wooooooorrrrrrrllllllllddddd upside down” (and nobody wants to be confronted by a “dddddllllllllrrrrrrrooooooow” on the loose), while we can imagine, fairly easily, starting to incorporate other apt Google Chart types into the mix. But we have to walk before we run, or even amble. And so, today, along with …

• sharing (via email or SMS, covered by adding two new relevant form buttons with type=submit) code …
  
var xsmsis=location.search.split('sms=')[1] ? decodeURIComponent(location.search.split('sms=')[1].split('&')[0]).replace(/\+/g,' ').replace(/^SMS$/g, '') : "";
var xemailis=location.search.split('email=')[1] ? decodeURIComponent(location.search.split('email=')[1].split('&')[0]).replace(/\+/g,' ').replace(/^Email$/g, ' ') : (document.URL.indexOf('email=') != -1 ? " " : "");

if (xsmsis.trim() != '' && xsmsis.replace(/\,/g,'').trim().replace(/0/g,'').replace(/1/g,'').replace(/2/g,'').replace(/3/g,'').replace(/4/g,'').replace(/5/g,'').replace(/6/g,'').replace(/7/g,'').replace(/8/g,'').replace(/9/g,'') == '') {
aso = document.createElement("a");
document.body.appendChild(aso);
aso.style = "display: none";
aso.href = 'sms:' + xsmsis + '&body=' + encodeURIComponent(document.URL.replace('&email=', '&emNOWAYail=').replace('&sms=', '&smNOWAYs='));
aso.click();
setTimeout(() => {
document.body.removeChild(aso);
}, 100);
}

if (xemailis != '') {
aeo = document.createElement("a");
document.body.appendChild(aeo);
aeo.style = "display: none";
aeo.href = 'mailto:' + xemailis.trim() + '?subject=Google%20Chart%20Histogram%20and%20Pie%20Chart%20XML%20Data%20Display%20...&body=' + encodeURIComponent(document.URL.replace('&email=', '&emNOWAYail=').replace('&sms=', '&smNOWAYs='));
aeo.click();
setTimeout(() => {
document.body.removeChild(aeo);
}, 100);
}

  … we …
• introduce an “Ideas …” dropdown … with its onchange event logic below …
  
function populate(selo) {
if (selo.value.trim() != '') {
var pts=selo.value.split('#');
for (var ipts=0; ipts case 0:
document.getElementById('xurl').value=pts[ipts];
if (pts[ipts].indexOf('//data.ny.gov/api/views/d6yy-54nr/rows.xml') == -1) {
document.getElementById('xbasis').value='count';
document.getElementById('xcountbasisposition').value='0';
document.getElementById('xcountbasisposition').type='number';
}
break;

case 1:
document.getElementById('xpath').value=pts[ipts];
break;

case 2:
document.getElementById('xtitle').value=pts[ipts];
break;

case 3:
document.getElementById('xdelim').value=pts[ipts].replace('+',' ');
break;

case 3:
document.getElementById('xbasis').value=pts[ipts].replace('+',' ');
break;

default:
break;

}
}

}

}

  … where we help the user fill out those tricky “form textbox asks” for data sets with topics of interest

… and we hope by experimenting along these lines of thought, we’ll chip away at it, and make some other Google Chart types come into play, into the future, with this current Public XML Data URL Based web application we have going.

And so, feel free to try the changed new version of our histogram_pie.html arrangement with this link.

Previous relevant Powerball Number Pick XML Genericization Tutorial is shown below.

Powerball Number Pick XML Genericization Tutorial

There have been many projects where we start with hardcoded wording involved in the webpage, and then to move on, we parameterize (or genericize) those hardcodings via …

• HTML form … user data collection …
• gathered together via a method=GET form into an “argument rich” follow up URL navigation … calling the same webpage …
• and displaying results according to those user defined HTML form inputs

Yesterday’s Powerball Number Pick Counts XML Tutorial project is no different. We saw that potential hardcodings were to do with …

• chart title
• XML data public URL
• XML XPath
• data delimiter

… and then we stopped and thought a bit about “value adding”. Was “Count” as a “measure basis” the only “measure basis” of any relevance for the Histograms and Pie Charts? In the end we continued …

• Count … as the default “measure basis” … to which we added (all reducing a set of numbers to one representative value in the case of Powerball Winning Numbers, at least) …
• Count of an ordinal position according to the data delimiter and the data itself (doh!)
• Mean (if numerical)
• Median (if numerical)
• Mode (if numerical)
• Sum (if numerical)
• Range (if numerical)
• Standard Deviation (if numerical)

… could all be “value add” evaluations a user might find interesting, even just playing around with the Powerball NY US 2010 XML data.

See how we got to our more generic HTML and Javascript histogram_pie.html arrangement with this link.

Previous relevant Powerball Number Pick Counts XML Tutorial is shown below.

Powerball Number Pick Counts XML Tutorial

We’re starting a …

• public data source … repository based …
• XML … data based …
• Ajax … driven (and thanks to W3Schools here, for the XML XPath basis in code) …
• clientside web application … outputting, in this first draft (which is very “bare bones”), as outputs …
• Google Charts …
  1. Histogram Chart
  2. Pie Chart

… new powerball_us_ny_winning_numbers_since_21010.html web application project today. Why those two charts? Well, they give different views of built up data pairings, involving two data members, one “subject item” (which today, happens to be numerical, but does not have to be) and the other numerical data item having a relationship with that “subject item” as a measure, in today’s case that being a count of …


New York State, US Powerball Number Pick Counts since 2010


Yes, you can glean many things from the public online data resources out there, today’s one for us coming from the US Governmental data.gov … thanks all around.

In the code, what is the crucial single codeline, at least for us? It’s …

… this codeline’s “slicing through complexity” cut through, perfectly suited to the XML data scenario above, presenting those Google Chart displays, as the end result.

If this was interesting you may be interested in this too.

If this was interesting you may be interested in this too.

If this was interesting you may be interested in this too.

If this was interesting you may be interested in this too.

If this was interesting you may be interested in this too.

If this was interesting you may be interested in this too.