定義
ST_Intersection は、2 つのジオメトリ オブジェクトを入力として、インターセクト セットを二次元のジオメトリ オブジェクトとして返します。
構文
Oracle および PostgreSQL
sde.st_intersection (geometry1 sde.st_geometry, geometry2 sde.st_geometry)
SQLite
st_intersection (geometry1 geometryblob, geometry2 geometryblob)
戻り値のタイプ
Oracle および PostgreSQL
ST_Geometry
SQLite
Geometryblob
例
消防部長は、有害廃棄物汚染の可能性がある半径内に入る病院、学校、看護施設の領域を取得する必要があります。
病院、学校、看護施設は、次の CREATE TABLE ステートメントで作成される population テーブルに格納されます。ポリゴンとして定義された shape 列は、各保護区域の外周を格納します。
有害区域は、次の CREATE TABLE ステートメントで作成される waste_sites テーブルに格納されます。ポイントとして定義された site 列は、各有害区域の地理的な中心位置を格納します。
ST_Buffer 関数は、有害廃棄物区域の周囲にバッファーを作成します。ST_Intersection 関数は、バッファーされた有害廃棄物区域と保護区域のインターセクトからポリゴンを作成します。
Oracle
CREATE TABLE population (
id integer,
shape sde.st_geometry
);
CREATE TABLE waste_sites (
id integer,
site sde.st_geometry
);
INSERT INTO population VALUES (
1,
sde.st_geometry ('polygon ((.20 .30, .30 .30, .30 .40, .20 .40, .20 .30))', 4326)
);
INSERT INTO population VALUES (
2,
sde.st_geometry ('polygon ((.30 .30, .30 .50, .50 .50, .50 .30, .30 .30))', 4326)
);
INSERT INTO population VALUES (
3,
sde.st_geometry ('polygon ((.40 .40, .40 .60, .60 .60, .60 .40, .40 .40))', 4326)
);
INSERT INTO waste_sites VALUES (
40,
sde.st_geometry ('point (.60 .60)', 4326)
);
INSERT INTO waste_sites VALUES (
50,
sde.st_geometry ('point (.30 .30)', 4326)
);
SELECT sa.id, sde.st_astext (sde.st_intersection (sde.st_buffer (hs.site, .1), sa.shape)) Intersection
FROM population sa, waste_sites hs
WHERE hs.id = 50
AND sde.st_astext (sde.st_intersection (sde.st_buffer (hs.site, .01), sa.shape))
NOT LIKE '%EMPTY%';
ID INTERSECTION
1 POLYGON (( 0.29000000 0.30000000, 0.30000000 0.30000000, 0.30000000
0.31000000, 0.29934597 0.30997859, 0.29869474 0.30991445, 0.29804910 0.30980785,
0.29741181 0.30965926, 0.29678561 0.30946930, 0.29617317 0.30923880, 0.29557711
0.30896873, 0.29500000 0.30866025, 0.29444430 0.30831470, 0.29391239 0.30793353
, 0.29340654 0.30751840, 0.29292893 0.30707107, 0.29248160 0.30659346, 0.2920664
7 0.30608761, 0.29168530 0.30555570, 0.29133975 0.30500000, 0.29103127 0.3044228
9, 0.29076121 0.30382683, 0.29053070 0.30321440, 0.29034074 0.30258819, 0.290192
15 0.30195090, 0.29008555 0.30130526, 0.29002141 0.30065403, 0.29000000 0.300000
00))
2 POLYGON (( 0.30000000 0.30000000, 0.31000000 0.30000000, 0.30997859
0.30065403, 0.30991445 0.30130526, 0.30980785 0.30195090, 0.30965926 0.30258819,
0.30946930 0.30321440, 0.30923880 0.30382683, 0.30896873 0.30442289, 0.30866025
0.30500000, 0.30831470 0.30555570, 0.30793353 0.30608761, 0.30751840 0.30659346
, 0.30707107 0.30707107, 0.30659346 0.30751840, 0.30608761 0.30793353, 0.3055557
0 0.30831470, 0.30500000 0.30866025, 0.30442289 0.30896873, 0.30382683 0.3092388
0, 0.30321440 0.30946930, 0.30258819 0.30965926, 0.30195090 0.30980785, 0.301305
26 0.30991445, 0.30065403 0.30997859, 0.30000000 0.31000000, 0.30000000 0.300000
00))
PostgreSQL
CREATE TABLE population (
id serial,
shape sde.st_geometry
);
CREATE TABLE waste_sites (
id serial,
site sde.st_geometry
);
INSERT INTO population (shape) VALUES (
sde.st_geometry ('polygon ((.20 .30, .30 .30, .30 .40, .20 .40, .20 .30))', 4326)
);
INSERT INTO population (shape) VALUES (
sde.st_geometry ('polygon ((.30 .30, .30 .50, .50 .50, .50 .30, .30 .30))', 4326)
);
INSERT INTO population (shape) VALUES (
sde.st_geometry ('polygon ((.40 .40, .40 .60, .60 .60, .60 .40, .40 .40))', 4326)
);
INSERT INTO waste_sites (site) VALUES (
sde.st_geometry ('point (.60 .60)', 4326)
);
INSERT INTO waste_sites (site) VALUES (
sde.st_geometry ('point (.30 .30)', 4326)
);
--Replace hs.id with ID value of second record in waste_sites table if not 2.
SELECT sa.id, sde.st_astext (sde.st_intersection (sde.st_buffer (hs.site, .01), sa.shape))
AS Intersection
FROM population sa, waste_sites hs
WHERE hs.id = 2
AND sde.st_astext (sde.st_intersection (sde.st_buffer (hs.site, .1), sa.shape))::varchar
NOT LIKE '%EMPTY%';
id intersection
1 POLYGON (( 0.29000000 0.30000000, 0.30000000 0.30000000, 0.30000000
0.31000000, 0.29934597 0.30997859, 0.29869474 0.30991445, 0.29804910 0.30980785,
0.29741181 0.30965926, 0.29678561 0.30946930, 0.29617317 0.30923880, 0.29557711
0.30896873, 0.29500000 0.30866025, 0.29444430 0.30831470, 0.29391239 0.30793353
, 0.29340654 0.30751840, 0.29292893 0.30707107, 0.29248160 0.30659346, 0.2920664
7 0.30608761, 0.29168530 0.30555570, 0.29133975 0.30500000, 0.29103127 0.3044228
9, 0.29076121 0.30382683, 0.29053070 0.30321440, 0.29034074 0.30258819, 0.290192
15 0.30195090, 0.29008555 0.30130526, 0.29002141 0.30065403, 0.29000000 0.300000
00))
2 POLYGON (( 0.30000000 0.30000000, 0.31000000 0.30000000, 0.30997859
0.30065403, 0.30991445 0.30130526, 0.30980785 0.30195090, 0.30965926 0.30258819,
0.30946930 0.30321440, 0.30923880 0.30382683, 0.30896873 0.30442289, 0.30866025
0.30500000, 0.30831470 0.30555570, 0.30793353 0.30608761, 0.30751840 0.30659346
, 0.30707107 0.30707107, 0.30659346 0.30751840, 0.30608761 0.30793353, 0.3055557
0 0.30831470, 0.30500000 0.30866025, 0.30442289 0.30896873, 0.30382683 0.3092388
0, 0.30321440 0.30946930, 0.30258819 0.30965926, 0.30195090 0.30980785, 0.301305
26 0.30991445, 0.30065403 0.30997859, 0.30000000 0.31000000, 0.30000000 0.300000
00))
SQLite
CREATE TABLE population (
id integer primary key autoincrement not null
);
SELECT AddGeometryColumn (
NULL,
'population',
'shape',
4326,
'polygon',
'xy',
'null'
);
CREATE TABLE waste_sites (
id integer primary key autoincrement not null
);
SELECT AddGeometryColumn (
NULL,
'waste_sites',
'site',
4326,
'point',
'xy',
'null'
);
INSERT INTO population (shape) VALUES (
st_geometry ('polygon ((.20 .30, .30 .30, .30 .40, .20 .40, .20 .30))', 4326)
);
INSERT INTO population (shape) VALUES (
st_geometry ('polygon ((.30 .30, .30 .50, .50 .50, .50 .30, .30 .30))', 4326)
);
INSERT INTO population (shape) VALUES (
st_geometry ('polygon ((.40 .40, .40 .60, .60 .60, .60 .40, .40 .40))', 4326)
);
INSERT INTO waste_sites (site) VALUES (
st_geometry ('point (.60 .60)', 4326)
);
INSERT INTO waste_sites (site) VALUES (
st_geometry ('point (.30 .30)', 4326)
);
--Replace hs.id with ID value of second record in waste_sites table if not 2.
SELECT sa.id, st_astext (st_intersection (st_buffer (hs.site, .01), sa.shape))
AS "Intersection"
FROM population sa, waste_sites hs
WHERE hs.id = 2
AND st_astext (st_intersection (st_buffer (hs.site, .1), sa.shape))
NOT LIKE '%EMPTY%';
id Intersection
1 POLYGON (( 0.29000000 0.30000000, 0.30000000 0.30000000, 0.30000000
0.31000000, 0.29934597 0.30997859, 0.29869474 0.30991445, 0.29804910 0.30980785,
0.29741181 0.30965926, 0.29678561 0.30946930, 0.29617317 0.30923880, 0.29557711
0.30896873, 0.29500000 0.30866025, 0.29444430 0.30831470, 0.29391239 0.30793353
, 0.29340654 0.30751840, 0.29292893 0.30707107, 0.29248160 0.30659346, 0.2920664
7 0.30608761, 0.29168530 0.30555570, 0.29133975 0.30500000, 0.29103127 0.3044228
9, 0.29076121 0.30382683, 0.29053070 0.30321440, 0.29034074 0.30258819, 0.290192
15 0.30195090, 0.29008555 0.30130526, 0.29002141 0.30065403, 0.29000000 0.300000
00))
2 POLYGON (( 0.30000000 0.30000000, 0.31000000 0.30000000, 0.30997859
0.30065403, 0.30991445 0.30130526, 0.30980785 0.30195090, 0.30965926 0.30258819,
0.30946930 0.30321440, 0.30923880 0.30382683, 0.30896873 0.30442289, 0.30866025
0.30500000, 0.30831470 0.30555570, 0.30793353 0.30608761, 0.30751840 0.30659346
, 0.30707107 0.30707107, 0.30659346 0.30751840, 0.30608761 0.30793353, 0.3055557
0 0.30831470, 0.30500000 0.30866025, 0.30442289 0.30896873, 0.30382683 0.3092388
0, 0.30321440 0.30946930, 0.30258819 0.30965926, 0.30195090 0.30980785, 0.301305
26 0.30991445, 0.30065403 0.30997859, 0.30000000 0.31000000, 0.30000000 0.300000
00))