{"id":228,"date":"2012-11-04T19:48:08","date_gmt":"2012-11-04T17:48:08","guid":{"rendered":"http:\/\/www.flip-design.de\/?p=228"},"modified":"2012-11-17T23:16:41","modified_gmt":"2012-11-17T21:16:41","slug":"langen-und-breitengrade-in-den-geography-datentyp-umwandeln","status":"publish","type":"post","link":"https:\/\/www.flip-design.de\/?p=228","title":{"rendered":"L\u00e4ngen- und Breitengrade in den Geography Datentyp umwandeln"},"content":{"rendered":"<p><a href=\"http:\/\/www.flip-design.de\/wp-content\/uploads\/2012\/11\/Weltkugel.gif\"><img decoding=\"async\" loading=\"lazy\" class=\"alignleft size-thumbnail wp-image-229\" title=\"Weltkugel\" src=\"http:\/\/www.flip-design.de\/wp-content\/uploads\/2012\/11\/Weltkugel-150x150.gif\" alt=\"\" width=\"84\" height=\"84\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2012\/11\/Weltkugel-150x150.gif 150w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2012\/11\/Weltkugel-297x300.gif 297w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2012\/11\/Weltkugel.gif 335w\" sizes=\"(max-width: 84px) 100vw, 84px\" \/><\/a>H\u00e4ufig verf\u00fcgen Datenbanken mit Adressen \u00fcber L\u00e4ngen- und Breitengrade. Um diese bspw. in einem Report darstellen zu k\u00f6nnen, ist es notwendig, dass diese in den Geography Datentyp, bspw. als Punkt umgewandelt werden. U.a. k\u00f6nnen diese dann super in einen Report mit dem MapControl eingef\u00fcgt werden (ab 2008R2). Aus Performance Gr\u00fcnden empfehle ich, die Daten nicht bei jeder Abfrage dann umwandeln zu lassen, sondern entsprechend neben den Geo-Informationen zu speichern.<\/p>\n<p>Bspw. k\u00f6nnen die L\u00e4ngen- und Breitengrade so ausgewertet werden:<\/p>\n<pre escaped=\"true\" lang=\"tsql\">-- Tabelle mit Geo- Informationen\r\nCREATE TABLE dbo.Orte (\r\nid int not null primary key identity(1,1),\r\nort varchar(255) not null,\r\nbreitengrad decimal(10,8),\r\nlaengengrad decimal(10,8));\r\nGO\r\n\r\nINSERT INTO dbo.Orte (ort, laengengrad, breitengrad)\r\nVALUES\r\n('Koblenz', '7.589907', '50.360025'),\r\n('Hamburg', '9.9936818', '53.5510846'),\r\n('Berlin', '13.4060912', '52.519171'),\r\n('M\u00fcnchen', '11.5770851', '48.1366069');<\/pre>\n<p><strong>Abfrage mit Umwandlung:<\/strong><\/p>\n<pre escaped=\"true\" lang=\"tsql\">SELECT\r\nort\r\n, GEOGRAPHY::STPointFromText('POINT(' + CONVERT(VARCHAR, laengengrad) + ' ' + CONVERT(VARCHAR, breitengrad) + ')',4326).STBuffer(20000)\r\nFROM dbo.Orte<\/pre>\n<p><strong>Ergebnis:<\/strong><br \/>\n<a href=\"http:\/\/www.flip-design.de\/wp-content\/uploads\/2012\/11\/geo.png\"><img decoding=\"async\" loading=\"lazy\" class=\"alignleft size-full wp-image-230\" title=\"geo\" src=\"http:\/\/www.flip-design.de\/wp-content\/uploads\/2012\/11\/geo.png\" alt=\"\" width=\"1118\" height=\"450\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2012\/11\/geo.png 1118w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2012\/11\/geo-300x120.png 300w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2012\/11\/geo-1024x412.png 1024w\" sizes=\"(max-width: 1118px) 100vw, 1118px\" \/><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>H\u00e4ufig verf\u00fcgen Datenbanken mit Adressen \u00fcber L\u00e4ngen- und Breitengrade. Um diese bspw. in einem Report darstellen zu k\u00f6nnen, ist es notwendig, dass diese in den Geography Datentyp, bspw. als Punkt umgewandelt werden. U.a. k\u00f6nnen diese dann super in einen Report &hellip; <a href=\"https:\/\/www.flip-design.de\/?p=228\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[25,3],"tags":[],"_links":{"self":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/228"}],"collection":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=228"}],"version-history":[{"count":3,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/228\/revisions"}],"predecessor-version":[{"id":240,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/228\/revisions\/240"}],"wp:attachment":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=228"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=228"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=228"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}