{"id":15715,"date":"2022-10-27T06:00:00","date_gmt":"2022-10-26T21:00:00","guid":{"rendered":"https:\/\/www.muscle-hypertrophy.com\/?p=15715"},"modified":"2022-08-01T09:28:13","modified_gmt":"2022-08-01T00:28:13","slug":"%e7%b5%8c%e5%ba%a6%e3%83%bb%e7%b7%af%e5%ba%a6%e3%81%8b%e3%82%89point%e3%82%b8%e3%82%aa%e3%83%a1%e3%83%88%e3%83%aa%e3%82%92sql-server%e3%81%a7%e7%94%9f%e6%88%90%e3%81%99%e3%82%8b","status":"publish","type":"post","link":"https:\/\/www.muscle-hypertrophy.com\/?p=15715","title":{"rendered":"\u7d4c\u5ea6\u30fb\u7def\u5ea6\u304b\u3089Point\u30b8\u30aa\u30e1\u30c8\u30ea\u3092SQL Server\u3067\u751f\u6210\u3059\u308b"},"content":{"rendered":"\n

\u3000eStat\u304b\u3089\u56fd\u52e2\u8abf\u67fb\u306e\u5c0f\u5730\u57df\u306e\u5883\u754c\u30c7\u30fc\u30bf\u3092\u30c0\u30a6\u30f3\u30ed\u30fc\u30c9\u3057SQL Server\u306b\u30a2\u30c3\u30d7\u30ed\u30fc\u30c9\u3059\u308b<\/a>\u3067\u306f\u30dd\u30ea\u30b4\u30f3\u3092\u542b\u3080\u30c7\u30fc\u30bf\u3092 SQL Server \u306b\u30a2\u30c3\u30d7\u30ed\u30fc\u30c9\u3059\u308b\u307e\u3067\u306e\u8a18\u4e8b\u3092\u8a18\u3057\u305f\uff0e\u4eca\u56de\u306f\u305d\u306e\u30c6\u30fc\u30d6\u30eb\u5185\u306b\u8a18\u9332\u3055\u308c\u305f\u7d4c\u5ea6\uff0c\u7def\u5ea6\u304b\u3089 Point \u30b8\u30aa\u30e1\u30c8\u30ea\u3092\u751f\u6210\u3059\u308b\uff0e\u7b2c 6 \u7ae0\u3000\u7a7a\u9593\u30c7\u30fc\u30bf\u3092\u30a4\u30f3\u30dd\u30fc\u30c8\u3059\u308b (Beginning Spatial with SQL Server 2008)<\/a>\u306b\u5023\u3063\u3066\u3044\u308b\uff0e<\/p>\n

<\/p>\n

WKT\u3068\u306f<\/h2>\n

\u3000Well-Known Text<\/a> \u306e\u982d\u6587\u5b57\u3092\u53d6\u3063\u3066 WKT \u3067\u3042\u308b\uff0e\u898f\u683c\u3092\u7b56\u5b9a\u3057\u305f\u306e\u306f OGC (Open Geospatial Consortium)<\/a> \u3067\u3042\u308b\uff0eSQL Server \u306b\u306f\u3044\u304f\u3064\u304b\u306e\u30b8\u30aa\u30e1\u30c8\u30ea\u304c\u5b58\u5728\u3059\u308b\u304c\uff0c\u4ee3\u8868\u7684\u306a\u3082\u306e\u306f Point, LineString, Poygon \u306e\uff13\u3064\u3067\u3042\u308b\uff0e<\/p>\n

Point<\/h3>\n

\u3000Point<\/a> \u306f\u305f\u30601\u70b9\u304b\u3089\u306a\u308b\u30b8\u30aa\u30e1\u30c8\u30ea\u3067\u3042\u308b\uff0eWKT \u3067\u306f () \u5185\u306e\u5f15\u6570\u306f X \u5ea7\u6a19\uff08\u7d4c\u5ea6\uff09\uff0cY \u5ea7\u6a19\uff08\u7def\u5ea6\uff09\u306e\u9806\u306b\u8a18\u8ff0\u3057\uff0cX \u5ea7\u6a19\u3068 Y \u5ea7\u6a19\u306f\u534a\u89d2\u30b9\u30da\u30fc\u30b9\u3067\u63a5\u7d9a\u3059\u308b\uff0e\u5177\u4f53\u7684\u306b\u306f\u4ee5\u4e0b\u306e\u3088\u3046\u306b\u8a18\u8ff0\u3059\u308b\uff0e<\/p>\n

Point(Lon Lat)<\/pre>\n
LineString<\/h3>\n
\u3000LineString<\/a> \u306f2\u3064\u4ee5\u4e0a\u306e Point \u304b\u3089\u306a\u308b\u30b8\u30aa\u30e1\u30c8\u30ea\u3067\u3042\u308b\uff0eWKT \u3067\u306f\u534a\u89d2\u30b9\u30da\u30fc\u30b9\u3067\u63a5\u7d9a\u3057\u305f X \u5ea7\u6a19\u3068 Y \u5ea7\u6a19\u306e\u30bf\u30d7\u30eb\u3092\u30ab\u30f3\u30de\u3067\u63a5\u7d9a\u3059\u308b\uff0e\u5177\u4f53\u7684\u306b\u306f\u4ee5\u4e0b\u306e\u3088\u3046\u306b\u8a18\u8ff0\u3059\u308b\uff0e<\/p>\n
LineString(Lon Lat, Lon Lat, ...)<\/pre>\n
Polygon<\/h3>\n
\u3000Polygon<\/a> \u306f3\u3064\u4ee5\u4e0a\u306e Point \u304b\u3089\u306a\u308b\u30b8\u30aa\u30e1\u30c8\u30ea\u3067\u3042\u308b\uff0eWKT \u3067\u306f LineString \u540c\u69d8\uff0c\u534a\u89d2\u30b9\u30da\u30fc\u30b9\u3067\u63a5\u7d9a\u3057\u305f X \u5ea7\u6a19\u3068 Y \u5ea7\u6a19\u306e\u30bf\u30d7\u30eb\u3092\u30ab\u30f3\u30de\u3067\u63a5\u7d9a\u3059\u308b\uff0e\u5177\u4f53\u7684\u306b\u306f\u4ee5\u4e0b\u306e\u3088\u3046\u306b\u8a18\u8ff0\u3059\u308b\uff0e<\/p>\n
Polygon((Lon lat, Lon Lat, Lon Lat, ...))<\/pre>\n
geography::STPointFromText \u30e1\u30bd\u30c3\u30c9\u3067 WKT \u304b\u3089 Point \u30b8\u30aa\u30e1\u30c8\u30ea\u3092\u751f\u6210\u3059\u308b<\/h2>\n
\u3000\u516c\u5f0f\u306e\u8aac\u660e\u306f STPointFromText (geography \u30c7\u30fc\u30bf\u578b)<\/a>\u306b\u3042\u308b\uff0e\u5f15\u6570\u306b WKT \u3068 SRID<\/a> \u3092\u6e21\u3059\u3068 Point \u30b8\u30aa\u30e1\u30c8\u30ea\u3092\u751f\u6210\u3059\u308b\u30e1\u30bd\u30c3\u30c9\u3067\u3042\u308b\uff0e\u7d4c\u5ea6\u306f X_CODE, \u7def\u5ea6\u306f Y_CODE \u3067\u3042\u308b\uff0eSRID \u306f STSrid \u30e1\u30bd\u30c3\u30c9<\/a>\u304b\u3089 4612 \u3067\u3042\u308b\u3068\u5206\u304b\u308b\u305f\u3081\uff0c\u305d\u308c\u306b\u5023\u3046\uff0e<\/p>\n
\u3000\u30e1\u30bd\u30c3\u30c9\u306e\u69cb\u9020\u304b\u3089\u63a8\u6e2c\u3067\u304d\u308b\u3088\u3046\u306b\uff0cWKT \u304b\u3089 LineString \u3092\u751f\u6210\u3059\u308b STLineFromText<\/a> \u30e1\u30bd\u30c3\u30c9\uff0cWKT \u304b\u3089 Polygon \u3092\u751f\u6210\u3059\u308b STPolyFromText<\/a> \u30e1\u30bd\u30c3\u30c9\u3082\u5b58\u5728\u3059\u308b\uff0e<\/p>\n
USE ZIPCODEDB;\nGO\nSELECT\tZ.X_CODE\n,\tZ.Y_CODE\n,\tZ.geom.STSrid\tAS 'SRID'\n,\t'Point('+ CAST(Z.X_CODE AS nvarchar(255)) + ' '+ CAST(Z.Y_CODE AS nvarchar(255)) + ')'\tAS WKT\n,\tgeography::STPointFromText('Point('+ CAST(Z.X_CODE AS nvarchar(255)) + ' '+ CAST(Z.Y_CODE AS nvarchar(255)) + ')', 4612)\tAS 'Location'\nFROM\tdbo.T_Small_Geographic_Area\tAS Z<\/pre>\n
\u30c6\u30fc\u30d6\u30eb\u69cb\u9020\u306e\u5909\u66f4<\/h2>\n
\u3000\u30c6\u30fc\u30d6\u30eb\u306b Point \u30b8\u30aa\u30e1\u30c8\u30ea\u3092\u683c\u7d0d\u3059\u308b\u305f\u3081\u306e\u5217\u3092\u8ffd\u52a0\u3057\uff0cWKT \u304b\u3089\u751f\u6210\u3057\u305f Point \u30b8\u30aa\u30e1\u30c8\u30ea\u3092\u5b9f\u969b\u306b\u683c\u7d0d\u3059\u308b\uff0e<\/p>\n
\u30c6\u30fc\u30d6\u30eb\u306b geography \u578b\u306e Location \u5217\u3092\u8ffd\u52a0\u3059\u308b<\/h3>\n
\u3000\u4e0b\u8a18\u306e\u30af\u30a8\u30ea\u3092\u5b9f\u884c\u3057\u3066\u30c6\u30fc\u30d6\u30eb\u306b geography \u578b\u306e\u5217 Location \u3092\u8ffd\u52a0\u3059\u308b\uff0e<\/p>\n
USE ZIPCODEDB;\nGO\nALTER TABLE T_Small_Geographic_Area\nADD Location geography\nGO<\/pre>\n
Location \u5217 \u306b Point \u3092 SET \u3059\u308b<\/h3>\n
\u3000\u4e0b\u8a18\u306e\u30af\u30a8\u30ea\u3092\u5b9f\u884c\u3057\u3066 Location \u5217\u306b Point \u3092 SET \u3059\u308b\uff0e<\/p>\n
USE ZIPCODEDB;\nGO\nUPDATE T_Small_Geographic_Area\nSET Location = geography::STPointFromText('Point('+ CAST(T_Small_Geographic_Area.X_CODE AS nvarchar(255)) + ' '+ CAST(T_Small_Geographic_Area.Y_CODE AS nvarchar(255)) + ')', 4612)<\/pre>\n
\u3000\u6210\u529f\u3059\u308b\u3068\u4e0b\u8a18\u306e\u7d50\u679c\u304c\u8868\u793a\u3055\u308c\u308b\uff0e<\/p>\n
(232019 \u884c\u51e6\u7406\u3055\u308c\u307e\u3057\u305f)<\/pre>\n
\u7d50\u679c<\/h2>\n
\u3000\u4e0b\u8a18\u306e\u30af\u30a8\u30ea\u3092\u5b9f\u884c\u3059\u308b\uff0e<\/p>\n
USE ZIPCODEDB;\nGO\nSELECT\tTOP 10 Location\nFROM\tdbo.T_Small_Geographic_Area;<\/pre>\n
\u3000\u7d50\u679c\u306f\u4e0b\u8a18\u306e\u3068\u304a\u308a\u3067\u3042\u308b\uff0e<\/p>\n
Location\n0x04120000010C910F7A36AB864540D9CEF753E3A96140\n0x04120000010C91ED7C3F35864540355EBA490CAA6140\n0x04120000010CBF7D1D386784454004560E2DB2A96140\n0x04120000010CCBA145B6F38545401283C0CAA1A96140\n0x04120000010CBC7493180486454021B0726891A96140\n0x04120000010C925CFE43FA854540FED478E926A96140\n0x04120000010C90A0F831E6864540F6285C8FC2A96140\n0x04120000010C18265305A3824540AAF1D24D62A86140\n0x04120000010CC74B378941884540BC74931804AA6140\n0x04120000010CE3A59BC420884540448B6CE7FBA96140<\/pre>\n
\u307e\u3068\u3081<\/h2>\n
\u30002020\u5e74\u306e\u56fd\u52e2\u8abf\u67fb\u306e\u5c0f\u5730\u57df\u306e\u5883\u754c\u30c7\u30fc\u30bf\u306e\u30c6\u30fc\u30d6\u30eb\u306b\u3042\u308b\u7d4c\u5ea6\u30fb\u7def\u5ea6\u304b\u3089Point\u30b8\u30aa\u30e1\u30c8\u30ea\u3092\u751f\u6210\u3057\u305f\uff0e\u5b9a\u7fa9\u66f8\u306b\u3088\u308b\u3068 X_CODE, Y_CODE \u306f\u56f3\u5f62\u4e2d\u5fc3\u306e X \u5ea7\u6a19\u304a\u3088\u3073 Y \u5ea7\u6a19\u3067\u3042\u308b\uff0e<\/p>\n","protected":false},"excerpt":{"rendered":"
\u3000eStat\u304b\u3089\u56fd\u52e2\u8abf\u67fb\u306e\u5c0f\u5730\u57df\u306e\u5883\u754c\u30c7\u30fc\u30bf\u3092\u30c0\u30a6\u30f3\u30ed\u30fc\u30c9\u3057SQL Server\u306b\u30a2\u30c3\u30d7\u30ed\u30fc\u30c9\u3059\u308b\u3067\u306f\u30dd\u30ea\u30b4\u30f3\u3092\u542b\u3080\u30c7\u30fc\u30bf\u3092 SQL Server \u306b\u30a2\u30c3\u30d7\u30ed\u30fc\u30c9\u3059\u308b\u307e\u3067\u306e\u8a18\u4e8b\u3092\u8a18\u3057\u305f\uff0e\u4eca\u56de\u306f\u305d\u306e\u30c6\u30fc\u30d6\u30eb\u5185\u306b\u8a18\u9332\u3055\u308c\u305f\u7d4c … <\/p>\n
“\u7d4c\u5ea6\u30fb\u7def\u5ea6\u304b\u3089Point\u30b8\u30aa\u30e1\u30c8\u30ea\u3092SQL Server\u3067\u751f\u6210\u3059\u308b” \u306e<\/span>\u7d9a\u304d\u3092\u8aad\u3080<\/a><\/p>\n","protected":false},"author":1,"featured_media":15391,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[4527,7554,7594,5564,4673,4964,7587,12050,7155,7605,7560,7065,4569,12051,12046,4672,4657,2740,7448,12056,12047,12055,7899,5415,6392,7154,9508,12053,7464,12048,7311,12049,7312,1801,2861,12054,2783,9582,7617,2684,462,12045,665,12057,12058,12059,4443,10952,7792,12052,7181,4016,4017,2834],"class_list":{"0":"post-15715","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","6":"hentry","7":"category-technology","8":"tag-add","9":"tag-alter-table","10":"tag-cast","11":"tag-estat","12":"tag-from","13":"tag-geography","15":"tag-geom","16":"tag-go","17":"tag-linestring","18":"tag-lon","19":"tag-nvarchar","20":"tag-point","22":"tag-poygon","23":"tag-select","24":"tag-set","25":"tag-sql-server","26":"tag-srid","27":"tag-stlinefromtext","28":"tag-stpointfromtext-2","29":"tag-stpolyfromtext","30":"tag-stsrid","31":"tag-top","32":"tag-update","33":"tag-use","34":"tag-well-known-text","35":"tag-well-known-text-2","36":"tag-wkt","37":"tag-x_code","38":"tag-x","39":"tag-y_code","40":"tag-y","41":"tag-1801","42":"tag-2861","43":"tag-12054","44":"tag-2783","45":"tag-9582","46":"tag-7617","47":"tag-2684","48":"tag-462","49":"tag-12045","50":"tag-665","51":"tag-12057","54":"tag-4443","55":"tag-10952","56":"tag-7792","57":"tag-12052","58":"tag-7181","59":"tag-4016","60":"tag-4017","61":"tag-2834"},"_links":{"self":[{"href":"https:\/\/www.muscle-hypertrophy.com\/index.php?rest_route=\/wp\/v2\/posts\/15715","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.muscle-hypertrophy.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.muscle-hypertrophy.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.muscle-hypertrophy.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.muscle-hypertrophy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=15715"}],"version-history":[{"count":26,"href":"https:\/\/www.muscle-hypertrophy.com\/index.php?rest_route=\/wp\/v2\/posts\/15715\/revisions"}],"predecessor-version":[{"id":15751,"href":"https:\/\/www.muscle-hypertrophy.com\/index.php?rest_route=\/wp\/v2\/posts\/15715\/revisions\/15751"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.muscle-hypertrophy.com\/index.php?rest_route=\/wp\/v2\/media\/15391"}],"wp:attachment":[{"href":"https:\/\/www.muscle-hypertrophy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=15715"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.muscle-hypertrophy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=15715"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.muscle-hypertrophy.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=15715"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}