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 |