public void CanProperlyCreateCommandLineOptionsForOgr2OgrUsingGeoJSON()
{
// Arrange
// -------
var gdalDataDirectoryInfo = new DirectoryInfo(@"C:\Program Files\GDAL\gdal-data");
const string sourceLayerName = "MySourceLayerName";
var inputGdbFile = new FileInfo(@"C:\temp\MyZippedGdbFile.gdb.zip");
// Act
// ---
var actualCommandLineArguments = Ogr2OgrCommandLineRunner.BuildCommandLineArgumentsForFileGdbToGeoJson(
inputGdbFile,
gdalDataDirectoryInfo,
sourceLayerName, CoordinateSystemId, true);
// Assert
// ------
// Expecting a command line something like this:
//"C:\Program Files\GDAL\ogr2ogr.exe" --config GDAL_DATA "C:\\Program Files\\GDAL\\gdal-data" -t_srs EPSG:4326 -explodecollections -f GeoJSON /dev/stdout "C:\\svn\\sitkatech\\trunk\\Corral\\Source\\ProjectFirma.Web\\Models\\GdalOgr\\SampleFileGeodatabase.gdb.zip"
var expectedCommandLineArguments = new[] { "--config", "GDAL_DATA", gdalDataDirectoryInfo.FullName, "-t_srs", Ogr2OgrCommandLineRunner.GetMapProjection(CoordinateSystemId), "-explodecollections", "-f", "GeoJSON", "/dev/stdout", inputGdbFile.FullName, string.Format("\"{0}\"", sourceLayerName) };
Assert.That(actualCommandLineArguments, Is.EquivalentTo(expectedCommandLineArguments), "Should produce expected arguments");
var expectedCommandLineArgumentsEncodedString = string.Join(" ", expectedCommandLineArguments.Select(ProcessUtility.EncodeArgumentForCommandLine).ToList());
var actualCommandLineArgumentsEncodedString = string.Join(" ", actualCommandLineArguments.Select(ProcessUtility.EncodeArgumentForCommandLine).ToList());
Assert.That(actualCommandLineArgumentsEncodedString, Is.EqualTo(expectedCommandLineArgumentsEncodedString), "Should produce the expected command line argument string in the correct order");
}
public void CanReadExtentFromGeoJsonFile()
{
var gdbFileInfo = FileUtility.FirstMatchingFileUpDirectoryTree(@"LTInfo.Common\GdalOgr\SampleFileGeodatabase.gdb.zip");
const string sourceLayerName = "MySampleFeatureClass";
// Act
// ---
const int totalMilliseconds = 110000;
const string pathToOgr2OgrExecutable = @"C:\Program Files\GDAL\ogr2ogr.exe";
var ogr2OgrCommandLineRunner = new Ogr2OgrCommandLineRunner(pathToOgr2OgrExecutable, CoordinateSystemId, totalMilliseconds);
var geoJson = ogr2OgrCommandLineRunner.ImportFileGdbToGeoJson(gdbFileInfo, sourceLayerName, true);
// Act
// ---
const string pathToOgrInfoExecutable = @"C:\Program Files\GDAL\ogrinfo.exe";
var result = OgrInfoCommandLineRunner.GetExtentFromGeoJson(new FileInfo(pathToOgrInfoExecutable), geoJson, totalMilliseconds);
// Assert
// ------
var expectedResult = new Tuple <double, double, double, double>(-122.825678, 45.555868, -122.272895, 45.938212);
Assert.That(result.Item1, Is.EqualTo(expectedResult.Item1));
Assert.That(result.Item2, Is.EqualTo(expectedResult.Item2));
Assert.That(result.Item3, Is.EqualTo(expectedResult.Item3));
Assert.That(result.Item4, Is.EqualTo(expectedResult.Item4));
}
public void CanProperlyCreateCommandLineOptionsForArcGisQueryToMsSQL()
{
// Arrange
// -------
var gdalDataDirectoryInfo = new DirectoryInfo(@"C:\Program Files\GDAL\gdal-data");
const string destinationTableName = "MyTableWithGeometry";
const string arcGisQuery = "http://sampleserver3.arcgisonline.com/ArcGIS/rest/services/Hydrography/GeospatialArea173811/FeatureServer/0/query?where=objectid+%3D+objectid&outfields=*&f=json";
// Act
// ---
const string sourceColumnName = "areasqkm";
const string destinationColumnName = "SquareKm";
var actualCommandLineArguments = Ogr2OgrCommandLineRunner.BuildCommandLineArgumentsForArgGisQueryToMsSql(arcGisQuery, gdalDataDirectoryInfo, TempDbSqlDatabase.DatabaseConnectionStringToTempDb, destinationTableName, sourceColumnName, destinationColumnName, CoordinateSystemId);
// Assert
// ------
// Expecting a command line something like this:
//"C:\Program Files\GDAL\ogr2ogr.exe" -append -sql "select areasqkm as SquareKm from OGRGeoJSON --config GDAL_DATA "C:\\Program Files\\GDAL\\gdal-data" -f MSSQLSpatial dbconnectionstring test.json "http://sampleserver3.arcgisonline.com/ArcGIS/rest/services/Hydrography/GeospatialArea173811/FeatureServer/0/query?where=objectid+%3D+objectid&outfields=*&f=json" -nln SomeTableName"
var expectedCommandLineArguments = new[]
{
"-append", "-sql", string.Format("SELECT {0} AS {1} FROM {2}", sourceColumnName, destinationColumnName, Ogr2OgrCommandLineRunner.OgrGeoJsonTableName), "--config", "GDAL_DATA", gdalDataDirectoryInfo.FullName, "-t_srs", Ogr2OgrCommandLineRunner.GetMapProjection(CoordinateSystemId),
"-f", "MSSQLSpatial", TempDbSqlDatabase.DatabaseConnectionStringToTempDb, string.Format("\"{0}\"", arcGisQuery), "-nln", destinationTableName
};
Assert.That(actualCommandLineArguments, Is.EquivalentTo(expectedCommandLineArguments), "Should produce expected arguments");
var expectedCommandLineArgumentsEncodedString = string.Join(" ", expectedCommandLineArguments.Select(ProcessUtility.EncodeArgumentForCommandLine).ToList());
var actualCommandLineArgumentsEncodedString = string.Join(" ", actualCommandLineArguments.Select(ProcessUtility.EncodeArgumentForCommandLine).ToList());
Assert.That(actualCommandLineArgumentsEncodedString, Is.EqualTo(expectedCommandLineArgumentsEncodedString), "Should produce the expected command line argument string in the correct order");
}
public static string RunOgr2OgrAndGetVersionNumber(string pathToOgr2OgrExecutable, double totalMilliseconds)
{
var ogr2OgrCommandLineArguments = new List <string> {
"--version"
};
var ogr2orgRunner = new Ogr2OgrCommandLineRunner(pathToOgr2OgrExecutable, LtInfoGeometryConfiguration.DefaultCoordinateSystemId, totalMilliseconds);
string stdErrorAndStdOutAsString = ogr2orgRunner.ExecuteOgr2OgrCommand(ogr2OgrCommandLineArguments).StdOutAndStdErr;
// Parse out the version number such as "GDAL 1.11.22.0" version number string
const string gdalVersionNumberRegex = "GDAL ([0-9]+.[0-9]+.[0-9]+)";
var result = Regex.Match(stdErrorAndStdOutAsString, gdalVersionNumberRegex);
return(result.Success ? result.Groups[1].Value : $"Could not find ogr2ogr version number from stdout output: {stdErrorAndStdOutAsString}");
}
public void CanReadColumnNamesFromGeoJsonString()
{
var gdbFileInfo = FileUtility.FirstMatchingFileUpDirectoryTree(@"LTInfo.Common\GdalOgr\SampleFileGeodatabase.gdb.zip");
const string sourceLayerName = "MySampleFeatureClass";
// Act
// ---
const int totalMilliseconds = 110000;
const string pathToOgr2OgrExecutable = @"C:\Program Files\GDAL\ogr2ogr.exe";
var ogr2OgrCommandLineRunner = new Ogr2OgrCommandLineRunner(pathToOgr2OgrExecutable, GdalGeoJsonTest.CoordinateSystemId, totalMilliseconds);
var geoJson = ogr2OgrCommandLineRunner.ImportFileGdbToGeoJson(gdbFileInfo, sourceLayerName, true);
var a = JsonTools.DeserializeObject <GeoJSON.Net.Feature.FeatureCollection>(geoJson);
var columnList = new List <string> {
"Ogr_Fid", "Ogr_Geometry", "MyIntColumn", "MyStringColumn", "MyFloatColumn"
};
}
public void CanExecuteOgr2OgrForFileGdbToGeoJson()
{
// Arrange
// -------
var gdbFileInfo = FileUtility.FirstMatchingFileUpDirectoryTree(@"LTInfo.Common\GdalOgr\SampleFileGeodatabase.gdb.zip");
const string sourceLayerName = "MySampleFeatureClass";
// Act
// ---
const int totalMilliseconds = 110000;
const string pathToOgr2OgrExecutable = @"C:\Program Files\GDAL\ogr2ogr.exe";
var ogr2OgrCommandLineRunner = new Ogr2OgrCommandLineRunner(pathToOgr2OgrExecutable, CoordinateSystemId, totalMilliseconds);
var geoJson = ogr2OgrCommandLineRunner.ImportFileGdbToGeoJson(gdbFileInfo, sourceLayerName, true);
// Assert
// ------
Assert.That(geoJson, Contains.Substring("\"properties\":"), "Should have properties");
}
public void CanReadColumnNamesFromGeoJsonString()
{
// Arrange
// -------
var gdbFileInfo = FileUtility.FirstMatchingFileUpDirectoryTree(@"LTInfo.Common\GdalOgr\SampleFileGeodatabase.gdb.zip");
const string sourceLayerName = "MySampleFeatureClass";
const int totalMilliseconds = 110000;
const string pathToOgr2OgrExecutable = @"C:\Program Files\GDAL\ogr2ogr.exe";
var ogr2OgrCommandLineRunner = new Ogr2OgrCommandLineRunner(pathToOgr2OgrExecutable, LtInfoGeometryConfiguration.DefaultCoordinateSystemId, totalMilliseconds);
// Act
// ---
var geoJson = ogr2OgrCommandLineRunner.ImportFileGdbToGeoJson(gdbFileInfo, sourceLayerName, true);
// Assert
// ------
var featureCollection = JsonTools.DeserializeObject <GeoJSON.Net.Feature.FeatureCollection>(geoJson);
var propertyNames = featureCollection.Features.First().Properties.Select(x => x.Key).ToList();
var expectedPropertyNames = new List <string> {
"MyStringColumn", "IgnoredTextColumn", "IgnoredIntColumn", "MyIntColumn", "MyFloatColumn", "Shape_Length", "Shape_Area"
};
Assert.That(propertyNames, Is.EquivalentTo(expectedPropertyNames), "Should get expected columns");
}
public void CanExecuteOgr2OgrFromGeoJsonSingleColumnToExistingMsSql()
{
var gdbFileInfo = FileUtility.FirstMatchingFileUpDirectoryTree(@"LTInfo.Common\GdalOgr\SampleFileGeodatabase.gdb.zip");
const string sourceLayerName = "MySampleFeatureClass";
// Act
// ---
const int totalMilliseconds = 110000;
const string pathToOgr2OgrExecutable = @"C:\Program Files\GDAL\ogr2ogr.exe";
var ogr2OgrCommandLineRunner = new Ogr2OgrCommandLineRunner(pathToOgr2OgrExecutable, CoordinateSystemId, totalMilliseconds);
var geoJson = ogr2OgrCommandLineRunner.ImportFileGdbToGeoJson(gdbFileInfo, sourceLayerName, true);
var sourceColumnName1 = "mystringcolumn";
var destinationTableName = "test_table";
var destinationColumnName = "attribute";
try
{
CreateOgrRequiredTables(destinationTableName, null);
// Act
// ---
ogr2OgrCommandLineRunner.ImportGeoJsonToMsSql(geoJson, TempDbSqlDatabase.DatabaseConnectionStringToTempDb, destinationTableName, sourceColumnName1, destinationColumnName, string.Format(", {0} as ProjectID", 77));
var result = ExecAdHocSql($"select * from {destinationTableName}");
// Assert
// ------
Assert.That(result, Is.Not.Null, "Should have found the table imported");
Assert.That(result.Rows.Count, Is.EqualTo(6), "Should have gotten 6 rows");
//var myStringColumns = result.Rows.Cast<DataRow>().Select(x => x.IsNull(destinationColumnName) ? null : x[destinationColumnName].ToString()).ToList();
//Assert.That(myStringColumns, Is.EquivalentTo(new[] { "?\0Excavate channels to lakes", "?\0Excavate channels to lakes", null, null, "?\0LCEP s", "?\0LCEP s" }), "Should have gotten these values for MyStringColumn");
}
finally
{
// Cleanup
// -------
try
{
ExecAdHocSql("drop table " + destinationTableName);
}
catch
{
// ignored
}
try
{
ExecAdHocSql("drop table spatial_ref_sys");
}
catch
{
// ignored
}
try
{
ExecAdHocSql("drop table geometry_columns");
}
catch
{
// ignored
}
}
}
public void CanExecuteOgr2OgrForArcGisQueryToMsSQL()
{
// Arrange
// -------
const string arcGisQuery = "http://sampleserver3.arcgisonline.com/ArcGIS/rest/services/Hydrography/GeospatialArea173811/FeatureServer/0/query?where=objectid%20IN%20%281%2C2%2C3%2C4%29&outfields=*&f=json";
const string destinationTableName = "test_table";
const string sourceColumnName = "areasqkm";
const string destinationColumnName = "attribute";
// Act
// ---
const int totalMilliseconds = 110000;
const string pathToOgr2OgrExecutable = @"C:\Program Files\GDAL\ogr2ogr.exe";
var ogr2OgrCommandLineRunner = new Ogr2OgrCommandLineRunner(pathToOgr2OgrExecutable, CoordinateSystemId, totalMilliseconds);
try
{
CreateOgrRequiredTables(null, destinationTableName);
// Act
// ---
ogr2OgrCommandLineRunner.ImportArcGisQueryToMsSql(arcGisQuery, destinationTableName, sourceColumnName, destinationColumnName, TempDbSqlDatabase.DatabaseConnectionStringToTempDb);
var result = ExecAdHocSql(string.Format("select * from {0}", destinationTableName));
// Assert
// ------
Assert.That(result, Is.Not.Null, "Should have found the table imported");
Assert.That(result.Rows.Count, Is.EqualTo(4), "Should have gotten 4 rows");
var myStringColumns = result.Rows.Cast <DataRow>().Select(x => x.IsNull(destinationColumnName) ? null : (string)x[destinationColumnName]).ToList();
Assert.That(myStringColumns, Is.EquivalentTo(new[] { "0.043", "0.012", "0.013", "0.008" }), "Should have gotten these values for areasquarekm");
}
finally
{
// Cleanup
// -------
try
{
ExecAdHocSql("drop table " + destinationTableName);
}
catch
{
// ignored
}
try
{
ExecAdHocSql("drop table spatial_ref_sys");
}
catch
{
// ignored
}
try
{
ExecAdHocSql("drop table geometry_columns");
}
catch
{
// ignored
}
}
}
public void CanExecuteOgr2OgrFromGeoJsonSingleColumnToExistingMsSql()
{
AssertCustom.IgnoreOnBuildServer();
var gdbFileInfo = FileUtility.FirstMatchingFileUpDirectoryTree(@"LTInfo.Common\GdalOgr\SampleFileGeodatabase.gdb.zip");
const string sourceLayerName = "MySampleFeatureClass";
// Act
// ---
const int totalMilliseconds = 110000;
const string pathToOgr2OgrExecutable = @"C:\Program Files\GDAL\ogr2ogr.exe";
var ogr2OgrCommandLineRunner = new Ogr2OgrCommandLineRunner(pathToOgr2OgrExecutable, CoordinateSystemId, totalMilliseconds);
var geoJson = ogr2OgrCommandLineRunner.ImportFileGdbToGeoJson(gdbFileInfo, sourceLayerName, true);
var destinationTableName = "test_table";
var sqlSelectClause = string.Format("mystringcolumn as attribute, {0} as ProjectID", 77);
try
{
CreateOgrRequiredTables(destinationTableName, null);
// Act
// ---
ogr2OgrCommandLineRunner.ImportGeoJsonToMsSql(geoJson, TempDbSqlDatabase.DatabaseConnectionStringToTempDb, destinationTableName, sqlSelectClause);
var result = ExecAdHocSql(string.Format("select * from {0}", destinationTableName));
// Assert
// ------
Assert.That(result, Is.Not.Null, "Should have found the table imported");
Assert.That(result.Rows.Count, Is.EqualTo(6), "Should have gotten 6 rows");
}
finally
{
// Cleanup
// -------
try
{
ExecAdHocSql("drop table " + destinationTableName);
}
catch
{
// ignored
}
try
{
ExecAdHocSql("drop table spatial_ref_sys");
}
catch
{
// ignored
}
try
{
ExecAdHocSql("drop table geometry_columns");
}
catch
{
// ignored
}
}
}
