3

(Ubuntu server 18)

It seems that raster2pgsql is included in the postgis package but I can't find it separately to download either source or binary. Supposedly there's an executable somewhere in the postgresql lib folders. After reading some things I have the idea that it's not default in the installation or when compiling and maybe I won't find it if I used the extension script distributed with postgresql. How do I add it?

That said, the intention is to generate raster data and load it using Python scripts, so a gdal driver might be more convenient since we are already using gdal.

The script runs on a separate machine from the pg instance, if that matters.

What's the most straightforward way to pipe large volumes of raster data into postgis on a remote box?

1 Answer 1

0

On my Windows 10 it defaults to C:\Program Files\PostgreSQL\11\bin for the raster2pgsql.exe. If you get the raster2pgsql most of it should also run on Ubuntu.

I made a Python script for loading all tiff in a folder to PostGIS using raster2pgsql.

"""
    Import all raster files in folder to PostgreSQL PostGIS
"""
import os


def run_raster2pgsql(file_path):
    file_name = os.path.basename(file_path)     # strip path
    file_name = os.path.splitext(file_name)[0]  # strip extension

    dbname = 'sandbox'
    schema = 'tilstandsvurdering'
    host = 'localhost'
    user = 'postgres'
    pw = 'xxx'
    port = '5432'

    bin_path = r"C:\Program Files\PostgreSQL\11\bin"

    cmd = f"""
set PGPASSWORD={pw}&&SET PATH=%PATH%;{bin_path}&& "raster2pgsql.exe" -I -C "{file_path}" -F {schema}.{file_name} | psql -U {user} -d {dbname} -h {host} -p {port}
        """

    #print(cmd)
    os.system(cmd)
    
    print('************Done with file')

if __name__ == '__main__':
    raster_dir = r'C:\data\Tilstandsvurderinger\Magasinkoter\export'
    raster_ext = 'tiff'

    for root, dirs, files in os.walk(raster_dir):
        tiff_files = [file for file in files if os.path.splitext(file)[1] == '.tiff']
        for filename in tiff_files:
            filepath = os.path.join(root, filename)
            run_raster2pgsql(filepath)
        break   # no recursive walk

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.