These are the notes from my presentation: http://www.meetup.com/Elasticsearch-Philadelphia/events/164506092/
http://youtu.be/6Idi13rQx1o?t=12m39s
Elasticsearch introduced aggregations into their 1.0.0 version. This allows you to create analytics's for your data inside Elasticsearch.
The data for this was provided by the City of Philadelphia. James Tyack transformed the data into Elasticsearch's format for loading.
https://github.com/CityOfPhiladelphia/phl-energy-benchmarking-data/blob/master/Benchmarking_Data_Public.csv
https://github.com/jamestyack/rubyOpenDataTools/blob/master/philly_energy_benchmark_data/phl_energy_benchmarking_elastic_upload.json
The data model. Some notes anything I intend to sum or average I made either a double or long. Also, I used a keyword tokenizer for "PROPERTY_TYPE" to keep the categories together. Otherwise the aggregations would have rolled up by a single word and not the whole category.
$ curl -XPUT 'http://localhost:9200/philly_energy' -d '
{
"settings": {
"number_of_shards": 5,
"analysis": {
"analyzer": {
"key": {
"tokenizer": "keyword",
"filter": [
"lowercase"
]
}
}
}
},
"mappings": {
"building": {
"properties": {
"WEATHER_NORMALIZED_SITE_EUI": {
"type": "double"
},
"ELECTRIC_USE": {
"type": "long"
},
"TOTAL_GHG_EMISSIONS": {
"type": "double"
},
"OIL_USE": {
"type": "double"
},
"SECTOR": {
"type": "string"
},
"PERCENT_BETTER": {
"type": "double"
},
"BLDG_FLOOR_AREA": {
"type": "long"
},
"ENERGY_STAR_SCORE": {
"type": "string"
},
"YEAR_BUILT": {
"type": "long"
},
"GAS_USE": {
"type": "double"
},
"STEAM_USE": {
"type": "double"
},
"SITE_EUI": {
"type": "double"
},
"ASSET_ZIP": {
"type": "string"
},
"SOURCE _EUI": {
"type": "double"
},
"ASSET_ADDR": {
"type": "string"
},
"FACILITY_DESCRIPTION": {
"type": "string"
},
"PROPERTY_TYPE": {
"type": "string", "analyzer" : "key"
},
"WEATHER_NORMALIZED_SOURCE_EUI": {
"type": "double"
},
"NUMBER_OF_BUILDINGS": {
"type": "long"
},
"ASSET_ID": {
"type": "string"
}
}
}
}
}'
{"acknowledged":true}
$ curl -X PUT 'http://localhost:9200/philly_energy/_alias/pe'
{"acknowledged":true}
Load the data assuming you downloaded the data into /tmp/pe.dat.
$ curl -s -XPOST localhost:9200/_bulk --data-binary @/tmp/pe.dat
{"took":105,"errors":false,"items":[{"create":{"_index":"philly_energy","_type":"building","_id":"jZD9hwX7TEm_VWV3G2UDfQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"d5RKm33gTKaXUuFv52xpTw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"TresrqkFS3K-qvWBbCYAqw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"migmCgp3RYKhTUbi9Po7tA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"yC8MolceR-mTN3C2cZqjbw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"lSqQrAiPTk6eeG-RrAnu9w","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"9grAtNzcTgO6sPuZ7H6PNw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"pk58AXSvSgGqhuzeUH2fuA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"NI9GEFHERb6OgGXRhltPzA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"fQJaKz7hQCuyzfukSQ0mAA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"kA0E-4SiSDGJTkmMGRZJKw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"aDc_xQgARhq5QyuHg6aXVw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"7ryDa-GfSiqD7mXPwloh0w","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"YMJBo-tkRXKriXxvA7FP8Q","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"uJu7XmiqTdmJOxRlL11NYA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"vd6ZZPZyQFSTGnVsl8QnPw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"hw5lKn7hRr26MAigUucH6A","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"DRuB3YDaTwW0thDkJSNGFg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"a4W2GMyoRd-3pvy07KXmJA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"BpKphzsyS4a5-DvjHNy52w","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"QVm2ImUYRj2_YSfaAr6wBg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"-A4ruNTyT2CTXgfi308ZKg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"JTQTngqjTEGonKG3pHJI7A","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"PZ86P-laS1yioPux2_OJ-g","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"z3rv4vobQlKTGG4JofNLKQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"6JyGiHkKR3uQPGeijzvs9w","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"jA75x06_QpiS9KHNhemHmg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"OPecoX-pRXeYah8gbuyACQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"eaT_35LcTM2f6wgTN52pAQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"rN4969B-R4q9qZddjBXFrw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"6KCend1IQUKLlmvYtW_peA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"6rUa1ohWRYuNHTqh96Xblg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"hONw88VHQjKZR_ureQFVqg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"w6o5u4_QTYGGDOzbPoQUrA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"cYtHoa0qR2GbwYnBrluL5g","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"pjQ6CYfAQXmwshfNB8vm0Q","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"91202-fyQXikkQ0csnfZQQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"4KZpUUfRR6WjGi4bIhzbww","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"p_Pfm_ktT0mC6Aa9V74IiA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"hKku4TjqQkabWMQILe8K5Q","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"Ssokym6jTWe69bv2DOlVsQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"hKCUoUKIQci6TwRObaowbQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"tjJgRMBWTN6te8fnmySqvQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"xzx0rpiATQ64BGJrQ2A0Lw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"sRpBZ4fWRgqkuuT6smjV0Q","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"KqM5c585RvOgHhkDrgspgg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"TFo0lN4xQ8-cDiCECxTzNQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"utt89kHSSbSW4M6Jrakb2w","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"cdyuVZqgTuCTv-HtFZVhXg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"YK0RG3rmRp6Bc78FGj-Jww","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"cLOl4OyxTJO-1zJWKUTBYQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"jI5zMLXSQaykXXc_DunjZA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"CnsRzzZzThiNILSQaaEQgQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"nHP4dlImSviSyK5sa1VCWA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"WPP_yIlVR-Kw7yMjhOJ37w","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"0F9bTwLFQPSGr8Vy4V96vA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"CCXGGBx1TYmG-_TP4sIp_A","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"6A1TBU28Ri22n7rdnyRbSw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"c-MpTEiXT0-NhTfwGHtQiw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"Hi-UWbSCSxKVly0fFWr8rw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"xNScIlGCQg2HMDDKNemoqw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"qNqHfjPMSzuuRKE30q_GWw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"sDWOMEUsSja65dnLwhIOcQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"hM5b4Z1GQ7qbaCamQ22ssA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"TCEfbU1aRzelgAR-0hlusA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"E_2T4_-1Q1WxcQiLc0xnhw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"c1-ZvLSTTAGowxi_vAYDFw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"RjaCYofqQISbwEH6PmeosQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"x4q5IpsaT8imMKHMniZC8A","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"cN2KU0F5TS-GL7hL-wszWw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"YAmmp30IR8yJpIXRPlecaA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"-4I8YGPnQoaQrJLsN51G3Q","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"E7rNyxQKS6OKjdxx2THmVg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"3KZ4SrkgQ-qsoA4t8LsPyQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"6Q4XYRtkTz6mBAAh932nUQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"VfycIKg-R-WqIljhxU8Q4Q","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"oqKOn4yiSDKg_yeAgz1LWg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"65lT1L0PR7u2sHl_PFOhGw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"O1c1vQVOSd2Abm9MiGokZQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"roFhfV7YSxS0NLzS1l9M8Q","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"GrcxXeuiSQudjqDOhiWpZA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"qs37-dPJS-ysXRQO76d0Sg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"qgHmJD1ISTCa0P49W6flUg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"ifhNYW1xRWOVfD_IiajrCA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"J1e2DEgsRaidLOh0Ut_dEg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"fyXTWtZzTqyBMf5ox60OFQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"_S2IlZpaQIqehqMTFsAlFw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"SOetHn-FTRumd50uc93aPg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"OYcErWUTRZCjzhWWmUCOyQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"9Bq9P3HJQjqmKwOpCY6k4A","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"11nQwGkXTHGySGmJN01Lyw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"e6zRCLY7Tzqgf4gitrgo6g","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"WybvumB5Ti6gHMcqgH4I2A","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"FvvI5DEFQ-ysrug91t1XUQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"YKZFeIj-Q2S0prEqSIYW1A","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"DQRy9ASlRmeyOSJ5-j0M9g","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"DVBsPSiPQ0GVlMdV2FOhGg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"Resw5iiDT8qGguZqoYyD8w","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"rBakQefjRz6GBEjzJMFh2A","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"_aDOYuv9Rz2igVCM2S4JPg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"ocXtB3tsTx-g0taaWBDkIQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"uLphu9G5RhGxdkCb9uCJCw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"6tX93bqWRzay6UPgewsNPA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"f0o6xpz-SUeSACRHU9bHGw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"KO6nkG9NTF6rpNU-aeLc6Q","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"aux8rN5XRFurU7SScc6Fmg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"hqNcF8eMSoOj74gkVApGUg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"rf_47xQ1TuibahTcgFkDGg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"l0qxz9UvTLWqQKyKhgZOQQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"QaWx3xw7SDWFEmf06cMRAw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"4iHpZ9C3RnCsbYFbjidalA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"xthI877oR0ycdqQwFMRjVA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"92B5V3srQKmb0Eb0QKY7Pw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"0fskFrTJSBOAP2e6af_LUQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"JoduknymQG2TJxB5OX5d6g","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"X0OfWnmUSca0o4tNzN_9tQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"i3ZtfDQpTKitSA41YG8YmA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"7BJaQp54Sd-O1wfe_mshDQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"hNwd8xJYRnSTXQtlyz4kgA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"8XtuEdhRQtuoRuZos6XysQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"nGdk4D69TZqb0np4eDuj1g","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"Hb0sbflFQt-Esqk2nwkbrA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"rhYP4lfATaWahoEkFfl8kQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"iEmRrY4NTm6IKEnHlU8EJg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"jxKxFSZHRmOo7Qx_hjzSPg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"jRdfK2WAQnm22YvwaDakrA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"eQqq1X0RTnqS7F2lzib6AQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"Yx2dRTz7TtixR1NA-OHQGA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"DptHnqDBQlOPRKEsPv_p8g","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"kph_4zu0QpWBOhvAKrojzA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"K5CaASmoS7i1l6StF0psJA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"tiA1ni7-REycfcz1S7gADw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"Y8yUKpHiSIGx-IysVrADmQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"Z_beIQcdTAS61msM0fC_2Q","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"4n0_w-S6SsG4jVhhLXQwPw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"9ZfZAH4RQFKL2Rnabh1yIA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"oBkIPW8WS1-bRNM4VgJLnA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"gKirGMG3TpOT69hf2SiQoQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"HkQmDAJXRHqk5HxVrzIK2w","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"1qSA8gVpQEOi___2Mb0nyA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"0bIl8CahSBq27jKw1_bJmQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"sw-2RmEuRAqKr1KtQIvO6Q","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"uvxKk_I8Qv-H5sTeyM4kDQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"B4Mv48X2TNupNDJDiNqcLA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"MGl7Ak5ORnGQ5JoUONZM2w","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"_gxF0cGKRLKVaM5-uwgQzw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"P_HVTiibRqKqVivwh9clBg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"X9_5Rk-ORDaBI85J84m4Eg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"QnlEcw1QT6KmNe7BgWFwZA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"woosPLYsS3y8XqMleGIp5Q","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"TZvXO0GRTraqMIBJTKGtDA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"GoE6a0wmReKBSy1NaXXmFg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"fSuPoF-ASiaSB56SDdqNQQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"QNHfLgBaT5i0HTz_4VWm7Q","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"gQPGPssdRq6AeII8rT66RQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"6Osl4kLgRcORqR1-FM3Xjw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"IVSDg-CJQ0-oE0Th0H_kHw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"Epnd3nM3TNWk0IzHP4m1Yg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"0CTj1JwUTo2gfn41Q1o8JQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"HcBzhC8sQy-gmJZpZtUYnw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"vg688zdTRi2o1NjIQWBZWQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"3OzNDAcCRq2LHD2VMrLdYw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"o2N8q7WzR6CWfE4d2a7HVA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"6KP9fwYoRViEp-fJF-1Prg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"ZuS8qvPVTmSu5-a75E-T7Q","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"sohp7XJjSxGIgrU1QlFlcw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"sx8q8viXQfuVK8g_WDxi_g","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"YrfwcuKDSxqtwD1eGCkQgw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"RqAiXaADR2ilnf6fNIQzAA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"Vo9dxHOYSM2Tz831m2CzQQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"6gO5kTwqRJ-HJ1rhCWIedw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"9-nj7zeeTsKZt3qkWg6wvg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"8h2hVf6FQkylIVZDrUB6Aw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"wpmj0g0kRdGI4SFQM8pzlQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"SgRTXoY-QTKk9AJOJ6-lXQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"Nz92MErwTMye-N1OSZBGOw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"Pk7d5DavTG2gqw__DRX9Qg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"ltE0Y6M0T26lyLsrkzOKtA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"se_6642URFedZXcwOL-N8g","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"UagLDdasR8GkNf2zT4kmgw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"-yAXB48sTUS2fgKslVaKBQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"olqwNMt8ROuoRknAucJNrw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"B1VvggxwRmWzYqL_JwAlWg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"xPpkOTSTTVeULOW9pJ92BQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"_7D8i7JZT5yfTqnPPEn65Q","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"bred3tbKSnSL8mtTTN4LPQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"Rqg-RcolQiCKmUQtBUa7_w","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"Qv3oRyWsTLK_D2sekrMUDg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"6LTHuQw4QWaSE8-fQEWe5g","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"XdsMH3SCSBKgP3lfk2bF8Q","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"wIxPtuJLStCcjQleYv677g","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"1L4sVmYuQMW2w4plBqJmdA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"Iifs4sSpS_OLN9nDUb4mug","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"jYXotFQjQjWSi_UoJwXT2A","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"gjXWttrzShyoUCnkZxuJyg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"aui2O1G_RoepIZcxL4Z5gg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"vLPk2-uKQ9SEJQH6kCokvQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"gqGS9wRTTMOP4zr26CRLhQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"pd2PeAauQlKVyhTUGEZcHg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"PYafcu8ZSvC7RXIi2t_78w","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"8y5709ruS8K0avG0lf-D0g","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"WwZE7zVIRIy31VpSPJ3F9w","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"5uslg5_bSeWZ9p6zwpTM8Q","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"etTu8ohZSxKWrdafLtf9Kg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"XqgetrO9QqWYMzYneAFQRA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"Q2o2n2oVS1O4aESgoHbZXg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"IcDQHLIWTTuAC3UB1-o_-g","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"uUySsLSDSFCKkkrQWivXQw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"dWN310uJSiivq941kU62Rg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"zyzSlJJyS9yum5qGtqAe4A","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"YS9sur_FSoCif72Y2hRkBQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"OTxp13xJQwKEHrafda5_UQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"9yHYftusRBSKwjohKBUtKg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"3LoCUz2VR0-loKN5v4HlOA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"atwGgu0zS9COzRYmvLqSZw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"Bw2e4VuxSOCf0uleeRa_Wg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"V3aQXSorRcCccHh_wNnXAg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"6SDlTDWLTXi4zA_I7zXH6A","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"r2Up-WJ8SMm7OqGL6GEG1w","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"uGasV8qCSjGsup_Z40sIkQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"NY5TUCKITCaNtNaB03ekTw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"gM6-Agk6RVGWflwkrQ_P4A","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"Glf8eSV6Qzy7Pg7y-v3cGA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"qzr65awRSUuDwnd3JdTclw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"a4voWN4yT2Oz_FN15R9YxQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"Wqh5IrivT_GGT4LD6zwSFA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"BiAQO4JJQvq5MolPSQHcUQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"wVf52zpJQb2fcZ79BB8YAQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"poZaeJCDQimsnYlZyxVW8A","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"FtK6YKPJR8OWq7nJIPh_Bg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"uPsJBTMPR2SY0GkRBdo3yA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"jN-DM-_IQwuxWbyNgHPimw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"DUifP8ZtQTu7FFrPSOLYag","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"1dYi9Yb0SxmZwq-TmEsrRg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"nOhf7I-cTyiYVMjjAFU3tA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"xyL2deXbSsK79_vV2Q3Rew","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"D_VRktEKRGSJDBT1oOoOwg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"f_T_ZUGmSj-MEJYzEKYWmw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"s7mk8jVtQ_6oo4cIRqIzCA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"5I2Xveg0QdGOn0e67BxsJw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"RtZmKAxNRLigEP2CjJQEKQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"H7IorpHvSaaH812L9cFg7A","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"41K2Ii8USdu_6U2bVf__OQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"JR7ROqm_QRagzXV2eAusgQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"rn9VatTGRcywfnkeUYm9Nw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"pBWm787ETv-C0yZyT-t6Jw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"9AYn0oZoRhacIcSdKpySWw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"C2PBBP7lTDiPlrk1VH7pRw","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"ab1eowikTGSvj-gq0QXTRQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"DN615cr9RUmAMqDKjEFL5Q","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"FfTWDNzFSXyUPKouGEx9lg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"x7LsVktqTAOW959G6XO-JQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"utqvXUwDQ3Giqmxha-CiaA","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"mWj9dzjgRMGntA2VD6479w","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"wUmqIVi5QyO7I7IVCHTfSQ","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"k7iHOpK6TIq5FU1Fu0NPRg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"R2Gbz524R2eot9kw7t7z-A","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"XOAqG86DTmSA5-3tapcyPg","_version":1,"status":201}},{"create":{"_index":"philly_energy","_type":"building","_id":"rs1G0QS6R5WZ6oaHM3s7qw","_version":1,"status":201}}]}
The old way to do facets in elastic search. This is still very powerful but, the new way is even more powerful.
$ curl -X POST 'http://localhost:9200/philly_energy/building/_search?pretty=true' -d '{
"query": {
"match_all": {}
},
"facets": {
"tags": {
"terms": {
"field": "PROPERTY_TYPE"
}
}
},
"size": 0
}'
{
"took" : 4,
"timed_out" : false,
"_shards" : {
"total" : 5,
"successful" : 5,
"failed" : 0
},
"hits" : {
"total" : 259,
"max_score" : 0.0,
"hits" : [ ]
},
"facets" : {
"tags" : {
"_type" : "terms",
"missing" : 0,
"total" : 259,
"other" : 21,
"terms" : [ {
"term" : "other - recreation",
"count" : 80
}, {
"term" : "fire station",
"count" : 50
}, {
"term" : "library",
"count" : 46
}, {
"term" : "police station",
"count" : 21
}, {
"term" : "repair services (vehicle, shoe, locksmith, etc.)",
"count" : 11
}, {
"term" : "office",
"count" : 8
}, {
"term" : "medical office",
"count" : 7
}, {
"term" : "museum",
"count" : 6
}, {
"term" : "prison/incarceration",
"count" : 5
}, {
"term" : "other - public services",
"count" : 4
} ]
}
}
}
An example with a script
$ curl -X POST 'http://localhost:9200/pe/building/_search?pretty=true' -d '{
"query": {
"match_all": {}
},
"facets": {
"tags": {
"terms": {
"field": "PROPERTY_TYPE",
"script": "term == \u0027fire station\u0027 ? true : false"
}
}
},
"size": 0
}'
{
"took" : 5,
"timed_out" : false,
"_shards" : {
"total" : 5,
"successful" : 5,
"failed" : 0
},
"hits" : {
"total" : 259,
"max_score" : 0.0,
"hits" : [ ]
},
"facets" : {
"tags" : {
"_type" : "terms",
"missing" : 0,
"total" : 259,
"other" : 209,
"terms" : [ {
"term" : "fire station",
"count" : 50
} ]
}
}
}
This is the new way to do a facet in Elasticsearch 1.0.0.
$ curl -X POST 'http://localhost:9200/philly_energy/building/_search?pretty=true' -d '{
"query": {
"match_all": {}
},
"aggs": {
"by_type": {
"terms": {
"field": "PROPERTY_TYPE"
}
}
},
"size": 0
}'
{
"took" : 6,
"timed_out" : false,
"_shards" : {
"total" : 5,
"successful" : 5,
"failed" : 0
},
"hits" : {
"total" : 259,
"max_score" : 0.0,
"hits" : [ ]
},
"aggregations" : {
"by_type" : {
"buckets" : [ {
"key" : "other - recreation",
"doc_count" : 80
}, {
"key" : "fire station",
"doc_count" : 50
}, {
"key" : "library",
"doc_count" : 46
}, {
"key" : "police station",
"doc_count" : 21
}, {
"key" : "repair services (vehicle, shoe, locksmith, etc.)",
"doc_count" : 11
}, {
"key" : "medical office",
"doc_count" : 8
}, {
"key" : "office",
"doc_count" : 8
}, {
"key" : "museum",
"doc_count" : 6
}, {
"key" : "prison/incarceration",
"doc_count" : 4
}, {
"key" : "other",
"doc_count" : 3
} ]
}
}
}
Here is a histogram with groups of every 50 years and within those 50 year buckets it count how many buildings of different types were completed.
$ curl -X POST 'http://localhost:9200/philly_energy/building/_search?pretty=true' -d '{
"query": {
"match_all": {}
},
"aggs": {
"by_year": {
"histogram": {
"field": "YEAR_BUILT",
"interval": 50
},
"aggs": {
"by_type": {
"terms": {
"field": "PROPERTY_TYPE"
}
}
}
}
},
"size": 0
}'
{
"took" : 79,
"timed_out" : false,
"_shards" : {
"total" : 5,
"successful" : 5,
"failed" : 0
},
"hits" : {
"total" : 259,
"max_score" : 0.0,
"hits" : [ ]
},
"aggregations" : {
"by_year" : {
"buckets" : [ {
"key" : 1700,
"doc_count" : 1,
"by_type" : {
"buckets" : [ {
"key" : "other - recreation",
"doc_count" : 1
} ]
}
}, {
"key" : 1750,
"doc_count" : 1,
"by_type" : {
"buckets" : [ {
"key" : "museum",
"doc_count" : 1
} ]
}
}, {
"key" : 1800,
"doc_count" : 1,
"by_type" : {
"buckets" : [ {
"key" : "museum",
"doc_count" : 1
} ]
}
}, {
"key" : 1850,
"doc_count" : 6,
"by_type" : {
"buckets" : [ {
"key" : "office",
"doc_count" : 2
}, {
"key" : "fire station",
"doc_count" : 1
}, {
"key" : "other - recreation",
"doc_count" : 1
}, {
"key" : "police station",
"doc_count" : 1
}, {
"key" : "prison/incarceration",
"doc_count" : 1
} ]
}
}, {
"key" : 1900,
"doc_count" : 92,
"by_type" : {
"buckets" : [ {
"key" : "other - recreation",
"doc_count" : 47
}, {
"key" : "library",
"doc_count" : 19
}, {
"key" : "fire station",
"doc_count" : 13
}, {
"key" : "museum",
"doc_count" : 3
}, {
"key" : "police station",
"doc_count" : 2
}, {
"key" : "courthouse",
"doc_count" : 1
}, {
"key" : "ice/curling rink",
"doc_count" : 1
}, {
"key" : "laboratory",
"doc_count" : 1
}, {
"key" : "non-refrigerated warehouse",
"doc_count" : 1
}, {
"key" : "office",
"doc_count" : 1
} ]
}
}, {
"key" : 1950,
"doc_count" : 150,
"by_type" : {
"buckets" : [ {
"key" : "fire station",
"doc_count" : 36
}, {
"key" : "other - recreation",
"doc_count" : 29
}, {
"key" : "library",
"doc_count" : 26
}, {
"key" : "police station",
"doc_count" : 16
}, {
"key" : "repair services (vehicle, shoe, locksmith, etc.)",
"doc_count" : 11
}, {
"key" : "medical office",
"doc_count" : 8
}, {
"key" : "office",
"doc_count" : 5
}, {
"key" : "other - public services",
"doc_count" : 3
}, {
"key" : "prison/incarceration",
"doc_count" : 3
}, {
"key" : "laboratory",
"doc_count" : 2
} ]
}
}, {
"key" : 2000,
"doc_count" : 6,
"by_type" : {
"buckets" : [ {
"key" : "other - recreation",
"doc_count" : 2
}, {
"key" : "police station",
"doc_count" : 2
}, {
"key" : "library",
"doc_count" : 1
}, {
"key" : "prison/incarceration",
"doc_count" : 1
} ]
}
} ]
}
}
}
Here I show the most floor space on average by building type.
$ curl -X POST 'http://localhost:9200/philly_energy/building/_search?pretty=true' -d '{
"query": {
"match_all": {}
},
"aggs": {
"by_type": {
"terms": {
"field": "PROPERTY_TYPE",
"size": 50,
"order": {
"area": "desc"
}
},
"aggs": {
"area": {
"avg": {
"field": "BLDG_FLOOR_AREA"
}
}
}
}
},
"size": 0
}'
{
"took" : 20,
"timed_out" : false,
"_shards" : {
"total" : 5,
"successful" : 5,
"failed" : 0
},
"hits" : {
"total" : 259,
"max_score" : 0.0,
"hits" : [ ]
},
"aggregations" : {
"by_type" : {
"buckets" : [ {
"key" : "office",
"doc_count" : 8,
"area" : {
"value" : 595797.625
}
}, {
"key" : "courthouse",
"doc_count" : 2,
"area" : {
"value" : 425000.0
}
}, {
"key" : "prison/incarceration",
"doc_count" : 6,
"area" : {
"value" : 418769.1666666667
}
}, {
"key" : "museum",
"doc_count" : 6,
"area" : {
"value" : 144898.5
}
}, {
"key" : "senior care community",
"doc_count" : 3,
"area" : {
"value" : 124181.0
}
}, {
"key" : "ice/curling rink",
"doc_count" : 1,
"area" : {
"value" : 66000.0
}
}, {
"key" : "laboratory",
"doc_count" : 3,
"area" : {
"value" : 51233.333333333336
}
}, {
"key" : "performing arts",
"doc_count" : 1,
"area" : {
"value" : 48557.0
}
}, {
"key" : "repair services (vehicle, shoe, locksmith, etc.)",
"doc_count" : 11,
"area" : {
"value" : 46718.0
}
}, {
"key" : "multifamily housing",
"doc_count" : 1,
"area" : {
"value" : 33998.0
}
}, {
"key" : "other",
"doc_count" : 3,
"area" : {
"value" : 27911.0
}
}, {
"key" : "medical office",
"doc_count" : 8,
"area" : {
"value" : 24793.0
}
}, {
"key" : "other - public services",
"doc_count" : 4,
"area" : {
"value" : 23008.5
}
}, {
"key" : "police station",
"doc_count" : 21,
"area" : {
"value" : 22157.04761904762
}
}, {
"key" : "residence hall/dormitory",
"doc_count" : 1,
"area" : {
"value" : 20679.0
}
}, {
"key" : "library",
"doc_count" : 46,
"area" : {
"value" : 14417.95652173913
}
}, {
"key" : "non-refrigerated warehouse",
"doc_count" : 2,
"area" : {
"value" : 13228.0
}
}, {
"key" : "other - recreation",
"doc_count" : 80,
"area" : {
"value" : 12987.2
}
}, {
"key" : "fire station",
"doc_count" : 50,
"area" : {
"value" : 8340.54
}
}, {
"key" : "",
"doc_count" : 2,
"area" : {
"value" : null
}
} ]
}
}
}
Last I show how to query for what building type uses the most electric per square foot and also the average electric by square foot. This will give us the highest cost per square foot to run and give use a nice baseline.
$ curl -X POST 'http://localhost:9200/philly_energy/building/_search?pretty=true' -d '{
"query": {
"match_all": {}
},
"aggs": {
"by_type": {
"terms": {
"field": "PROPERTY_TYPE",
"size": 50,
"order": {
"electric_by_area": "desc"
}
},
"aggs": {
"electric_by_area": {
"avg": {
"script": "doc[\u0027ELECTRIC_USE\u0027].value / doc[\u0027BLDG_FLOOR_AREA\u0027].value > 0?doc[\u0027BLDG_FLOOR_AREA\u0027].value : 1"
}
}
}
},
"total_electric_by_area": {
"avg": {
"script": "doc[\u0027ELECTRIC_USE\u0027].value / doc[\u0027BLDG_FLOOR_AREA\u0027].value > 0?doc[\u0027BLDG_FLOOR_AREA\u0027].value : 1"
}
}
},
"size": 0
}'
{
"took" : 10,
"timed_out" : false,
"_shards" : {
"total" : 5,
"successful" : 5,
"failed" : 0
},
"hits" : {
"total" : 259,
"max_score" : 0.0,
"hits" : [ ]
},
"aggregations" : {
"total_electric_by_area" : {
"value" : 50937.66795366795
},
"by_type" : {
"buckets" : [ {
"key" : "office",
"doc_count" : 8,
"electric_by_area" : {
"value" : 595797.625
}
}, {
"key" : "courthouse",
"doc_count" : 2,
"electric_by_area" : {
"value" : 425000.0
}
}, {
"key" : "prison/incarceration",
"doc_count" : 6,
"electric_by_area" : {
"value" : 418769.1666666667
}
}, {
"key" : "museum",
"doc_count" : 6,
"electric_by_area" : {
"value" : 144898.5
}
}, {
"key" : "senior care community",
"doc_count" : 3,
"electric_by_area" : {
"value" : 124181.0
}
}, {
"key" : "ice/curling rink",
"doc_count" : 1,
"electric_by_area" : {
"value" : 66000.0
}
}, {
"key" : "laboratory",
"doc_count" : 3,
"electric_by_area" : {
"value" : 51233.333333333336
}
}, {
"key" : "performing arts",
"doc_count" : 1,
"electric_by_area" : {
"value" : 48557.0
}
}, {
"key" : "repair services (vehicle, shoe, locksmith, etc.)",
"doc_count" : 11,
"electric_by_area" : {
"value" : 46718.0
}
}, {
"key" : "multifamily housing",
"doc_count" : 1,
"electric_by_area" : {
"value" : 33998.0
}
}, {
"key" : "other",
"doc_count" : 3,
"electric_by_area" : {
"value" : 27911.0
}
}, {
"key" : "medical office",
"doc_count" : 8,
"electric_by_area" : {
"value" : 24793.0
}
}, {
"key" : "other - public services",
"doc_count" : 4,
"electric_by_area" : {
"value" : 23008.5
}
}, {
"key" : "police station",
"doc_count" : 21,
"electric_by_area" : {
"value" : 22157.04761904762
}
}, {
"key" : "residence hall/dormitory",
"doc_count" : 1,
"electric_by_area" : {
"value" : 20679.0
}
}, {
"key" : "library",
"doc_count" : 46,
"electric_by_area" : {
"value" : 14417.95652173913
}
}, {
"key" : "non-refrigerated warehouse",
"doc_count" : 2,
"electric_by_area" : {
"value" : 13228.0
}
}, {
"key" : "other - recreation",
"doc_count" : 80,
"electric_by_area" : {
"value" : 12987.2
}
}, {
"key" : "fire station",
"doc_count" : 50,
"electric_by_area" : {
"value" : 8340.54
}
}, {
"key" : "",
"doc_count" : 2,
"electric_by_area" : {
"value" : 0.0
}
} ]
}
}
}
Can you provide a API link to type all above commands or we must login to terminal to type those commands. thanks
ReplyDeleteNoticed you removed the repository(therefor the link is broken) to the ruby opendata conversion tool you created for converting data from the published to a json format ES can consume. Was there a reason for this? Was interested in seeing how your tool worked.
ReplyDelete