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 bool TableExists(string pTableName)
{
var mSql = String.Format("SELECT name FROM sqlite_master WHERE type='table' AND name='{0}';",
pTableName);
var mReader = SLib.ExecuteReader(mSql);
if (mReader.HasRows)
{
return(true);
}
else
{
return(false);
}
}
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 int GetLastInsertId()
{
return((int)SLib.ExecuteScalar("SELECT last_insert_rowid();"));
}
public static object ExecuteScalar(string pSQL)
{
var mCmd = SLib.Cmd(pSQL);
return(mCmd.ExecuteScalar());
}
public static SQLiteDataReader ExecuteReader(string pSQL)
{
var mCmd = SLib.Cmd(pSQL);
return(mCmd.ExecuteReader());
}
public static int ExecuteNonQuery(string pSQL)
{
var mCmd = SLib.Cmd(pSQL);
return(mCmd.ExecuteNonQuery());
}
