Definition
ST_Intersection takes two geometry objects and returns the intersection set as a two-dimensional geometry object.
Syntax
Oracle and PostgreSQL
sde.st_intersection (geometry1 sde.st_geometry, geometry2 sde.st_geometry)
SQLite
st_intersection (geometry1 geometryblob, geometry2 geometryblob)
Return type
Oracle and PostgreSQL
ST_Geometry
SQLite
Geometryblob
Example
The fire marshal must obtain the areas of the hospitals, schools, and nursing homes intersected by the radius of a possible hazardous waste contamination.
The hospitals, schools, and nursing homes are stored in the population table created with the CREATE TABLE statement that follows. The shape column, defined as a polygon, stores the outline of each of the sensitive areas.
The hazardous sites are stored in the waste_sites table created with the CREATE TABLE statement that follows. The site column, defined as a point, stores a location that is the geographic center of each hazardous site.
The ST_Buffer function generates a buffer surrounding the hazardous waste sites. The ST_Intersection function generates polygons from the intersection of the buffered hazardous waste sites and the sensitive areas.
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))