Bulk upload of directory of shape files into databse using PowerShell, shp2sql, ogrinfo and psql to feed into Postgis

ragis.bulk.upload.shp2psql.ps1

# Set the target database connection details
# these are default for the ragis lectures

$remoteHost = "34.118.61.196"
$databaseName = "ragis"

$targetSrid = 7801  # 

if ($args.Count -lt 2) {
    Write-Host "Usage:  bulk.import.ps1 [source dir] [target schema]"
    Exit
}

$sourceDir = $args[0]
$schema = $args[1]

Write-Host "`n finding ogrinfo path..." -ForegroundColor Yellow
$OGR = $(fd ogrinfo / -tx --max-results 1) | Select-Object -First 1
Write-Host "`n finding psql path..." -ForegroundColor Yellow
$PSQL = $(fd psql / -tx --max-results 1) | Select-Object -First 1
Write-Host "`n finding shp2sql path..." -ForegroundColor Yellow
$SHP2SQL = $(fd shp2pgsql / -tx --max-results 1) | Select-Object -First 1

# Get all shapefiles in the source directory
$shapefiles = Get-ChildItem -Path $sourceDir -Filter "*.shp" -Recurse

foreach ($shapefile in $shapefiles) {
    $shapefilePath = $shapefile.FullName
    $tableName = $shapefile.BaseName

    Write-Host "`n============================================" -ForegroundColor Green
    Write-Host "Processing shapefile: $($shapefile.Name)" -ForegroundColor Cyan

    # Check if the SRID is defined in the spatial_ref_sys table
    $sourceSrid = & $OGR -so $shapefilePath $tableName | Select-String -Pattern 'EPSG' -Context 0,1
    $sourceSrid = $sourceSrid.Line.Split(',')[1].Remove(4)
    $existingSrid = & $PSQL -h $remoteHost -U $username -d $databaseName -tAc "SELECT srid FROM spatial_ref_sys WHERE srid = $sourceSrid"
    if ($null -eq $existingSrid) {
        Write-Host "`nMissing SRID $sourceSrid definition to spatial_ref_sys table..." -ForegroundColor Yellow
        return 
    }

    # Check if the table already exists in the database
    $tableExists = & $PSQL -h $remoteHost -U $username -d $databaseName -tAc "SELECT EXISTS (SELECT FROM information_schema.tables WHERE table_schema = '$schema' AND table_name = '$tableName')"
    if ($tableExists -eq "t") {
        Write-Host "Table '$schema.$tableName' already exists. Skipping import." -ForegroundColor Yellow
        continue
    }

    # Import the shapefile into the database
    Write-Host "`nImporting shapefile into database..." -ForegroundColor Yellow
    & $SHP2SQL -DI -s $sourceSrid`:$targetSrid $shapefilePath $schema`.$tableName | & $PSQL -h $remoteHost -U $username -d $databaseName
}