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 |