Unity Catalog SQL examples
Try the GeoBrix Unity Catalog functions with ready-made queries. Twelve SQL files cover every function by category, and each query uses small inputs and returns rows.
Install the functions
Run gbx.uc.install once from an env 6 notebook or job. It creates the functions, and with procedures=True the procedures, in one schema. The Unity Catalog functions page has the call and the Volume folder rules.
The examples use the catalog geospatial_docs and the schema geobrix_uc. Replace both names with your own, in the USE lines at the top of each file and in the table names of 11_procedures.sql.
Run the examples
- Import the files into your workspace. Run
gbx:examples:uc-sql --uploadfrom a GeoBrix checkout, or copy the files fromexamples/uc-functions. - Open
00_setup.sqlin the SQL editor and run it. It builds the sample tables the other files read. - Open any other file and run one statement at a time.
Each statement starts with a -- cid: comment that names it. The SQL editor ignores the comment, and the gbx:examples:uc-sql command uses it to run every file and check every result.
A statement that calls a GeoBrix Python function takes about 10 seconds on a SQL warehouse. A full run takes about 25 minutes.
Check the whole set from a terminal
bash scripts/commands/gbx-examples-uc-sql.sh --profile <profile> --upload
The command runs the files in order on the warehouse. It fails when a statement errors or a query returns no rows. With --upload it also imports the files into /Workspace/Users/<you>/geobrix/uc-function-examples/.
The files
- 00 Setup
- 01 BNG grid
- 02 Quadbin and custom grids
- 03 H3
- 04 Vector
- 05 Raster accessors
- 06 Raster operations
- 07 Indices and terrain
- 08 Raster generators
- 09 Raster to grid
- 10 Aggregation recipes
- 11 Procedures
Builds the sample tables. Run it first.
-- GeoBrix Unity Catalog functions: setup.
-- Run this file first. It builds the small sample tables the other files query.
-- The files use the catalog geospatial_docs and the schema geobrix_uc: change both names below
-- (and in the other files) when you installed the functions somewhere else.
-- cid: use_catalog
USE CATALOG geospatial_docs;
-- cid: use_schema
USE SCHEMA geobrix_uc;
-- cid: check_functions_installed
-- Shows that the GeoBrix functions are in the schema: this counts them.
SELECT count(*) AS geobrix_functions
FROM information_schema.routines
WHERE routine_schema = 'geobrix_uc' AND routine_name LIKE 'gbx_%';
-- cid: create_ex_rasters
-- Two tiny GeoTIFFs become tiles: a 24 x 24 pixel DEM and a 5-band scene (red, green, blue, near infrared, shortwave infrared),
-- both in British National Grid (EPSG:27700) with 10 m pixels. gbx_rst_fromcontent reads the file bytes.
-- To use your own file instead, read it with read_files(<path>, format => 'binaryFile') and pass its content.
CREATE OR REPLACE TABLE ex_rasters AS
SELECT name, gbx_rst_fromcontent(unhex(hexbytes), 'GTiff') AS tile
FROM VALUES
('dem',
concat(
'49492a000800000011000001030001000000180000000101030001000000180000000201030001000000200000000301',
'030001000000080000000601030001000000010000001101040001000000900100001501030001000000010000001601',
'030001000000180000001701040001000000760600001c01030001000000010000003d01030001000000030000005301',
'030001000000030000000e830c0003000000da00000082840c0006000000f2000000af8703002000000022010000b187',
'0200270000006201000081a40200060000008a0100000000000000000000000024400000000000002440000000000000',
'000000000000000000000000000000000000000000000000000000000000a02c20410000000000080641000000000000',
'00000100010000000700000400000100010001040000010001000204b1871f0000000108b18707001f00060800000100',
'8e23000c00000100346c040c0000010029234f5347423336202f2042726974697368204e6174696f6e616c2047726964',
'7c4f53474233367c00002d3939393900789c85d57b3c54691807f0993367ce9c313e150aa5256d34b98b6ce94a379489',
'0825a5882e268a6a5b84552e51142ab52ed14585462e6b546b42e35aa244e8226537a5b4ab987399d9f78cfedcd3bee7',
'ffeff33bcf79cef3da33fefb983399100b1c083c2c0882984c0643a190cb23ec4e7d0dc14f5e733d9a7fe3ec1e83fa0d',
'ed2b6f195c5e3c722e35af75227483813df92ca819141b729ddd6d4b1d8c0bed697d8a8795359405184c8a271f1ce8f1',
'c1ae7925764c30e2b36bfc0637576bef6b5dd61c6e81f89eccd2547f62e713380db13922fa69c609437e55359fd68720',
'160c43300b3c304b991ff072d2ac745e77d69c424d6181a4386c723ab1ee4c97ceae87aea410352adf6dd6e4c60e4ad4',
'ee338d71d55d778d75be70cd2d3adf02826000b3d83075281fc42749329fc8deb3d97f41b9969beffc57de655ff96e47',
'4423ae6d110ed0b16e559f50b6edec04ff10b5c882bafa4f1269e6b3045a9f45e586d9e0c06cd0202a3e491284b18b96',
'da2fb65d01ed3ca4d6fef49dc77d9f51cbbdcd02457dcb9cc8473bdd37955e2e8a8d324dc079fb0aec1c6bd482e87ccb',
'f1e86c046123a010880f9a4310446d717643e7c3b8d999cd2287732505293f0b0f9eef59fae0b824a149f2a2991fc7fd',
'929803873d651ee82f6e6af971c22a3a7f2ec84ef11c045480c7e313048e076958e8a709977dde95e255db230ee5bf6b',
'681c5e54b27e6656f298753163876fbea9de60e68c1d797f419f1d666fd590d2fadf780ecaa17c309b24d0715c37baa9',
'50474351b3d075cc42255f2f6b21ccb23e6bf0bac9d1197531fe607eb02acde8e33bf38c04f3a32df1c2ab0298ceb702',
'ad87291e4595be323d86639d4381f32e57495251d5a424fdd01b5f847ca3904fb95171d642a3a5b1eb83f927da055dcc',
'ca43a1cba7f2173f4dca35a3f3ad6104c447512e17451016e83e488f61189ebce92e6cec73d8a3a952b56dc61c3daba4',
'45abafae996dba31be6304cde14c1dc8b3dd9e7f90d5c6b770123c4b79141048eb83cf8a7000af8272d82c868220802e',
'c3f00592c880ab5f66f10e3b1f52709ba3fc721cfddf94dd1db68ffc274dd6bf223c9d3ff9ca120f8f07d06fc4de6942',
'4bcf9b74fe3c309820be8a8a0a97c3861824e065324c8677e8d8dfd53a5c7545b55eaaf9dca733b574cb090bb39d8df6',
'4358fda3e809d5130399b2a4d1444f9bf7e707420feb1efa9bd647289fcb1bf715242e938d81029867a03abcefada57e',
'e48c518dbc018bdaaef8968c7bc6555eafd6c518bd951c2959bb55cdbd207045a331eae4b23b829c4be7db001e01f179',
'3c2e07662a084c36067c195e99fd71c12dcbb830a92b2af30a3eadaadd6aaab39f5713bbb2c7cfc4eab48be293a8c866',
'29b97d4fff3ebf257a9e1bcb697d909f33ee2330534ec864a3a000868ffcf16c43a777c985d7dea30d0889841db11337',
'aee6bcae4ed91ffef286ee0efe34454562ed60e4b617a686c69a97adacfec7e7a9f0500ecc90e3b2b1711f91b67bf76e',
'15a5b7dade8f2d3e93c1541f3878c95e5f9b67fb8356f024e958e2cd59212b9186ecf4b9759fee8c6906a87ddf57f61f',
'6131e498d2c730e24359af43cda2589f94e15013f725cbc5dd92c022bf8d7efe86ce17033ae1cc91f744c460f0bdf0f7',
'67dc5fbedc36a41afe9dfe533e981f148c27a9cc0fda4f5c3f3ea29bf8c5149b19cd9212a3ead91ded496fae17564b2b',
'86522b561c70acfb65775758b38f255b6379d925313be1d877e78703c69fcb4120a5af6c0f69e7a4f3625e724970ca50',
'a4dbd55ae64d684adf0a752d23f322a7e8bc3575ebc5ce5692f96bc3ddd61af8f5796f1dc8b5fddefc532fc04595e3af',
'1c4fd01ea2eaab20b909d5ace95dd6dfe57171e25bef78dfc68c8cdc0b1dfdab4eb9bb7d3cb740b4bf2d366ee116d3fd',
'3031ccc811d1ffbf60fd801f8c5a6f60fcc17ccaa8f8e4d4f4e32682bddbe56a510b37a9a45a2fee9acedc65a6af8b86',
'4d31b9fbe87e6d6698478f2cb62abed4a6fda2a4411e339976bf51f70a02360482c090428ee3203d4e10f25d7cd14b69',
'7d2e7ffeade46e69f2dea29066b1ee938aacb06b62bdd7d203369241439b6dc327ca844fa3479e27cfb1b847ebb3be15',
'00b70b9321a776270ee29322f124cff9533b9dbdfa7ab7249cfedd443b29aaf7d7b53abdb7ab9f8e79b4b6cc4c9b7e31',
'468f53673853f14270ffc96d01fdfdc266b3a81dc4069717f0a9bb8524e4f251c7e0d6c2a009a1111a967fbae672caea',
'5d1cb3cc5e652c0e7f7073ae5d5ba47d4b4e65c82cfdc7e571cb2a63426fa715fe0bf0620fda')),
('scene',
concat(
'49492a000800000012000001030001000000180000000101030001000000180000000201030005000000e60000000301',
'030001000000080000000601030001000000010000001101040001000000b10100001501030001000000050000001601',
'030001000000180000001701040001000000fc0d00001c01030001000000010000003d01030001000000020000005201',
'030004000000f00000005301030005000000f80000000e830c00030000000201000082840c00060000001a010000af87',
'0300200000004a010000b1870200270000008a01000081a4020002000000300000000000000010001000100010001000',
'000000000000000001000100010001000100000000000000244000000000000024400000000000000000000000000000',
'00000000000000000000000000000000000000000000a02c204100000000000806410000000000000000010001000000',
'0700000400000100010001040000010001000204b1871f0000000108b18707001f000608000001008e23000c00000100',
'346c040c0000010029234f5347423336202f2042726974697368204e6174696f6e616c20477269647c4f53474233367c',
'00789c2d98098c9d5515c7cff7bdf7bd37f3de9be9be4f4ba5a540b1505a0a942562a329042c65ab500b54a4084440c3',
'a628482265df54c4a8180d812882480c21246a1068b1842ed075982ed376a6d34e675fdebe1c7fe7bc769299db7bbfef',
'def33fe77ffee7dcefeaf833b15bc28e686a1417d5b8c42526050da5a609fe8a1435af290944a481df49a93257d34669',
'9488f5511dd16992d5acaa36c95c560bfca82e9251edd59894f51a19d6212d6a5557f0fc017613b957baf4900e307a4a',
'8eeb317689c9d37254db1805f2a41cd36ef61579460e6b97963529374b0fcf67d96539bbe498abea186c6bc6de04e787',
'fc0e38f31d9d2273641c48fec87a8b64b0f8499d24d3b037aff7b1faadf8f3b1ebc3dea8256a004703b8aaec56c6e612',
'bf4b5ae1b4384f87ac54741cab45d66a9af1e7ecdf199c5b64a6a22d9c9c53db6119287b74101c573177506de7db4174',
'5487f1c7d3d2affbc116ca8b7200e4bdecf40ed8be60d4247fc6071dec96d517e510c80778ee51de38e69e5f2123ec90',
'90924ec49b168948fa38670ca3aafe130be78032905fe804992a9324a7b7e949320fdc49b94d67ca15f167636bc3be68',
'6294c4eec8635bc23a8b6d80d772c47c223b55f99b27ae552cb1f3ccb3836a9c98c7df617057f5abec9005655a56b2c7',
'10de2aea75d2ad87c1d3280f826337ab25fd35161ed23e4e7847dab5955806b241da58b5b9bfcb5edd83df0afa363e38',
'8c0f22f91567f4ba4f2f03f9087f63b069c8f958d6e3c4b9055e36cb1f88c354e23baa8f81ed74bc90946b75ba2c9226',
'2cba5067cbd5f1c762ebc2a3d18448b1b30ada187b18a6a4a32d321b822ac28f21ef971d85c2912a881424a710cb1167',
'fe3978a9d739b99aff0d334acb1dd8d7caa8a43f97838cfa19bd059fdb419e917f83772b7315dd0ccaedbcadfab96cd7',
'5dcc55751378777906bc40a48f734a556f9701f77d554f03410cab9a6507ff6f213a457d0d169f22e319afd1b9325fc6',
'f2dc157ab29cc5534959c43b97c7d7c3e79e6852147a4e46f03629c6e500dc59760cf04dc8ef9c674b81089a4f67320a',
'9ce373c8836e50847201270e626904de41b86b5efb319977482d122f83ed4bf7c61bb21f944738e3103836a9f1718fec',
'd64ff1515c3aa515bf74b35fabec82f743bcf306bedac77e19b993fd02ec1a265681eb4a46f6c1c7161089fc8e7c3815',
'7b73ba12bc0b411e97f37972a94c07d54c9d2fd7c45f006f57343daaa8e543235862bc19ba3259fe5674ac189302b12c',
'b1fc15b04e66ae004a91056494f93c94afb3da8375555d8de67460694cee87bbedb033256fcb1122d8c73b1fcb1edd41',
'3e56743b2cfe94b7558360b77e4c7cd3d247bcbed04e766f05491bfe6d900fc8f376225dd4bba5438b6ec399a06d1053',
'aa2ff1d24c3145791c455a00ca9a7e13169f2693995b409e2d23271b24ad6781777dec26f27766646f3748d133310043',
'93e7709668659cd9e68346a92b13be22b283ac65d9ade47c6d908be15a2f886a7a2bf3fbd518f0182ad5ea885e23966d',
'fe8ec5f23346393d0af20d2009240cb6e84770161524bedb9ccf3dac6e75d5db40a47bc09b9607c8daac67d402ec4b11',
'9951bca8a06bc6fa27f42ba09c0adecb60e04532019b33b0f83c980d5fb1f5daf873b1356137fa6c9999c676e3b2e9d6',
'44b15cce715a8cd9aa6b7793c73d0b9279ce6c53eab340dec57a424c9587994dc8f741be0f4b03f909397880514ddf25',
'469bc191d33dc468137c8777e0f80494350d83cdba91b95092c14ebc6179de2f5fc0833ef6fb84485bc487f511a96bbc',
'a0417932cc18bd03359c0af7e2f228562f9429e8cd2558b80494a82e9eb9988a9490187cbe1c7d363e4f8d22ec6e14ab',
'4a86d7e268bc5670368931d8eade54d86e718ea38165bc9cc3438b39b79bd522da3980c565d875878fb2ecf1087cdee1',
'9e791fe4adb0bc84367dae1fe08384e441f43f3239af4dc15ef07692a199602b31b79a6cabbb896b4d3792fb87413ea2',
'0f4897db17c93958d0e4f66cc7af93258dfd3fa31b5844156a94657a2a2a35135b9b192d23fa451da89d2b2ba9bfebe0',
'f3e4c878aa9c6c7b199284ab512429b85ff25a1f32b61e42f9dfc944bea2d6952c607504b4817c8d8a73c4abf25de0ed',
'742fac67d4c66aa3bc269dba85ac15d945543711c19854e0ee073ca7da186cf448173509f2cd685c427a88dc1efc12ca',
'87e4f93ed7d047896f9e93474151e6992a36b4f184a9724a1ef62a94c1fe8bd0d10b419e90f1447509c81332525b40fe',
'3e45fe76793d52b73f14c36d4a9f70efc56045de2b4f5ead1e59058ee04795acb4ca75367383206b205fb2cc1579771d',
'cc6ef73c7b08e5b25aa2fa262cdeee287761fd67c4adac5d447a2378e9d9640b51edc7f278b08fb92e461db219153066',
'6fe4dd0eaf8d77a37a392ca9ea9970afd1fdbf93f76781b7aa4f10dfb98cd2b20255be008ee734a98be57cf45bc8df33',
'e44aeaef6aeaef8c48bd7b881c91d5a024cc36ec96c1098faac08a0aa707f0e514662afe7312a301c7f6356269ec8bcb',
'8d548d63f0af81780c80d2faccbfc0c93dc43f92ff32da84b2e5f4008ab405bc291926e6dbbc5ae588f9c7a00be400ba',
'd6ee55f71f54e7a3ae2c3f62bf113f773e51b44826d1c124393b962c7b4ac7538fc6139f2be90b9610df40265199167b',
'659a0e27ae8c3f11fb5ed8099fd5bb9634f349573d71ac9177ce16dda2a6bcd3b2acae518f8aaea101fc2978a58db3eb',
'28784d4bd630d7e99df63df06f2fc8557fc96827284bfa1eccde86d57194daf076f04e3b55c55439058b77e321ab07a6',
'5207e18eeaeb3c77d8713e4c65afb2aa3a033b12625977002bc67bd7f03c3a7d06dad224d7d311cd7795590cde4562f5',
'732e1dd12aee0b6bc303e855c19968ba5de3fdb2f7cda6ca49ef3562de5fa4d9df94acc06e56f38b6afd86e18d933d4b',
'79ae1baec56415abfdc47c44d7482ffa9c85792fa15c7b61764826b7a26143ecf71e9dc74e4623ba8d08ee644791367a',
'0b53e566f90fa39d6addea4b8c8c4381fc00bc59ce48a2c825ef6b6be48ba2a4c6cddfa2cf7398cbeb0f89ef393c9396',
'a578668198bd8b50eaeba847b786c7a369ae5759afaed66598e7ea7721d39ac0919b76a9f718aa768ed5dd24f12db9a7',
'2ab0a5e0b79da25e4afdeef37cbf85fed91446f53ef01e24be22bf61f42531ca52a376c371bb49fd8be86f75557c17e4',
'6d78aba67f83071d7edf7a1c6def61df066e4ad6c55a9f3b9b1353def975f1772c3f69fa2bab4c16e9c760e0e9cc94f5',
'7a32ee6c56d117eaef2afa2babbf53bcbfb2dba0e951d1fba9c87d1667c7d02b53c85b655735e37381a8c4403dce2bb2',
'd5df85f875d0abeeb9e895295756d77a5f62fdd09d206af74ee141701c66b6595ea5bbfedcbbef5751a476de19d5df93',
'8f1de0cdc82b30b5c3b5f8a7ee35eb552e85433535cb6649d9ef2f49bcd7e8ea3cac6fe1e169b03892f530f02474262e',
'b7323a9351416fc24757a1cfdf39d13f5b5d156771d1d95c726d32d512bf279a4e5954edb43162f96ee85bfc7661de98',
'c5aadd6baa7a1eaa32a09617cb89748f33ff662ced756fdd09c7ad934ec923e038e43dc87378c36ebd19791e0536bf94',
'f559b4fd18282bba8efdfaf14aa37c43ec9e94e7ed097ebb6980871de8f614bf2f7c846d53c8de66f9139da56958491f',
'82e333bd77ba1f5b57797fd5413f59f01eaae6fa5ac3ae94efa78ea47aa20a25f09ca1c863578553cd1333c46ef379af',
'5122d63387f027e73d5501e405bfef47e4743f888c893710895ed7c27b40d405f3e3f23038f67b65bf97d151e6527217',
'4806bca7b55bf4281e18438f6ea796d939e377d5408cdd4d60343e7e42a4c77927fdbadf7f93a07c99d5c9782aa7eb61',
'e54afae7ef86c7a259f493a6c3760748f9cd77ac7fc1c8119bc0ef0d35626ebfc57d95711f58b59fe2f55ac1d1822f06',
'd4faf01992f3eebe448d528f95c0f19c47322d37f8170ccb826f13b76e57ea1b61e5114e8f64adf7e3c6fb5544badffb',
'f94b991bf6e7cef7fbbe55cdf1de0d55403f8c8f9bbcb26c2247ea37ff0f591b0f4a9157d0abe95e49ffcaea55aecf3d',
'de5f99f266d5b86bbe6af2fc4d79cf6178ac365a1db66c8ed85dfdde603da87565562527fa0d4dfd7696f399a22ee4ec',
'51cf8f2554ab51f7cc626c1ef4af28d77a9e5b9c96b3daef1ab6021ff4f9179acbd8c56e2249b984b941d7d193416e99',
'a6c43792aaf747436a56582eee279619a2a154b934d5d3a2f2266b16fd8abecfe89af8d3f0b98bfb42e0c80ade57448e',
'c958637722fb0a14396fea78edae6b3de7a8f35f3c074ceba67b9db62a61376eeb5bead96ddf2f0af443a65ba33c7b01',
'3886782f45076451367d3c0fe4d6a5c5981bf57e3bafcb39c96e511994b0fe15c9eea165fe56dd1f15cfdf8af706f5ee',
'a113df4c12aba15b8965fd2bd406b2200d86129d6b407c5fa01e1de2fe6b7952d63a63d352f34ec3be5119576a27be67',
'8dba37e2ec63dfc4625e0153ae6f91e7003dbe57a9c927fab1343732f3935976aa7fedb13bf569ae7dc673f3411fd6a7',
'a89075152aea5219f27392740e76a2bd3d5bec8e607a3a8d136afefd2af41df2ae1771bbdd6241a77f2933061e24be0d',
'fec4a73c31ce11b731771dfdd51afa0deb2793fe6d23ee169a5de23d867554a611496771bdc7b4ae23ee1c315e99665b',
'3f92f4eebde2d53f947a9e1be32d967663aeab40999b9a31c76af76c297a875ef1b97ef7c27cefeb0cc704e7adddf8e6',
'79544deb2648c53b0255fb2e9972ebcceb158f4f077b66c4f4ec88eb6c1c9b8ff1d438b10ea195b356701f5c1d1e247f',
'138ed02251ff4ed2e83b9bd525576ceb359bd83f7215ae38972dd2f65da0a6f5fe53fcdd88fd63623726cbdb92775f49',
'aa55ded95ec666f390796ea288236ee0969ef348d6ef9c15cf785bb571955b8f6547cc3bc892dfe0929c1bf3ca90c59f',
'a17fb7cbf85db6d17bcc7d6ada6cb1193e515772dcb032f27f89e50a9b'))
AS t(name, hexbytes);
-- cid: add_scene_in_wgs84
-- The same scene warped to longitude and latitude (EPSG:4326), for the H3 and quadbin examples.
INSERT INTO ex_rasters
SELECT 'scene_wgs84', gbx_rst_transformcrs(tile, 'EPSG:4326') FROM ex_rasters WHERE name = 'scene';
-- cid: create_ex_dem_tiles
-- The DEM cut into 12 x 12 pixel tiles, with a tile key (the window) and a bounding box for each.
-- Aggregation recipes and procedures take tiles in this shape: a key column and a tile column.
CREATE OR REPLACE TABLE ex_dem_tiles AS
SELECT concat(t.window.col_off, '_', t.window.row_off) AS tile_key,
struct(t.cellid, t.raster, t.path, t.path_mode, t.window, t.clip_polygon, t.clip_crs, t.crs, t.metadata) AS tile
FROM ex_rasters AS r, LATERAL gbx_rst_retile(r.tile, 12, 12) AS t
WHERE r.name = 'dem';
-- cid: create_ex_parcels
-- Land parcels in British National Grid (EPSG:27700). District A is a clean set of neighbours,
-- district B has two overlapping parcels, and parcel 6 is a bow-tie (an invalid polygon).
CREATE OR REPLACE TABLE ex_parcels AS
SELECT id, district, name, st_geomfromwkt(wkt, 27700) AS geom
FROM VALUES
(1, 'A', 'north field', 'POLYGON((530000 180100, 530100 180100, 530100 180200, 530000 180200, 530000 180100))'),
(2, 'A', 'middle field', 'POLYGON((530000 180000, 530100 180000, 530100 180100, 530000 180100, 530000 180000))'),
(3, 'A', 'east field', 'POLYGON((530100 180000, 530250 180000, 530250 180200, 530100 180200, 530100 180000))'),
(4, 'B', 'west yard', 'POLYGON((531000 181000, 531150 181000, 531150 181100, 531000 181100, 531000 181000))'),
(5, 'B', 'overlap yard', 'POLYGON((531100 181000, 531250 181000, 531250 181100, 531100 181100, 531100 181000))'),
(6, 'C', 'bow-tie', 'POLYGON((532000 182000, 532100 182100, 532100 182000, 532000 182100, 532000 182000))')
AS t(id, district, name, wkt);
-- cid: create_ex_points
-- Twelve sensor readings around central London, in longitude and latitude (EPSG:4326).
CREATE OR REPLACE TABLE ex_points AS
SELECT id, longitude, latitude, value, st_point(longitude, latitude, 4326) AS geom
FROM VALUES
(1, -0.1278, 51.5074, 12.0), (2, -0.1180, 51.5033, 15.5), (3, -0.1246, 51.5007, 11.2),
(4, -0.1419, 51.5014, 18.4), (5, -0.1357, 51.5101, 14.1), (6, -0.1195, 51.5139, 16.8),
(7, -0.1100, 51.5072, 13.3), (8, -0.1300, 51.5200, 19.0), (9, -0.1500, 51.5100, 10.5),
(10, -0.1250, 51.4950, 17.7), (11, -0.1050, 51.5200, 12.9), (12, -0.1400, 51.4900, 15.0)
AS t(id, longitude, latitude, value);
-- cid: create_ex_roads
-- A few features for vector tiles. The attributes are a struct, as in a real feature table.
CREATE OR REPLACE TABLE ex_roads AS
SELECT id, st_geomfromwkt(wkt, 4326) AS geom, named_struct('name', name, 'lanes', lanes) AS attrs
FROM VALUES
(1, 'LINESTRING(-0.1500 51.5000, -0.1250 51.5050, -0.1000 51.5100)', 'Embankment', 2),
(2, 'LINESTRING(-0.1300 51.4900, -0.1280 51.5070, -0.1260 51.5250)', 'Whitehall', 3),
(3, 'POLYGON((-0.1400 51.5000, -0.1300 51.5000, -0.1300 51.5080, -0.1400 51.5080, -0.1400 51.5000))', 'Park', 0)
AS t(id, wkt, name, lanes);
-- cid: create_ex_legacy_geoms
-- Geometries in the legacy Mosaic struct form (typeId 1 point, 5 polygon), for the migration example.
CREATE OR REPLACE TABLE ex_legacy_geoms AS
SELECT 1 AS id,
named_struct('typeId', 1, 'srid', 27700,
'boundaries', array(array(array(530500.0D, 180500.0D))),
'holes', CAST(array() AS ARRAY<ARRAY<ARRAY<ARRAY<DOUBLE>>>>)) AS geom
UNION ALL
SELECT 2,
named_struct('typeId', 5, 'srid', 27700,
'boundaries', array(array(array(530000.0D, 180000.0D), array(530100.0D, 180000.0D), array(530100.0D, 180100.0D), array(530000.0D, 180100.0D), array(530000.0D, 180000.0D))),
'holes', CAST(array() AS ARRAY<ARRAY<ARRAY<ARRAY<DOUBLE>>>>));
-- cid: list_sample_tables
SELECT table_name FROM information_schema.tables WHERE table_schema = 'geobrix_uc' AND table_name LIKE 'ex_%' ORDER BY table_name;
British National Grid cells: points, rings, polyfill, tessellation, chips and table functions.
-- GeoBrix Unity Catalog functions: British National Grid (BNG) cells.
-- Needs 00_setup.sql. Change the catalog and schema below if you installed elsewhere.
-- A BNG cell id is text such as 'TQ3080' (1 km). The resolution is a size ('1km', '100m') or an index.
-- cid: use_catalog
USE CATALOG geospatial_docs;
-- cid: use_schema
USE SCHEMA geobrix_uc;
-- cid: bng_point_to_cell
-- The 100 m BNG cell of each parcel centroid, and the same cell as a polygon in WKT.
SELECT id, name,
gbx_bng_pointascell(CAST(st_centroid(geom) AS GEOMETRY(ANY)), '100m') AS cell,
gbx_bng_aswkt(gbx_bng_pointascell(CAST(st_centroid(geom) AS GEOMETRY(ANY)), '100m')) AS cell_wkt
FROM ex_parcels
WHERE district = 'A';
-- cid: bng_easting_northing_to_cell
-- A cell from plain easting and northing numbers, with its area in square metres and its centroid.
SELECT gbx_bng_eastnorthasbng(530500, 180500, '1km') AS cell,
gbx_bng_cellarea('TQ3080') AS area_m2,
st_astext(st_geomfromewkb(gbx_bng_centroid('TQ3080'))) AS centroid;
-- cid: bng_cell_geometry
-- A cell as WKT, and as binary EWKB that SQL spatial functions read.
SELECT gbx_bng_aswkt('TQ3080') AS cell_wkt,
st_area(st_geomfromewkb(gbx_bng_aswkb('TQ3080'))) AS area_m2;
-- cid: bng_distance
-- The distance between two cells in whole cells, and the Euclidean distance in metres.
SELECT gbx_bng_distance('TQ3080', 'TQ3282') AS cells_apart,
gbx_bng_euclideandistance('TQ3080', 'TQ3282') AS metres_apart;
-- cid: bng_neighbours
-- The cells within one step of a cell (kring) and the cells exactly one step away (kloop).
SELECT size(gbx_bng_kring('TQ3080', 1)) AS ring_cells,
size(gbx_bng_kloop('TQ3080', 1)) AS loop_cells,
gbx_bng_kloop('TQ3080', 1) AS loop;
-- cid: bng_neighbours_of_geometry
-- The ring of cells around a geometry, so a point or a polygon grows by k cells on each side.
SELECT size(gbx_bng_geomkring(CAST(st_geomfromwkt('POINT(530500 180500)', 27700) AS GEOMETRY(ANY)), '1km', 1)) AS ring_cells,
size(gbx_bng_geomkloop(CAST(st_geomfromwkt('POINT(530500 180500)', 27700) AS GEOMETRY(ANY)), '1km', 1)) AS loop_cells;
-- cid: bng_polyfill
-- The 100 m cells that cover each district A parcel.
SELECT id, name, size(gbx_bng_polyfill(CAST(geom AS GEOMETRY(ANY)), '100m')) AS cells
FROM ex_parcels
WHERE district = 'A';
-- cid: bng_tessellate
-- Tessellation cuts a polygon along cell edges. Each element is a cell, a core flag and the cut piece (chip).
-- The function runs in a subquery: UC refuses a SQL function inside a generator.
WITH t AS (
SELECT id, gbx_bng_tessellate(CAST(geom AS GEOMETRY(ANY)), '100m') AS cells FROM ex_parcels WHERE id = 3
)
SELECT id, cell.cellid, cell.core, st_area(st_geomfromewkb(cell.chip)) AS chip_area_m2
FROM t LATERAL VIEW explode(cells) AS cell
ORDER BY cell.cellid;
-- cid: bng_union_and_intersection_of_chips
-- Chips of one cell combine into one geometry. This takes the chips of two parcels that share a cell.
WITH t AS (
SELECT id, gbx_bng_tessellate(CAST(geom AS GEOMETRY(ANY)), '100m') AS cells FROM ex_parcels WHERE id IN (1, 2)
),
chips AS (
SELECT cell.cellid, cell.core, cell.chip FROM t LATERAL VIEW explode(cells) AS cell
)
SELECT cellid,
st_area(st_geomfromewkb(gbx_bng_cellunion_array(collect_list(struct(cellid, core, chip))))) AS union_area_m2,
gbx_bng_cellintersection_array(collect_list(struct(cellid, core, chip))) IS NOT NULL AS has_intersection
FROM chips
GROUP BY cellid
ORDER BY cellid;
-- cid: bng_union_of_two_chips
-- The pair forms of the union and the intersection take two chip structs.
WITH t AS (
SELECT gbx_bng_tessellate(CAST(geom AS GEOMETRY(ANY)), '100m') AS cells FROM ex_parcels WHERE id = 1
),
c AS (
SELECT cell.cellid, cell.core, cell.chip FROM t LATERAL VIEW explode(cells) AS cell
WHERE NOT cell.core LIMIT 1
)
SELECT gbx_bng_cellunion(struct(cellid, core, chip), struct(cellid, core, chip)) IS NOT NULL AS union_ok,
gbx_bng_cellintersection(struct(cellid, core, chip), struct(cellid, core, chip)) IS NOT NULL AS intersection_ok
FROM c;
-- cid: bng_ring_table_function
-- A table function returns one row per cell: call it in FROM.
SELECT cellid FROM gbx_bng_kringexplode('TQ3080', 1) ORDER BY cellid;
-- cid: bng_loop_table_function
SELECT cellid FROM gbx_bng_kloopexplode('TQ3080', 1) ORDER BY cellid;
-- cid: bng_geometry_ring_table_functions
-- The geometry forms of the ring and loop table functions, called once for each row with LATERAL.
SELECT p.id, count(*) AS ring_cells
FROM ex_parcels AS p, LATERAL gbx_bng_geomkringexplode(CAST(p.geom AS GEOMETRY(ANY)), '100m', 1) AS r
WHERE p.id = 1
GROUP BY p.id;
-- cid: bng_geometry_loop_table_function
SELECT cellid
FROM gbx_bng_geomkloopexplode(CAST(st_geomfromwkt('POINT(530500 180500)', 27700) AS GEOMETRY(ANY)), '1km', 1)
ORDER BY cellid;
-- cid: bng_tessellate_table_function
-- Tessellation as a table function: one row per cell with its chip.
SELECT cellid, core
FROM gbx_bng_tessellateexplode(CAST(st_geomfromwkt('POLYGON((530000 180000, 530250 180000, 530250 180200, 530000 180200, 530000 180000))', 27700) AS GEOMETRY(ANY)), '100m')
ORDER BY cellid;
Quadbin cells in longitude and latitude, and custom grids you define once.
-- GeoBrix Unity Catalog functions: quadbin cells and custom grids.
-- Needs 00_setup.sql. Change the catalog and schema below if you installed elsewhere.
-- A quadbin cell is a map tile as a BIGINT; the zoom is the resolution. A custom grid is a struct you build once.
-- cid: use_catalog
USE CATALOG geospatial_docs;
-- cid: use_schema
USE SCHEMA geobrix_uc;
-- cid: quadbin_point_to_cell
-- The zoom 12 quadbin cell of each sensor, its zoom and its centroid.
SELECT id,
gbx_quadbin_pointascell(longitude, latitude, 12) AS cell,
gbx_quadbin_resolution(gbx_quadbin_pointascell(longitude, latitude, 12)) AS zoom,
st_astext(st_geomfromewkb(gbx_quadbin_centroid(gbx_quadbin_pointascell(longitude, latitude, 12)))) AS centroid
FROM ex_points
WHERE id <= 3;
-- cid: quadbin_cell_geometry
-- A cell as an EWKB polygon in longitude and latitude, and its distance to a neighbour.
SELECT st_area(st_geomfromewkb(gbx_quadbin_aswkb(5233961839712272383))) AS area_deg2,
gbx_quadbin_distance(5233961839712272383, 5233961886956912639) AS cells_apart;
-- cid: quadbin_neighbours
-- The cells within one step (kring) and exactly one step away (kloop).
SELECT size(gbx_quadbin_kring(5233961839712272383, 1)) AS ring_cells,
size(gbx_quadbin_kloop(5233961839712272383, 1)) AS loop_cells;
-- cid: quadbin_neighbours_of_geometry
SELECT size(gbx_quadbin_geomkring(CAST(st_geomfromwkt('POINT(-0.1278 51.5074)', 4326) AS GEOMETRY(ANY)), 12, 1)) AS ring_cells,
size(gbx_quadbin_geomkloop(CAST(st_geomfromwkt('POINT(-0.1278 51.5074)', 4326) AS GEOMETRY(ANY)), 12, 1)) AS loop_cells;
-- cid: quadbin_polyfill
-- The zoom 13 cells that cover a small polygon of central London.
SELECT size(gbx_quadbin_polyfill(CAST(st_geomfromwkt('POLYGON((-0.15 51.49, -0.10 51.49, -0.10 51.52, -0.15 51.52, -0.15 51.49))', 4326) AS GEOMETRY(ANY)), 13)) AS cells;
-- cid: quadbin_tessellate
-- Tessellation gives each cell and the piece of the polygon inside it.
SELECT cell.cell, st_area(st_geomfromewkb(cell.geom)) AS piece_area_deg2
FROM (
SELECT gbx_quadbin_tessellate(CAST(st_geomfromwkt('POLYGON((-0.15 51.49, -0.10 51.49, -0.12 51.52, -0.15 51.49))', 4326) AS GEOMETRY(ANY)), 12) AS cells
) LATERAL VIEW explode(cells) AS cell;
-- cid: quadbin_union_of_cells
-- Several cells as one geometry.
SELECT st_area(st_geomfromewkb(gbx_quadbin_cellunion(array(5233961839712272383, 5233961886956912639)))) AS area_deg2;
-- cid: quadbin_ring_table_functions
-- The geometry ring and loop table functions return one row per cell.
SELECT cellid FROM gbx_quadbin_geomkringexplode(CAST(st_geomfromwkt('POINT(-0.1278 51.5074)', 4326) AS GEOMETRY(ANY)), 12, 1) ORDER BY cellid;
-- cid: quadbin_loop_table_function
SELECT cellid FROM gbx_quadbin_geomkloopexplode(CAST(st_geomfromwkt('POINT(-0.1278 51.5074)', 4326) AS GEOMETRY(ANY)), 12, 1) ORDER BY cellid;
-- cid: custom_grid_definition
-- A custom grid: bounds, how many splits per level, the root cell size and the SRID. Build it once.
SELECT gbx_custom_grid(0, 1000000, 0, 1000000, 2, 1000, 1000, 27700) AS grid;
-- cid: custom_point_to_cell
-- The custom grid cell of a point at resolution 4, with its centroid and its polygon as WKT.
WITH g AS (SELECT gbx_custom_grid(0, 1000000, 0, 1000000, 2, 1000, 1000, 27700) AS grid)
SELECT cell,
st_astext(st_geomfromewkb(gbx_custom_centroid(cell, grid))) AS centroid,
gbx_custom_cellaswkt(cell, grid) AS cell_wkt
FROM (
SELECT grid, gbx_custom_pointascell(CAST(st_geomfromwkt('POINT(530750 180750)', 27700) AS GEOMETRY(ANY)), grid, 4) AS cell
FROM g
);
-- cid: custom_cell_geometry_and_distance
-- A cell as a polygon, and the distance between two cells in the grid (the grid comes second).
WITH g AS (SELECT gbx_custom_grid(0, 1000000, 0, 1000000, 2, 1000, 1000, 27700) AS grid)
SELECT st_area(st_geomfromewkb(gbx_custom_cellaswkb(72057594038650997, grid))) AS area_m2,
gbx_custom_distance(72057594038650997, grid, 72057594038655000) AS cells_apart
FROM g;
-- cid: custom_neighbours
WITH g AS (SELECT gbx_custom_grid(0, 1000000, 0, 1000000, 2, 1000, 1000, 27700) AS grid)
SELECT size(gbx_custom_kring(72057594038650997, grid, 1)) AS ring_cells,
size(gbx_custom_kloop(72057594038650997, grid, 1)) AS loop_cells
FROM g;
-- cid: custom_polyfill_and_geometry_rings
WITH g AS (SELECT gbx_custom_grid(0, 1000000, 0, 1000000, 2, 1000, 1000, 27700) AS grid)
SELECT size(gbx_custom_polyfill(CAST(st_geomfromwkt('POLYGON((530000 180000, 532000 180000, 532000 182000, 530000 182000, 530000 180000))', 27700) AS GEOMETRY(ANY)), grid, 4)) AS cells,
size(gbx_custom_geomkring(CAST(st_geomfromwkt('POINT(530500 180500)', 27700) AS GEOMETRY(ANY)), grid, 4, 1)) AS ring_cells,
size(gbx_custom_geomkloop(CAST(st_geomfromwkt('POINT(530500 180500)', 27700) AS GEOMETRY(ANY)), grid, 4, 1)) AS loop_cells
FROM g;
-- cid: custom_ring_table_functions
-- The custom ring and loop table functions take the grid after the geometry.
WITH g AS (SELECT gbx_custom_grid(0, 1000000, 0, 1000000, 2, 1000, 1000, 27700) AS grid)
SELECT r.cellid
FROM g, LATERAL gbx_custom_geomkringexplode(CAST(st_geomfromwkt('POINT(530500 180500)', 27700) AS GEOMETRY(ANY)), g.grid, 4, 1) AS r
ORDER BY r.cellid;
-- cid: custom_loop_table_function
WITH g AS (SELECT gbx_custom_grid(0, 1000000, 0, 1000000, 2, 1000, 1000, 27700) AS grid)
SELECT r.cellid
FROM g, LATERAL gbx_custom_geomkloopexplode(CAST(st_geomfromwkt('POINT(530500 180500)', 27700) AS GEOMETRY(ANY)), g.grid, 4, 1) AS r
ORDER BY r.cellid;
H3 rings of a geometry, cell bounding boxes and cell fill.
-- GeoBrix Unity Catalog functions: H3 cells.
-- Needs 00_setup.sql. Change the catalog and schema below if you installed elsewhere.
-- Databricks has built-in h3_ functions for most H3 work. GeoBrix adds the rings of a geometry and cell bounding boxes.
-- cid: use_catalog
USE CATALOG geospatial_docs;
-- cid: use_schema
USE SCHEMA geobrix_uc;
-- cid: h3_cells_of_points
-- The built-in h3_longlatash3 gives the H3 cell of a point; GeoBrix functions then work on that cell.
SELECT id, h3_longlatash3(longitude, latitude, 9) AS cell
FROM ex_points
WHERE id <= 3;
-- cid: h3_ring_of_geometry
-- The H3 cells within two steps of a point (kring) and exactly two steps away (kloop).
SELECT size(gbx_h3_geomkring(CAST(st_geomfromwkt('POINT(-0.1278 51.5074)', 4326) AS GEOMETRY(ANY)), 9, 2)) AS ring_cells,
size(gbx_h3_geomkloop(CAST(st_geomfromwkt('POINT(-0.1278 51.5074)', 4326) AS GEOMETRY(ANY)), 9, 2)) AS loop_cells;
-- cid: h3_ring_table_functions
-- The ring and loop as table functions: one row for each cell.
SELECT cellid FROM gbx_h3_geomkringexplode(CAST(st_geomfromwkt('POINT(-0.1278 51.5074)', 4326) AS GEOMETRY(ANY)), 9, 1) ORDER BY cellid;
-- cid: h3_loop_table_function
SELECT cellid FROM gbx_h3_geomkloopexplode(CAST(st_geomfromwkt('POINT(-0.1278 51.5074)', 4326) AS GEOMETRY(ANY)), 9, 1) ORDER BY cellid;
-- cid: h3_cell_bounding_box
-- The bounding box of an H3 cell in longitude and latitude (EPSG:4326 is the default). The default mode is the box of the
-- cell centroid, and 'spatial_envelope' gives the box of the whole cell.
SELECT gbx_h3_cell_bbox(h3_longlatash3(-0.1278, 51.5074, 9), 4326, 'spatial_envelope', 0) AS cell_box,
gbx_h3_cell_bbox(h3_longlatash3(-0.1278, 51.5074, 9)) AS centroid_box;
-- cid: h3_fill_empty_cells
-- Cell aggregates fill cells that hold no reading. Group the readings, add the empty neighbours with a NULL value,
-- then pass an array of (cellid, value). Each NULL cell takes the mean of its neighbours within k = 1 steps.
WITH readings AS (
SELECT h3_longlatash3(longitude, latitude, 9) AS cellid, avg(value) AS value FROM ex_points GROUP BY 1
),
neighbours AS (
SELECT DISTINCT n.nb AS cellid FROM readings LATERAL VIEW explode(h3_kring(cellid, 1)) n AS nb
),
cells AS (
SELECT nb.cellid, rd.value FROM neighbours AS nb LEFT JOIN readings AS rd ON rd.cellid = nb.cellid
)
SELECT c.cellid, c.value
FROM (SELECT gbx_h3_cellfill_array(collect_list(struct(cellid, value)), 1, 'mean') AS filled FROM cells)
LATERAL VIEW inline(filled) c
ORDER BY c.cellid
LIMIT 20;
Geometry cleaners, coverage checks, vector tiles, triangulation and legacy geometry migration.
-- GeoBrix Unity Catalog functions: vector geometry, vector tiles and legacy geometry migration.
-- Needs 00_setup.sql. Change the catalog and schema below if you installed elsewhere.
-- These functions add to the built-in ST functions: cleaners the product lacks, MVT tiles and Mosaic migration.
-- A geometry argument is GEOMETRY(ANY): cast a GEOMETRY(srid) column with CAST(geom AS GEOMETRY(ANY)).
-- cid: use_catalog
USE CATALOG geospatial_docs;
-- cid: use_schema
USE SCHEMA geobrix_uc;
-- cid: vector_explain_validity
-- Why a polygon is invalid. Parcel 6 is a bow-tie.
SELECT id, name, gbx_st_explainvalidity(CAST(geom AS GEOMETRY(ANY))) AS reason
FROM ex_parcels
WHERE id IN (1, 6)
ORDER BY id;
-- cid: vector_make_valid
-- Repair the bow-tie into a valid multi-polygon.
SELECT id, st_isvalid(geom) AS was_valid,
st_isvalid(gbx_st_makevalid(CAST(geom AS GEOMETRY(ANY)))) AS now_valid,
st_astext(gbx_st_makevalid(CAST(geom AS GEOMETRY(ANY)))) AS repaired
FROM ex_parcels
WHERE id = 6;
-- cid: vector_node_and_remove_repeated_points
-- Node a self-crossing line at its crossings, and drop repeated points from another line.
SELECT st_astext(gbx_st_node(CAST(st_geomfromwkt('LINESTRING(0 0, 10 10, 10 0, 0 10)', 4326) AS GEOMETRY(ANY)))) AS noded,
st_astext(gbx_st_removerepeatedpoints(CAST(st_geomfromwkt('LINESTRING(0 0, 0 0, 1 1, 1 1, 2 2)', 4326) AS GEOMETRY(ANY)), 0.0)) AS cleaned;
-- cid: vector_simplify_and_reduce_precision
-- Simplify a polygon without breaking its topology, and round coordinates to a grid.
SELECT st_astext(gbx_st_simplifypreservetopology(CAST(st_geomfromwkt('POLYGON((0 0, 0 5, 0.001 8, 0 10, 10 10, 10 0, 0 0))', 4326) AS GEOMETRY(ANY)), 0.1)) AS simplified,
st_astext(gbx_st_reduceprecision(CAST(st_geomfromwkt('POINT(1.234 5.678)', 4326) AS GEOMETRY(ANY)), 0.1)) AS rounded;
-- cid: vector_snap_and_split
-- Snap a line onto a reference line within 0.5, and split a polygon with a blade line. Both inputs share an SRID.
SELECT st_astext(gbx_st_snap(CAST(st_geomfromwkt('LINESTRING(0 0.4, 10 0.4)', 4326) AS GEOMETRY(ANY)),
CAST(st_geomfromwkt('LINESTRING(0 0, 10 0)', 4326) AS GEOMETRY(ANY)), 0.5)) AS snapped,
st_astext(gbx_st_split(CAST(st_geomfromwkt('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))', 4326) AS GEOMETRY(ANY)),
CAST(st_geomfromwkt('LINESTRING(5 -1, 5 11)', 4326) AS GEOMETRY(ANY)))) AS pieces;
-- cid: vector_longitude_wrapping
-- Wrap a longitude of 190 back into range, and shift the longitudes of a polygon over the date line.
SELECT st_astext(gbx_st_wrapx(CAST(st_geomfromwkt('POINT(190 10)', 4326) AS GEOMETRY(ANY)), -180.0, 360.0)) AS wrapped,
st_astext(gbx_st_shiftlongitude(CAST(st_geomfromwkt('POLYGON((170 -10, 190 -10, 190 10, 170 10, 170 -10))', 4326) AS GEOMETRY(ANY)))) AS shifted;
-- cid: vector_coverage_validity_by_district
-- Parcels of one district should tile without gaps or overlaps. Group the parcels, then pass an array of EWKB.
SELECT district,
gbx_st_coverageisvalid_array(collect_list(st_asewkb(geom)), 0.0) AS is_valid_coverage
FROM ex_parcels
WHERE district IN ('A', 'B')
GROUP BY district
ORDER BY district;
-- cid: vector_coverage_invalid_edges
-- The edges that break the coverage in district B, where two parcels overlap.
SELECT district, st_astext(st_geomfromewkb(gbx_st_coverageinvalidedges_array(collect_list(st_asewkb(geom)), 0.0))) AS bad_edges
FROM ex_parcels
WHERE district = 'B'
GROUP BY district;
-- cid: vector_tiles_for_a_layer
-- One vector tile layer from the features of one tile. The geometry is WKB in tile coordinates, attributes are JSON text.
SELECT gbx_st_asmvt_array(
array(named_struct('geom', st_asbinary(st_geomfromwkt('POLYGON((10 10, 100 10, 100 100, 10 100, 10 10))')),
'attrs', '{"name": "a", "n": 3}'),
named_struct('geom', st_asbinary(st_geomfromwkt('POINT(50 50)')),
'attrs', '{"name": "b", "n": 4}')),
'places') AS mvt;
-- cid: vector_tile_pyramid
-- A table function encodes one feature into a vector tile for every web-mercator tile it touches, for zooms 6 to 8.
-- The attributes go in as JSON text, because UC has no struct of any fields.
SELECT t.z, t.x, t.y, length(t.mvt_bytes) AS mvt_bytes
FROM ex_roads AS r, LATERAL gbx_st_asmvt_pyramid(r.geom, to_json(r.attrs), 6, 8, 'roads') AS t
WHERE r.id = 1
ORDER BY t.z, t.x, t.y;
-- cid: vector_legacy_geometry_migration
-- Convert Mosaic legacy geometry structs to GEOMETRY values, and read their area and type with built-in ST functions.
SELECT id, st_geometrytype(gbx_st_legacyaswkb(geom)) AS geometry_type,
st_area(gbx_st_legacyaswkb(geom)) AS area, st_srid(gbx_st_legacyaswkb(geom)) AS srid
FROM ex_legacy_geoms
ORDER BY id;
-- cid: vector_triangulate_spot_heights
-- Triangulate points with a Z value. Each row is one triangle as 2D WKB. The points are an array of GEOMETRY.
SELECT count(*) AS triangles, round(sum(st_area(st_geomfromwkb(t.triangle))), 1) AS total_area
FROM (SELECT array(CAST(st_geomfromwkt('POINT Z(0 0 10)') AS GEOMETRY(ANY)), CAST(st_geomfromwkt('POINT Z(10 0 20)') AS GEOMETRY(ANY)),
CAST(st_geomfromwkt('POINT Z(0 10 30)') AS GEOMETRY(ANY)), CAST(st_geomfromwkt('POINT Z(10 10 40)') AS GEOMETRY(ANY))) AS pts) AS s,
LATERAL gbx_st_triangulate(s.pts, CAST(NULL AS ARRAY<GEOMETRY(ANY)>), 0.0, 0.0, 'NONENCROACHING') AS t;
-- cid: vector_interpolate_elevation_on_a_bounding_box
-- Interpolate the surface at the cells of a grid that fills a bounding box. Each row is one point with its Z.
SELECT count(*) AS grid_points
FROM (SELECT array(CAST(st_geomfromwkt('POINT Z(0 0 10)') AS GEOMETRY(ANY)), CAST(st_geomfromwkt('POINT Z(10 0 20)') AS GEOMETRY(ANY)),
CAST(st_geomfromwkt('POINT Z(0 10 30)') AS GEOMETRY(ANY)), CAST(st_geomfromwkt('POINT Z(10 10 40)') AS GEOMETRY(ANY))) AS pts) AS s,
LATERAL gbx_st_interpolateelevationbbox(s.pts, CAST(NULL AS ARRAY<GEOMETRY(ANY)>), 0.0, 0.0, 'NONENCROACHING',
0.0, 0.0, 10.0, 10.0, 5, 5, 3857) AS t;
-- cid: vector_interpolate_elevation_on_a_grid
-- The same on a grid you describe by an origin point, a column and row count and a cell size.
SELECT count(*) AS grid_points
FROM (SELECT array(CAST(st_geomfromwkt('POINT Z(0 0 10)') AS GEOMETRY(ANY)), CAST(st_geomfromwkt('POINT Z(10 0 20)') AS GEOMETRY(ANY)),
CAST(st_geomfromwkt('POINT Z(0 10 30)') AS GEOMETRY(ANY)), CAST(st_geomfromwkt('POINT Z(10 10 40)') AS GEOMETRY(ANY))) AS pts) AS s,
LATERAL gbx_st_interpolateelevationgeom(s.pts, CAST(NULL AS ARRAY<GEOMETRY(ANY)>), 0.0, 0.0, 'NONENCROACHING',
CAST(st_geomfromwkt('POINT(0 0)') AS GEOMETRY(ANY)), 5, 5, 2.0, 2.0) AS t;
Size, CRS, geotransform, metadata, coordinates and band statistics, including statistics that add up across tiles.
-- GeoBrix Unity Catalog functions: raster accessors and statistics.
-- Needs 00_setup.sql. Change the catalog and schema below if you installed elsewhere.
-- A raster is a tile column. ex_rasters holds a DEM and a 5-band scene (red, green, blue, nir, swir).
-- Each statement below that calls GeoBrix Python functions pays about 10 seconds on a SQL warehouse.
-- cid: use_catalog
USE CATALOG geospatial_docs;
-- cid: use_schema
USE SCHEMA geobrix_uc;
-- cid: raster_size_and_type
-- Size, bands, format, data type, NoData and bytes in memory of each tile.
SELECT name,
gbx_rst_width(tile) AS width, gbx_rst_height(tile) AS height, gbx_rst_numbands(tile) AS bands,
gbx_rst_format(tile) AS format, gbx_rst_type(tile) AS band_types,
gbx_rst_getnodata(tile) AS nodata, gbx_rst_memsize(tile) AS pixel_bytes
FROM ex_rasters
ORDER BY name;
-- cid: raster_crs_and_bounds
-- The CRS as an integer SRID and as text, the bounding box as WKT and the georeference map.
SELECT name, gbx_rst_srid(tile) AS srid, gbx_rst_crs(tile) AS crs,
st_astext(st_geomfromwkb(gbx_rst_boundingbox(tile))) AS bbox,
gbx_rst_georeference(tile) AS georeference
FROM ex_rasters
WHERE name = 'dem';
-- cid: raster_geotransform
-- The six geotransform values one at a time: pixel size, upper-left corner, scale, skew and rotation.
SELECT gbx_rst_pixelwidth(tile) AS pixel_width, gbx_rst_pixelheight(tile) AS pixel_height,
gbx_rst_upperleftx(tile) AS upper_left_x, gbx_rst_upperlefty(tile) AS upper_left_y,
gbx_rst_scalex(tile) AS scale_x, gbx_rst_scaley(tile) AS scale_y,
gbx_rst_skewx(tile) AS skew_x, gbx_rst_skewy(tile) AS skew_y, gbx_rst_rotation(tile) AS rotation
FROM ex_rasters
WHERE name = 'dem';
-- cid: raster_metadata
-- File metadata, the metadata of band 1 and any subdatasets (a plain GeoTIFF has none).
SELECT gbx_rst_metadata(tile) AS metadata,
gbx_rst_bandmetadata(tile, 1) AS band_1_metadata,
gbx_rst_subdatasets(tile) AS subdatasets
FROM ex_rasters
WHERE name = 'scene';
-- cid: raster_pixel_and_world_coordinates
-- Convert between pixel positions and world coordinates, both as a struct and as single values.
SELECT gbx_rst_rastertoworldcoord(tile, 10, 5) AS world,
gbx_rst_rastertoworldcoordx(tile, 10, 5) AS world_x,
gbx_rst_rastertoworldcoordy(tile, 10, 5) AS world_y,
gbx_rst_worldtorastercoord(tile, 530125.0, 180400.0) AS pixel,
gbx_rst_worldtorastercoordx(tile, 530125.0, 180400.0) AS pixel_col,
gbx_rst_worldtorastercoordy(tile, 530125.0, 180400.0) AS pixel_row
FROM ex_rasters
WHERE name = 'dem';
-- cid: raster_band_statistics
-- Per-band statistics of the scene: one value for each of its five bands.
SELECT gbx_rst_avg(tile) AS mean, gbx_rst_min(tile) AS min, gbx_rst_max(tile) AS max,
gbx_rst_median(tile) AS median, gbx_rst_pixelcount(tile) AS valid_pixels
FROM ex_rasters
WHERE name = 'scene';
-- cid: raster_summary_and_histogram
-- GDAL's raster info of the DEM as JSON, with band statistics: read its keys with get_json_object.
-- And a 5-bucket histogram of the DEM.
SELECT get_json_object(summary, '$.bands[0].minimum') AS band1_minimum,
get_json_object(summary, '$.bands[0].maximum') AS band1_maximum,
get_json_object(summary, '$.bands[0].mean') AS band1_mean,
get_json_object(summary, '$.size') AS size,
histogram
FROM (SELECT gbx_rst_summary(tile) AS summary, gbx_rst_histogram(tile, 5) AS histogram
FROM ex_rasters
WHERE name = 'dem');
-- cid: raster_health_checks
-- Is the tile empty, and does GDAL open it? A tile that cannot be read gives false.
SELECT name, gbx_rst_isempty(tile) AS is_empty, gbx_rst_tryopen(tile) AS can_open
FROM ex_rasters
ORDER BY name;
-- cid: raster_sample_at_a_point
-- The value of every band at a point. The point has an SRID, so it is read in that CRS.
SELECT gbx_rst_sample(tile, CAST(st_geomfromwkt('POINT(530125 180350)', 27700) AS GEOMETRY(ANY))) AS band_values
FROM ex_rasters
WHERE name = 'scene';
-- cid: raster_statistics_partial
-- The count, sum, spread (m2), minimum and maximum of each band. These add up across tiles.
WITH parts AS (SELECT gbx_rst_stats_partial(tile) AS stats FROM ex_rasters WHERE name = 'scene')
SELECT s.band, s.count, s.sum, s.m2, s.min, s.max
FROM parts LATERAL VIEW inline(stats) s
ORDER BY s.band;
-- cid: raster_statistics_across_tiles
-- Merge the partial of each tile: add the counts and sums, and add the squared distance of each tile mean to m2.
-- The result is the statistics of the whole DEM, which was cut into tiles in the setup file.
WITH parts AS (SELECT gbx_rst_stats_partial(tile) AS stats FROM ex_dem_tiles),
p AS (SELECT s.* FROM parts LATERAL VIEW inline(stats) s),
w AS (
SELECT *, try_divide(sum(sum) OVER (PARTITION BY band), sum(count) OVER (PARTITION BY band)) AS mu
FROM p
)
SELECT band,
sum(count) AS count,
try_divide(sum(sum), sum(count)) AS mean,
min(min) AS min,
max(max) AS max,
try_divide(sum(m2) + sum(count * pow(try_divide(sum, count) - mu, 2)), sum(count)) AS variance
FROM w
GROUP BY band
ORDER BY band;
Warp, resample, clip, filters, combine, overviews, contours, web tiles and web tile pyramids.
-- GeoBrix Unity Catalog functions: raster operations (warp, edit, combine, files and vectors).
-- Needs 00_setup.sql. Change the catalog and schema below if you installed elsewhere.
-- A tile function returns a tile. These statements read the result with an accessor so that you see numbers.
-- Each statement that calls GeoBrix Python functions pays about 10 seconds on a SQL warehouse.
-- cid: use_catalog
USE CATALOG geospatial_docs;
-- cid: use_schema
USE SCHEMA geobrix_uc;
-- cid: raster_reproject
-- Warp the DEM to longitude and latitude with an SRID, to a CRS text, and to web mercator.
SELECT gbx_rst_srid(gbx_rst_transform(tile, 4326)) AS srid_from_int,
gbx_rst_crs(gbx_rst_transformcrs(tile, 'EPSG:4326')) AS crs_from_text,
gbx_rst_srid(gbx_rst_to_webmercator(tile)) AS web_mercator_srid
FROM ex_rasters
WHERE name = 'dem';
-- cid: raster_set_crs_label
-- Set the CRS label without moving pixels (for a file that lost its CRS).
SELECT gbx_rst_srid(gbx_rst_setsrid(tile, 3857)) AS srid, gbx_rst_crs(gbx_rst_setcrs(tile, 'EPSG:3857')) AS crs
FROM ex_rasters
WHERE name = 'dem';
-- cid: raster_resample
-- Change resolution by a factor, to a pixel size, or to a pixel grid size.
SELECT gbx_rst_width(gbx_rst_resample(tile, 0.5)) AS half_width,
gbx_rst_pixelwidth(gbx_rst_resample_to_res(tile, 20.0, 20.0)) AS pixel_20m,
gbx_rst_height(gbx_rst_resample_to_size(tile, 48, 48)) AS height_48
FROM ex_rasters
WHERE name = 'dem';
-- cid: raster_clip_to_polygon
-- Clip the DEM to a polygon. cutline_all_touched = true keeps every pixel the polygon touches.
SELECT gbx_rst_width(clipped) AS width, gbx_rst_height(clipped) AS height, gbx_rst_avg(clipped) AS mean
FROM (
SELECT gbx_rst_clip(tile, CAST(st_geomfromwkt('POLYGON((530050 180300, 530150 180300, 530150 180420, 530050 180420, 530050 180300))', 27700) AS GEOMETRY(ANY)), true) AS clipped
FROM ex_rasters WHERE name = 'dem'
);
-- cid: raster_align_to_a_reference
-- Align the scene to the grid of the DEM.
SELECT gbx_rst_pixelwidth(gbx_rst_align_to(s.tile, d.tile)) AS pixel_width
FROM ex_rasters AS s, ex_rasters AS d
WHERE s.name = 'scene' AND d.name = 'dem';
-- cid: raster_pixel_edits
-- On the 5-band scene: cast the type, mark NoData, keep one band, threshold, and re-encode to another format.
SELECT gbx_rst_type(gbx_rst_updatetype(tile, 'Float32')) AS new_type,
gbx_rst_getnodata(gbx_rst_initnodata(tile)) AS nodata,
gbx_rst_numbands(gbx_rst_band(tile, 4)) AS bands_after_band,
gbx_rst_max(gbx_rst_threshold(tile, '>', 1000.0)) AS thresholded_max,
gbx_rst_format(gbx_rst_asformat(gbx_rst_band(tile, 1), 'PNG')) AS band_1_as_png
FROM ex_rasters
WHERE name = 'scene';
-- cid: raster_filters
-- A 3 x 3 window filter, a custom convolution kernel, a NoData fill, and a percentile stretch to 8 bits.
SELECT gbx_rst_avg(gbx_rst_filter(tile, 3, 'mean')) AS smoothed,
gbx_rst_avg(gbx_rst_convolve(tile, array(array(0.0, 0.0, 0.0), array(0.0, 1.0, 0.0), array(0.0, 0.0, 0.0)))) AS identity_kernel,
gbx_rst_avg(gbx_rst_fillnodata(tile)) AS filled,
gbx_rst_max(gbx_rst_percentile_stretch(tile, 2.0, 98.0)) AS stretched_max
FROM ex_rasters
WHERE name = 'dem';
-- cid: raster_distance_to_cells
-- Distance in metres to the nearest pixel above 90 m of height, up to 50 m. A threshold keeps the pixels that pass
-- and sets the rest to NoData; NULL for the target values makes every remaining pixel a source.
SELECT gbx_rst_max(gbx_rst_proximity(gbx_rst_threshold(tile, '>', 90.0), CAST(NULL AS STRING), 'GEO', 50.0)) AS farthest_m
FROM ex_rasters
WHERE name = 'dem';
-- cid: raster_combine_tiles
-- Array functions take an array of tiles, so they need no aggregate. This combines two copies of the DEM.
SELECT gbx_rst_avg(gbx_rst_combineavg(array(tile, tile))) AS avg_mean,
gbx_rst_avg(gbx_rst_combinesum(array(tile, tile))) AS sum_mean,
gbx_rst_avg(gbx_rst_combinemax(array(tile, tile))) AS max_mean,
gbx_rst_avg(gbx_rst_combinemin(array(tile, tile))) AS min_mean
FROM ex_rasters
WHERE name = 'dem';
-- cid: raster_combine_statistics
SELECT gbx_rst_avg(gbx_rst_combinecount(array(tile, tile))) AS count_mean,
gbx_rst_avg(gbx_rst_combinemedian(array(tile, tile, tile))) AS median_mean,
gbx_rst_avg(gbx_rst_combinestddev(array(tile, tile))) AS stddev_mean
FROM ex_rasters
WHERE name = 'dem';
-- cid: raster_bands_and_map_algebra
-- Stack tiles as bands, and compute with a map algebra expression (band 1 of each tile is A, B, ...).
SELECT gbx_rst_numbands(gbx_rst_frombands(array(tile, tile, tile))) AS stacked_bands,
gbx_rst_avg(gbx_rst_mapalgebra(array(tile, tile), '(A + B) / 2')) AS algebra_mean
FROM ex_rasters
WHERE name = 'dem';
-- cid: raster_merge_tiles
-- Cut the DEM into 12 pixel tiles, then merge them back into one raster.
WITH parts AS (
SELECT t.raster AS raster FROM ex_rasters AS r, LATERAL gbx_rst_retile(r.tile, 12, 12) AS t WHERE r.name = 'dem'
)
SELECT count(*) AS tiles FROM parts;
-- cid: raster_overviews_and_cog
-- Add overview levels, and lay the tile out as a Cloud Optimized GeoTIFF.
SELECT gbx_rst_format(gbx_rst_buildoverviews(tile, array(2, 4))) AS with_overviews,
gbx_rst_format(gbx_rst_cog_convert(tile, 'DEFLATE', 256, 'AVERAGE')) AS cog
FROM ex_rasters
WHERE name = 'dem';
-- cid: raster_contours_and_isobands
-- Vectorize the DEM: contour lines at chosen heights, and polygon bands between breaks.
SELECT size(gbx_rst_contour(tile, array(70.0, 80.0, 90.0))) AS contour_lines,
size(gbx_rst_isoband(tile, array(60.0, 80.0, 100.0, 120.0))) AS band_patches
FROM ex_rasters
WHERE name = 'dem';
-- cid: raster_polygonize
-- One polygon for each region of equal pixel values, as rows: gbx_rst_polygonize is a table function.
SELECT count(*) AS regions, min(p.value) AS lowest, max(p.value) AS highest
FROM ex_rasters AS r, LATERAL gbx_rst_polygonize(r.tile) AS p
WHERE r.name = 'dem';
-- cid: raster_web_map_tile
-- Render the scene as a 256 pixel PNG web tile (zoom 18, a tile that covers it).
SELECT length(gbx_rst_tilexyz(tile, 18, 130988, 87118, 'PNG', 256, 'bilinear', 'auto')) AS png_bytes
FROM ex_rasters
WHERE name = 'scene_wgs84';
-- cid: raster_web_tile_pyramid
-- Render the web tiles the scene touches across a range of zoom levels. Each row is one tile.
SELECT t.z, count(*) AS tiles, sum(length(t.bytes)) AS png_bytes
FROM ex_rasters AS r, LATERAL gbx_rst_xyzpyramid(r.tile, 17, 18) AS t
WHERE r.name = 'scene_wgs84'
GROUP BY t.z
ORDER BY t.z;
Spectral indices, slope, aspect, hillshade, canopy height and land cover.
-- GeoBrix Unity Catalog functions: spectral indices, terrain and classification.
-- Needs 00_setup.sql. Change the catalog and schema below if you installed elsewhere.
-- The scene has five bands in this order: 1 red, 2 green, 3 blue, 4 near infrared, 5 shortwave infrared.
-- Each statement that calls GeoBrix Python functions pays about 10 seconds on a SQL warehouse.
-- cid: use_catalog
USE CATALOG geospatial_docs;
-- cid: use_schema
USE SCHEMA geobrix_uc;
-- cid: raster_vegetation_indices
-- Mean index values over the scene. Each function takes the 1-based band numbers it needs.
SELECT gbx_rst_avg(gbx_rst_ndvi(tile, 1, 4)) AS ndvi,
gbx_rst_avg(gbx_rst_ndwi(tile, 2, 4)) AS ndwi,
gbx_rst_avg(gbx_rst_nbr(tile, 4, 5)) AS nbr,
gbx_rst_avg(gbx_rst_savi(tile, 1, 4, 0.5)) AS savi,
gbx_rst_avg(gbx_rst_evi(tile, 1, 4, 3)) AS evi
FROM ex_rasters
WHERE name = 'scene';
-- cid: raster_named_index
-- gbx_rst_index picks an index by name and takes a map from band name to band number.
SELECT gbx_rst_avg(gbx_rst_index(tile, 'ndvi', map('red', 1, 'nir', 4))) AS ndvi
FROM ex_rasters
WHERE name = 'scene';
-- cid: raster_terrain
-- Terrain from the DEM: slope in percent, aspect, hillshade, and three ruggedness measures.
SELECT gbx_rst_avg(gbx_rst_slope(tile, 'percent')) AS slope_percent,
gbx_rst_avg(gbx_rst_aspect(tile)) AS aspect,
gbx_rst_avg(gbx_rst_hillshade(tile, 315.0, 45.0, 1.0)) AS hillshade,
gbx_rst_avg(gbx_rst_tpi(tile)) AS tpi,
gbx_rst_avg(gbx_rst_tri(tile)) AS tri,
gbx_rst_avg(gbx_rst_roughness(tile)) AS roughness
FROM ex_rasters
WHERE name = 'dem';
-- cid: raster_color_relief_and_viewshed
-- Colour the DEM from a color table's text (blue at 60 m, red at 120 m), and the share of the DEM a 2 m tall
-- observer in its middle sees (a visible cell is 255). The observer is a GEOMETRY in the DEM's SRID.
SELECT gbx_rst_numbands(gbx_rst_color_relief(tile, '60 0 0 255\n120 255 0 0')) AS relief_bands,
gbx_rst_avg(gbx_rst_viewshed(tile, CAST(st_point(530120, 180360, 27700) AS GEOMETRY(ANY)), 2.0))[0] / 255 AS visible_share
FROM ex_rasters
WHERE name = 'dem';
-- cid: raster_canopy_height
-- A canopy height model is the surface model minus the terrain model. Here the surface is the DEM plus 8 m.
SELECT gbx_rst_avg(gbx_rst_chm(gbx_rst_mapalgebra(array(tile), 'A + 8'), tile)) AS mean_height_m
FROM ex_rasters
WHERE name = 'dem';
-- cid: raster_land_cover
-- Classify the scene into vegetation (0), bare (1), impervious (2) and dark (3) from its colours.
SELECT gbx_rst_histogram(gbx_rst_land_cover(tile), 4, 0.0, 4.0) AS class_histogram
FROM ex_rasters
WHERE name = 'scene';
Cut a tile into tiles, tessellate it into grid cells, and build tiles from points, polygons and cells.
-- GeoBrix Unity Catalog functions: raster generators (cut a tile into tiles, and build tiles from points).
-- Needs 00_setup.sql. Change the catalog and schema below if you installed elsewhere.
-- A table function returns one row for each tile it makes: call it with LATERAL.
-- Each statement that calls GeoBrix Python functions pays about 10 seconds on a SQL warehouse.
-- cid: use_catalog
USE CATALOG geospatial_docs;
-- cid: use_schema
USE SCHEMA geobrix_uc;
-- cid: generator_retile
-- Cut the DEM into 12 x 12 pixel tiles. Each row records the pixel window it came from.
SELECT t.window.col_off AS col_off, t.window.row_off AS row_off, t.window.width AS width, t.window.height AS height
FROM ex_rasters AS r, LATERAL gbx_rst_retile(r.tile, 12, 12) AS t
WHERE r.name = 'dem'
ORDER BY col_off, row_off;
-- cid: generator_maketiles
-- Cut a tile into tiles of at most a size in megabytes (here 1 MB, so the small DEM stays whole).
SELECT count(*) AS tiles
FROM ex_rasters AS r, LATERAL gbx_rst_maketiles(r.tile, 1) AS t
WHERE r.name = 'dem';
-- cid: generator_overlapping_tiles
-- Cut with an overlap of 25 percent between neighbours.
SELECT count(*) AS tiles
FROM ex_rasters AS r, LATERAL gbx_rst_tooverlappingtiles(r.tile, 12, 12, 25) AS t
WHERE r.name = 'dem';
-- cid: generator_separate_bands
-- One tile for each band of the scene. The band number is in the metadata.
SELECT t.metadata['band'] AS band, length(t.raster) AS bytes
FROM ex_rasters AS r, LATERAL gbx_rst_separatebands(r.tile) AS t
WHERE r.name = 'scene'
ORDER BY band;
-- cid: generator_merge_tiles_back
-- Merge tiles back into one raster. Collect tiles into an array and pass it to gbx_rst_merge.
WITH pieces AS (
SELECT struct(t.cellid, t.raster, t.path, t.path_mode, t.window, t.clip_polygon, t.clip_crs, t.crs, t.metadata) AS tile
FROM ex_rasters AS r, LATERAL gbx_rst_retile(r.tile, 12, 12) AS t
WHERE r.name = 'dem'
)
SELECT gbx_rst_width(gbx_rst_merge(collect_list(tile))) AS merged_width,
gbx_rst_height(gbx_rst_merge(collect_list(tile))) AS merged_height
FROM pieces;
-- cid: generator_rasterize_polygons
-- Burn a polygon into a raster: pixels it covers take the value, the others are NoData.
SELECT gbx_rst_pixelcount(gbx_rst_rasterize(CAST(geom AS GEOMETRY(ANY)), 7.0, 530000.0, 180000.0, 530250.0, 180200.0, 25, 20, 27700)) AS covered_pixels
FROM ex_parcels
WHERE id = 1;
-- cid: generator_points_to_raster_bins
-- Bin points into pixels with a statistic. Arrays of coordinates and values make one raster.
SELECT gbx_rst_pixelcount(
gbx_rst_binpoints(collect_list(longitude), collect_list(latitude), collect_list(value),
-0.16, 51.48, -0.10, 51.53, 30, 25, 4326, 'mean')) AS occupied_pixels
FROM ex_points;
-- cid: generator_points_to_raster_surface
-- Interpolate points into a surface by inverse-distance weighting.
SELECT gbx_rst_avg(
gbx_rst_gridfrompoints(collect_list(CAST(geom AS GEOMETRY(ANY))), collect_list(value),
-0.16, 51.48, -0.10, 51.53, 30, 25, 4326)) AS surface_mean
FROM ex_points;
-- cid: generator_terrain_from_spot_heights
-- Triangulate spot heights (points with a Z value) into a terrain raster. A point with no Z is refused.
SELECT gbx_rst_avg(
gbx_rst_dtmfromgeoms(
array(CAST(st_geomfromwkt('POINT Z(0 0 10)') AS GEOMETRY(ANY)), CAST(st_geomfromwkt('POINT Z(10 0 20)') AS GEOMETRY(ANY)),
CAST(st_geomfromwkt('POINT Z(0 10 30)') AS GEOMETRY(ANY)), CAST(st_geomfromwkt('POINT Z(10 10 40)') AS GEOMETRY(ANY))),
CAST(NULL AS ARRAY<GEOMETRY(ANY)>), 0.0, 0.0, 0.0, 0.0, 10.0, 10.0, 20, 20, 3857)) AS terrain_mean;
-- cid: generator_h3_cells_to_raster
-- Burn H3 cell values into a raster. The extent comes from the cells when you pass NULL.
-- The result is the bytes of a GeoTIFF; gbx_rst_fromcontent turns them back into a tile.
SELECT length(gbx_rst_h3_rasterize(collect_list(struct(cellid, value)), 4326, CAST(NULL AS DOUBLE),
CAST(NULL AS DOUBLE), CAST(NULL AS DOUBLE), CAST(NULL AS DOUBLE), CAST(NULL AS DOUBLE),
CAST(NULL AS INT), CAST(NULL AS INT), 'centroids', 1)) AS geotiff_bytes
FROM (SELECT h3_longlatash3(longitude, latitude, 8) AS cellid, avg(value) AS value FROM ex_points GROUP BY 1);
-- cid: generator_bng_cells_to_raster
-- Burn BNG cell values into a raster. The cell ids are text. The result is the bytes of a GeoTIFF.
WITH t AS (
SELECT gbx_bng_polyfill(CAST(geom AS GEOMETRY(ANY)), '100m') AS cells FROM ex_parcels WHERE id = 3
)
SELECT length(gbx_rst_bng_rasterize(collect_list(struct(cellid, value)), 27700,
CAST(NULL AS DOUBLE), CAST(NULL AS DOUBLE), CAST(NULL AS DOUBLE), CAST(NULL AS DOUBLE), CAST(NULL AS DOUBLE),
CAST(NULL AS INT), CAST(NULL AS INT), 'centroids', 1)) AS geotiff_bytes
FROM (SELECT cellid, CAST(row_number() OVER (ORDER BY cellid) AS DOUBLE) AS value
FROM t LATERAL VIEW explode(cells) AS cellid);
-- cid: generator_quadbin_cells_to_raster
-- The same for quadbin cells. Here each cell holds the mean reading of the sensors in it.
SELECT length(gbx_rst_quadbin_rasterize(collect_list(struct(cellid, value)), 4326,
CAST(NULL AS DOUBLE), CAST(NULL AS DOUBLE), CAST(NULL AS DOUBLE), CAST(NULL AS DOUBLE), CAST(NULL AS DOUBLE),
CAST(NULL AS INT), CAST(NULL AS INT), 'centroids', 1)) AS geotiff_bytes
FROM (SELECT gbx_quadbin_pointascell(longitude, latitude, 12) AS cellid, avg(value) AS value FROM ex_points GROUP BY 1);
-- cid: generator_custom_grid_cells_to_raster
-- The same for a custom grid. The grid goes in after the cells.
WITH g AS (SELECT gbx_custom_grid(0, 1000000, 0, 1000000, 2, 1000, 1000, 27700) AS grid),
c AS (
SELECT g.grid, gbx_custom_pointascell(CAST(st_centroid(p.geom) AS GEOMETRY(ANY)), g.grid, 4) AS cellid, CAST(p.id AS DOUBLE) AS value
FROM ex_parcels AS p, g
)
SELECT length(gbx_rst_custom_rasterize(collect_list(struct(cellid, value)), first(grid), 27700,
CAST(NULL AS DOUBLE), CAST(NULL AS DOUBLE), CAST(NULL AS DOUBLE), CAST(NULL AS DOUBLE), CAST(NULL AS DOUBLE),
CAST(NULL AS INT), CAST(NULL AS INT), 'centroids', 1)) AS geotiff_bytes
FROM c;
-- cid: generator_h3_tessellate
-- Cut the WGS84 scene into the tile of each H3 cell it covers. Each row is one cell and its tile bytes.
SELECT count(*) AS cells, sum(length(t.raster)) AS tile_bytes
FROM ex_rasters AS r, LATERAL gbx_rst_h3_tessellate(r.tile, 10) AS t
WHERE r.name = 'scene_wgs84';
-- cid: generator_bng_tessellate
-- The same for BNG cells of 100 m. The resolution is text.
SELECT count(*) AS cells, sum(length(t.raster)) AS tile_bytes
FROM ex_rasters AS r, LATERAL gbx_rst_bng_tessellate(r.tile, '100m') AS t
WHERE r.name = 'scene';
-- cid: generator_quadbin_tessellate
-- The same for quadbin cells at zoom 18.
SELECT count(*) AS cells, sum(length(t.raster)) AS tile_bytes
FROM ex_rasters AS r, LATERAL gbx_rst_quadbin_tessellate(r.tile, 18) AS t
WHERE r.name = 'scene_wgs84';
-- cid: generator_custom_grid_tessellate
-- The same for a custom grid. The grid goes in after the tile.
WITH g AS (SELECT gbx_custom_grid(0, 1000000, 0, 1000000, 2, 1000, 1000, 27700) AS grid)
SELECT count(*) AS cells, sum(length(t.raster)) AS tile_bytes
FROM ex_rasters AS r, g, LATERAL gbx_rst_custom_tessellate(r.tile, g.grid, 5) AS t
WHERE r.name = 'scene';
Summarize pixels into H3, quadbin, BNG and custom cells.
-- GeoBrix Unity Catalog functions: raster to grid (summarize pixels into grid cells).
-- Needs 00_setup.sql. Change the catalog and schema below if you installed elsewhere.
-- Each grid has one table function for each statistic (avg, count, max, median, min, stddev, sum, variance)
-- and a partial that gives several statistics in one pass. Rows hold the band, the cell and the measure.
-- Each statement that calls GeoBrix Python functions pays about 10 seconds on a SQL warehouse.
-- cid: use_catalog
USE CATALOG geospatial_docs;
-- cid: use_schema
USE SCHEMA geobrix_uc;
-- cid: raster_to_h3_average
-- The mean of band 1 of the scene (red) in each H3 cell at resolution 11.
SELECT g.band, g.cellID, g.measure
FROM ex_rasters AS r, LATERAL gbx_rst_h3_rastertogridavg(r.tile, 11) AS g
WHERE r.name = 'scene_wgs84' AND g.band = 1
ORDER BY g.cellID
LIMIT 10;
-- cid: raster_to_h3_all_statistics
-- Every H3 statistic function on the DEM: how many cells each gives.
SELECT 'avg' AS stat, count(*) AS cells FROM ex_rasters AS r, LATERAL gbx_rst_h3_rastertogridavg(r.tile, 11) WHERE r.name = 'dem'
UNION ALL SELECT 'count', count(*) FROM ex_rasters AS r, LATERAL gbx_rst_h3_rastertogridcount(r.tile, 11) WHERE r.name = 'dem'
UNION ALL SELECT 'max', count(*) FROM ex_rasters AS r, LATERAL gbx_rst_h3_rastertogridmax(r.tile, 11) WHERE r.name = 'dem'
UNION ALL SELECT 'median', count(*) FROM ex_rasters AS r, LATERAL gbx_rst_h3_rastertogridmedian(r.tile, 11) WHERE r.name = 'dem'
UNION ALL SELECT 'min', count(*) FROM ex_rasters AS r, LATERAL gbx_rst_h3_rastertogridmin(r.tile, 11) WHERE r.name = 'dem'
UNION ALL SELECT 'stddev', count(*) FROM ex_rasters AS r, LATERAL gbx_rst_h3_rastertogridstddev(r.tile, 11) WHERE r.name = 'dem'
UNION ALL SELECT 'sum', count(*) FROM ex_rasters AS r, LATERAL gbx_rst_h3_rastertogridsum(r.tile, 11) WHERE r.name = 'dem'
UNION ALL SELECT 'variance', count(*) FROM ex_rasters AS r, LATERAL gbx_rst_h3_rastertogridvariance(r.tile, 11) WHERE r.name = 'dem';
-- cid: raster_to_quadbin
-- The same summary in quadbin cells at zoom 20. The quadbin functions take the zoom as an integer.
SELECT 'avg' AS stat, count(*) AS cells FROM ex_rasters AS r, LATERAL gbx_rst_quadbin_rastertogridavg(r.tile, 20) WHERE r.name = 'dem'
UNION ALL SELECT 'count', count(*) FROM ex_rasters AS r, LATERAL gbx_rst_quadbin_rastertogridcount(r.tile, 20) WHERE r.name = 'dem'
UNION ALL SELECT 'max', count(*) FROM ex_rasters AS r, LATERAL gbx_rst_quadbin_rastertogridmax(r.tile, 20) WHERE r.name = 'dem'
UNION ALL SELECT 'median', count(*) FROM ex_rasters AS r, LATERAL gbx_rst_quadbin_rastertogridmedian(r.tile, 20) WHERE r.name = 'dem'
UNION ALL SELECT 'min', count(*) FROM ex_rasters AS r, LATERAL gbx_rst_quadbin_rastertogridmin(r.tile, 20) WHERE r.name = 'dem'
UNION ALL SELECT 'stddev', count(*) FROM ex_rasters AS r, LATERAL gbx_rst_quadbin_rastertogridstddev(r.tile, 20) WHERE r.name = 'dem'
UNION ALL SELECT 'sum', count(*) FROM ex_rasters AS r, LATERAL gbx_rst_quadbin_rastertogridsum(r.tile, 20) WHERE r.name = 'dem'
UNION ALL SELECT 'variance', count(*) FROM ex_rasters AS r, LATERAL gbx_rst_quadbin_rastertogridvariance(r.tile, 20) WHERE r.name = 'dem';
-- cid: raster_to_bng
-- BNG cells at 50 m. The BNG functions give the cell as text.
SELECT 'avg' AS stat, count(*) AS cells FROM ex_rasters AS r, LATERAL gbx_rst_bng_rastertogridavg(r.tile, '50m') WHERE r.name = 'dem'
UNION ALL SELECT 'count', count(*) FROM ex_rasters AS r, LATERAL gbx_rst_bng_rastertogridcount(r.tile, '50m') WHERE r.name = 'dem'
UNION ALL SELECT 'max', count(*) FROM ex_rasters AS r, LATERAL gbx_rst_bng_rastertogridmax(r.tile, '50m') WHERE r.name = 'dem'
UNION ALL SELECT 'median', count(*) FROM ex_rasters AS r, LATERAL gbx_rst_bng_rastertogridmedian(r.tile, '50m') WHERE r.name = 'dem'
UNION ALL SELECT 'min', count(*) FROM ex_rasters AS r, LATERAL gbx_rst_bng_rastertogridmin(r.tile, '50m') WHERE r.name = 'dem'
UNION ALL SELECT 'stddev', count(*) FROM ex_rasters AS r, LATERAL gbx_rst_bng_rastertogridstddev(r.tile, '50m') WHERE r.name = 'dem'
UNION ALL SELECT 'sum', count(*) FROM ex_rasters AS r, LATERAL gbx_rst_bng_rastertogridsum(r.tile, '50m') WHERE r.name = 'dem'
UNION ALL SELECT 'variance', count(*) FROM ex_rasters AS r, LATERAL gbx_rst_bng_rastertogridvariance(r.tile, '50m') WHERE r.name = 'dem';
-- cid: raster_to_custom_grid
-- Custom grid cells at resolution 5. A custom grid function takes the grid after the tile.
WITH g AS (SELECT gbx_custom_grid(0, 1000000, 0, 1000000, 2, 1000, 1000, 27700) AS grid)
SELECT 'avg' AS stat, count(*) AS cells FROM ex_rasters AS r, g, LATERAL gbx_rst_custom_rastertogridavg(r.tile, g.grid, 5) WHERE r.name = 'dem'
UNION ALL SELECT 'count', count(*) FROM ex_rasters AS r, g, LATERAL gbx_rst_custom_rastertogridcount(r.tile, g.grid, 5) WHERE r.name = 'dem'
UNION ALL SELECT 'max', count(*) FROM ex_rasters AS r, g, LATERAL gbx_rst_custom_rastertogridmax(r.tile, g.grid, 5) WHERE r.name = 'dem'
UNION ALL SELECT 'median', count(*) FROM ex_rasters AS r, g, LATERAL gbx_rst_custom_rastertogridmedian(r.tile, g.grid, 5) WHERE r.name = 'dem'
UNION ALL SELECT 'min', count(*) FROM ex_rasters AS r, g, LATERAL gbx_rst_custom_rastertogridmin(r.tile, g.grid, 5) WHERE r.name = 'dem'
UNION ALL SELECT 'stddev', count(*) FROM ex_rasters AS r, g, LATERAL gbx_rst_custom_rastertogridstddev(r.tile, g.grid, 5) WHERE r.name = 'dem'
UNION ALL SELECT 'sum', count(*) FROM ex_rasters AS r, g, LATERAL gbx_rst_custom_rastertogridsum(r.tile, g.grid, 5) WHERE r.name = 'dem'
UNION ALL SELECT 'variance', count(*) FROM ex_rasters AS r, g, LATERAL gbx_rst_custom_rastertogridvariance(r.tile, g.grid, 5) WHERE r.name = 'dem';
-- cid: raster_to_h3_many_statistics_one_pass
-- The partial gives the building blocks (count, sum, min, max, m2) of every statistic in one pass.
-- It has default arguments, so call it in a subquery and explode the column: UC refuses a SQL function in a generator.
WITH parts AS (
SELECT gbx_rst_h3_rastertogrid_partial(tile, 11) AS cells FROM ex_rasters WHERE name = 'dem'
)
SELECT c.band, c.cellid, c.count, try_divide(c.sum, c.count) AS avg, c.min, c.max
FROM parts LATERAL VIEW inline(cells) c
ORDER BY c.cellid
LIMIT 10;
-- cid: raster_to_grid_across_tiles
-- A statistic across many tiles: cut the DEM into tiles, take the partial of each, then merge with GROUP BY.
-- Sum count and sum rather than average the per-tile averages.
WITH pieces AS (
SELECT struct(t.cellid, t.raster, t.path, t.path_mode, t.window, t.clip_polygon, t.clip_crs, t.crs, t.metadata) AS tile
FROM ex_rasters AS r, LATERAL gbx_rst_retile(r.tile, 12, 12) AS t
WHERE r.name = 'dem'
),
parts AS (
SELECT gbx_rst_bng_rastertogrid_partial(tile, '50m') AS cells FROM pieces
),
p AS (SELECT c.* FROM parts LATERAL VIEW inline(cells) c)
SELECT band, cellid, sum(count) AS count, try_divide(sum(sum), sum(count)) AS avg, min(min) AS min, max(max) AS max
FROM p
GROUP BY band, cellid
ORDER BY cellid;
-- cid: raster_to_quadbin_and_custom_partials
-- The quadbin and custom partials work the same way.
WITH g AS (SELECT gbx_custom_grid(0, 1000000, 0, 1000000, 2, 1000, 1000, 27700) AS grid),
q AS (SELECT gbx_rst_quadbin_rastertogrid_partial(tile, 20) AS cells FROM ex_rasters WHERE name = 'dem'),
c AS (SELECT gbx_rst_custom_rastertogrid_partial(r.tile, g.grid, 5) AS cells FROM ex_rasters AS r, g WHERE r.name = 'dem')
SELECT (SELECT count(*) FROM q LATERAL VIEW inline(cells) x) AS quadbin_cells,
(SELECT count(*) FROM c LATERAL VIEW inline(cells) y) AS custom_cells;
Array and partial forms that replace aggregates, with their native SQL twins.
-- GeoBrix Unity Catalog functions: aggregation recipes.
-- Needs 00_setup.sql. Change the catalog and schema below if you installed elsewhere.
-- Unity Catalog has no user-defined aggregate. A function takes the whole group as an array instead:
-- GROUP BY the key, collect the members with collect_list, then call the array function once for each group.
-- Some recipes use only built-in SQL and need no GeoBrix function.
-- cid: use_catalog
USE CATALOG geospatial_docs;
-- cid: use_schema
USE SCHEMA geobrix_uc;
-- cid: recipe_binpoints_partial_and_finalize
-- Points to a raster in two steps. The partial gives each point its pixel (col, row). A GROUP BY reduces the points
-- of each pixel with any statistic. The finalize turns the pixels into a tile. No point leaves SQL.
WITH pts AS (
SELECT gbx_rst_binpoints_partial(longitude, latitude, -0.16, 51.48, -0.10, 51.53, 30, 25) AS px, value
FROM ex_points
),
bins AS (
SELECT px.col AS col, px.row AS row, avg(value) AS value FROM pts WHERE px IS NOT NULL GROUP BY px.col, px.row
)
SELECT gbx_rst_pixelcount(gbx_rst_binpoints_finalize(collect_list(struct(col, row, value)), -0.16, 51.48, -0.10, 51.53, 30, 25, 4326)) AS occupied_pixels
FROM bins;
-- cid: recipe_native_h3_fill
-- Fill H3 cells that hold no reading with the mean of their neighbours, using only built-in SQL.
-- Use this when you do not need the array function: it is plain Spark SQL.
WITH readings AS (
SELECT h3_longlatash3(longitude, latitude, 9) AS cellid, avg(value) AS value FROM ex_points GROUP BY 1
),
cells AS (
SELECT nb.cellid, rd.value
FROM (SELECT DISTINCT n.nb AS cellid FROM readings LATERAL VIEW explode(h3_kring(cellid, 1)) n AS nb) AS nb
LEFT JOIN readings AS rd ON rd.cellid = nb.cellid
),
holes AS (SELECT cellid FROM cells WHERE value IS NULL),
neighbours AS (
SELECT h.cellid, c.value
FROM (SELECT cellid, explode(h3_kring(cellid, 1)) AS nb FROM holes) AS h
JOIN cells AS c ON c.cellid = h.nb
WHERE c.value IS NOT NULL
)
SELECT count(*) AS empty_cells_filled, round(avg(mean_value), 2) AS mean_of_filled
FROM (SELECT cellid, avg(value) AS mean_value FROM neighbours GROUP BY cellid);
-- cid: recipe_quadbin_cell_fill
-- The quadbin cell fill array function: group by a key, pass an array of (cellid, value).
-- A NULL value is a hole and takes the mean of the cells around it.
SELECT gbx_quadbin_cellfill_array(
array(named_struct('cellid', 5233961839712272383, 'value', CAST(NULL AS DOUBLE)),
named_struct('cellid', 5233961826827370495, 'value', 10.0),
named_struct('cellid', 5233961831122337791, 'value', 20.0)),
1, 'mean') AS filled;
-- cid: recipe_bng_cell_fill_by_group
-- A BNG fill for each group. The groups come from GROUP BY.
WITH src AS (
SELECT * FROM VALUES
('a', 'TQ3080', CAST(NULL AS DOUBLE)), ('a', 'TQ3180', 10.0), ('a', 'TQ3081', 20.0),
('b', 'TQ3080', CAST(NULL AS DOUBLE)), ('b', 'TQ3180', 30.0)
AS t(grp, cellid, value)
)
SELECT grp, gbx_bng_cellfill_array(collect_list(struct(cellid, value)), 1, 'mean') AS filled
FROM src
GROUP BY grp
ORDER BY grp;
-- cid: recipe_custom_grid_cell_fill
WITH g AS (SELECT gbx_custom_grid(0, 1000000, 0, 1000000, 2, 1000, 1000, 27700) AS grid)
SELECT gbx_custom_cellfill_array(
array(named_struct('cellid', 180530L, 'value', CAST(NULL AS DOUBLE)),
named_struct('cellid', 180531L, 'value', 10.0),
named_struct('cellid', 181530L, 'value', 20.0)),
grid, 1, 'mean') AS filled
FROM g;
-- cid: recipe_union_of_bng_chips_with_native_sql
-- Merge the chips of one BNG cell with built-in ST functions. A core chip stands for the whole cell.
WITH t AS (
SELECT gbx_bng_tessellate(CAST(geom AS GEOMETRY(ANY)), '100m') AS cells FROM ex_parcels WHERE id IN (1, 2, 3)
),
chips AS (SELECT cell.cellid, cell.core, cell.chip FROM t LATERAL VIEW explode(cells) AS cell)
SELECT cellid,
CASE WHEN bool_or(core) THEN st_area(st_geomfromewkb(gbx_bng_aswkb(cellid)))
ELSE st_area(st_union_agg(st_geomfromewkb(chip))) END AS area_m2
FROM chips
GROUP BY cellid
ORDER BY cellid;
-- cid: recipe_combine_tiles_by_key
-- Combine the tiles of each key pixel by pixel. The tiles of a key must be aligned (same size, extent and CRS).
-- Here every key holds the same DEM tile twice.
WITH keyed AS (
SELECT name AS tile_key, tile FROM ex_rasters WHERE name = 'dem'
UNION ALL
SELECT name, tile FROM ex_rasters WHERE name = 'dem'
)
SELECT tile_key, gbx_rst_avg(gbx_rst_combineavg(collect_list(tile))) AS mean_of_combined
FROM keyed
GROUP BY tile_key;
-- cid: recipe_mosaic_tiles_by_key
-- Merge the tiles of each key into one raster. Here the 12 pixel DEM tiles go back into one mosaic.
SELECT 'dem' AS name, gbx_rst_width(gbx_rst_merge(collect_list(tile))) AS width, gbx_rst_height(gbx_rst_merge(collect_list(tile))) AS height
FROM ex_dem_tiles;
-- cid: recipe_vector_tile_layer_by_tile
-- One vector tile layer for each tile key. The geometry is WKB in tile coordinates, the attributes JSON text.
WITH features AS (
SELECT * FROM VALUES
(0, 0, 0, st_asbinary(st_geomfromwkt('POLYGON((10 10, 100 10, 100 100, 10 100, 10 10))')), '{"name": "a"}'),
(0, 0, 0, st_asbinary(st_geomfromwkt('POINT(50 50)')), '{"name": "b"}'),
(1, 0, 0, st_asbinary(st_geomfromwkt('POINT(20 20)')), '{"name": "c"}')
AS t(z, x, y, geom, attrs)
)
SELECT z, x, y, length(gbx_st_asmvt_array(collect_list(struct(geom, attrs)), 'places')) AS mvt_bytes
FROM features
GROUP BY z, x, y
ORDER BY z, x, y;
Whole ETL steps that run inside Unity Catalog.
-- GeoBrix Unity Catalog functions: procedures.
-- Needs 00_setup.sql and an install made with procedures = True. Change the catalog and schema below,
-- and the table names in each CALL, if you installed elsewhere.
-- A procedure runs a whole ETL step in Unity Catalog: it reads a table, calls GeoBrix functions and merges the result
-- into another table. Table names are parameters, written as catalog.schema.table. Each CALL creates its target table.
-- cid: use_catalog
USE CATALOG geospatial_docs;
-- cid: use_schema
USE SCHEMA geobrix_uc;
-- cid: procedure_clean_geometries
-- Repair the parcels. Valid and repaired geometries go to the target. What cannot be repaired goes to a rejects table with the reason.
CALL gbx_clean_geometries(
source_table => 'geospatial_docs.geobrix_uc.ex_parcels',
target_table => 'geospatial_docs.geobrix_uc.ex_parcels_clean',
rejects_table => 'geospatial_docs.geobrix_uc.ex_parcels_rejects',
repair_level => 'linework');
-- cid: procedure_clean_geometries_result
SELECT 'clean' AS tbl, count(*) AS n FROM ex_parcels_clean
UNION ALL SELECT 'rejects', count(*) FROM ex_parcels_rejects;
-- cid: procedure_polygons_to_bng
-- Tessellate the cleaned parcels into 50 m British National Grid cells: one row per parcel and cell.
CALL gbx_polygons_to_bng(
source_table => 'geospatial_docs.geobrix_uc.ex_parcels_clean',
target_table => 'geospatial_docs.geobrix_uc.ex_parcel_cells',
bng_resolution => '50m',
keep_core_geom => false);
-- cid: procedure_polygons_to_bng_result
SELECT id, count(*) AS cells, sum(CASE WHEN core THEN 1 ELSE 0 END) AS core_cells
FROM ex_parcel_cells
GROUP BY id
ORDER BY id;
-- cid: procedure_migrate_mosaic_geometry
-- Convert a table with legacy Mosaic geometry structs into a table with a GEOMETRY column.
CALL gbx_migrate_mosaic_geometry(
source_table => 'geospatial_docs.geobrix_uc.ex_legacy_geoms',
target_table => 'geospatial_docs.geobrix_uc.ex_migrated_geoms');
-- cid: procedure_migrate_mosaic_geometry_result
SELECT id, st_geometrytype(geom) AS geometry_type, st_srid(geom) AS srid FROM ex_migrated_geoms ORDER BY id;
-- cid: procedure_features_to_mvt
-- Cut road features into vector tiles for zooms 6 to 8, one row for each tile.
CALL gbx_features_to_mvt(
source_table => 'geospatial_docs.geobrix_uc.ex_roads',
target_table => 'geospatial_docs.geobrix_uc.ex_road_tiles',
min_zoom => 6,
max_zoom => 8,
mvt_layer => 'roads');
-- cid: procedure_features_to_mvt_result
SELECT z, count(*) AS tiles FROM ex_road_tiles GROUP BY z ORDER BY z;
-- cid: procedure_points_to_h3_surface
-- Average the sensor readings in H3 cells (resolution 9), fill the gaps from neighbours and write one GeoTIFF surface.
CALL gbx_points_to_h3_surface(
source_table => 'geospatial_docs.geobrix_uc.ex_points',
target_table => 'geospatial_docs.geobrix_uc.ex_h3_surface',
surface_name => 'sensors',
h3_resolution => 9,
fill_rings => 1);
-- cid: procedure_points_to_h3_surface_result
-- The surface is stored as GeoTIFF bytes. gbx_rst_fromcontent turns them into a tile.
SELECT surface, gbx_rst_width(t) AS width, gbx_rst_height(t) AS height
FROM (SELECT surface, gbx_rst_fromcontent(tile, 'GTiff') AS t FROM ex_h3_surface);
-- cid: procedure_mosaic_by_key
-- Merge the tiles of each key into one tile per key. Here each 12 pixel DEM tile is its own key, so the keys stay apart.
CALL gbx_mosaic_by_key(
source_table => 'geospatial_docs.geobrix_uc.ex_dem_tiles',
target_table => 'geospatial_docs.geobrix_uc.ex_dem_mosaics');
-- cid: procedure_mosaic_by_key_result
SELECT count(*) AS merged_tiles FROM ex_dem_mosaics;
-- cid: procedure_combine_by_key
-- Combine aligned tiles pixel by pixel for each key. The source holds each DEM tile twice, so the average equals the tile.
CREATE OR REPLACE TABLE ex_aligned_tiles AS
SELECT tile_key, tile FROM ex_dem_tiles
UNION ALL
SELECT tile_key, tile FROM ex_dem_tiles;
-- cid: procedure_combine_by_key_call
CALL gbx_combine_by_key(
source_table => 'geospatial_docs.geobrix_uc.ex_aligned_tiles',
target_table => 'geospatial_docs.geobrix_uc.ex_combined_tiles',
op => 'avg');
-- cid: procedure_combine_by_key_result
SELECT count(*) AS combined_tiles FROM ex_combined_tiles;
-- cid: procedure_zonal_stats
-- Summarize the DEM inside zones. The zones need zone_id and geom; the tiles need tile_id, tile and bbox.
CREATE OR REPLACE TABLE ex_zones AS
SELECT zone_id, st_geomfromwkt(wkt, 27700) AS geom
FROM VALUES
(1, 'POLYGON((530010 180300, 530110 180300, 530110 180400, 530010 180400, 530010 180300))'),
(2, 'POLYGON((530120 180260, 530220 180260, 530220 180360, 530120 180360, 530120 180260))')
AS t(zone_id, wkt);
-- cid: procedure_zonal_stats_tiles
CREATE OR REPLACE TABLE ex_zonal_tiles AS
SELECT name AS tile_id, tile, st_geomfromwkt('POLYGON((530000 180240, 530240 180240, 530240 180480, 530000 180480, 530000 180240))', 27700) AS bbox
FROM ex_rasters WHERE name = 'dem';
-- cid: procedure_zonal_stats_call
CALL gbx_zonal_stats(
source_table => 'geospatial_docs.geobrix_uc.ex_zonal_tiles',
zones_table => 'geospatial_docs.geobrix_uc.ex_zones',
target_table => 'geospatial_docs.geobrix_uc.ex_zone_stats');
-- cid: procedure_zonal_stats_result
SELECT zone_id, tile_id, left(summary, 80) AS summary FROM ex_zone_stats ORDER BY zone_id;
Next steps
- Unity Catalog functions: the full list of functions, limits and recipes.
- Availability matrix: which surfaces carry each function.