ThinkGeo.com    |     Documentation    |     Premium Support

How to create Shape File columns for SQLite columns?

#1

I try to create a SQLite OSM table to Shape File Converter
tool and have failed to create correct set of shape file columns for SQLite columns.


My DbfColumn
GetDBFColumnForSQLite(FeatureSourceColumn
featureSourceColumn) function works for the string type fields, but the code is definitely
wrong for other field types. I cannot find a code sample which allows me to set
a shape file field for different types of the SQLite fields. When the code creates a new shape file with my set of columns it raises the "Your input arguments are illegal, please check" error. For example, I'm getting this error for the osm_road5m_linestring table.


I have tried to use some code from your forum posts but no luck
(thinkgeo.com/forums/MapSuit...fault.aspx
and thinkgeo.com/forums/MapSuite...fault.aspx).


Please help.


                      Collection<DbfColumn> allColumns = new Collection<DbfColumn>();


                      foreach (FeatureSourceColumn column in sqliteFeatureSource.GetColumns())


{


                     if
(column.ColumnName.ToLower() == "geometry"
|| column.ColumnName.ToLower() == "id")
continue;


                     DbfColumn
dbfColumn = GetDBFColumnForSQLite(column);


                    if
(dbfColumn != null)


                    {


                       
allColumns.Add(dbfColumn);


                    }


                }


 ….



         private
DbfColumn GetDBFColumnForSQLite(FeatureSourceColumn featureSourceColumn)


        {


            switch
(featureSourceColumn.TypeName.ToUpper())


            {


                case
"STRING":


                case
"CHARACTER":


                case
"VARCHAR":


                case
"VARYING CHARACTER":


                case
"CHARACTER VARYING":


                case
"NCHAR":


                case
"NATIVE CHARACTER":


                case
"NVARCHAR":


                case
"TEXT":


                case
"CLOB":


                    return
new DbfColumn(featureSourceColumn.ColumnName,
DbfColumnType.Character,
featureSourceColumn.MaxLength, 0);


                case
"MEMO":


                    return
new DbfColumn(featureSourceColumn.ColumnName,
DbfColumnType.Memo,
featureSourceColumn.MaxLength, 0);


                case
"INTEGER":


                case
"INT":


                case
"TINYINT":


                case
"SMALLINT":


                case
"MEDIUMINT":


                case
"BIGINT":


                case
"UNSIGNED BIG INT":


                case
"INT2":


                case
"INT8":


                    return
new DbfColumn(featureSourceColumn.ColumnName,
DbfColumnType.IntegerInBinary, 4, 0);


                case
"LOGICAL":


                    return
new DbfColumn(featureSourceColumn.ColumnName,
DbfColumnType.Logical, 4, 0);


                case
"DOUBLE":


                case
"DOUBLE PRECISION":


                    return
new DbfColumn(featureSourceColumn.ColumnName,
DbfColumnType.Float, 0, 8);


                case
"FLOAT":


                case
"NUMERIC":


 
                  return new DbfColumn(featureSourceColumn.ColumnName, DbfColumnType.Numeric, 0, 8);


                case
"DECIMAL":


                case
"REAL":


                    return
new DbfColumn(featureSourceColumn.ColumnName,
DbfColumnType.Float, 0,
featureSourceColumn.MaxLength);


                case
"DATE":


                    return
new DbfColumn(featureSourceColumn.ColumnName,
DbfColumnType.Date,
featureSourceColumn.MaxLength, 0);


                case
"DATETIME":


                    return
new DbfColumn(featureSourceColumn.ColumnName,
DbfColumnType.DateTime,
featureSourceColumn.MaxLength, 0);


                default:


                    return
new DbfColumn(featureSourceColumn.ColumnName,
DbfColumnType.Character,
featureSourceColumn.MaxLength, 0);


            }


        }

Thanks,

Gene



#2

