Skip to main content

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​

  1. Import the files into your workspace. Run gbx:examples:uc-sql --upload from a GeoBrix checkout, or copy the files from examples/uc-functions.
  2. Open 00_setup.sql in the SQL editor and run it. It builds the sample tables the other files read.
  3. 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​

Builds the sample tables. Run it first.

00_setup.sql
-- 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;

Next steps​