Contenidos
Crear la base de datos gps en MySQL desde PowerShell
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
[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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
[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
|
1 2 3 4 5 6 7 8 9 |
$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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
$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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
[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() |
