SQL Serverで領域に含まれるフィーチャーを取得する #SQLServer

~~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 |