STV_Intersect Scalar Function
Spatially intersects a point or points with a set of polygons. The STV_Intersect scalar function returns the identifier associated with an intersecting polygon.
Behavior Type
Syntax
STV_Intersect( { g | x , y } USING PARAMETERS index= 'index_name')
Arguments
g |
A geometry or geography (WGS84) column that contains points. The g column can contain only point geometries or geographies. If the column contains a different geometry or geography type, STV_Intersect terminates with an error. |
x |
x-coordinate or longitude, FLOAT. |
y |
y-coordinate or latitude, FLOAT. |
Parameters
index = 'index_name' |
Name of the spatial index, of type VARCHAR. |
Returns
The identifier of a matching polygon. If the point does not intersect any of the index's polygons, then the STV_Intersect scalar function returns NULL.
Examples
The following examples show how you can use STV_Intersect scalar.
Using two floats, return the gid of a matching polygon or NULL:
=> CREATE TABLE pols (gid INT, geom GEOMETRY(1000)); CREATE TABLE => COPY pols(gid, gx filler LONG VARCHAR, geom AS ST_GeomFromText(gx)) FROM STDIN; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. >> 1|POLYGON((31 74,8 70,8 50,36 53,31 74)) >> \. => SELECT STV_Create_Index(gid, geom USING PARAMETERS index='my_polygons_1', overwrite=true, max_mem_mb=256) OVER() FROM pols; type | polygons | SRID | min_x | min_y | max_x | max_y | info ----------+----------+------+-------+-------+-------+-------+------ GEOMETRY | 1 | 0 | 8 | 50 | 36 | 74 | (1 row) => SELECT STV_Intersect(12.5683, 55.6761 USING PARAMETERS index = 'my_polygons_1'); STV_Intersect --------------- 1 (1 row)
Using a GEOMETRY column, return the gid of a matching polygon or NULL:
=> CREATE TABLE polygons (gid INT, geom GEOMETRY(700)); CREATE TABLE => COPY polygons (gid, gx filler LONG VARCHAR, geom AS ST_GeomFromText(gx)) FROM stdin delimiter '|'; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. >> 1|POLYGON((-31 74,8 70,8 50,-36 53,-31 74)) >> 2|POLYGON((-38 50,4 13,11 45,0 65,-38 50)) >> 3|POLYGON((-18 42,-10 65,27 48,14 26,-18 42)) >> \. => SELECT STV_Create_Index(gid, geom USING PARAMETERS index='my_polygons', overwrite=true, max_mem_mb=256) OVER() FROM polygons; type | polygons | SRID | min_x | min_y | max_x | max_y | info ----------+----------+------+-------+-------+-------+-------+------ GEOMETRY | 3 | 0 | -38 | 13 | 27 | 74 | (1 row)
=> CREATE TABLE points (gid INT, geom GEOMETRY(700)); CREATE TABLE => COPY points (gid, gx filler LONG VARCHAR, geom AS ST_GeomFromText(gx)) FROM stdin delimiter '|'; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. >> 100|POINT(-1 52) >> 101|POINT(-20 0) >> 102|POINT(-8 25) >> 103|POINT(0 0) >> 104|POINT(1 5) >> 105|POINT(20 45) >> 106|POINT(-20 5) >> 107|POINT(-20 1) >> \. => SELECT gid AS pt_gid, STV_Intersect(geom USING PARAMETERS index='my_polygons') AS pol_gid FROM points ORDER BY pt_gid; pt_gid | pol_gid --------+--------- 100 | 1 101 | 102 | 2 103 | 104 | 105 | 3 106 | 107 | (8 rows)