Hi Gene, 
  
 It looks we have a utility convert shape file to sqlite here: wiki.thinkgeo.com/wiki/map_suite_wpf_desktop_edition_all_samples#shapefile_to_sqlite_convertion_tool 
  
 Please view it to see whether there is any help. 
  
 It looks the exception meant your input type are not supported by DbfColumn, can you debug it and see where throw this exception? 
  
 And if possible please sent us a sample contains your test data and code so we can help you on it. 
  
 Regards, 
  
 Don

#3

Hi Don,



I’ve started with ThinkGeo convert shape file to sqlite utility code to create my SQLite to Shape File Converter tool.



My test data is osm_road5m_linestring table in the ThinkGeo OSM SQLite database.



 I don’t know what field  has problem because the code allows to add all fields but crashes on  the ShapeFileFeatureSource.CreateShapeFile function.



The following is my code:



        void
backgroundWorker_DoWork(object sender, DoWorkEventArgs e)


        {


            try


            {


                string[]
args = e.Argument as string[];


                string
selectedShapeFileName = args[0];


                string
databaseName = args[1]; 


                string
tblName = args[2]; 


                string
prjFileName = args[3];


                string
connectingString = string.Format(“Data Source={0};Version=3;”,
databaseName);              


                List<string> tableIDList = GetTableIDList(tblName,
databaseName);


                SqliteFeatureSource
sqliteFeatureSource = new SqliteFeatureSource(connectingString, tblName, “id”, “geometry”);


                sqliteFeatureSource.Open();


                WellKnownType
wkType = sqliteFeatureSource.GetFirstFeaturesWellKnownType();


                ShapeFileType
sfType = ShapeFileType.Null;


                switch
(wkType)


                {


                    case
WellKnownType.Line:


                    case
WellKnownType.Multiline:


                        sfType = ShapeFileType.Polyline;


                        break;


                    case
WellKnownType.Multipoint:


                        sfType = ShapeFileType.Multipoint;


                        break;


                    case
WellKnownType.Polygon:


                    case WellKnownType.Multipolygon:


                        sfType = ShapeFileType.Polygon;


                        break;


                    case
WellKnownType.Point:


                        sfType = ShapeFileType.Point;


                        break;


                }


                //
Get the columns


                Collection<DbfColumn> allColumns = new Collection<DbfColumn>();


                List<string> fields = new
List<string>();


                foreach
(FeatureSourceColumn column in sqliteFeatureSource.GetColumns())


                {


                    if
(column.ColumnName.ToLower() == “geometry”
|| column.ColumnName.ToLower() == “id”)
continue;


                    DbfColumn
dbfColumn = GetDBFColumnForSQLite(column);       


                    if
(dbfColumn != null)


                    {


                        allColumns.Add(dbfColumn);


                       
fields.Add(column.ColumnName);


                    }


                }


                if
(allColumns.Count == 0)


                {


                    DbfColumn
dbfDummyColumn = new DbfColumn(“name”, DbfColumnType.Character,
254, 0);         


                   
allColumns.Add(dbfDummyColumn);


                    fields.Add(“name”);


                }


                ShapeFileFeatureSource.CreateShapeFile(sfType,
selectedShapeFileName, allColumns);


                ShapeFileFeatureLayer
shapeFileLayer = new ShapeFileFeatureLayer(selectedShapeFileName,
ShapeFileReadWriteMode.ReadWrite);


                shapeFileLayer.Open();


                shapeFileLayer.EditTools.BeginTransaction();


                foreach
(string fID in
tableIDList)


                {


                    Feature
feat = sqliteFeatureSource.GetFeatureById(fID, ReturningColumnsType.AllColumns);


 


                    if
(feat.ColumnValues.Count == 0)


                    {


                        continue;


                    }


                   
shapeFileLayer.EditTools.Add(feat);


                }


                TransactionResult
result = shapeFileLayer.EditTools.CommitTransaction();


                shapeFileLayer.Close();                    


                sqliteFeatureSource.Close();


 


                System.IO.File.Copy(prjFileName,
selectedShapeFileName.Substring(0, selectedShapeFileName.LastIndexOf(’.’)) + “.prj”);


            }


            catch
(System.Exception)


            {           


                throw;


            }


        }




      private DbfColumn GetDBFColumnForSQLite(FeatureSourceColumn featureSourceColumn)


        {


            switch (featureSourceColumn.TypeName.ToUpper())


            {


                case “STRING”:


                case “CHARACTER”:


                case “VARCHAR”:


                case “VARYING CHARACTER”:


                case “CHARACTER VARYING”:


                case “NCHAR”:


                case “NATIVE CHARACTER”:


                case “NVARCHAR”:


                case “TEXT”:


                case “CLOB”:


                    return new DbfColumn(featureSourceColumn.ColumnName, DbfColumnType.Character, featureSourceColumn.MaxLength, 0);


                case “MEMO”:


                    return new DbfColumn(featureSourceColumn.ColumnName, DbfColumnType.Memo, featureSourceColumn.MaxLength, 0);


                case “INTEGER”:


                case “INT”:


                case “TINYINT”:


                case “SMALLINT”:


                case “MEDIUMINT”:


                case “BIGINT”:


                case “UNSIGNED BIG INT”:


                case “INT2”:


                case “INT8”:


                    return new DbfColumn(featureSourceColumn.ColumnName, DbfColumnType.IntegerInBinary, 4, 0);


                case “LOGICAL”:


                    return new DbfColumn(featureSourceColumn.ColumnName, DbfColumnType.Logical, 4, 0);


                case “DOUBLE”:


                case “DOUBLE PRECISION”:


                    return new DbfColumn(featureSourceColumn.ColumnName, DbfColumnType.Float, 0, 8);


                case “FLOAT”:


                case “NUMERIC”:


                    return new DbfColumn(featureSourceColumn.ColumnName, DbfColumnType.Numeric, 0, 8);


                case “DECIMAL”:


                case “REAL”:


                    return new DbfColumn(featureSourceColumn.ColumnName, DbfColumnType.Float, 0, featureSourceColumn.MaxLength);


                case “DATE”:


                    return new DbfColumn(featureSourceColumn.ColumnName, DbfColumnType.Date, featureSourceColumn.MaxLength, 0);


                case “DATETIME”:


                    return new DbfColumn(featureSourceColumn.ColumnName, DbfColumnType.DateTime, featureSourceColumn.MaxLength, 0);


                default:


                    return new DbfColumn(featureSourceColumn.ColumnName, DbfColumnType.Character, featureSourceColumn.MaxLength, 0);


            }



        }



