How to insert into Geography column using parameterized SQL

Question

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.

Two answer your first question: you get a SP:CacheMiss because you're trying to build dynamic SQL queries: each statement has different text, so each statement hashes differently, so each statement gets a cache miss. — AlwaysLearning
– AlwaysLearning, Commented Dec 11, 2020 at 23:19
The error message is correct: [@Long] is not a column. Maybe you meant @Long? — AlwaysLearning
– AlwaysLearning, Commented Dec 11, 2020 at 23:22

AlwaysLearning · Accepted Answer · 2020-12-11 23:28:43Z

0

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.

answered Dec 11, 2020 at 23:28

AlwaysLearning

8,9425 gold badges32 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to insert into Geography column using parameterized SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related