29:null 2a:["$","title",null,{"children":"DuckDB-wasm Spatial Extension Example | shimizu | Observable"}] 2f:T4d9,{ Extensionloaded; container.innerHTML = ""; const deckgl = new deck.DeckGL({ container, controller: true, initialViewState:{ longitude: 139.7658155, latitude: 35.681386, zoom: 9.5, minZoom: 5, maxZoom: 15, } }); const tileLayer = new deck.TileLayer({ id: "tile-layer", data: "https://c.tile.openstreetmap.org/{z}/{x}/{y}.png", minZoom: 0, maxZoom: 19, tileSize: 256, renderSubLayers: (props) => { const { //type error: Property 'east' does not exist on type 'TileBoundingBox'. bbox: { west, south, east, north } } = props.tile; return new deck.BitmapLayer(props, { //@ts-ignore data:undefined, image: props.data, bounds: [west, south, east, north] }); } }); const geojsonLayer = new deck.GeoJsonLayer({ id:"geojson-layer", data:geojson, pointRadiusMinPixels:6, getLineWidth: 10, getPointRadius: 30, getFillColor: [255, 0, 0], getLineColor: [0, 0, 0], }) deckgl.setProps({layers: [tileLayer, geojsonLayer]}) }30:[] 2d:{"id":"e8c3d409186bb0b0","slug":null,"title":"DuckDB-wasm Spatial Extension Example","thumbnail":"0353fe0414c0fb09d0f3ca90bf77ba2bef237302c9c50c8a24b18ad3c26c2fea","access_level":"public","create_time":"$D2024-05-22T11:59:59.982Z","update_time":"$D2024-11-18T09:21:36.314Z","view_time":null,"first_public_time":"$D2024-05-22T14:14:38.964Z","fork":{"id":"25fb9910de9e5e81","slug":null,"title":"DuckDB-wasm Deckgl Binary attributes Example","owner":{"id":"d24c0ca9d1300164","login":"shimizu","name":"shimizu"}},"stars":8,"starred":false,"owner":{"type":"team","id":"d24c0ca9d1300164","login":"shimizu","name":"shimizu","avatar_url":"https://avatars-next.observableusercontent.com/4911d1cb615a6d69d84f6803f9c18eb02a7074312fbb7a8868ab03134a7e9ddc","tier":"starter_2024"},"version":434,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"871e5a5f08c8ef40","login":"shimizu","name":"shimizu","avatar_url":"https://avatars-next.observableusercontent.com/4911d1cb615a6d69d84f6803f9c18eb02a7074312fbb7a8868ab03134a7e9ddc","tier":null}],"members":[{"type":"individual","id":"871e5a5f08c8ef40","login":"shimizu","name":"shimizu","avatar_url":"https://avatars-next.observableusercontent.com/4911d1cb615a6d69d84f6803f9c18eb02a7074312fbb7a8868ab03134a7e9ddc","tier":null,"role":"editor"},{"type":"team","id":"d24c0ca9d1300164","login":"shimizu","name":"shimizu","avatar_url":"https://avatars-next.observableusercontent.com/4911d1cb615a6d69d84f6803f9c18eb02a7074312fbb7a8868ab03134a7e9ddc","tier":"starter_2024","role":"editor"}],"comments":[],"custom_thumbnail":true,"trashed":false,"subscribed":null,"forks":2,"collection":null,"type":"notebook","body":{"title":"DuckDB-wasm Spatial Extension Example","cells":[{"id":0,"value":"# DuckDB-wasm Spatial Extension Example\n\nDuckDBでリモードから地理空間データを読み込んでdeck.glで表示するサンプル。\n元データは「[国土数値情報 \\| 警察署データ](https://nlftp.mlit.go.jp/ksj/gml/datalist/KsjTmplt-P18.html)」から、ダウンロードした東京都のデータをparquetファイルに変換しクラウドストレージに設置。\n\n位置情報はWKT (Well-known text)にして属性値に保存。\n\n参考サイト\n- [PostGEESE? Introducing The DuckDB Spatial Extension – DuckDB](https://duckdb.org/2023/04/28/spatial.html)\n- [Spatial Extension – DuckDB](https://duckdb.org/docs/extensions/spatial.html)\n\n\n＊なぜGeoParquetを使わなかったのか？→ ~~DuckDBでは、GeoParquetのgeometryをDuckDBのジオメトリ型に自動的に変換してくれないようで、型変換がめんどくさかったのでWKTにした。~~\n\nDackDB-wasmのバージョンが上がって、GeoParquetファイルのgeometryに対応したっぽい。なので、wktフィールドはいらなくなって、geometryを直接取得できるようになっていると思うけど、以下のコードではwktフィールドの値を使って地図にポイントを表示している。\n\n","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":113,"value":"送信クエリ","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":54,"value":"viewof query = Inputs.textarea(\n {\n label: \"query\", \n placeholder: \"What’s your story?\", \n rows: 18,\n minlength: 100,\n value:`SELECT \n name,\n address,\n WKT,\n ST_AsGeoJSON(geo) as geo\nFROM (SELECT \n P18_001 as name,\n P18_004 as address,\n WKT,\n ST_GeomFromText(WKT) as geo\nFROM \n \"https://storage.googleapis.com/g3-open-resource/parquet/police_station2.parquet\")\nLIMIT 800\n`\n })","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":115,"value":"クエリの実行とレスポンス","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":374,"value":"stmt = {\n Extensionloaded;\n return await conn.prepare(query);\n} ","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":378,"value":"arrowTable = {\n Extensionloaded\n return await stmt.query()\n}; ","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":336,"value":"## 地図上に表示","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":389,"value":"geojson = { \n const geo = arrowTable.getChild(\"geo\"); //geoカラムのデータを取得\n const rows = geo.length //行数を取得\n const features = []\n for (let i = 0; i < rows;i++){\n //ST_AsGeoJSONによってGeoJSON(テキスト)に変換されているのでパースする\n features.push({type:\"Feature\", geometry:JSON.parse(geo.get(i))})\n }\n return features\n}\n","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":149,"value":"container = html `

`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":155,"value":"©OpenStreetMap","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":117,"value":"## 実行結果をテーブルで表示","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":15,"value":"tabledata = {\n \n const name = arrowTable.getChild(\"name\");\n const address = arrowTable.getChild(\"address\") \n const wkt = arrowTable.getChild(\"WKT\"); \n const rows = name.length \n\n const res = [];\n for (let i = 0; i < rows;i++){\n res.push({name:name.get(i), address:address.get(i), wkt:wkt.get(i)})\n }\n \n return res; \n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":101,"value":"Inputs.table(tabledata)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":82,"value":"# DuckDB セットアップ","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":12,"value":"getDb = async () => {\n // JSDELIVR_BUNDLESからDuckDBのバンドルを選択して取得する\n const bundle = await duckdb.selectBundle(JSDELIVR_BUNDLES);\n \n // 新しいWeb Workerを作成するためのURLを生成する\n // Blobを使用して、インポートスクリプトを含む新しいJavaScriptファイルを作成する\n const worker_url = URL.createObjectURL(\n new Blob([`importScripts(\"${bundle.mainWorker}\");`], {\n type: \"text/javascript\",\n })\n );\n \n // 上記で作成したURLを使用して新しいWeb Workerを作成\n const worker = new Worker(worker_url);\n \n // コンソールにログを出力するためのロガーを作成\n const logger = new duckdb.ConsoleLogger();\n \n // 作成したWeb Workerとロガーを使用して、非同期DuckDBインスタンスを作成\n const db = new duckdb.AsyncDuckDB(logger, worker);\n \n // DuckDBインスタンスを初期化\n await db.instantiate(bundle.mainModule, bundle.pthreadWorker);\n \n // Web WorkerのURLを解放\n URL.revokeObjectURL(worker_url);\n \n // 初期化されたDuckDBインスタンスを返す\n return db;\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":107,"value":"データベースを作成する","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":33,"value":"db = getDb()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":109,"value":"データベースに接続する","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":39,"value":"conn = db.connect()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":340,"value":"拡張機能(Spatial Extension)を読み込む","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":138,"value":"Extensionloaded = {\n await conn.query(`INSTALL spatial;`);\n await conn.query(`LOAD spatial;`);\n\n return true;\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":146,"value":"# Deck.gl 地図描画","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":157,"value":"$2f","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":5,"value":"# libs","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":7,"value":"duckdb = import(\"https://cdn.jsdelivr.net/npm/@duckdb/duckdb-wasm/+esm\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":18,"value":" JSDELIVR_BUNDLES = duckdb.getJsDelivrBundles()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":141,"value":"deck = require.alias({\n // optional dependencjei\n h3: {}\n})('deck.gl@^8.3.7/dist.min.js')","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W30","stdlib":"1","titling":"auto"}} 2e:"$2d:body"