public static ReturnValue ExportToSQLiteDatabase(IPointLayer pLyr, string pOPFn, ProjectionInfo pSrcProj, ProjectionInfo pTgtProj)
{
var mRV = new ReturnValue(true);
var mReturnValue = new ReturnValue();
var mTgtSRS = ExtFunctions.GetSpatialReferenceByEPSG(pTgtProj.AuthorityCode);
bool mTransformationRequired = pSrcProj != pTgtProj ? true : false;
if (mTransformationRequired)
{
pLyr.Reproject(pTgtProj);
}
SLib.Create(pOPFn);
// Check if table exists
if (!SLib.TableExists("geoobjects"))
{
var mSql = "CREATE TABLE geoobjects (title TEXT, description TEXT, gtype TEXT, left REAL, bottom REAL, right REAL, top REAL, wkt TEXT)";
SLib.ExecuteNonQuery(mSql);
}
SLib.ExecuteNonQuery("BEGIN");
foreach (IFeature mFeature in pLyr.DataSet.Features)
{
var mGeom = OSGeo.OGR.Geometry.CreateFromWkb(mFeature.ToBinary());
string mWkt;
mGeom.ExportToWkt(out mWkt);
var mSql = String.Format("INSERT INTO geoobjects (title, description, gtype, left, bottom, right, top, wkt) values ('{0}','{1}', '{2}', {3}, {4}, {5}, {6}, '{7}')",
mFeature.DataRow["ADDRESSUNITNR"] + ", " + mFeature.DataRow["ROADNAME_EN"],
"",
"address",
mGeom.GetX(0),
mGeom.GetY(0),
mGeom.GetX(0),
mGeom.GetY(0),
mWkt
);
SLib.ExecuteNonQuery(mSql);
}
SLib.ExecuteNonQuery("END");
SLib.Dbx.Close();
SLib.Dbx.Dispose();
if (mTransformationRequired)
{
pLyr.Reproject(pSrcProj);
}
return(mRV);
}
public static void ExportMultipleToMyAbuDhabiNet(frmMain pFrm, string[] pInputFilenames, string pOutputFilename)
{
// Overwrite first entry
bool pAppend = false;
pFrm.Log("Starting to process selected files...");
foreach (var mDbFilename in pInputFilenames)
{
var mDb = new Database(mDbFilename);
var mGroup = new MapGroup();
mGroup.LegendText = mDb.DbBaseName;
var mAddressUnitFeatures = new AddressUnitFeature(mDb);
mAddressUnitFeatures.PopulateFromTable();
pFrm.Log("Number of address unit features in source: " + mAddressUnitFeatures.NumRows());
IPointLayer mAddressUnitLayer = (IPointLayer)ExtFunctions.GetFeatureLayer(mGroup.Layers, mAddressUnitFeatures, LayerNames.AddressUnitSigns, MapSymbols.PointSymbol(SignColors.AddressUnitSign, 3), ExtFunctions.GetProjByEPSG(32640));
var mAddressUnitResult = ExtFunctions.ExportToMyAbuDhabiNet(pOutputFilename, mAddressUnitLayer, ExtFunctions.GetProjByEPSG(4326), ExtFunctions.GetProjByEPSG(4326), pAppend, SignType.addressUnitNumberSign);
pFrm.Log(mAddressUnitResult.GetMessages());
//After first file-write, set append to true
pAppend = true;
var mSnsFeatures = new StreetNameSignFeature(mDb);
mSnsFeatures.PopulateFromTable();
pFrm.Log("Number of street name sign features in source: " + mSnsFeatures.NumRows());
IPointLayer mSnsLayer = (IPointLayer)ExtFunctions.GetFeatureLayer(mGroup.Layers, mSnsFeatures, LayerNames.StreetNameSigns, MapSymbols.PointSymbol(SignColors.StreetNameSign, 3), ExtFunctions.GetProjByEPSG(32640));
var mSnsResult = ExtFunctions.ExportToMyAbuDhabiNet(pOutputFilename, mSnsLayer, ExtFunctions.GetProjByEPSG(4326), ExtFunctions.GetProjByEPSG(4326), true, SignType.streetNameSign);
pFrm.Log(mSnsResult.GetMessages());
var mAgsFeatures = new AddressGuideSignFeature(mDb);
mAgsFeatures.PopulateFromTable();
pFrm.Log("Number of address guide sign features in source: " + mAgsFeatures.NumRows());
IPointLayer mAgsLayer = (IPointLayer)ExtFunctions.GetFeatureLayer(mGroup.Layers, mAgsFeatures, LayerNames.AddressGuideSigns, MapSymbols.PointSymbol(SignColors.AddressGuideSign, 3), ExtFunctions.GetProjByEPSG(32640));
var mAgsResult = ExtFunctions.ExportToMyAbuDhabiNet(pOutputFilename, mAgsLayer, ExtFunctions.GetProjByEPSG(4326), ExtFunctions.GetProjByEPSG(4326), true, SignType.addressGuideSign);
pFrm.Log(mAgsResult.GetMessages());
pFrm.Log("Completed parsing: " + mDbFilename);
Application.DoEvents();
}
pFrm.Log("Wrote output to file: " + pOutputFilename);
}
private void exportSelectedPointLayerAsGPXToolStripMenuItem_Click(object sender, EventArgs e)
{
IPointLayer mLyr = ExtFunctions.GetSelectedAddressUnitLayer(theMap);
if (mLyr == null)
{
Utilities.LogDebug("No point layer was selected");
return;
}
var mOPFn = SelectOutputFilename(null, mLyr.LegendText + ".gpx", "GPS Exchange Format|*.gpx");
if (mOPFn == null)
{
Utilities.LogDebug("No filename specified");
return;
}
var mFieldMap = new Dictionary <string, string>();
mFieldMap.Add(ExtFunctions.TitleFieldName, "name");
mFieldMap.Add("ROADNAME_EN", "desc");
var success = ExtFunctions.ExportFeatureLayerToOGR(
pDrvNm: "GPX",
pFLyr: mLyr,
pOPFn: mOPFn,
pSrcProj: theMap.Projection,
pTgtProj: ExtFunctions.GetProjByEPSG(4326),
pHasTitle: true,
pTitleFieldNames: "ADDRESSUNITNR,ROADID",
pTitleFormat: "wpt{0}-{1}",
pDSCOpts: new List <string>()
{
"GPX_USE_EXTENSIONS=NO"
},
pFieldMap: mFieldMap,
pOnlyInFieldMap: true
);
}
public static ReturnValue ExportFeatureLayerToMySQL(string pConnStr, IPointLayer pLayer, string pSchemaName, bool pTruncateTable, int pSrcEPSG = 32640, int pTgtEPSG = 4326)
{
var mRV = new ReturnValue();
var mSrcSRS = new SpatialReference(null);
mSrcSRS.ImportFromEPSG(pSrcEPSG);
var mTgtSRS = new SpatialReference(null);
mTgtSRS.ImportFromEPSG(pTgtEPSG);
var mTransformation = Osr.CreateCoordinateTransformation(mSrcSRS, mTgtSRS);
MySQLLib.Create(pConnStr);
// Check if table exists
if (!MySQLLib.TableExists("geoobjects", pSchemaName))
{
var mSql = "delimiter $$ CREATE TABLE `geoobjects` ( `id` int(11) NOT NULL AUTO_INCREMENT, `geom` geometry NOT NULL, `left` float(10,6) NOT NULL, `bottom` float(10,6) NOT NULL, `right` float(10,6) NOT NULL, `top` float(10,6) NOT NULL, `namepart` varchar(250) DEFAULT NULL, `numberpart` int(11) DEFAULT NULL, `title` varchar(250) DEFAULT NULL, `description` text, `gtype` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`), SPATIAL KEY `idxSpatial` (`geom`), FULLTEXT KEY `idxFullText` (`title`,`description`)) ENGINE=MyISAM AUTO_INCREMENT=2585 DEFAULT CHARSET=utf8$$";
SLib.ExecuteNonQuery(mSql);
}
if (pTruncateTable)
{
var mSql = "DELETE FROM geoobjects";
MySQLLib.ExecuteNonQuery(mSql);
}
foreach (IFeature mFeature in pLayer.DataSet.Features)
{
var mGeom = OSGeo.OGR.Geometry.CreateFromWkb(mFeature.ToBinary());
if (pSrcEPSG != pTgtEPSG)
{
mGeom.Transform(mTransformation);
}
Envelope mEnvelope = new Envelope();
mGeom.GetEnvelope(mEnvelope);
string mWkt;
mGeom.ExportToWkt(out mWkt);
var mSql = String.Format("INSERT INTO `admadr`.`geoobjects` (`geom`, `left`, `bottom`, `right`, `top`, `namepart`, `numberpart`, `title`, `description`, `gtype`) VALUES (GeomFromText('{0}'),{1},{2},{3},{4},'{5}',{6}, '{7}', '{8}', '{9}')",
mWkt,
mEnvelope.MinX,
mEnvelope.MinY,
mEnvelope.MaxX,
mEnvelope.MaxY,
Utilities.NormalizeString(mFeature.DataRow["ROADNAME_EN"].ToString()),
mFeature.DataRow["ADDRESSUNITNR"], // Function to convert to number?
mFeature.DataRow["ADDRESSUNITNR"] + ", " + Utilities.NormalizeString(mFeature.DataRow["ROADNAME_EN"].ToString()),
Utilities.GetVariants(mFeature.DataRow["ROADNAME_EN"].ToString()),
"address"
);
MySQLLib.ExecuteNonQuery(mSql);
}
MySQLLib.Dbx.Close();
MySQLLib.Dbx.Dispose();
return(mRV);
}
public static ReturnValue ExportToMyAbuDhabiNet(string pSQLFilename, IPointLayer pLayer, ProjectionInfo pSrcProjection, ProjectionInfo pTgtProjection, Boolean pAppend = false, string pSignType = null)
{
NumberFormatInfo mNumFormat = new CultureInfo("en-US", false).NumberFormat;
double[] mXY = new double[2];
var mRV = new ReturnValue();
var mTgtSRS = ExtFunctions.GetSpatialReferenceByEPSG(pTgtProjection.AuthorityCode);
if (pSrcProjection != pTgtProjection)
{
pLayer.Reproject(pTgtProjection);
}
int
mFeatureCount = pLayer.DataSet.Features.Count(),
mBatchSize = 20,
mBatchCounter = 1,
mWriteCounter = 0;
var mValues = new List <String>();
var mStreamWriter = new StreamWriter(pSQLFilename, pAppend, Encoding.UTF8, 1024);
foreach (IFeature mFeature in pLayer.DataSet.Features)
{
var mGeom = OSGeo.OGR.Geometry.CreateFromWkb(mFeature.ToBinary());
Envelope mEnvelope = new Envelope();
mGeom.GetEnvelope(mEnvelope);
string mWkt;
mGeom.ExportToWkt(out mWkt);
if (pSignType == SignType.addressUnitNumberSign)
{
mValues.Add(String.Format("('{0}','{1}','{2}','{3}',{4},'{5}','{6}','{7}','{8}','{9}','{10}',{11},{12})",
mFeature.DataRow["QR_CODE"],
"ANS",
"Sign description",
SignType.addressUnitNumberSign,
mFeature.DataRow["ADDRESSUNITNR"],
"Street name description",
mFeature.DataRow["ROADNAME_EN"].ToString().MySQLEscape(),
"Street name description Arabic",
mFeature.DataRow["ROADNAME_AR"].ToString().MySQLEscape(),
mFeature.DataRow["DISTRICT_EN"].ToString().MySQLEscape(),
mFeature.DataRow["DISTRICT_AR"].ToString().MySQLEscape(),
mEnvelope.MinX.ToString(mNumFormat),
mEnvelope.MinY.ToString(mNumFormat)
));
mWriteCounter++;
}
else if (pSignType == SignType.streetNameSign)
{
mValues.Add(String.Format("('{0}','{1}','{2}','{3}',{4},'{5}','{6}','{7}','{8}','{9}','{10}',{11},{12})",
mFeature.DataRow["QR_CODE"],
"SNS",
"Sign description",
SignType.streetNameSign,
"null",
"",
"",
"",
"",
"",
"",
mEnvelope.MinX.ToString(mNumFormat),
mEnvelope.MinY.ToString(mNumFormat)
));
mWriteCounter++;
}
else if (pSignType == SignType.addressGuideSign)
{
mValues.Add(String.Format("('{0}','{1}','{2}','{3}',{4},'{5}','{6}','{7}','{8}','{9}','{10}',{11},{12})",
mFeature.DataRow["QR_CODE"],
"AGS",
"Sign description",
SignType.addressGuideSign,
"null",
"",
"",
"",
"",
"",
"",
mEnvelope.MinX.ToString(mNumFormat),
mEnvelope.MinY.ToString(mNumFormat)
));
mWriteCounter++;
}
if (mBatchCounter++ < mBatchSize)
{
mBatchCounter++;
}
else
{
var mSql = String.Format(sqlStatements.insertUpdateMyAbuDhabiNetSQL,
String.Join(",", mValues));
mBatchCounter = 1;
mValues.Clear();
mStreamWriter.WriteLine(mSql);
}
}
mRV.AddMessage("Parsed " + mWriteCounter + " features to SQL");
mStreamWriter.Flush();
mStreamWriter.Close();
return(mRV);
}
public static ReturnValue ExportToGeopaparazzi(IPointLayer pLyr, string pOPFn, ProjectionInfo pSrcProjection, ProjectionInfo pTgtProjection)
{
NumberFormatInfo mNumFormat = new CultureInfo("en-US", false).NumberFormat;
var mReturnValue = new ReturnValue();
var mTgtSRS = ExtFunctions.GetSpatialReferenceByEPSG(4326);
bool mTransformationRequired = pSrcProjection != pTgtProjection ? true : false;
if (mTransformationRequired)
{
pLyr.Reproject(pTgtProjection);
}
// Set source and target directories
var mSrcGPDir = Application.StartupPath + "\\Templates\\geopaparazzi";
var mTgtGPDir = Application.StartupPath + "\\Temp\\geopaparazzi";
// If temp directory exists, delete it
if (Directory.Exists(mTgtGPDir))
{
Directory.Delete(mTgtGPDir, true);
Debug.WriteLine("Deleted directory");
}
// Copy template
Utilities.DirectoryCopy(mSrcGPDir, mTgtGPDir, true);
Debug.WriteLine("Copied directory");
var mConnStr = @"Data Source=" + mTgtGPDir + "\\geopaparazzi.db;PRAGMA journal_mode=PERSIST;";
var mSdb = new SQLiteConnection(mConnStr);
mSdb.Open();
var mCmd = new SQLiteCommand("begin", mSdb);
mCmd.ExecuteNonQuery();
foreach (IFeature mFeature in pLyr.DataSet.Features)
{
var mGeom = OSGeo.OGR.Geometry.CreateFromWkb(mFeature.ToBinary());
//mFeature.Transform(mTransformation);
mCmd.CommandText = String.Format("INSERT INTO notes (lon, lat, altim, ts, text, cat, type) values ({0},{1},0,'{2}','{3}','POI',0)",
mGeom.GetX(0).ToString(mNumFormat),
mGeom.GetY(0).ToString(mNumFormat),
DateTime.Now.ToString("yyyy-MM-dd h:m:s"),
mFeature.DataRow["ADDRESSUNITNR"] + ", " + mFeature.DataRow["ROADNAME_EN"]).Replace("Street", "").Trim();
Debug.WriteLine(mCmd.CommandText);
mCmd.ExecuteNonQuery();
}
mCmd.CommandText = "end";
mCmd.ExecuteNonQuery();
mCmd.Dispose();
mSdb.Close();
mSdb.Dispose();
var mFiles = new List <string>()
{
mTgtGPDir + "\\geopaparazzi.db",
mTgtGPDir + "\\tags.json",
mTgtGPDir + "\\media\\empty.txt"
};
var mNames = new List <string>()
{
"geopaparazzi\\geopaparazzi.db",
"geopaparazzi\\tags.json",
"geopaparazzi\\media\\empty.txt"
};
var mZipFile = ZipFile.Create(pOPFn);
mZipFile.BeginUpdate();
for (int i = 0; i < mFiles.Count(); i++)
{
mZipFile.Add(mFiles[i], mNames[i]);
}
mZipFile.CommitUpdate();
mZipFile.Close();
if (mTransformationRequired)
{
pLyr.Reproject(pSrcProjection);
}
return(mReturnValue);
}
