<html><head><meta http-equiv="Content-Type" content="text/html charset=us-ascii"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;">Hello Andreas,<div><br></div><div>This indexing scheme appears to be a nice design; using base 36 is clever.</div><div> </div><div>I'm currently working with a 5 cm grid of ocean bathymetry, so an 11 cm resolution tiling solution is "close enough" for this use case.</div><div><br></div><div>I tried examining your example at <a href="https://heights-database.firebaseio.com/">https://heights-database.firebaseio.com/</a> but am getting an "error completing the request" message.</div><div><br></div><div>Are there suitable locally-hosted open source json database solutions?</div><div><br></div><div>-Mike</div><div><br><div apple-content-edited="true">
<span class="Apple-style-span" style="border-collapse: separate; border-spacing: 0px;">--<br>Mike McCann<br>Software Engineer<br>Monterey Bay Aquarium Research Institute<br>7700 Sandholdt Road<br>Moss Landing, CA 95039-9644<br>Voice: 831.775.1769 Fax: 831.775.1736 <a href="http://www.mbari.org">http://www.mbari.org</a></span>
</div>
<br><div><div>On May 26, 2016, at 11:39 AM, Andreas Plesch <<a href="mailto:andreasplesch@gmail.com">andreasplesch@gmail.com</a>> wrote:</div><br class="Apple-interchange-newline"><blockquote type="cite"><div dir="ltr"><div><div><div>I think I settled on a json structure for a tiled elevation grid which came out quite a bit different than originally anticipated.<br><br></div>Since json is text based, I am using base 36 for numbering to compress a bit. So in addition to numerical digits, the letters a-z are also used. JS has builtin functions to convert from and to base 36. The fundamental unit is micro-degrees which corresponds to about 110km/1000000 = 0.11m. Combined, this means a 6 digit coordinate can resolve down to 11 cm.<br><br></div>Tiles are 36 by 36 cells. A tile can be sparse, eg. not all cell values need to be specified. However, in order to make it easy for a tile consumer to fill in gaps, at least one value in a tile needs to (should) be provided.<br><br></div><div>The size of a tile depends on the zoom level. Zoom level 5 tiles are 36 microdegrees long on each side. Zoom level 4 tiles are 36x36 zoom level 5 tiles large, eg. each side is 36x36 = 1296 microdegrees long, eg. about 140m. Zoom level 3 tiles are about 5 km long on each side.<br><br></div><div>Here is a table of tile sizes:<br><br></div><div><font face="monospace,monospace">z | edge | in meters<br></font></div><div><font face="monospace,monospace">5 | 36 | 4<br>4 | 1296 | 143<br>3 | 46 656| 5132<br></font></div><div><font face="monospace,monospace">2 |1 679 616|185km<br></font></div><div><font face="monospace,monospace">1 |60 466 176| 6651km <br></font></div><div><br></div><div>The size of each cell in a tile is 1/36 of the tile size (or the size the tile of the next higher zoom level).<br></div><div><br></div><div>The json structure is<br><br></div><div><font face="monospace,monospace">/z/lon/lat/cell_lon/cell_lat<br><br></font></div><div><font face="monospace,monospace">z is the zoom factor<br></font></div><div><font face="monospace,monospace">lon/lat is the location of the tile shortened to z digits<br></font></div><div><font face="monospace,monospace">cell_lon/cell_lat is the position of an elevation within the tile<br><br></font></div><div><font face="monospace,monospace"><font face="arial,helvetica,sans-serif">A level 2 tile would be<br>/2/23/2<br><br></font></font></div><div><font face="monospace,monospace"><font face="arial,helvetica,sans-serif">Its location is from 230000,20000 to 23z000,2z000 in microdegrees. This comes out to be<br></font></font></div><div><font face="monospace,monospace"><font face="arial,helvetica,sans-serif">parseInt(230000,36)/1000000 = 125.9712 degrees E, 3.359232 N to<br></font></font></div><div><font face="monospace,monospace"><font face="arial,helvetica,sans-serif">127.60416 E, 4.992192 N<br><br></font></font></div><div><font face="monospace,monospace"><font face="arial,helvetica,sans-serif">A query or lookup like heights[2][32][2] returns all populated cells in the 36x36 tile. The consumer then can easily reconstruct the position of each reported cell.<br><br></font></font></div><div><font face="monospace,monospace"><font face="arial,helvetica,sans-serif">A path like<br></font></font></div><div><font face="monospace,monospace"><font face="arial,helvetica,sans-serif">/2/23/2/8/7 returns a single datum. It looks up the elevation at<br></font></font></div><div><font face="monospace,monospace"><font face="arial,helvetica,sans-serif">238000, 27000 in microdegrees.<br><br></font></font></div><div><font face="monospace,monospace"><font face="arial,helvetica,sans-serif">Reversely, the procedure to extract an elevation at a given location (144.5678, 44.1245) is:<br><br></font></font></div><div><font face="monospace,monospace"><font face="arial,helvetica,sans-serif">1) decide on resolution(zoom level), depends on json data base, say 3 (143m cell resolution).<br></font></font></div><div><font face="monospace,monospace"><font face="arial,helvetica,sans-serif">2) convert to microdegrees<br></font></font></div><div><font face="monospace,monospace"><font face="arial,helvetica,sans-serif">lon =longitude * 1e6<br></font></font></div><div><font face="monospace,monospace"><font face="arial,helvetica,sans-serif">3) truncate to zoomlevel-1 and convert to base 36<br></font></font></div><div><font face="monospace,monospace"><font face="arial,helvetica,sans-serif">lon = Math.round(lon/Math.pow(36,z-1))*Math.pow(36,z-1).toString(36) (2e2l00, q9r00)<br></font></font></div><div><font face="monospace,monospace"><font face="arial,helvetica,sans-serif">3) determine tile to use according to zoom level:<br></font></font></div><div><font face="monospace,monospace"><font face="arial,helvetica,sans-serif">/3/2e2/q9/<br></font></font></div><div><font face="monospace,monospace"><font face="arial,helvetica,sans-serif">4) lookup elevation in tile <br></font></font></div><div><font face="monospace,monospace"><font face="arial,helvetica,sans-serif">/3/2e2/q9/l/r<br></font></font></div><div><font face="monospace,monospace"><font face="arial,helvetica,sans-serif">5) if successful done, if not get whole tile /3/2e2/q9<br></font></font></div><div><font face="monospace,monospace"><font face="arial,helvetica,sans-serif">and traverse for nearest neighbour or inverse distance weighted average<br></font></font></div><div><font face="monospace,monospace"><font face="arial,helvetica,sans-serif">possibly get surrounding tiles as well for more sophisticated interpolation.<br></font></font></div><div><font face="monospace,monospace"><font face="arial,helvetica,sans-serif">The advantage of a tiled grid would be to first get the whole tile to avoid connecting twice.<br></font></font></div><div><font face="monospace,monospace"><font face="arial,helvetica,sans-serif">Or to get the whole tile set by just /3/ <br><br></font></font></div><div><font face="monospace,monospace"><font face="arial,helvetica,sans-serif">It would be nice to attach meta information to tile sets and perhaps single tiles.<br><br></font></font></div><div><font face="monospace,monospace"><font face="arial,helvetica,sans-serif">I uploaded such a tiled elevation json database of California with zoom levels up to 3 (but only 1km resolution, eg. 3 tiles are sparse) to <br></font></font><br><font face="monospace,monospace"><font face="arial,helvetica,sans-serif">
<a href="https://heights-database.firebaseio.com/">https://heights-database.firebaseio.com/</a>
<br></font></font></div><div><font face="monospace,monospace"><font face="arial,helvetica,sans-serif"><br></font></font></div><div><font face="monospace,monospace"><font face="arial,helvetica,sans-serif">with public read access for experimentation.<br><br></font></font></div><div><font face="monospace,monospace"><font face="arial,helvetica,sans-serif">-Andreas<br></font></font></div><div><font face="monospace,monospace"><font face="arial,helvetica,sans-serif"><br><br></font></font></div><div><font face="monospace,monospace"><font face="arial,helvetica,sans-serif"><br></font></font></div><div><font face="monospace,monospace"><font face="arial,helvetica,sans-serif"><br><br><br><br><br></font></font></div><div><font face="monospace,monospace"><font face="arial,helvetica,sans-serif"><br><br></font></font></div><div><font face="monospace,monospace"><font face="arial,helvetica,sans-serif"><br></font></font></div><div><font face="monospace,monospace"><font face="arial,helvetica,sans-serif"><br><br></font></font></div><div><font face="monospace,monospace"><font face="arial,helvetica,sans-serif"><br></font></font></div><div><font face="monospace,monospace"><br><br><br></font></div><div><font face="monospace,monospace"><br></font></div><div><br><br></div><div><br><br><br><br><br><br><br><br><br><br><br><br></div><div><br><br><br><div><br><br><div><br><br><br></div></div></div></div><div class="gmail_extra"><br><div class="gmail_quote">On Wed, May 25, 2016 at 4:33 PM, Andreas Plesch <<a href="mailto:andreasplesch@gmail.com">andreasplesch@gmail.com</a>> wrote:<br><blockquote class="gmail_quote"><div dir="ltr"><div>Hi,<br><br></div>given the json x3d activity and availability of firebase as performant, easy to use, online json database engine, I started to think about a json structure for tiled elevation grid data.<br><div><div><br></div><div>As an example dataset I am using a global 1km (30 arcsecond) resolution dataset, cut down to the continental U.S. or California.<br><br></div><div>The simplest structure is an untiled, flat list like this:<br></div><div>{ "lon1:lat1" : elev1,<br> "lon2:lat2" : elev2,<br> ... }<br></div><div>I transformed the dataset to this structure, and uploaded the json to firebase. It works as expected, eg. you can query the database but you need to know the exact keys, or array of keys for an area. It is actually pretty fast.<br></div><div><br></div><div>This where tiling comes in. My favorite idea sofar is to use the digits of lon.,lat. as hierarchical keys, with an addtional key "h" for the actual data. For a three by three digit, one degree grid, It would look like this:<br><br></div><div>{"0:0" : {"h": h00_ave,<br></div><div> "0:0" : { "h": h00_ave,<br></div><div> "0:0" : { h: elev },<br> "0:1" : { h: elev }, ...<br> "9:9" : { h: elev }<br> },<br><div> "0:1" : { "h": h01_ave,<br> "0:0" : { h: elev },<br> "0:1" : { h: elev }, ...<br> "9:9" : { h: elev }<br> }, ...<br><div> "9:9" : { "h": h99_ave,<br> "0:0" : { h: elev },<br> "0:1" : { h: elev }, ...<br> "9:9" : { h: elev }<br> }<br> "0:1" : {"h": h01_ave, //100N does not actually exist <br> "0:0" : { ... }, ...<br> "9:9" : { ... }<br> }, ...<br> "9:9" : { ... }<br>}<br></div><div> <br></div>The elev. data are actual elevation, the h??_ave value are representative (average) elevation for a complete tile.<br><br>A json path to an elevation at 123E longitude and 40N latitude looks like this:<br>/1:0/2:4/3:0/h<br><div>A json path to all elevations in the area between 120 and 129E longitude and 40 and 49N latitude is:<br>/1:0/2:4/<br></div>This path refers to an object:<br>{ "h": 356, //average elevation in this area<br><div> "0:0" : {"h": 344 }, // ...<br></div><div> "9:9" : {"h": 377 }<br>}<br><br></div>which has all grid point elevations in this 10x10 degree tile.<br>So 129E,049N has an elevation of 377.<br><br>Hm, there is an issue. A json path<br>/2:0/<br><div>would refer to an area between 200E,000N to 299E,099N and would require loading all of the subtree although we are only interested in the h??_ave values.<br></div><div><font face="arial,helvetica,sans-serif"><br></font></div><div><font face="arial,helvetica,sans-serif">-Andreas<br></font></div><div><font face="arial,helvetica,sans-serif"><br>Thinking out loud: This probably means putting the tile level first in the hierarchy like in slippy maps. The tile level would be the number of significant digits.<br></font></div><div><font face="arial,helvetica,sans-serif">/3/0:0 //average for 000:000 to 099:099<br></font></div><div><font face="arial,helvetica,sans-serif">/2/0:0 //average for 000:000 to 009:009<br></font></div><div><font face="arial,helvetica,sans-serif">/2/1:0 //average for 010:000 to 019:009<br></font></div><div><font face="arial,helvetica,sans-serif">/2/ // all averages in 10x10 cells<br></font></div><div><font face="arial,helvetica,sans-serif">/1/222:89 // data in 222:89<br></font></div><div><font face="arial,helvetica,sans-serif">/1/ // all data in 1x1 cells<br></font></div><div><font face="arial,helvetica,sans-serif">looks better ...<font color="#888888"><br><br><br></font></font></div><font color="#888888"><div><font face="arial,helvetica,sans-serif"><br></font></div><div><br></div><div><br></div><div><br><br></div></font></div><font color="#888888"> <br> <br></font></div><font color="#888888"><div><br></div><div><br><br></div><div><br><br clear="all"><br>-- <br><div>Andreas Plesch<br>39 Barbara Rd.<br>Waltham, MA 02453</div>
</div></font></div></div>
</blockquote></div><br><br clear="all"><br>-- <br><div class="gmail_signature">Andreas Plesch<br>39 Barbara Rd.<br>Waltham, MA 02453</div>
</div>
_______________________________________________<br>geospatial mailing list<br><a href="mailto:geospatial@web3d.org">geospatial@web3d.org</a><br>http://web3d.org/mailman/listinfo/geospatial_web3d.org<br></blockquote></div><br></div></body></html>