CODEWITHSUNDEEP
sql-serverpowershellsqlcommand
Greg
Greg

Reputation: 4055

How to insert into Geography column using parameterized SQL

When I insert into a geography column with geography::STPointFromText, I have to dynamically build the column value, which ends up causing a SP:CacheMiss. My SQL cache is littered with singleton insert statements due to the way the insert is as we are repopulating this table every 15 min. Is it possible to parameterize this during insert, so it is cached after the first insert?

Currently my code is below - I populate the table first with NULL in the geography column, then at the end, I update the whole table, setting the geography column at once.

$cn = New-Object System.Data.SQLClient.SQLConnection
$cn.ConnectionString = "server='$sqlInstance';database='$sqlDb';trusted_connection=true;"
$cn.Open()

foreach ($loc in $locations) {

    $sql = "INSERT INTO dbo.Locations (vehicleID, Lat, Long, GeoLocation) 
            VALUES (@vehicleID, @Lat, @Long, NULL);"

    #$sql = "INSERT INTO dbo.Locations (vehicleID, Lat, Long, KeyOn, avgSpeed, GeoLocation) 
    #        VALUES (@vehicleID, @Lat, @Long, geography::STPointFromText('POINT(' + CAST([@Long] AS VARCHAR(20)) + ' ' + CAST([@Lat] AS VARCHAR(20)) + ')', 4326));"

    $cmd = New-Object System.Data.SQLClient.SQLCommand ($sql, $cn)
    $cmd.Parameters.Add("@vehicleID", [System.Data.SqlDbType]::Int).Value = $loc.vehicleId;
    $cmd.Parameters.Add("@Lat", [System.Data.SqlDbType]::Decimal).Value = $loc.latitude;
    $cmd.Parameters["@Lat"].Precision = 18;
    $cmd.Parameters["@Lat"].Scale = 5;
    $cmd.Parameters.Add("@Long", [System.Data.SqlDbType]::Decimal).Value = $loc.longitude;
    $cmd.Parameters["@Long"].Precision = 18;
    $cmd.Parameters["@Long"].Scale = 5;
    $reader = $cmd.ExecuteNonQuery()
}

$sql = "UPDATE VZ.Locations SET [GeoLocation] = geography::STPointFromText('POINT(' + CAST([Long] AS VARCHAR(20)) + ' ' + CAST([Lat] AS VARCHAR(20)) + ')', 4326);"
$cmd = New-Object System.Data.SQLClient.SQLCommand ($sql, $cn)
$reader = $cmd.ExecuteNonQuery()

$cn.Close()

If I uncomment the commented code that has the geography calculation as part of the insert, I get a SQL error saying @Lat and @Long are invalid columns.

Upvotes: 0

Views: 508

Answers (1)

AlwaysLearning
AlwaysLearning

Reputation: 8829

I'd be inclined to construct the point text in PowerShell, e.g.:

$point = "POINT($($loc.longitude),$($loc.latitude))"

Then send that as a @Point parameter to your command to be inserted as:

geography::STPointFromText(@Point)

Doing that should solve both your SP:CacheMiss issue and the error messages about [@Long] and [@Lat] not being columns.

Upvotes: 0

Related Questions