SQL Serverで領域に含まれるフィーチャーを取得する
ArcGIS ServerのREST APIでデータを取得するのが面倒だからArcGIS Serverを介さずにデータを取得できないか試してみたらできました．
例えばフィーチャーを格納するテーブルがあって
CREATE TABLE SAMPLE_FEATURE(SHAPE geometry NULL);
こんな感じにデータが格納されているとします．
INSERT INTO SAMPLE_FEATURE(SHAPE) VALUES(geometry::STPointFromText('POINT(133 33)', 4326));
INSERT INTO SAMPLE_FEATURE(SHAPE) VALUES(geometry::STPointFromText('POINT(133 34)', 4326));
INSERT INTO SAMPLE_FEATURE(SHAPE) VALUES(geometry::STPointFromText('POINT(134 33)', 4326));
INSERT INTO SAMPLE_FEATURE(SHAPE) VALUES(geometry::STPointFromText('POINT(134 34)', 4326));
INSERT INTO SAMPLE_FEATURE(SHAPE) VALUES(geometry::STPointFromText('POINT(135 34)', 4326));
INSERT INTO SAMPLE_FEATURE(SHAPE) VALUES(geometry::STPointFromText('POINT(135 35)', 4326));
INSERT INTO SAMPLE_FEATURE(SHAPE) VALUES(geometry::STPointFromText('POINT(135 36)', 4326));
INSERT INTO SAMPLE_FEATURE(SHAPE) VALUES(geometry::STPointFromText('POINT(136 35)', 4326));
INSERT INTO SAMPLE_FEATURE(SHAPE) VALUES(geometry::STPointFromText('POINT(136 36)', 4326));
INSERT INTO SAMPLE_FEATURE(SHAPE) VALUES(geometry::STPointFromText('POINT(136 37)', 4326));
INSERT INTO SAMPLE_FEATURE(SHAPE) VALUES(geometry::STPointFromText('POINT(137 36)', 4326));
INSERT INTO SAMPLE_FEATURE(SHAPE) VALUES(geometry::STPointFromText('POINT(137 37)', 4326));
全件取得する場合はこんな感じです．
SELECT SHAPE.STX AS X, SHAPE.STY AS Y FROM SAMPLE_FEATURE;
| X | Y |
|-----|----|
| 133 | 33 |
| 133 | 34 |
| 134 | 33 |
| 134 | 34 |
| 135 | 34 |
| 135 | 35 |
| 135 | 36 |
| 136 | 35 |
| 136 | 36 |
| 136 | 37 |
| 137 | 36 |
| 137 | 37 |
ある領域に含まれるフィーチャーのみを抽出したい場合は，領域をあらわすポリゴンと交差するかを判定します．
DECLARE @extent_wkt nvarchar(1000) = 'POLYGON ((134 36,136 36,136 34,134 34,134 36))'
DECLARE @polygon geometry = geometry::STGeomFromText(@extent_wkt, 4326)
SELECT SHAPE.STX AS X, SHAPE.STY AS Y FROM SAMPLE_FEATURE WHERE SHAPE.STIntersects(@polygon) = 1;
| X | Y |
|-----|----|
| 134 | 34 |
| 135 | 34 |
| 135 | 35 |
| 135 | 36 |
| 136 | 35 |
| 136 | 36 |
Author And Source
この問題について(SQL Serverで領域に含まれるフィーチャーを取得する), 我々は、より多くの情報をここで見つけました https://qiita.com/maitake9116/items/dacda48da2724ab54398著者帰属：元の著者の情報は、元のURLに含まれています。著作権は原作者に属する。
Content is automatically searched and collected through network algorithms . If there is a violation . Please contact us . We will adjust (correct author information ,or delete content ) as soon as possible .