End-to-End Guide: Ingesting Overture Maps Data into Snowflake and Serving It as a GeoService with Snowflake-Koop Provider

-- Database, schema creation for GIS data

create database GEOSPATIAL;

Create schema OVERTURE;

USE SCHEMA OVERTURE

--Creating the stage. The stage will point to the S3 bucket containing the Parquet files.

CREATE STAGE overture_stage
URL='s3://overturemaps-us-west-2/';


-- Important so set the BINARY_AS_TEXT option to false so the binary data is not converted to text while loading.

CREATE OR REPLACE FILE FORMAT overture_buildings_format
  TYPE = parquet
  BINARY_AS_TEXT = FALSE;


--Infer schema will automatically get the details from Parquet files

CREATE OR REPLACE TABLE OVERTURE.BUILDINGS
USING TEMPLATE (
  SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*))
    FROM TABLE(
     INFER_SCHEMA(
       LOCATION=>'@overture_stage/release/2024-10-23.0/theme=buildings',
       FILE_FORMAT=>'overture_buildings_format'
     )
));


--This took 1 hours 51 munutes to load the data using small compute. Total 2.4 B records loaded.

COPY INTO OVERTURE.BUILDINGS
FROM @overture_stage/release/2024-10-23.0/theme=buildings
FILE_FORMAT = (FORMAT_NAME= 'overture_buildings_format') 
MATCH_BY_COLUMN_NAME=CASE_INSENSITIVE;

--Create sample table with just 1000 records for Koop work-flow.
-- This table has 26 columns

CREATE TABLE BUILDINGS_SAMPLE AS
SELECT *
FROM BUILDINGS
LIMIT 1000;

--Add new column "geom" as Geography Data type
-- MAKE SURE SAMPLE table has ID column which is of INTEGER

ALTER TABLE OVERTURE.BUILDINGS_SAMPLE
ADD COLUMN geom GEOGRAPHY;

--Update "Geom" column with actual shape from "geometry" column using ST_ST_GEOGFROMWKB

UPDATE OVERTURE.BUILDINGS_SAMPLE 
SET geom = ST_GEOGFROMWKB("geometry", TRUE);

 
 -- Update building sample table with Synthetic data . As most of attributes are null 

-- Update columns Number of floors, Facade color, Facade Material, Roof color and Minimum Height with random data 


UPDATE OVERTURE.BUILDINGS_SAMPLE 
SET "num_floors" = new_values.new_random_id
FROM (
    SELECT "id", 
           ROW_NUMBER() OVER (ORDER BY UNIFORM(1, 10, RANDOM())) AS new_random_id
    FROM OVERTURE.BUILDINGS_SAMPLE 
) AS new_values
WHERE OVERTURE.BUILDINGS_SAMPLE."id" = new_values."id";


UPDATE OVERTURE.BUILDINGS_SAMPLE
SET "facade_color" = new_values.random_color
FROM (
    SELECT "id",
           CASE MOD(ROW_NUMBER() OVER (ORDER BY RANDOM()), 5)
               WHEN 0 THEN 'Red'
               WHEN 1 THEN 'Blue'
               WHEN 2 THEN 'Green'
               WHEN 3 THEN 'Yellow'
               WHEN 4 THEN 'Orange'
           END AS random_color
    FROM OVERTURE.BUILDINGS_SAMPLE
) AS new_values
WHERE OVERTURE.BUILDINGS_SAMPLE."id" = new_values."id";

UPDATE OVERTURE.BUILDINGS_SAMPLE
SET "facade_material" = new_values.random_material
FROM (
    SELECT "id",
           CASE MOD(ROW_NUMBER() OVER (ORDER BY RANDOM()), 5)
               WHEN 0 THEN 'Brick'
               WHEN 1 THEN 'Concrete'
               WHEN 2 THEN 'Glass'
               WHEN 3 THEN 'Steel'
               WHEN 4 THEN 'Wood'
           END AS random_material
    FROM OVERTURE.BUILDINGS_SAMPLE
) AS new_values
WHERE OVERTURE.BUILDINGS_SAMPLE."id" = new_values."id";

UPDATE OVERTURE.BUILDINGS_SAMPLE
SET "roof_color" = new_values.random_material
FROM (
    SELECT "id",
           CASE MOD(ROW_NUMBER() OVER (ORDER BY RANDOM()), 5)
               WHEN 0 THEN 'Gray'
               WHEN 1 THEN 'Black'
               WHEN 2 THEN 'Brown'
               WHEN 3 THEN 'Ash'
               WHEN 4 THEN 'Wood'
           END AS random_material
    FROM OVERTURE.BUILDINGS_SAMPLE
) AS new_values
WHERE OVERTURE.BUILDINGS_SAMPLE."id" = new_values."id";



UPDATE OVERTURE.BUILDINGS_SAMPLE
set "min_height" = 7

{
    "koopProviderSnowflake":{
        "snowflakeAccount":"<account>",  --https://<youraccount>.snowflakecomputing.com
        "snowflakeWarehouse":"COMPUTE_WH", 
        "snowflakeUser":"<name>", -- account name
        "snowflakePassword":"<password>", -- your password
        "serviceDefinitions":{
            "locations": [
                {
                "serviceIndex":0,
                "serviceDesc":"Sample location data",
                "tableName":"OVERTURE.BUILDINGS_SAMPLE",
                "geographyField":"geom",
                "primaryId":"id",
                "supportsPagination": true,
                "fields":[
                    { 
                        "name": "id",
                        "type": "Integer", 
                        "alias": "id"
                    },
                    { 
                        "name": "num_floors",
                        "type": "Integer", 
                        "alias": "num_floors"
                    },
                    { 
                        "name": "roof_color",
                        "type": "String", 
                        "alias": "roof_color"
                    },
                    { 
                        "name": "min_height",
                        "type": "Integer", 
                        "alias": "min_height"
                    }, 
                    { 
                        "name": "facade_color",
                        "type": "String", 
                        "alias": "facade_color"
                    },
                    { 
                        "name": "facade_material",
                        "type": "String", 
                        "alias": "facade_material"
                    }               
                                  
                ],
                "maxReturnCount":100,  -- max return count 100 
                "geometryType":"esriGeometryPolygon"   -- Buildings are type polygon
                }
            ]
        }
    }
}