#4

Hi Gene, 
  
 The problem seems to be caused by the length of DbfColumn which must be less than or equal 65535.  
  
 Please try the following code to create a character DbfColumn: 
  
return new DbfColumn(featureSourceColumn.ColumnName, DbfColumnType.Character, 65535, 0);
 
 Thanks,

#5

Don,



I’ve tried your new value for a character column and was able to create a shape file and convert features, but got the The “Field type is invalid or unsupported” error in ArcCatalog. It looks like some column type setting is still wrong.



The osm_road5m_linestring table shows the following create table statement in the SQLite browser:



CREATE TABLE osm_road5m_linestring (“osm_id” bigint,“highway” character varying,“ref” character varying,“way_z_order” integer,id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,“geometry” BLOB).



Please help.



Thanks,
Gene

#6

Hi Gene, 
  
 I guess the reason is that the length of column name is bigger than 10. The length of column name “way_z_order” is 11 which is bigger than 10,  a “.dbc” file will be created and column’s name will be truncated when call the “CreateShapeFile” method. So please try to rename the “way_z_order”. 
  
 Please try another table if it doesn’t work right. 
  
 Any questions please let me know. 
  
 Thanks, 
 Peter 


#7

Don,



I’ve trimmed field names, but got the same error.


I’ve already provided you with my code and you have the osm_road5m_linestring table to test. The problem is definitely with the GetDBFColumnForSQLite function.


I believe it should be easy for you to reproduce the problem.


Could you please help me with this issue? Please let me know
if I need to create a ticket.



Thanks,

Gene



#8

Hi Gene, 



Please change the “GetDBFColumnForSQLite” method and add features to shapefile as following: 



“GetDBFColumnForSQLite” method: 


