{"id":410,"date":"2008-01-24T12:03:03","date_gmt":"2008-01-24T11:03:03","guid":{"rendered":"http:\/\/soci.hu\/blog\/index.php\/2008\/01\/24\/sql-server-2008-ujdonsagok-16-terbeli-adattipusok-3\/"},"modified":"2008-01-25T10:25:42","modified_gmt":"2008-01-25T09:25:42","slug":"sql-server-2008-ujdonsagok-16-terbeli-adattipusok-3","status":"publish","type":"post","link":"https:\/\/soci.hu\/blog\/index.php\/2008\/01\/24\/sql-server-2008-ujdonsagok-16-terbeli-adattipusok-3\/","title":{"rendered":"SQL Server 2008 \u00fajdons\u00e1gok 17. – t\u00e9rbeli adatt\u00edpusok 3."},"content":{"rendered":"

Miel\u0151tt tov\u00e1bb taglaln\u00e1m a met\u00f3dusokat, egy kis kieg\u00e9sz\u00edt\u00e9s, egy kommentre v\u00e1laszolva.<\/p>\n

MI A FEN\u00c9RE J\u00d3 EZ AZ EG\u00c9SZ SPATIAL iz\u00e9?<\/p>\n

Nos, ha nem 1×1-es n\u00e9gyzetekkel dolgozunk, hanem mondjuk t\u00e9rk\u00e9p\u00e9szeti adatokkal, akkor m\u00e1ris sok \u00e9rdekes k\u00e9rd\u00e9st lehet megv\u00e1laszolni ezekkel a l\u00fcke met\u00f3dusokkal, mint p\u00e9ld\u00e1ul:
\n-Mekkora Magyarorsz\u00e1g ter\u00fclete?
\n-Melyik v\u00e1ros van legk\u00f6zelebb az orsz\u00e1g k\u00f6z\u00e9ppontj\u00e1hoz?
\n-Milyen messze van egym\u00e1st\u00f3l a k\u00e9t legt\u00e1volabbi telep\u00fcl\u00e9s?
\n-Milyen hossz\u00fa a Duna magyarorsz\u00e1gi szakasza?
\n-Melyik telep\u00fcl\u00e9s fekszik foly\u00f3parton?
\n-Mekkora a v\u00edzzel bor\u00edtott \u00e9s a sz\u00e1raz ter\u00fcletek ar\u00e1nya egy adott r\u00e9szben?
\n-Hol van ATM automata x km-es k\u00f6rzetben?
\nStb.<\/p>\n

Mivel siker\u00fclt szereznem nem t\u00fal r\u00e9szletes, de val\u00f3s adatokat Magyarorsz\u00e1gr\u00f3l \u00e9s a szomsz\u00e9dokr\u00f3l, ezekre a k\u00e9rd\u00e9sekre igyekszek v\u00e1laszt adni a k\u00e9s\u0151bbi r\u00e9szekben. \u00cdgy m\u00e1r \u00e9rthet\u0151, mi\u00e9rt kellenek ezek a met\u00f3dusok?<\/p>\n

Geometry met\u00f3dusok folytat\u00e1s, el\u0151zm\u00e9nyek itt<\/a>.<\/p>\n

H\u00e1ny dimenzi\u00f3s az alakat? STDimensions<\/strong>. A pont 0, a vonal 1, a soksz\u00f6g 2. Semmi meglepet\u00e9s, t\u00f6bb nem lehet, mert 2 dimenzi\u00f3s a koordin\u00e1ta-rendszer\u00fcnk.<\/p>\n

Halmazm\u0171veletek j\u00f6nnek.<\/p>\n

K\u00e9t alakzat metszete, STIntersection<\/strong>.<\/p>\n

\r\nDECLARE @g geometry;\r\nDECLARE @h geometry;\r\nSET @g = geometry::STGeomFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))', 0);\r\nSET @h = geometry::STGeomFromText('POLYGON((1 1, 3 1, 3 3, 1 3, 1 1))', 0);\r\nSELECT @g, 'Blue' as [Color], 0.3 as [Thickness]\r\nUNION ALL\r\nSELECT @h, 'Green' as [Color], 0.2 as [Thickness]\r\nUNION ALL\r\nSELECT @g.STIntersection(@h), 'Orange' as [Color], 0.1 as [Thickness];\r\n<\/pre>\n
\r\nPOLYGON ((0 0, 0 2, 2 2, 2 0, 0 0))      Blue   0.3\r\nPOLYGON ((1 1, 3 1, 3 3, 1 3, 1 1))      Green  0.2\r\nPOLYGON ((1 1, 2 1, 2 2, 1 2, 1 1))      Orange 0.1\r\n<\/pre>\n
stintersection.png<\/a><\/p>\n
A kis s\u00e1rga ter\u00fclet a metszet.<\/p>\n
K\u00e9t alakzat uni\u00f3ja (hogy van ez sz\u00e9pen, magyarul?): STUnion<\/strong>.<\/p>\n
\r\nDECLARE @g geometry;\r\nDECLARE @h geometry;\r\nSET @g = geometry::STGeomFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))', 0);\r\nSET @h = geometry::STGeomFromText('POLYGON((1 1, 3 1, 3 3, 1 3, 1 1))', 0);\r\nselect @g, 0.2 as [Thickness], 'Yellow' Color\r\nunion all\r\nselect @h, 0.2 as [Thickness], 'Blue' Color\r\nunion all\r\nselect @g.STUnion(@h), 0.1 as [Thickness], 'Red' Color;\r\n<\/pre>\n
\r\nPOLYGON ((0 0, 0 2, 2 2, 2 0, 0 0))      0.2                            Yellow\r\nPOLYGON ((1 1, 3 1, 3 3, 1 3, 1 1))      0.2                            Blue\r\nPOLYGON ((0 0, 2 0, 2 1, 3 1, 3 3, 1 3,  0.1                            Red\r\n<\/pre>\n
stunion.png<\/a><\/p>\n
Szimmetrikus k\u00fcl\u00f6nbs\u00e9g, STSymDifference<\/strong>. Az egyiket is kivonj\u00e1k a m\u00e1sikb\u00f3l (STDifference<\/strong>, l\u00e1ttuk m\u00e1r az el\u0151z\u0151 r\u00e9szben), majd a m\u00e1sikat is az egyikb\u0151l. Az ered\u0151 persze egy geometrikus alakzatokb\u00f3l \u00e1ll\u00f3 kollekci\u00f3 lesz. Hogy ezt tudjuk vizualiz\u00e1lni, az STGeometryN<\/strong> met\u00f3dussal beleindexel\u00fcnk mindk\u00e9t kollekci\u00f3-elembe, \u00e9s pirossal illetve naranccsal mutatom meg \u0151ket:<\/p>\n
\r\nDECLARE @g geometry;\r\nDECLARE @h geometry;\r\nSET @g = geometry::STGeomFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))', 0);\r\nSET @h = geometry::STGeomFromText('POLYGON((1 1, 3 1, 3 3, 1 3, 1 1))', 0);\r\nSELECT @g, 'Green' as [Color], 0.2 as [Thickness]\r\nUNION ALL\r\nSELECT @h, 'Yellow' as [Color], 0.2 as [Thickness]\r\nUNION ALL\r\nSELECT @g.STSymDifference(@h).STGeometryN(1), 'Red' as [Color], 0.1 as [Thickness]\r\nUNION ALL\r\nSELECT @g.STSymDifference(@h).STGeometryN(2), 'Orange' as [Color], 0.1 as [Thickness];\r\n<\/pre>\n
\r\nPOLYGON ((0 0, 0 2, 2 2, 2 0, 0 0))      Green  0.2\r\nPOLYGON ((1 1, 3 1, 3 3, 1 3, 1 1))      Yellow 0.2\r\nPOLYGON ((2 1, 3 1, 3 3, 1 3, 1 2, 2 2,  Red    0.1\r\nPOLYGON ((0 0, 2 0, 2 1, 1 1, 1 2, 0 2,  Orange 0.1\r\n<\/pre>\n
stunion.png<\/a><\/p>\n
Teljesen benne van-e az egyik alakzat a m\u00e1sikban? STWithin<\/strong>.<\/p>\n
\r\nDECLARE @g geometry;\r\nDECLARE @h geometry;\r\nSET @g = geometry::STGeomFromText('POLYGON((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 3 1, 3 3, 1 3, 1 1))', 0);\r\nSET @h = geometry::STGeomFromText('POLYGON((1.5 1.5, 2.5 1.5, 2.5 2.5, 1.5 2.5, 1.5 1.5))', 0);\r\n\r\nSELECT @g, 'Red' as [Color], 0.1 as [Thickness]\r\nUNION ALL\r\nSELECT @h, 'Blue' as [Color], 0.1 as [Thickness]\r\n<\/pre>\n
stwithin1.png<\/a><\/p>\n
Ennek a kimenete false. Tr\u00fckk\u00f6s p\u00e9ld\u00e1t raktam \u00f6ssze, egy lukas n\u00e9gyzetet, amiben van egy m\u00e1sik n\u00e9gyzet. Persze, hogy nincs benne egyik a m\u00e1sikban, nemhogy r\u00e9szben, de egy\u00e1ltal\u00e1n.<\/p>\n
Kapcsolatban \u00e1ll-e k\u00e9t alakat? STDisjoint<\/strong>. Ne keverj\u00fck a jointtal<\/a>. :)
\nAkkor Disjoint k\u00e9t alakzat, ha a metszet\u00fck \u00fcres halmaz.<\/p>\n","protected":false},"excerpt":{"rendered":"
Miel\u0151tt tov\u00e1bb taglaln\u00e1m a met\u00f3dusokat, egy kis kieg\u00e9sz\u00edt\u00e9s, egy kommentre v\u00e1laszolva. MI A FEN\u00c9RE J\u00d3 EZ AZ EG\u00c9SZ SPATIAL iz\u00e9? Nos, ha…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,4,21],"tags":[],"class_list":["post-410","post","type-post","status-publish","format-standard","hentry","category-adatbazisok","category-szakmai-elet","category-sql-server-2005"],"_links":{"self":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/410","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/comments?post=410"}],"version-history":[{"count":0,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/410\/revisions"}],"wp:attachment":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=410"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=410"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=410"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}