sql server - Dimension for geozones or Lat & Long in data warehouse -


i have dimplace dimension has name of place (manually entered user) , latitude , longitude of place (automatically captured). since places entered manually same place in there multiple time different names, additionally, 2 distinct places close each other.

we want able analyze mpg between 2 "places" want group them make larger area - i.e. using lat & long put various spellings of 1 location, distinct close locations, in 1 record.

i planning on making new dimension - dimplacegeozone. looking resource loading lat & long values mapped ... something?? maybe postal code, or city name? can find script load common dimensions (like dimtime) - love similar lat & long values in north america?

i've done similar in past... 1 stumbling block hit front 2 locations, straddling border physically closer 2 locations both in same area.

i got around creating "double grid" system causes each location fall 4 areas. way 2 locations share @ least 1 "area" know within range of each other.

here's example, covering of united states...

if object_id('tempdb..#latlngareas', 'u') not null  drop table #latlngareas; go       cte_lat (         select              t.n,             beglatrange = -37.9 + (t.n / 10.0),             endlatrange  = -37.7 + (t.n / 10.0)                     dbo.tfn_tally(1030, 0) t         ),     cte_lng (         select              t.n,             beglngrange = -159.7 + (t.n / 10.0),             endlngrange = -159.5 + (t.n / 10.0)                     dbo.tfn_tally(3050, 0) t         ) select      area_id = row_number() on (order lat.n, lng.n),     lat.beglatrange,      lat.endlatrange,      lng.beglngrange,      lng.endlngrange     #latlngareas     cte_lat lat     cross join cte_lng lng;   select      b3.branch_id,     b3.name,     b3.lat,     b3.lng,     lla.area_id     dbo.contactbranch b3    -- replace dimplace     join #latlngareas lla         on b3.lat between lla.beglatrange , lla.endlatrange         , b3.lng between lla.beglngrange , lla.endlngrange;

hth, jason


Comments

Post a Comment

Popular posts from this blog

node.js - Node js - Trying to send POST request, but it is not loading javascript content -

i have been trying send post request website, , has not been loading full site. i guessing problem node js not loading javascript content, executing it. can see javascript in response, though never loads content javascript files producing in html. what mean is, doesn't execute javascript. how make node js code overcome this, , wait the full website load? have tried native code, , "request' module, both producing same errors. if helps, don't need native javascript. modules fine. some code have tried (request module): var options = { method: 'post', uri: 'https://url', formdata: { 'email': 'email', 'prize': '0', 'transactional': 'on' }, headers: { /* 'content-type': 'application/x-www-form-urlencoded' */ // set automatically } }; rp(options) .then(function (body) { console.log(body) }) .catch(function (err) { console.log(err) }); when fetch content...
Read more

javascript - Replicate keyboard event with html button -

i have tried few ways make happen. have javascript pinball game uses keyboard keys controls. converting application touch screen display. workaround, hoping overlay buttons when clicked simulate keyboard strokes. have done research , sounds should work. using jquery v3.2.1 here things have tried far... html element: <input type="button" class="coil"></input> $('.coil').trigger({type: 'keydown', which: 40, keycode: 40}); with this, not errors, nothing happens. var coilkey = $('body').keydown(function(e) { switch (e.keycode) { case 40: break;} }) $('.coil').click(coilkey); this 1 returns error in console... ((jquery.event.special[handleobj.origtype] || (intermediate value)).handle || handleobj.handler).apply not function below javascript game engine handles key listeners... addkeylistener: function (keyandlistener) { this.keylisteners.push(keyandlistener); }, // gi...
Read more

javascript - Web audio api 5.1 surround example not working in firefox -

i've been playing lott lately web audio api on both firefox , chrome. few days ago started experiment surround set. when trying surround audio in web audio api came notice example works fine in chrome v59 not in firefox v54. // create web audio api context var audioctx = new (window.audiocontext || window.webkitaudiocontext)(); audioctx.destination.channelinterpretation = 'discrete'; audioctx.destination.channelcountmode = 'explicit'; audioctx.destination.channelcount = 6; var oscillators = []; var merger = audioctx.createchannelmerger(6); console.log(audioctx.destination, merger); //merger.channelinterpretation = 'discrete'; //merger.channelcountmode = 'explicit'; //merger.channelcount = 6; var addoscilator = function(channel, frequency) { var oscillator = audioctx.createoscillator(); oscillator.frequency.value = frequency; // value in hertz oscillator.connect(merger,0,channel); oscillator.start(); oscillators.push(o...
Read more