Hi,
Could you advise a way to export SQL2008Layer to shapefiles?
I have checked ShapeFileFeatureSource.CreateShapeFile method, but not sure where to go from there.
Thx.
SQL2008Layer export to Shapefiles
Keith,
Thanks for your post and questions.
I think exporting data into ShapeFile from other sources including MsSqlFeatureSource include the following 2 steps:
1)Create empty shapes with columns using API CreateShapeFile.
This step we should passed in the columns we want to insert into dbf file.
2) Insert records into ShapeFile using our Transaction mode. Following is the code prototype.
targetShapeFileFetureLayer.Open();
try
{
targetShapeFileFetureLayer.EditTools.BeginTransaction();
foreach (Feature feature in allFetures)
{
targetShapeFileFetureLayer.EditTools.Add(feature);
}
}
finally
{
TransactionResult result = targetShapeFileFetureLayer.EditTools.CommitTransaction();
if (result.TotalFailureCount > 0)
{
//Errors are happening in the Transaction.
}
}
targetShapeFileFetureLayer.Close();
Any more questions please feel free to let me know.
Thanks.
Yale
Yale, thx for the response and sample code.
The problem I have atm is how to call CreateShapeFile API to pass IEnumerable<dbfcolumn><dbfcolumn>.
MsSql2008FeatureLayer sqlLayer = new MsSql2008FeatureLayer(blah, blah, blah..);
<featuresourcecolumn><featuresourcecolumn>Collection<FeatureSourceColumn> columnCollection = sqlLayer.FeatureSource.GetColumns();
What would be the easiest way to get an IEnumerable<dbfcolumn><dbfcolumn> from MsSql2008FeatureLayer FeatureSourceColumn collection?
While you can call myCollection.GetEnumerator(); to return a collection of <featuresourcecolumn>FeatureSourceColumn and iterate thought all to construct DbfColumn objects, how to put them in IEnumerable<dbfcolumn>?
This seems more like a c# programming question, not a ThinkGeo question, but your input will be appreciated.
</dbfcolumn></featuresourcecolumn></dbfcolumn></dbfcolumn></featuresourcecolumn></featuresourcecolumn></dbfcolumn></dbfcolumn>
Keith,
I am ashamed to misunderstand your questions.
As my understanding, this is not just a simple C# questions. The dbf data type is probably different with other data sources, so we may need to convert the types from SQL Server to the types used in dbf. Following code shows you some ideas how to achieve this:
Collection<DbfColumn> dbfColumns = new Collection<DbfColumn>();
foreach (FeatureSourceColumn featureSourceColumn in featureSourceColumns)
{
DbfColumnType dbfColumType = GetDbfType(featureSourceColumn.TypeName);
int length = featureSourceColumn.MaxLength;
int decimalLength = 0;
if (dbfColumType == DbfColumnType.Double)
{
decimalLength = 2;
}
dbfColumns.Add(new DbfColumn(featureSourceColumn.ColumnName, dbfColumType, length, decimalLength));
}
ShapeFileFeatureLayer.CreateShapeFile(ShapeFileType.Polygon, @"", dbfColumns);
private DbfColumnType GetDbfType(string type)
{
DbfColumnType returnType = DbfColumnType.Null;
switch (type.ToUpper(CultureInfo.InvariantCulture))
{
case "STRING":
returnType = DbfColumnType.String;
break;
case "INT":
returnType = DbfColumnType.Integer;
break;
case "DOUBLE":
returnType = DbfColumnType.Double;
break;
//Deal with the Date, Memo, Logical
default:
break;
}
return returnType;
}
Any more information mentioned by Johnny would be helpful.
Thanks.
Yale
Thx for the help Yale.
featureSourceColumn.MaxLength always returns -1 value. So to overcome this problem, I hard cod the column size depend on the data type. I have the following:
switch (column.TypeName.ToUpper(System.Globalization.CultureInfo.InvariantCulture))
{
case "DATETIME":
returnval = new DbfColumn(dbfColumnName
, DbfColumnType.Date
, 8
, 0);
break;
case "DOUBLE":
returnval = new DbfColumn(dbfColumnName
, DbfColumnType.Double
, 32
, 4);
break;
case "INT32":
returnval = new DbfColumn(dbfColumnName
, DbfColumnType.Integer
, 16
, 0);
break;
case "LOGICAL":
returnval = new DbfColumn(dbfColumnName
, DbfColumnType.Logical
, 32
, 0);
break;
case "MEMO":
returnval = new DbfColumn(dbfColumnName
, DbfColumnType.Memo
, 32
, 0);
break;
case "STRING":
returnval = new DbfColumn(dbfColumnName
, DbfColumnType.String
, 256
, 0);
break;
default:
throw new Exception( "Error while trying to export shapefile "
+ " due to un-supported data type "
+ column.TypeName);
}
Doing so works well. After iterate though all feature in source layer (SQL2008Layer in this case) and add it to the output shapefile layer (using ShapeFileFeatureSource to open the newly created shapefile) it does work. However, all columns in dbf records are blank. I am guess it has something to do with the column size I hard coded?
ShapeFileFeatureSource outputShapefile
= new ShapeFileFeatureSource(targetShapefileName, ShapeFileReadWriteMode.ReadWrite);
// copy feature from source layer to target shapefile
sqlLayer.FeatureSource.Open();
outputShapefile.Open();
outputShapefile.BeginTransaction();
foreach (var f in sqlLayer.FeatureSource.GetAllFeatures(0))
{
outputShapefile.AddFeature(f);
}
outputShapefile.CommitTransaction();
outputShapefile.Close();
sqlLayer.FeatureSource.Close();
Keith,
Thanks for our sharing and feedback.
Could you verify that the dbf column is empty by opening it in MapSuite Explorer and do some queries against it? I came into some tools opening DBF show wrong results for us.
And also, following code snippet shows you the way to watch the commit transaction result, can you have a look if all of them failed when committing transaction?
TransactionResult result = outputShapefile.CommitTransaction();
int count = result.TotalFailureCount;
Any more questions please feel free to let me know.
Thanks.
Yale
Yale,
I have checked the output shapefile using MapSuite Explorer but it shows blank values.
I have put in the following as you have recommended and it return 0 count for TotalFailureCount. However, the values for the records in DBF are still blank. However, I think the hard coded column size in DBFTYpe might be the problem (see my previous code post) as I have noticed values for decimal data type are populated correctly.
One more thing to mention is that column name size for DBF is restricted to 10 characters only so therefore when I map the columns from SQL2008Layer to ShapeFileFeatureLayer, I truncate it to 10 characters if it is over 10.
Would you be able to explain why featureSourceColumn.MaxLength (from SQL2008Layer) always returns -1 value?
TransactionResult result = outputShapefile.CommitTransaction();
int count = result.TotalFailureCount;
private DbfColumn CreateDbfColumn(FeatureSourceColumn column)
{
DbfColumn returnval;
string dbfColumnName = "";
if (column.ColumnName.Length < 10)
dbfColumnName = column.ColumnName;
else
dbfColumnName = column.ColumnName.Substring(0, 10);
switch (column.TypeName.ToUpper(System.Globalization.CultureInfo.InvariantCulture))
{
case "DATETIME":
case "DATE":
returnval = new DbfColumn(dbfColumnName
, DbfColumnType.Date
, 8
, 0);
break;
case "DOUBLE":
case "DECIMAL":
case "FLOAT":
case "NUMERIC":
case "REAL":
returnval = new DbfColumn(dbfColumnName
, DbfColumnType.Double
, 32
, 3);
break;
case "INT32":
case "SMALLINT":
case "TINYINT":
returnval = new DbfColumn(dbfColumnName
, DbfColumnType.Integer
, 16
, 0);
break;
case "LOGICAL":
returnval = new DbfColumn(dbfColumnName
, DbfColumnType.Logical
, 32
, 0);
break;
case "MEMO":
returnval = new DbfColumn(dbfColumnName
, DbfColumnType.Memo
, 32
, 0);
break;
case "STRING":
case "CHAR":
case "TEXT":
returnval = new DbfColumn(dbfColumnName
, DbfColumnType.String
, 255
, 0);
break;
default:
throw new Exception( "Error while trying to export shapefile "
+ "due to un-supported data type "
+ column.TypeName);
}
return returnval;
}
Keith,
Thanks for your post and questions.
It is wield that why data is not imported correctly even though the failure count is 0, I cannot make sure why this is happening. The second issue about the MaxLength, I checked that the default value of the MaxLength is 0 instead of -1, so I doubt when you create the FeatureSourceColumn, it is set it to -1?
Any more questions please feel free to let me know.
Thanks.
Yale
Yale,
thank you for information.
The weird thing is the FeatureSourceColumn was straight out of SQL2008Layer. It’s all ThinkGeo API call and there’s no any manual overwrite to FeatureSourceColumn property after SQL2008Layer is contracted (SQL2008Layer.Open()).
I understand the default value for MaxLength is 0. Can you check in SQL2008Layer class and confirm if the default value gets overwritten to -1 when certain error occurs?
Thank you.
Keith,
Thanks for your input and questions. I checked that we did not change the max length value to -1 in the MsSqlFeatureSource, so I do not think there is anything unexpected error is happening to cause this wield problem.
Can you have a quick try on the following code snippet to show the max length without any operations.
string connectString = "Data Source=192.168.0.58,1041;Initial Catalog=DatabaseName;Persist Security Info=True;User ID=username;Password=password";
MsSql2008FeatureLayer sql2008Layer = new MsSql2008FeatureLayer(connectString, "states", "recid");
sql2008Layer.Open();
Collection<FeatureSourceColumn> columns = sql2008Layer.QueryTools.GetColumns();
foreach (FeatureSourceColumn column in columns)
{
int maxLength = column.MaxLength;
System.Diagnostics.Debug.WriteLine(string.Format("Column {0} Maxlength is {1}", column.ColumnName, column.MaxLength));
}
Any more questions please feel free to let me know.
Thanks.
Yale
interesting behavior I have found.
The table name I pass to SQL2008Layer contractor is a database view (included a valid geometry column). Using the code snippet you have provided, all column return -1 for MaxLenght. However, the the map does get displayed using a view in SQL2008Layer!
Is it not recommended to use view to construct SQL2008Layer? Reason for using view is to have the ability to customize different layer displayed on same table or a combination of tables with lookups. Not sure why MaxLength returns -1 for data view.
Keith,
I think it's a bug when you want to use View by MsSql2008FeatureLayer, I have added it to our issue system, we will spend time to fix later, when we complete I will let you know. Right now, the workaround is create a custom MsSql2008FeatureLayer and override GetColumnsCore method of MsSql2008FeatureSource, the sample code you can look at attached text file, and then you can use following code to test.
string connectString = "Data Source=192.168.0.58,1041;Initial Catalog=DatabaseName;Persist Security Info=True;User ID=username;Password=password";
CustomMsSql2008FeatureLayer sql2008Layer = new CustomMsSql2008FeatureLayer(connectString, "states", "recid");
((CustomMsSql2008FeatureSource)sql2008Layer.FeatureSource).WkbFieldName = "Geo";
sql2008Layer.Open();
Collection<FeatureSourceColumn> columns = sql2008Layer.QueryTools.GetColumns();
foreach (FeatureSourceColumn column in columns)
{
int maxLength = column.MaxLength;
System.Diagnostics.Debug.WriteLine(string.Format("Column {0} Maxlength is {1}", column.ColumnName, column.MaxLength));
}
Please let me know if you want to more help.
Thanks
Keith
CustomMsSql2008FeatureLayer.txt (5.45 KB)