Ejercicios de PowerShell: crear una base de datos llamada gps y almacenar posiciones GPS (PowerShell, Bases de datos)

Crear la base de datos gps en MySQL desde PowerShell

[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
$Connection = New-Object MySql.Data.MySqlClient.MySqlConnection
$ConnectionString = "server=" + "localhost" + ";port=3306;uid=" + "root" + ";pwd=" + ";"
$Connection.ConnectionString = $ConnectionString
$Connection.Open()

$Query = 'create database gps'
$Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)
$DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)
$DataSet = New-Object System.Data.DataSet
$RecordCount = $dataAdapter.Fill($dataSet, "data")
$DataSet.Tables[0]
$Connection.Close()

Crear la tabla result con las columnas: ID, address, lat, lng y date

[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
$Connection = New-Object MySql.Data.MySqlClient.MySqlConnection
$ConnectionString = "server=" + "localhost" + ";port=3306;uid=" + "root" + ";pwd=" +  ";database="+"gps"
$Connection.ConnectionString = $ConnectionString
$Connection.Open()

$Query = 'create table result(ID int auto_increment primary key, address varchar(50), lat varchar(10), lng varchar(10), date datetime)'
$Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)
$DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)
$DataSet = New-Object System.Data.DataSet
$RecordCount = $dataAdapter.Fill($dataSet, "data")
$DataSet.Tables[0]

$Connection.Close()

Insertar datos en la tabla gps obtenidos de una consulta a Google Maps

Consulta a Google Maps

$calle='SW 4th St, Miami, FL, EE. UU.'
$date=Get-Date
$urlsi="https://maps.googleapis.com/maps/api/geocode/json?address=" + $calle
$result=(Invoke-WebRequest -Uri $urlsi).Content | ConvertFrom-JSON

$calle
$result.results."geometry".location.lat
$result.results."geometry".location.lng
$date

Insertar datos en la tabla gps

$calle='SW 4th St, Miami, FL, EE. UU.'
$date=Get-Date
$urlsi="https://maps.googleapis.com/maps/api/geocode/json?address=" + $calle
$result=(Invoke-WebRequest -Uri $urlsi).Content | ConvertFrom-JSON
$date=(Get-Date).Year.tostring()+'-'+(Get-Date).Month.tostring()+'-'+(Get-Date).Day.tostring()+'-'+(Get-Date).Hour.ToString()+'-'+(Get-Date).Minute.ToString()+'-'+(Get-Date).Second.tostring()

$calle
$lat=$result.results."geometry".location.lat
$lng=$result.results."geometry".location.lng
$date

[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
$Connection = New-Object MySql.Data.MySqlClient.MySqlConnection
$ConnectionString = "server=" + "localhost" + ";port=3306;uid=" + "root" + ";pwd=" +  ";database="+"gps"
$Connection.ConnectionString = $ConnectionString
$Connection.Open()

###################################
#Insertar en tabla result
###################################

$Query = "INSERT INTO result (address, lat, lng, date) values ('"+$calle+"','"+$lat+"','"+$lng+"','"+$date+"')"
$Query
$Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)
$DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)
$DataSet = New-Object System.Data.DataSet
$RecordCount = $dataAdapter.Fill($dataSet, "data")
$DataSet.Tables[0]

$Connection.Close()

Realizar una consulta a la base de datos gps y mostrar los datos que tiene la tabla result

[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
$Connection = New-Object MySql.Data.MySqlClient.MySqlConnection
$ConnectionString = "server=" + "localhost" + ";port=3306;uid=" + "root" + ";pwd=" + ";database="+"gps"
$Connection.ConnectionString = $ConnectionString
$Connection.Open()

$Query='select * from result'
$Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)
$DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)
$DataSet = New-Object System.Data.DataSet
$RecordCount = $dataAdapter.Fill($dataSet, "data")
$DataSet.Tables[0]
$Connection.Close()