<jupyter_start><jupyter_text>Reading Raster Data in OmniSci Example script importing a directory of raster data layers into OmniSci point tables with the same base names.<jupyter_code>import glob
import gdal
con = omnisci_connect()<jupyter_output><empty_output><jupyter_text>Point this somewhere Jupyter can write and your OmniSci instance has permission to read<jupyter_code>local_storage = "/freenas-theExpanse/mflaxman/"<jupyter_output><empty_output><jupyter_text>For all geotiffs in the given directory...<jupyter_code>file_spec = f"{local_storage}/*.tif"<jupyter_output><empty_output><jupyter_text>If your rasters are not in geo-coordinates, you can either use gdal to warp them, or add SRID to OmniSci CSV import.<jupyter_code>reprojected_grids = []
for layer in glob.glob(file_spec):
    if 'geo' in layer:
        continue # already projected
    geotiff = layer.replace(".tif","_geo.tif")
    if not os.path.exists(geotiff):
        print(f"Reprojecting to {geotiff}")
        ds = gdal.Warp(geotiff, layer, dstSRS='EPSG:4326' )
        ds = None
    reprojected_grids.append(geotiff)
print(f"Done back-projecting {len(reprojected_grids)} grid(s) to geo")
def convert2xyz(geotiff):
    outcsv = geotiff.replace(".tif",".csv")
    options = "-of XYZ -co ADD_HEADER_LINE=YES -co COLUMN_SEPARATOR=, "
    options += "--config GDAL_CACHEMAX 4096 " # -co NUM_THREADS=ALL_CPUS
    command = "gdal_translate " + options + geotiff + " " + outcsv
    # run from commandline - could also run from python library as gdal.Translate()
    !{command}
    return(outcsv)<jupyter_output><empty_output><jupyter_text>build appropriate schema for raster layer value and typenote that we are merging X,Y columns into a single OmniSci Point You could alternatively create a small rectangle for each cellbased on its geographic footprint.  This renders better up close, but is 4x heavier<jupyter_code>def ddl(table_name):
    ddl = "omnisci_geo GEOMETRY(POINT,4326), "
    ddl += "cell_value SMALLINT"  # your type may vary
    return(ddl)
def etl(geotiff):
    outcsv = convert2xyz(geotiff)
    df = pd.read_csv(outcsv)
    # optional: clean up, remove no-data values, etc. here if needed
    df.to_csv(outcsv, index=None) # overwrite prior
        
    # presuming file basename is sql-legal table name:
    table_name = geotiff.split('/')[-1].replace(".tif","")
    if not table_name in con.list_tables():
        c = f"CREATE TABLE {table_name} ({ddl(table_name)})"
        try:
            con.con.execute(c)
            print(f"Table created: {table_name}")
        except:
            print(f"Failed to create table: {table_name}")

        print("Loading from csv")
        q = f"COPY {table_name} FROM '{outcsv}'"
        try:
            con.con.execute(q)
            print(f"Loaded {table_name}")
        except:
            print("Csv reading is failing")
    else:
        print(f"Warning: table already exists in OmniSci, drop first to replace {table_name}")
for layer in reprojected_grids:
    print(f"Converting {layer} to OmniSci table")
    outcsv = convert2xyz(layer)
    etl(layer)<jupyter_output>Converting /freenas-theExpanse/mflaxman/la_terrain_within_5000m_of_tower_20701699_geo.tif to OmniSci table
Input file size is 279, 231
0...10...20...30...40...50...60...70...80...90...100 - done.
Input file size is 279, 231
0...10...20...30...40...50...60...70...80...90...100 - done.
Warning: table already exists in OmniSci, drop first to replace la_terrain_within_5000m_of_tower_20701699_geo
{'fraction_non_alphanumeric': 0.11346098885395828, 'fraction_numerical': 0.027722206344669906, 'mean_word_length': 3.6604527296937417, 'pattern_counts': {'":': 0, '<': 20, '<?xml version=': 0, '>': 20, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '256147', 'license': 'MIT', 'n_tokens_mistral': 1265, 'n_tokens_neox': 1174, 'n_words': 313, 'path': 'omnisci_raster_to_points.ipynb', 'repo_name': 'omnisci/raster_import', 'size': '3499'}