private static DbfColumn GetDBFColumnForSQLite(FeatureSourceColumn featureSourceColumn) 

{ 

    return new DbfColumn(featureSourceColumn.ColumnName, DbfColumnType.Character, Math.Min(featureSourceColumn.MaxLength, 255), 0); 

}



“backgroundWorker_DoWork” method: 


void backgroundWorker_DoWork(object sender, DoWorkEventArgs e) 

{

 … 

  shapeFileLayer.EditTools.BeginTransaction(); 

  int totalRecordCount = 0; 

  foreach (string fID in tableIDList) 

  { 

      Feature feat = sqliteFeatureSource.GetFeatureById(fID, ReturningColumnsType.AllColumns); 

      if (feat.ColumnValues.Count == 0)

      { 

        continue; 

      } 

      totalRecordCount++; 

      shapeFileLayer.EditTools.Add(feat); 

      if (totalRecordCount % 500 == 0) 

      { 

        shapeFileLayer.EditTools.CommitTransaction(); 

        shapeFileLayer.EditTools.BeginTransaction(); 

      } 

  } 

  shapeFileLayer.EditTools.CommitTransaction();

 …

}

I use the table “osm_land100k_polygon” and it works well. Attached is the screenshot on my side.







NOTE:

Please remember to trim the length of field names less than or equal 10.



Thanks, 

Peter

#9

Hi Peter,



Thank you for your help.



This approach works, and I’m already committing transactions for each 200 rows.



But I don’t agree with this solution in general. ThinkGeo Shape File to SQLite converter tool also creates text columns only. The ThinkGeo OSM SQLite database tables have all types of columns. It means if I import a table to a shape file, edit it ArcGIS, and export it back to SQLite, the new table will have a different structure. 



In this case:




        
  1. Some of the existing layers styles or queries may not work in the existing code.

    2.     
  2. String columns could increase the database size.

    3.     
  3. String columns could affect the database performance.


I think this is a critical issue because this is the only way to analyze and edit ThinkGeo OSM SQLite data outside the ThinkGeo tools or export a customer data to SQLIte.



Please let me know if I misunderstood something or if you have another solution.



Thanks,

Gene

#10

Hi Gene, 
  
 In addition, it also supports another column types, such as  “Float”, “Numeric”, and so on, but do not supports “IntegerInBinary” and “DoubleInBinary”. 
  
 If you have to import a table to a shapefile and export it back, maybe you can do it by programmatically (After edit it in ArcGIS and update the SQLite table, not create a new one).  
  
 Thanks, 
 Peter 


#11

Peter, 
  
 In this case it should be a way to convert a SQLite table to a Shape File and from Shape File to SQLite with a correct field types, not only strings. Also it should be a way to convert fields IntegerInBinary to Integer and DoubleInBinary to Double. 
  
 Thanks, 
 Gene

#12

Hi Gene, 
  
 Sorry I don’t understand what did you mean.  The shapefile, which is converted from a SQLite table by using IntegerInBinary or DoubleInBainary,  can’t be opened with the ArcGIS. It can be achieved by programmatically that to import a table to a shapefile and export it back without changing the structure of table. 
  
 The DbfColumnType doesn’t support the types “Integer” and “Double” at this time, but the “Integer” can be treated as “Numeric” and the “Double” can be treated as “Float”.  
  
 Thanks, 
 Peter  


#13

Hi Peter, 
  
 Your reply confirms that there is a way to convert SQLite field types to Shape File field types and Shape File field types to SQLite field types properly, but we need "GetDBFColumnForSQLite" and "GetSQLiteForDBFColumn  functions, which I failed to create (please see above messages in this post). 
  
 I would like to explain the problem again: 
  
 This is a critical issue because this is the only way to analyze and edit ThinkGeo OSM SQLite data outside the ThinkGeo tools or export a customer data to SQLite. ThinkGeo customers should be able to export/import SQLite data with different column types. 
  
 Thanks, 
 Gene

#14

Hi Gene, 
  
 Thanks for the suggestions. We’ll consider this scenario and enhance it in the future. 
  
 Thanks, 
 Peter