public static Boolean CleanFileGDBNames(frmMain pFrm, string pTgtFileGDB, string pSrcPGeoMDB, string pTgtFeatClass)
{
try
{
// Connect to database
var mOdbcConn = new OdbcConnection("Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=" + pSrcPGeoMDB + ";Uid=Admin;Pwd=;");
// Load road names
pFrm.Log("Start loading names");
var mRoadNames = new Dictionary <int, DataRow>();
var mDataAdapter = new OdbcDataAdapter(sqlStatements.selectRoadNamesSQL, mOdbcConn);
var mDataTable = new DataTable();
mDataAdapter.Fill(mDataTable);
int ctr1 = 0;
int total1 = mDataTable.Rows.Count;
foreach (DataRow mRow in mDataTable.Rows)
{
int mRoadId;
if (int.TryParse(mRow["ADRROADID"].ToString(), out mRoadId))
{
mRoadNames.Add(mRoadId, mRow);
}
ctr1++;
if (ctr1 % 50 == 0)
{
Application.DoEvents();
}
}
pFrm.Log("Done loading road names");
mOdbcConn.Close();
mDataAdapter.Dispose();
mDataAdapter = null;
mOdbcConn.Dispose();
mOdbcConn = null;
// Load district names
Gdal.SetConfigOption("SHAPE_ENCODING", "UTF-8");
DataSource districtDataSource = Ogr.Open(DistrictImport.GetShapefileName(), 0);
OSGeo.OGR.Layer districtLayer = districtDataSource.GetLayerByIndex(0);
if (districtLayer == null)
{
pFrm.Log("Could not load district names layer");
goto CleanUpDistricts;
}
pFrm.Log("Done loading district names");
var mFileGDBDrv = OSGeo.OGR.Ogr.GetDriverByName("FileGDB");
var mTgtDSrc = mFileGDBDrv.Open(pTgtFileGDB, 1);
var mTgtLyr = mTgtDSrc.GetLayerByName(pTgtFeatClass);
if (mTgtLyr == null)
{
pFrm.Log("Could not open layer " + pTgtFeatClass + " for update");
goto CleanUp;
}
pFrm.Log("Opened target FileGDB for update");
Feature mFeat = null;
int mNumTgtFeats = mTgtLyr.GetFeatureCount(1);
// Clean all existing district and street names
pFrm.Log("Processing");
for (int idxFeature = 0; idxFeature < mNumTgtFeats; idxFeature++)
{
if (null != (mFeat = mTgtLyr.GetFeature(idxFeature)))
{
Geometry mGeometry = mFeat.GetGeometryRef();
double[] p = new double[2];
mGeometry.GetPoint(0, p);
if (pTgtFeatClass == "Address_unit_signs")
{
int mRoadID = mFeat.GetFieldAsInteger("ROADID");
mFeat.SetField("ROADNAME_EN", mRoadNames.GetStringOrNull(mRoadID, "NAMEENGLISH"));
mFeat.SetField("ROADNAME_AR", mRoadNames.GetStringOrNull(mRoadID, "NAMEARABIC"));
mFeat.SetField("ROADNAME_POP_EN", null);
mFeat.SetField("ROADNAME_POP_AR", null);
mFeat.SetField("DESCRIPTION_EN", mRoadNames.GetStringOrNull(mRoadID, "DESCRIPTIONENGLISH"));
mFeat.SetField("DESCRIPTION_AR", mRoadNames.GetStringOrNull(mRoadID, "DESCRIPTIONARABIC"));
}
else if (pTgtFeatClass == "Street_name_signs")
{
int mRoadID1 = mFeat.GetFieldAsInteger("ROADID_P1");
int mRoadID2 = mFeat.GetFieldAsInteger("ROADID_P2");
mFeat.SetField("ROADNAME_EN_P1", mRoadNames.GetStringOrNull(mRoadID1, "NAMEENGLISH"));
mFeat.SetField("ROADNAME_AR_P1", mRoadNames.GetStringOrNull(mRoadID1, "NAMEARABIC"));
mFeat.SetField("ROADNAME_EN_P2", mRoadNames.GetStringOrNull(mRoadID2, "NAMEENGLISH"));
mFeat.SetField("ROADNAME_AR_P2", mRoadNames.GetStringOrNull(mRoadID2, "NAMEARABIC"));
}
else if (pTgtFeatClass == "Address_guide_sign")
{
int mRoadID = mFeat.GetFieldAsInteger("ROADID");
string tmpRoadEn = mRoadNames.GetStringOrNull(mRoadID, "NAMEENGLISH");
string tmpRoadAr = mRoadNames.GetStringOrNull(mRoadID, "NAMEARABIC");
mFeat.SetField("ROADNAME_EN", tmpRoadEn);
mFeat.SetField("ROADNAME_AR", tmpRoadAr);
}
districtLayer.SetSpatialFilter(mGeometry);
using (Feature matchDistrict = districtLayer.GetFeature(0))
{
if (matchDistrict != null &&
matchDistrict.IsFieldSet("NAMELATIN") &&
matchDistrict.IsFieldSet("NAMEARABIC"))
{
string tmpDistEn = matchDistrict.GetFieldAsString("NAMELATIN");
string tmpDistAr = matchDistrict.GetFieldAsString("NAMEARABIC");
mFeat.SetField("DISTRICT_EN", tmpDistEn);
mFeat.SetField("DISTRICT_AR", tmpDistAr);
}
else
{
mFeat.SetField("DISTRICT_EN", null);
mFeat.SetField("DISTRICT_AR", null);
pFrm.Log("No district name match for " + pTgtFeatClass + " with coordinates: " + p[0].ToString() + ", " + p[1].ToString());
}
}
Application.DoEvents();
mTgtLyr.SetFeature(mFeat);
DoEventsOnBatchSize:
if (idxFeature % 250 == 0 || idxFeature == mNumTgtFeats)
{
pFrm.Log("Processed " + idxFeature + " out of" + mNumTgtFeats + " features");
//Thread.Sleep(250);
mTgtLyr.SyncToDisk();
}
if (mGeometry != null)
{
mGeometry.Dispose();
}
if (mFeat != null)
{
mFeat.Dispose();
}
}
}
CleanUp:
if (mTgtLyr != null)
{
mTgtLyr.Dispose();
}
if (mTgtDSrc != null)
{
mTgtDSrc.Dispose();
}
if (mFileGDBDrv != null)
{
mFileGDBDrv.Dispose();
}
CleanUpDistricts:
if (districtLayer != null)
{
districtLayer.Dispose();
}
if (districtDataSource != null)
{
districtDataSource.Dispose();
}
}
catch (Exception ex)
{
pFrm.Log(ex.Message);
pFrm.Log(ex.StackTrace);
return(false);
}
return(true);
}
private DistrictEntity AddOrCreate(DistrictImport import)
{
var result = _districtService.GetItem(x => x.Code == import.Code);
return(result ?? _districtService.Add(import.Code, import.Name));
}
/// <summary>
///
/// </summary>
/// <param name="pFrm"></param>
/// <param name="pSrcFileGDB"></param>
/// <param name="pSQLFileName"></param>
/// <param name="pBatchSize"></param>
/// <param name="pAppend"></param>
/// <returns></returns>
public static ReturnValue ExportFileGDBToMyAbuDhabiDotNetSQL(frmMain pFrm, string pSrcFileGDB, string pSQLFileName, int pBatchSize = 25, bool pAppend = false)
{
var mRetVal = new ReturnValue(true);
NumberFormatInfo mNumFormat = new CultureInfo("en-US", false).NumberFormat;
// Open FileGDB
var mFileGDBDrv = OSGeo.OGR.Ogr.GetDriverByName("FileGDB");
var mSrcDSrc = mFileGDBDrv.Open(pSrcFileGDB, 1);
if (mSrcDSrc == null)
{
pFrm.Log("Could not open ESRI FGDB: " + pSrcFileGDB, true);
mRetVal.Success = false;
return(mRetVal);
}
// Create re-usable projection
var mSrcProj = ExtFunctions.GetSpatialReferenceByEPSG(32640);
var mTgtProj = ExtFunctions.GetSpatialReferenceByEPSG(4326);
var mTransformation = new OSGeo.OSR.CoordinateTransformation(mSrcProj, mTgtProj);
int mLineCount = 0;
int mFileCount = 1;
// Create file to write to
var mStreamWriter = new StreamWriter(pSQLFileName + "." + mFileCount, pAppend, Encoding.UTF8, 1024);
// Create re-usable feature object
OSGeo.OGR.Feature mFeat = null;
OSGeo.GDAL.Gdal.SetConfigOption("SHAPE_ENCODING", "UTF-8");
DataSource mDistrictsDataSource = Ogr.Open(DistrictImport.GetShapefileName(), 0);
Layer mDistricts = null;
if (mDistrictsDataSource != null)
{
mDistricts = mDistrictsDataSource.GetLayerByIndex(0);
}
var mRecords = new SignRecord();
// Process address unit signs
var mAUSLyr = mSrcDSrc.GetLayerByName("Address_unit_signs");
if (mAUSLyr == null)
{
mRetVal.AddMessage("Could not find address units layer", true);
return(mRetVal);
}
if (mAUSLyr != null)
{
pFrm.Log("Opened Address_unit_signs layer from FileGDB for reading...");
int mNumFeatures = mAUSLyr.GetFeatureCount(1);
int idxFeat = 0;
while (null != (mFeat = mAUSLyr.GetNextFeature()))
{
var mGeom = mFeat.GetGeometryRef();
Envelope mOrigEnvelope = new Envelope();
mGeom.GetEnvelope(mOrigEnvelope);
var mDistrictInfo = mDistricts.GetDistrictByPoint(mGeom, 32640);
mGeom.Transform(mTransformation);
Envelope mEnvelope = new Envelope();
mGeom.GetEnvelope(mEnvelope);
string mWkt;
mGeom.ExportToWkt(out mWkt);
mRecords.AddToBuffer(
mFeat.GetFieldAsString("QR_CODE"),
mFeat.GetFieldAsString("ADDRESSUNITNR"),
mFeat.GetFieldAsString("ROADNAME_EN").MySQLEscape(),
mFeat.GetFieldAsString("ROADNAME_AR").MySQLEscape(),
mDistrictInfo != null ? mDistrictInfo.GetFieldAsString("NAMELATIN").MySQLEscape() : "",
mDistrictInfo != null ? mDistrictInfo.GetFieldAsString("NAMEARABIC").MySQLEscape() : "",
mEnvelope.MinX.ToString(mNumFormat),
mEnvelope.MinY.ToString(mNumFormat),
mFeat.GetFieldAsString("DESCRIPTION_AR").MySQLEscape(),
mFeat.GetFieldAsString("DESCRIPTION_EN").MySQLEscape(),
"Address_unit_signs",
SignType.addressUnitNumberSign
);
idxFeat++;
if (idxFeat % pBatchSize == 0 || idxFeat == mNumFeatures)
{
var mSql = String.Format(sqlStatements.insertUpdateMyAbuDhabiNetSQL,
mRecords.GetBuffer());
mStreamWriter.WriteLine(mSql);
pFrm.pgBar.ProgressBar.Value = (idxFeat * 100) / mNumFeatures;
mLineCount++;
if (mLineCount % 500 == 0)
{
mFileCount++;
mStreamWriter.Flush();
mStreamWriter.Close();
mStreamWriter = new StreamWriter(pSQLFileName + "." + mFileCount, pAppend, Encoding.UTF8, 1024);
mLineCount = 0;
}
}
}
pFrm.Log("Done, processed " + idxFeat + " features...");
mAUSLyr.Dispose();
mAUSLyr = null;
}
// Process address unit signs
var mSNSLyr = mSrcDSrc.GetLayerByName("Street_name_signs");
if (mSNSLyr == null)
{
mRetVal.AddMessage("Could not find layer Street_name_signs", true);
return(mRetVal);
}
if (mSNSLyr != null)
{
Debug.WriteLine(mSNSLyr);
pFrm.Log("Opened Street_name_signs layer from FileGDB for reading...");
int mNumFeatures = mSNSLyr.GetFeatureCount(1);
int idxFeat = 0;
mFeat = null;
while (null != (mFeat = mSNSLyr.GetNextFeature()))
{
var mGeom = mFeat.GetGeometryRef();
Envelope mOrigEnvelope = new Envelope();
mGeom.GetEnvelope(mOrigEnvelope);
var mDistrictInfo = mDistricts.GetDistrictByPoint(mGeom, 32640);
mGeom.Transform(mTransformation);
Envelope mEnvelope = new Envelope();
mGeom.GetEnvelope(mEnvelope);
string mWkt;
mGeom.ExportToWkt(out mWkt);
mRecords.AddToBuffer(
mFeat.GetFieldAsString("QR_CODE"),
"",
mFeat.GetFieldAsString("ROADNAME_EN_P1").MySQLEscape() + "/" + mFeat.GetFieldAsString("ROADNAME_EN_P2").MySQLEscape(),
mFeat.GetFieldAsString("ROADNAME_AR_P1").MySQLEscape() + "/" + mFeat.GetFieldAsString("ROADNAME_AR_P2").MySQLEscape(),
mDistrictInfo != null ? mDistrictInfo.GetFieldAsString("NAMELATIN").MySQLEscape() : "",
mDistrictInfo != null ? mDistrictInfo.GetFieldAsString("NAMEARABIC").MySQLEscape() : "",
mEnvelope.MinX.ToString(mNumFormat),
mEnvelope.MinY.ToString(mNumFormat),
"",
"",
"Street_name_signs",
SignType.streetNameSign,
""
);
idxFeat++;
if (idxFeat % pBatchSize == 0 || idxFeat == mNumFeatures)
{
var mSql = String.Format(sqlStatements.insertUpdateMyAbuDhabiNetSQL,
mRecords.GetBuffer());
mStreamWriter.WriteLine(mSql);
Debug.WriteLine(mSql);
pFrm.pgBar.ProgressBar.Value = (idxFeat * 100) / mNumFeatures;
mLineCount++;
if (mLineCount % 500 == 0)
{
mFileCount++;
mStreamWriter.Flush();
mStreamWriter.Close();
mStreamWriter = new StreamWriter(pSQLFileName + "." + mFileCount, pAppend, Encoding.UTF8, 1024);
mLineCount = 0;
}
}
}
pFrm.Log("Done, processed " + idxFeat + " features...");
mSNSLyr.Dispose();
mSNSLyr = null;
}
// Process address guide signs
var mAGSLyr = mSrcDSrc.GetLayerByName("Address_guide_sign");
if (mAGSLyr == null)
{
mRetVal.AddMessage("Could not find layer Address_guide_sign", true);
return(mRetVal);
}
if (mAGSLyr != null)
{
pFrm.Log("Opened Address_guide_sign layer from FileGDB for reading...");
int mNumFeatures = mAGSLyr.GetFeatureCount(1);
int idxFeat = 0;
mFeat = null;
while (null != (mFeat = mAGSLyr.GetNextFeature()))
{
var mGeom = mFeat.GetGeometryRef();
Envelope mOrigEnvelope = new Envelope();
mGeom.GetEnvelope(mOrigEnvelope);
var mDistrictInfo = mDistricts.GetDistrictByPoint(mGeom, 32640);
mGeom.Transform(mTransformation);
Envelope mEnvelope = new Envelope();
mGeom.GetEnvelope(mEnvelope);
string mWkt;
mGeom.ExportToWkt(out mWkt);
mRecords.AddToBuffer(
mFeat.GetFieldAsString("QR_CODE"),
"",
mFeat.GetFieldAsString("ROADNAME_EN"),
mFeat.GetFieldAsString("ROADNAME_AR"),
mDistrictInfo != null ? mDistrictInfo.GetFieldAsString("NAMELATIN").MySQLEscape() : null,
mDistrictInfo != null ? mDistrictInfo.GetFieldAsString("NAMEARABIC").MySQLEscape() : null,
mEnvelope.MinX.ToString(mNumFormat),
mEnvelope.MinY.ToString(mNumFormat),
"",
"",
"Address_guide_sign",
SignType.addressGuideSign,
"");
idxFeat++;
if (idxFeat % pBatchSize == 0 || idxFeat == mNumFeatures)
{
var mSql = String.Format(sqlStatements.insertUpdateMyAbuDhabiNetSQL,
mRecords.GetBuffer());
mStreamWriter.WriteLine(mSql);
pFrm.pgBar.ProgressBar.Value = (idxFeat * 100) / mNumFeatures;
mLineCount++;
if (mLineCount % 500 == 0)
{
mFileCount++;
mStreamWriter.Flush();
mStreamWriter.Close();
mStreamWriter = new StreamWriter(pSQLFileName + "." + mFileCount, pAppend, Encoding.UTF8, 1024);
mLineCount = 0;
}
}
}
pFrm.Log("Done, processed " + idxFeat + " features...");
mAGSLyr.Dispose();
mAGSLyr = null;
}
mStreamWriter.Flush();
mStreamWriter.Close();
mStreamWriter.Dispose();
mSrcProj.Dispose();
mSrcProj = null;
mTgtProj.Dispose();
mTgtProj = null;
mTransformation.Dispose();
mTransformation = null;
mSrcDSrc.Dispose();
mSrcDSrc = null;
mFileGDBDrv.Dispose();
mFileGDBDrv = null;
return(mRetVal);
}
public static ReturnValue ExportDistrictsToMyAbuDhabiNet()
{
var r = new ReturnValue();
OSGeo.GDAL.Gdal.SetConfigOption("SHAPE_ENCODING", "UTF-8");
var shpDir = Path.GetFileNameWithoutExtension(DistrictImport.GetShapefileName());
var ds = Ogr.OpenShared(DistrictImport.GetShapefileName(), 0);
if (ds == null)
{
r.AddMessage("Data source doesn't exist", true);
return(r);
}
var lyr = ds.GetLayerByIndex(0);
if (lyr == null)
{
r.AddMessage("Layer doesn't destrict", true);
return(r);
}
OSGeo.OGR.Feature nf;
var srcProj = new SpatialReference(null);
var tgtProj = new SpatialReference(null);
srcProj.ImportFromEPSG(32640);
tgtProj.ImportFromEPSG(4326);
var trans = new CoordinateTransformation(srcProj, tgtProj);
string geomWkt;
Geometry geometry;
string districtname_ar;
string districtname_en;
string districtabbreviation;
StringBuilder sql = new StringBuilder();
//sql.AppendLine(sqlStatements.createDistrictTableSQL);
sql.AppendLine(sqlStatements.emptyDistrictsSQL);
while (null != (nf = lyr.GetNextFeature()))
{
geometry = nf.GetGeometryRef();
// Transformation goes here
geometry.Transform(trans);
geometry.ExportToWkt(out geomWkt);
districtabbreviation = nf.GetFieldAsString("DISTRICTAB");
districtname_ar = nf.GetFieldAsString("NAMEARABIC");
districtname_en = nf.GetFieldAsString("NAMELATIN");
sql.AppendFormat(sqlStatements.insertDistrictsSQL, districtname_ar.MySQLEscape(), districtname_en.MySQLEscape(), geomWkt, districtabbreviation);
}
var dlg = new SaveFileDialog();
dlg.FileName = "districts.sql";
dlg.Filter = "SQL files|*.sql";
dlg.Title = "Select where to save districts SQL";
if (dlg.ShowDialog() == DialogResult.OK)
{
File.WriteAllText(dlg.FileName, sql.ToString());
r.Success = true;
}
return(r);
}