Convert GeoPackage tablenames and columnnames from CamelCase to snake_case #powershell #gpkg #ogr2ogr

gpkg-cc-2-sc.ps1

<#
.SYNOPSIS
    Convert GeoPackage tablenames and columnnames from CamelCase to snake_case.
.DESCRIPTION
    Script to convert GeoPackage tablenames and columnnames from CamelCase to snake_case. Can be useful when exporting INSPIRE compliant datasets to GeoPackage.
.PARAMETER inputGPKG
    Input GeoPackage to convert from CamelCase to snake_case
.PARAMETER outputGPKG
    Output GeoPackage (will be overwritten if exists)
#>


param(
    [Parameter(Mandatory=$True,Position=1,HelpMessage="Input GeoPackage to convert from CamelCase to snake_case")]
    [string]$inputGPKG,
    [Parameter(Mandatory=$True,Position=2,HelpMessage="Output GeoPackage (will be overwritten if exists)")]
    [string]$outputGPKG
    )

Function Convert-Camelcase-To-Snakecase($inputString) {
    $outputString=[regex]::replace($inputString, '([A-Z])', { "_" + $args[0].Groups[1].Value.ToLower()})
    $outputString=[regex]::replace($outputString, '^_', { ""})
    Write-Output $outputString
}

function Get-Layers($gpkgPath){
    $layers=(ogrinfo "$gpkgPath" | Select-String -Pattern "[0-9]:\s.*"  | ForEach-Object{($_ -split "\s+")[1]})
    return $layers
}
function Get-OGR-Attributes($gpkgPath, $layer){
    $ogrInfoOutput=(ogrinfo "$gpkgPath" "$layer" -so) 
    $lineNumber=($ogrInfoOutput| select-string -Pattern '^Geometry\sColumn.*' | Select-Object -ExpandProperty LineNumber)
    $totalCount=($ogrInfoOutput | Measure-Object).Count
    $ogrAttributes=$ogrInfoOutput | Select-Object -Index ($lineNumber..$totalCount) | where { $_ -match '^(.*):.*'} | foreach { $matches[1]} 
    $geomColumn=$ogrInfoOutput | where { $_ -match '^Geometry\sColumn\s=\s(.*)'} | foreach {$matches[1]}
    return $ogrAttributes+$geomColumn
  }

  function Convert-Layer($inputPath, $outputPath, $layerName){
    $ogrAttributes=Get-OGR-Attributes $inputPath $layerName
    $queryAttributes=""
    foreach ($ogrAttribute in $ogrAttributes){
        $snakeAttribute=Convert-Camelcase-To-Snakecase $ogrAttribute
        if ($queryAttributes){
            $queryAttributes="${queryAttributes}, ${ogrAttribute} as ${snakeAttribute}"
        }else{
            $queryAttributes="${ogrAttribute} as ${snakeAttribute}"
        }
    }
    $query="SELECT ${queryAttributes} FROM ${layerName}"
    $newLayerName=Convert-Camelcase-To-Snakecase "$layerName"

    if (Test-Path $outputPath){
        & ogr2ogr -append -f GPKG "$outputPath" "$inputPath" -sql "$query" -nln "$newLayerName"
    }else {
        & ogr2ogr -f GPKG "$outputPath" "$inputPath" -sql "$query" -nln "$newLayerName"
    }
  }

$layers=(Get-Layers $inputGPKG)

if (Test-Path $outputGPKG){
    Remove-Item -Path $outputGPKG -Force
}

foreach ($layer in $layers){
    Convert-Layer $inputGPKG $outputGPKG $layer
}