{"id":401,"date":"2008-01-22T17:32:40","date_gmt":"2008-01-22T16:32:40","guid":{"rendered":"http:\/\/soci.hu\/blog\/index.php\/2008\/01\/22\/sql-server-2008-ujdonsagok-15-terbeli-adattipusok-2\/"},"modified":"2008-01-25T10:25:28","modified_gmt":"2008-01-25T09:25:28","slug":"sql-server-2008-ujdonsagok-15-terbeli-adattipusok-2","status":"publish","type":"post","link":"https:\/\/soci.hu\/blog\/index.php\/2008\/01\/22\/sql-server-2008-ujdonsagok-15-terbeli-adattipusok-2\/","title":{"rendered":"SQL Server 2008 \u00fajdons\u00e1gok 16. – t\u00e9rbeli adatt\u00edpusok 2."},"content":{"rendered":"

Az el\u0151z\u0151 r\u00e9sz v\u00e9g\u00e9n volt egy k\u00e9p, ami vizualiz\u00e1lta a geometriai alakzatokat. Ez a kis program a GeoQuery 2008<\/a>, \u00e9s rendk\u00edv\u00fcl j\u00f3 szolg\u00e1latot tesz megn\u00e9zni, melyik f\u00fcggv\u00e9ny mire szolg\u00e1l.<\/p>\n

Ebben a r\u00e9szben bemutatok p\u00e1r geometry met\u00f3dust, szem\u00e9rmetlen\u00fcl ellopva a program be\u00e9p\u00edtett msdn p\u00e9ld\u00e1it (k\u00f6sz\u00f6net a szerz\u0151nek a feldolgoz\u00e1s\u00e9rt). P\u00e1rat az\u00e9rt \u00e9n is sz\u00fcltem. :)<\/p>\n

A p\u00e9ld\u00e1kban a Thickness \u00e9s a Color oszlopok csak a programnak sz\u00f3lnak, hogyan jelen\u00edtse meg az alakzatokat.<\/p>\n

Ter\u00fcletsz\u00e1m\u00edt\u00e1s, STArea<\/strong>:<\/p>\n

\r\nDECLARE @g geometry;\r\nSET @g = geometry::STGeomFromText('POLYGON((0 0, 3 0, 3 3, 0 3, 0 0),(2 2, 2 1, 1 1, 1 2, 2 2))', 0);\r\nSELECT @g, @g.STArea() as [AreaInUnits], 0.1 as [Thickness];\r\n<\/pre>\n
\r\n8\r\n<\/pre>\n
starea.png<\/a><\/p>\n
Azaz van egy 3×3-as n\u00e9gyzet\u00fcnk, amiben van egy 1×1-es luk. \u00cdgy a ter\u00fclete 8, nem meglep\u0151.<\/p>\n
Hat\u00e1rol\u00f3vonalak, STBoundary<\/strong>. Ez m\u00e1r nem teljesen trivi\u00e1lis, a BOL sem seg\u00edt egyel\u0151re, csak a szabv\u00e1ny<\/a>.<\/p>\n
“-Point and MultiPoint instances do not have a boundary.
\n-LineString and MultiLineString boundaries are formed by the start points and end points, removing those that occur an even number of times.”
\n-The boundary of a Polygon consists of a set of LinearRings that make up its exterior and interior boundaries.”<\/p>\n
\r\nDECLARE @g geometry;\r\nSET @g = geometry::STPolyFromText('POLYGON((0 0, 0 3, 3 3, 3 0, 0 0), (1 1, 1 2, 2 1, 1 1))', 10);\r\nSELECT 'Red' Color, 0.2 as [Thickness], @g\r\nunion all\r\nselect 'Yellow' Color, 0.1 as [Thickness], @g.STBoundary()\r\n<\/pre>\n
\r\nPOLYGON((0 0, 0 3, 3 3, 3 0, 0 0), (1 1, 1 2, 2 1, 1 1))\r\nMULTILINESTRING((1 1, 1 2, 2 1, 1 1), (0 0, 3 0, 0 3, 0 0))\r\n<\/pre>\n
stboundary1.png<\/a><\/p>\n
A piros az alakzat, a s\u00e1rga a hat\u00e1rol\u00f3vonala. A kett\u0151 egybeesik, csak az ir\u00e1nyuk m\u00e1s, ez a kimenetb\u0151l l\u00e1tszik, illetve az egyik soksz\u00f6g, a m\u00e1sik vonalak halmaza.<\/p>\n
Ugyanez vonalakkal:<\/p>\n
\r\nDECLARE @g geometry;\r\nSET @g = geometry::STGeomFromText('LINESTRING(0 0, 2 2, 0 2, 2 0)', 0);\r\nSELECT @g, @g.STBoundary(), 0.1 as [Thickness];\r\n<\/pre>\n
\r\nLINESTRING(0 0, 2 2, 0 2, 2 0)\r\nMULTIPOINT((2,0), (0,0))\r\n<\/pre>\n
stboundary.png<\/a><\/p>\n
L\u00e1tszik, hogy a szabv\u00e1nynak megfelel\u0151 a kimenet.<\/p>\n
STBuffer<\/strong>. Azokat a pontokat adja vissza, amelyek egy alakzatt\u00f3l a megadott t\u00e1vols\u00e1gra, vagy ann\u00e1l k\u00f6zelebb fekszenek.
\nEgyszer\u0171 vonallal:<\/p>\n
\r\nDECLARE @g geometry;\r\nSET @g = geometry::STGeomFromText('LINESTRING(0 0, 4 0)', 0);\r\nSELECT @g, @g.STBuffer(1), 0.1 as [Thickness];\r\n<\/pre>\n
\r\nMarha sok pontb\u00f3l \u00e1ll\u00f3 POLYGON.\r\n<\/pre>\n
stbuffer1.png<\/a><\/p>\n
Bonyolultabb soksz\u00f6ggel:<\/p>\n
\r\nDECLARE @g geometry;\r\nSET @g = geometry::STGeomFromText('POLYGON((0 0, 0 3, 3 3, 3 0, 0 0), (1 1, 1 2, 2 1, 1 1))', 0);\r\nSELECT @g, 0.1 as [Thickness], 'Red' as Color\r\nunion all \r\nselect @g.STBuffer(1), 0.1 as [Thickness], 'Yellow' as Color;\r\n<\/pre>\n
\r\nMarha sok pontb\u00f3l \u00e1ll\u00f3 POLYGON.\r\n<\/pre>\n
stbuffer2.png<\/a><\/p>\n
A s\u00e1rga vonal a sz\u00e1m\u00edtott Buffer.<\/p>\n
K\u00f6z\u00e9ppont sz\u00e1m\u00edt\u00e1s, STCentroid<\/strong>. Matekosok ut\u00e1nakereshetnek, hogy sz\u00e1molj\u00e1k (\u00e9rdekelne a link).<\/p>\n
\r\nDECLARE @g geometry;\r\nSET @g = geometry::STGeomFromText('POLYGON((0 0, 3 0, 3 3, 0 3, 0 0),(2 2, 2 1, 1 1, 1 2, 2 2))', 0);\r\nSELECT @g, @g.STCentroid(), 0.1 as [Thickness];\r\n<\/pre>\n
\r\nPOINT(1.5 1.5)\r\n<\/pre>\n
stcentroid1.png<\/a><\/p>\n
Ez k\u00f6nny\u0171 volt. Bonyolultabb alakzatra meg majd kisz\u00e1molja a g\u00e9p. :)<\/p>\n
Tartalmazza-e az egyik alakzat a m\u00e1sikat (teljesen), STContains<\/strong>:<\/p>\n
\r\nDECLARE @g geometry;\r\nDECLARE @h geometry;\r\nSET @g = geometry::STGeomFromText('POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))', 0);\r\nSET @h = geometry::STGeomFromText('POINT(1 1)', 0);\r\nSELECT @g, @h, @g.STContains(@h), 0.1 as [Thickness];\r\n<\/pre>\n
\r\n1\r\n<\/pre>\n
Azt mondja, benne van, meglep\u0151. K\u00e9pet most nem mell\u00e9kelek, l\u00e1ttunk m\u00e1r ilyen n\u00e9gyzetet.<\/p>\n
Konk\u00e1vb\u00f3l konvex, STConvexHull<\/strong>:<\/p>\n
\r\nDECLARE @g geometry;\r\nSET @g = geometry::STGeomFromText('POLYGON((0 0, 0 2, 1 1, 2 2, 2 0, 0 0))', 0);\r\nSELECT @g, 'Original' AS [Display], 'Blue' as [Color], 0.2 as [Thickness]\r\nUNION ALL\r\nSELECT @g.STConvexHull(), 'Hull', 'Green' as [Color], 0.1 as [Thickness];\r\n<\/pre>\n
stconvexhull1.png<\/a><\/p>\n
A k\u00e9k az eredeti, az z\u00f6ld a kiegyengetett, konvexes\u00edtett.<\/p>\n
Metszi-e egym\u00e1st k\u00e9t alakzat? STCrosses<\/strong>.<\/p>\n
\r\nDECLARE @g geometry;\r\nDECLARE @h geometry;\r\nSET @g = geometry::STGeomFromText('LINESTRING(0 2, 2 0)', 0);\r\nSET @h = geometry::STGeomFromText('LINESTRING(0 0, 2 2)', 0);\r\nSELECT @g, @h, @g.STCrosses(@h), 0.1 as [Thickness];\r\n<\/pre>\n
\r\n1\r\n<\/pre>\n
K\u00fcl\u00f6nbs\u00e9gk\u00e9pz\u00e9s, azok hat\u00e1rol\u00f3pontok \u00e1ltal beker\u00edtett ter\u00fclet az egyik alakzatb\u00f3l, amelyek nincsenek benne egy m\u00e1sikban: STDifference<\/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 'Original Polygons' as [Display], @g, @h, 'Green' as [Color],  0.2 as [Thickness]\r\nUNION ALL\r\nSELECT 'First minus second', null, @g.STDifference(@h), 'Blue' as [Color], 0.1 as [Thickness]\r\nUNION ALL\r\nSELECT 'Second minus first', null, @h.STDifference(@g), 'Orange' as [Color], 0.05 as [Thickness];\r\n<\/pre>\n
\r\nOriginal Polygons  POLYGON ((0 0, 0 2, 2 2, 2 0, 0 0))      POLYGON ((1 1, 3 1, 3 3, 1 3, 1 1))      Green  0.20\r\nFirst minus second NULL                                     POLYGON ((0 0, 2 0, 2 1, 1 1, 1 2, 0 2,  Blue   0.10\r\nSecond minus first NULL                                     POLYGON ((2 1, 3 1, 3 3, 1 3, 1 2, 2 2,  Orange 0.05\r\n\r\n<\/pre>\n
stdifference.png<\/a><\/p>\n
J\u00f3pofa. Az als\u00f3 z\u00f6ld n\u00e9gyzet az egyik alakzatunk, a fels\u0151 z\u00f6ld a m\u00e1sik. A k\u00e9k azt mutatja, ha az als\u00f3b\u00f3l kivonjuk a fels\u0151t, a s\u00e1rga, ha a fels\u0151b\u0151l az als\u00f3t.<\/p>\n
Folyt. k\u00f6v.<\/p>\n","protected":false},"excerpt":{"rendered":"
Az el\u0151z\u0151 r\u00e9sz v\u00e9g\u00e9n volt egy k\u00e9p, ami vizualiz\u00e1lta a geometriai alakzatokat. Ez a kis program a GeoQuery 2008, \u00e9s rendk\u00edv\u00fcl j\u00f3…<\/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,58],"tags":[],"class_list":["post-401","post","type-post","status-publish","format-standard","hentry","category-adatbazisok","category-szakmai-elet","category-sql-server-2008"],"_links":{"self":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/401","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=401"}],"version-history":[{"count":0,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/401\/revisions"}],"wp:attachment":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=401"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=401"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=401"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}