} // 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
public static GeoApis.Polygon GetNearestBuildingPolygon(decimal latitide, decimal longitude)
{
OpenToolkit.Mathematics.DecimalVector2 geoPoint = new OpenToolkit.Mathematics.DecimalVector2(latitide, longitude);
GeoApis.LatLngBounds bounds = GeoApis.LatLngBounds.FromPoint(
new GeoApis.LatLng(latitide, longitude)
, 1000
); // this is, radius = 500m
decimal area = bounds.BoundsArea;
if (area > 0.25m)
{
System.Console.WriteLine("The maximum bbox size is 0.25, and your request was too large.\nEither request a smaller area, or use planet.osm.");
return(null);
} // End if (area > 0.25m)
string xml = null;
#if fromFile
xml = System.IO.File.ReadAllText(@"D:\Stefan.Steiger\Desktop\map.osm.xml", System.Text.Encoding.UTF8);
#else
const string OSM_API_VERSION = "0.6";
// string url = "https://www.openstreetmap.org/api/0.6/map?bbox=8.626273870468141,47.69679769756054,8.636573553085329,47.700530864557194&no_cache=1562588642802";
string url = "https://www.openstreetmap.org/api/" + OSM_API_VERSION + "/map?bbox=" + bounds.ToBBoxString();
string[] proxyList = ProxyHelper.GetProxyArray();
string proxy = null;
REPEAT_UNTIL_SUCCESS:
proxy = proxyList[s_rnd.Next(0, proxyList.Length)];
// proxy = "139.162.38.191:80";
// proxy = "178.128.51.105";
// proxy = "105.27.237.27:80";
proxy = "161.117.251.194:80";
proxy = "42.3.51.114:80";
proxy = "173.212.202.65:80";
proxy = "47.91.105.34:80";
try
{
using (System.Net.WebClient wc = new WebClientWithCustomTimeout(15))
{
// wc.Proxy = new System.Net.WebProxy(proxy);
xml = wc.DownloadString(url);
} // End Using wc
}
catch (System.Net.WebException webEx)
{
System.Console.WriteLine(webEx.Message);
System.Console.WriteLine(webEx.Status);
if (webEx.Status == System.Net.WebExceptionStatus.Timeout)
{
System.Console.WriteLine("Timeout ! ");
}
else if (webEx.Status == System.Net.WebExceptionStatus.ProtocolError)
{
System.Console.WriteLine("ProtocolError");
}
System.Net.HttpWebResponse response = webEx.Response as System.Net.HttpWebResponse;
if (response != null)
{
System.Console.WriteLine("HTTP Status Code: " + response.StatusCode.ToString() + ", " + response.StatusDescription);
if (response.StatusCode == System.Net.HttpStatusCode.Unauthorized)
{
System.Console.WriteLine("401");
}
string responseHtml = null;
using (System.IO.Stream responseStream = response.GetResponseStream())
{
using (System.IO.TextReader tr = new System.IO.StreamReader(responseStream))
{
responseHtml = tr.ReadToEnd();
}
}
System.Console.WriteLine(responseHtml);
}
else
{
// no http status code available
}
// goto REPEAT_UNTIL_SUCCESS;
}
catch (System.Exception ex)
{
System.Console.WriteLine(ex.Message);
// goto REPEAT_UNTIL_SUCCESS;
}
#endif
System.Xml.XmlDocument doc = new System.Xml.XmlDocument();
doc.LoadXml(xml);
System.Xml.XmlNodeList nodes = doc.SelectNodes("//node");
System.Collections.Generic.Dictionary <string, GeoApis.LatLng> nodeDictionary =
new System.Collections.Generic.Dictionary <string, GeoApis.LatLng>(
System.StringComparer.InvariantCultureIgnoreCase
);
System.Collections.Generic.Dictionary <string, GeoApis.Polygon> buildingPolygonDictionary =
new System.Collections.Generic.Dictionary <string, GeoApis.Polygon>(
System.StringComparer.InvariantCultureIgnoreCase
);
if (nodes.Count > 1000)
{
System.Console.WriteLine(nodes.Count);
throw new System.InvalidOperationException("Densely populated area. Too many nodes to process");
return(null);
}
foreach (System.Xml.XmlElement node in nodes)
{
string id = node.GetAttribute("id");
string nodeLat = node.GetAttribute("lat");
string nodeLong = node.GetAttribute("lon");
decimal dlat = 0;
decimal dlong = 0;
decimal.TryParse(nodeLat, out dlat);
decimal.TryParse(nodeLong, out dlong);
nodeDictionary[id] = new GeoApis.LatLng(dlat, dlong);
} // Next node
// https://stackoverflow.com/questions/1457638/xpath-get-nodes-where-child-node-contains-an-attribute
// querySelectorAll('way tag[k="building"]')
System.Xml.XmlNodeList buildings = doc.SelectNodes("//way[tag/@k=\"building\"]");
foreach (System.Xml.XmlElement building in buildings)
{
System.Collections.Generic.List <GeoApis.LatLng> lsPolygonPoints =
new System.Collections.Generic.List <GeoApis.LatLng>();
System.Xml.XmlNodeList buildingNodes = building.SelectNodes("./nd");
foreach (System.Xml.XmlElement buildingNode in buildingNodes)
{
string reff = buildingNode.GetAttribute("ref");
lsPolygonPoints.Add(nodeDictionary[reff]);
} // Next buildingNode
GeoApis.LatLng[] polygonPoints = toCounterClockWise(lsPolygonPoints.ToArray());
string id = building.GetAttribute("id");
GeoApis.Polygon poly = new GeoApis.Polygon(polygonPoints);
poly.OsmId = id;
buildingPolygonDictionary[id] = poly;
} // Next building
// System.Console.WriteLine(buildingPolygonDictionary);
decimal?min = null;
string uid = null;
foreach (System.Collections.Generic.KeyValuePair <string, GeoApis.Polygon> kvp in buildingPolygonDictionary)
{
if (OsmPolygonHelper.IsInside(kvp.Value.Points, geoPoint))
{
uid = kvp.Key;
min = 0;
break;
}
decimal minDist = kvp.Value.GetMinimumDistance(geoPoint);
if (min.HasValue)
{
if (minDist < min.Value)
{
min = minDist;
uid = kvp.Key;
} // End if (minDist < min.Value)
}
else
{
uid = kvp.Key;
min = minDist;
}
} // Next kvp
if (uid == null || !buildingPolygonDictionary.ContainsKey(uid))
{
return(null);
}
return(buildingPolygonDictionary[uid]);
} // End Sub
