} // End Sub
public static void GetAndInsertBuildingPolygon()
{
string sql = @"
INSERT INTO T_ZO_Objekt_Wgs84Polygon
(
ZO_OBJ_WGS84_UID
,ZO_OBJ_WGS84_GB_UID
,ZO_OBJ_WGS84_SO_UID
,ZO_OBJ_WGS84_Sort
,ZO_OBJ_WGS84_GM_Lat
,ZO_OBJ_WGS84_GM_Lng
)
SELECT
NEWID() ZO_OBJ_WGS84_UID -- uniqueidentifier
,@gb_uid AS ZO_OBJ_WGS84_GB_UID -- uniqueidentifier
,NULL AS ZO_OBJ_WGS84_SO_UID -- uniqueidentifier
,@i ZO_OBJ_WGS84_Sort -- int
,@lat ZO_OBJ_WGS84_GM_Lat -- decimal(23,20)
,@lng ZO_OBJ_WGS84_GM_Lng -- decimal(23,20)
;
";
System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
ConnectionFactory fac = new ConnectionFactory(GetConnectionString());
using (System.Data.Common.DbConnection connection = fac.Connection)
{
bool isZH = connection.ExecuteScalar <bool>("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE (1=1) AND TABLE_TYPE= 'BASE TABLE' AND TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'T_GebaeudeIMMO' ");
bool isRe = connection.ExecuteScalar <bool>("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE (1=1) AND TABLE_TYPE= 'BASE TABLE' AND TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'T_Premises' ");
string queryFile = "GetGbOsmPolygon.sql";
if (isZH)
{
queryFile = "GetGbOsmPolygon_STZH.sql";
}
else if (isRe)
{
queryFile = "GetGbOsmPolygon_RE.sql";
throw new System.NotImplementedException("Query for RE not implemented.");
}
System.Collections.Generic.List <BuildingToGeoCode> ls = System.Linq.Enumerable.ToList(
connection.Query <BuildingToGeoCode>(queryFile, typeof(OsmPolyonFinder))
);
foreach (BuildingToGeoCode building in ls)
{
System.Threading.Thread.Sleep(4000);
GeoApis.Polygon nearestBuilding = GetNearestBuildingPolygon(building.GB_GM_Lat, building.GB_GM_Lng);
if (nearestBuilding == null)
{
continue;
}
System.Console.WriteLine(nearestBuilding);
System.Console.WriteLine(nearestBuilding.OsmId); // 218003784
GeoApis.LatLng[] msPoints = nearestBuilding.ToClockWiseLatLngPoints();
string createPolygon = CreateSqlPolygon(msPoints);
System.Console.WriteLine(sql);
//SELECT
// geography::STPolyFromText('POLYGON((7.7867531 46.9361500,7.7869622 46.9361188,7.7869515 46.9360856,7.7869952 46.9360793,7.7870059 46.9361123,7.7870300 46.9361087,7.7870312 46.9361124,7.7870944 46.9361028,7.7870933 46.9360991,7.7872340 46.9360778,7.7873147 46.9363299,7.7871740 46.9363510,7.7871728 46.9363473,7.7871099 46.9363568,7.7871110 46.9363605,7.7868341 46.9364021,7.7867531 46.9361500))', 4326)
// ,geometry::STPolyFromText('POLYGON((7.7867531 46.9361500,7.7869622 46.9361188,7.7869515 46.9360856,7.7869952 46.9360793,7.7870059 46.9361123,7.7870300 46.9361087,7.7870312 46.9361124,7.7870944 46.9361028,7.7870933 46.9360991,7.7872340 46.9360778,7.7873147 46.9363299,7.7871740 46.9363510,7.7871728 46.9363473,7.7871099 46.9363568,7.7871110 46.9363605,7.7868341 46.9364021,7.7867531 46.9361500))', 4326)
// -- Geometry is BAD for area
// ,geography::STPolyFromText('POLYGON((7.7867531 46.9361500,7.7869622 46.9361188,7.7869515 46.9360856,7.7869952 46.9360793,7.7870059 46.9361123,7.7870300 46.9361087,7.7870312 46.9361124,7.7870944 46.9361028,7.7870933 46.9360991,7.7872340 46.9360778,7.7873147 46.9363299,7.7871740 46.9363510,7.7871728 46.9363473,7.7871099 46.9363568,7.7871110 46.9363605,7.7868341 46.9364021,7.7867531 46.9361500))', 4326).STArea() AS geogArea
// ,geometry::STPolyFromText('POLYGON((7.7867531 46.9361500,7.7869622 46.9361188,7.7869515 46.9360856,7.7869952 46.9360793,7.7870059 46.9361123,7.7870300 46.9361087,7.7870312 46.9361124,7.7870944 46.9361028,7.7870933 46.9360991,7.7872340 46.9360778,7.7873147 46.9363299,7.7871740 46.9363510,7.7871728 46.9363473,7.7871099 46.9363568,7.7871110 46.9363605,7.7868341 46.9364021,7.7867531 46.9361500))', 4326).STArea() AS geomArea
//";
GeoApis.LatLng[] osmPoints = nearestBuilding.ToCounterClockWiseLatLngPoints();
string sql2 = "DELETE FROM T_ZO_Objekt_Wgs84Polygon WHERE ZO_OBJ_WGS84_GB_UID = @gb_uid; ";
connection.Execute(sql2, new { gb_uid = building.GB_UID });
for (int i = 0; i < osmPoints.Length; ++i)
{
connection.Execute(sql,
new
{
gb_uid = building.GB_UID,
i = i,
lat = osmPoints[i].lat,
lng = osmPoints[i].lng
}
);
} // Next i
} // Next building
} // End Using connection
} // End Sub GetAndInsertBuildingPolygon