PostGIS 函数
1.ST_MakeEnvelope
— Creates a rectangular Polygon from minimum and maximum coordinates.
geometry ST_MakeEnvelope(float xmin, float ymin, float xmax, float ymax, integer srid=unknown);
Creates a rectangular Polygon from the minimum and maximum values for X and Y. Input values must be in the spatial reference
system specified by the SRID. If no SRID is specified the unknown spatial reference system (SRID 0) is used.
SELECT ST_AsText( ST_MakeEnvelope(10, 10, 11, 11, 4326) );
2.ST_GeneratePoints
— Converts a polygon or multi-polygon into a multi-point composed of randomly location points within the
original areas.
geometry ST_GeneratePoints( g geometry , npoints integer );
geometry ST_GeneratePoints( g geometry , npoints integer , seed integer );
ST_GeneratePoints generates pseudo-random points until the requested number are found within the input area. The optional
seed must be greater than zero, and is used to regenerate a deterministic sequence of points.
3.ST_Dump
— Returns a set of geometry_dump rows for the components of a geometry.
geometry_dump[] ST_Dump(geometry g1);
This is a set-returning function (SRF). It returns a set of geometry_dump rows, formed by a geometry (geom) and an array of
integers (path). When the input geometry is a simple type (POINT,LINESTRING,POLYGON) a single record will be returned
with an empty path array and the input geometry as geom. When the input geometry is a collection or multi it will return a record
for each of the collection components, and the path will express the position of the component inside the collection.
ST_Dump is useful for expanding geometries. It is the reverse of a GROUP BY in that it creates new rows. For example it can
be use to expand MULTIPOLYGONS into POLYGONS.
SELECT sometable.field1, sometable.field1, (ST_Dump(sometable.the_geom)).geom AS the_geom
FROM sometable;4.ST_Buffer
— (T) Returns a geometry covering all points within a given distance from the input geometry.
geometry ST_Buffer(geometry g1, float radius_of_buffer, text buffer_style_parameters=”);
geometry ST_Buffer(geometry g1, float radius_of_buffer, integer num_seg_quarter_circle);
geography ST_Buffer(geography g1, float radius_of_buffer, text buffer_style_parameters);
geography ST_Buffer(geography g1, float radius_of_buffer, integer num_seg_quarter_circle);
Returns a geometry/geography that represents all points whose distance from this Geometry/geography is less than or equal to
distance.
Geometry: Calculations are in the Spatial Reference System of the geometry. Introduced in 1.5 support for different end cap and
mitre settings to control shape.
Geography: For geography this is really a thin wrapper around the geometry implementation. It first determines the
best SRID that fits the bounding box of the geography object (favoring UTM, Lambert Azimuthal Equal Area (LAEA)
north/south pole, and falling back on mercator in worst case scenario) and then buffers in that planar spatial ref and
retransforms back to WGS84 geography
For geography this may not behave as expected if object is sufficiently large that it falls between two UTM zones or
crosses the dateline
Units of radius are measured in units of the spatial reference system.
The inputs can be POINTS, MULTIPOINTS, LINESTRINGS, MULTILINESTRINGS, POLYGONS, MULTIPOLYGONS, and
GeometryCollections.
SELECT ST_Buffer(ST_GeomFromText('POINT(100 90)'),50, 'quad_segs=8');
SELECT ST_Buffer(ST_GeomFromText( 'LINESTRING(50 50,150 150,150 50)'), 10, 'endcap=round join=round');
SELECT ST_Buffer(ST_GeomFromText( 'LINESTRING(50 50,150 150,150 50)'), 10, 'endcap=square join=round');
SELECT ST_Buffer(ST_GeomFromText( 'LINESTRING(50 50,150 150,150 50)'), 10, 'side=left');
SELECT ST_Buffer(
ST_ForceRHR(ST_Boundary(ST_GeomFromText( 'POLYGON ((50 50, 50 150, 150 150, 150 50, 50 50))'))),
), 20, 'side=left');5.ST_Transform
— Return a new geometry with its coordinates transformed to a different spatial reference system.
geometry ST_Transform(geometry g1, integer srid);
geometry ST_Transform(geometry geom, text to_proj);
geometry ST_Transform(geometry geom, text from_proj, text to_proj);
geometry ST_Transform(geometry geom, text from_proj, integer to_srid);
Returns a new geometry with its coordinates transformed to a different spatial reference system. The destination spatial reference
to_srid may be identified by a valid SRID integer parameter (i.e. it must exist in the spatial_ref_sys table). Alternatively, a spatial reference defined as a PROJ.4 string can be used for to_proj and/or from_proj, however these methods are
not optimized. If the destination spatial reference system is expressed with a PROJ.4 string instead of an SRID, the SRID of the
output geometry will be set to zero. With the exception of functions with from_proj, input geometries must have a defined
SRID.
ST_Transform is often confused with ST_SetSRID. ST_Transform actually changes the coordinates of a geometry from one
spatial reference system to another, while ST_SetSRID() simply changes the SRID identifier of the geometry.
6.ST_Intersection
— (T) Returns a geometry that represents the shared portion of geomA and geomB.
geometry ST_Intersection( geometry geomA , geometry geomB );
geography ST_Intersection( geography geogA , geography geogB );
Returns a geometry that represents the point set intersection of the Geometries.
In other words - that portion of geometry A and geometry B that is shared between the two geometries.
If the geometries do not share any space (are disjoint), then an empty geometry collection is returned.
ST_Intersection in conjunction with ST_Intersects is very useful for clipping geometries such as in bounding box, buffer, region
queries where you only want to return that portion of a geometry that sits in a country or region of interest.
SELECT ST_AsText(ST_Intersection('POINT(0 0)'::geometry, 'LINESTRING ( 2 0, 0 2 )'::geometry));
st_astext
---------------
GEOMETRYCOLLECTION EMPTY
SELECT ST_AsText(ST_Intersection('POINT(0 0)'::geometry, 'LINESTRING ( 0 0, 0 2 )'::geometry));
st_astext
---------------
POINT(0 0)
7.ST_Area
— Returns the area of a polygonal geometry.
float ST_Area(geometry g1);
float ST_Area(geography geog, boolean use_spheroid=true);
Returns the area of a polygonal geometry. For geometry types a 2D Cartesian (planar) area is computed, with units specified by
the SRID. For geography types by default area is determined on a spheroid with units in square meters. To compute the area
using the faster but less accurate spherical model use ST_Area(geog,false).
8.ST_PointOnSurface
— Returns a POINT guaranteed to lie on the surface.
geometry ST_PointOnSurface(geometry g1);
Returns a POINT guaranteed to intersect a surface.
SELECT ST_AsText(ST_PointOnSurface('POINT(0 5)'::geometry));
st_astext
------------
POINT(0 5)
(1 row)
SELECT ST_AsText(ST_PointOnSurface('LINESTRING(0 5, 0 10)'::geometry));
st_astext
------------
POINT(0 5)
(1 row)
SELECT ST_AsText(ST_PointOnSurface('POLYGON((0 0, 0 5, 5 5, 5 0, 0 0))'::geometry));
st_astext
----------------
POINT(2.5 2.5)
(1 row)9.ST_ClusterKMeans
— Window function that returns a cluster id for each input geometry using the K-means algorithm.
integer ST_ClusterKMeans(geometry winset geom, integer number_of_clusters);
Returns 2D distance based K-means cluster number for each input geometry. The distance used for clustering is the distance
between the centroids of the geometries.
CREATE TABLE nanjing_pts_clustered AS
SELECT geom, ST_ClusterKMeans(geom, 10) over () AS cluster
FROM nanjing_points;
CREATE TABLE nanjing_centers AS
SELECT cluster, ST_Centroid(ST_collect(geom)) AS geom
FROM nanjing_pts_clustered GROUP BY cluster;
SELECT ST_ClusterKMeans(geom,3) over (PARTITION BY type) AS cid, parcel_id, type
FROM parcels;
ROW_NUMBER() OVER( [ PRITITION BY col1] ORDER BY col2[ DESC ] )
ROW_NUMBER()为返回的记录定义个行编号， PARTITION BY col1 是根据col1分组，ORDER BY col2[ DESC ]是根据col2进行排序。
10.ST_Collect
— Creates a GeometryCollection or Multi* geometry from a set of geometries.
geometry ST_Collect(geometry g1, geometry g2);
geometry ST_Collect(geometry[] g1_array);
geometry ST_Collect(geometry set g1field);
Collects geometries into a geometry collection. The result is either a Multi* or a GeometryCollection, depending on whether
the input geometries have the same or different types (homogeneous or heterogeneous). The input geometries are left unchanged
within the collection.
SELECT ST_AsText( ST_Collect( ST_GeomFromText('POINT(1 2)'),
ST_GeomFromText('POINT(-2 3)') ));
11.ST_Centroid
— Returns the geometric center of a geometry
geometry ST_Centroid(geometry g1);
geography ST_Centroid(geography g1, boolean use_spheroid=true);
Computes the geometric center of a geometry, or equivalently, the center of mass of the geometry as a POINT. For [MULTI]POINTs,
this is computed as the arithmetic mean of the input coordinates. For [MULTI]LINESTRINGs, this is computed as the weighted
length of each line segment. For [MULTI]POLYGONs, "weight" is thought in terms of area. If an empty geometry is supplied, an empty GEOMETRYCOLLECTION is returned. If NULL is supplied, NULL is returned. If CIRCULARSTRING or
COMPOUNDCURVE are supplied, they are converted to linestring wtih CurveToLine first, then same than for LINESTRING
可以参考 ST_PointOnSurface, ST_GeometricMedian
12.ST_VoronoiPolygons
— Returns the cells of the Voronoi diagram constructed from the vertices of a geometry
geometry ST_VoronoiPolygons( g1 geometry , tolerance float8 , extend_to geometry );
ST_VoronoiPolygons computes a two-dimensional Voronoi diagram from the vertices of the supplied geometry. The result is
a GeometryCollection of Polygons that covers an envelope larger than the extent of the input vertices. Returns null if input
geometry is null. Returns an empty geometry collection if the input geometry contains only one vertex. Returns an empty
geometry collection if the extend_to envelope has zero area.
13.ST_Intersection
— (T) Returns a geometry that represents the shared portion of geomA and geomB.
geometry ST_Intersection( geometry geomA , geometry geomB );
geography ST_Intersection( geography geogA , geography geogB );
Returns a geometry that represents the point set intersection of the Geometries.
In other words - that portion of geometry A and geometry B that is shared between the two geometries.
If the geometries do not share any space (are disjoint), then an empty geometry collection is returned.
ST_Intersection in conjunction with ST_Intersects is very useful for clipping geometries such as in bounding box, buffer, region
queries where you only want to return that portion of a geometry that sits in a country or region of interest.
CREATE TABLE nanjing_divided AS
SELECT ST_Intersection(a.geom, b.geom) AS geom
FROM nanjing a
CROSS JOIN nanjing_voronoi b;
14.ST_ExteriorRing
— Returns a LineString representing the exterior ring of a Polygon.
geometry ST_ExteriorRing(geometry a_polygon);
Returns a line string representing the exterior ring of the POLYGON geometry. Return NULL if the geometry is not a polygon.
--If you have a table of polygons
SELECT gid, ST_ExteriorRing(the_geom) AS ering
FROM sometable;
--If you have a table of MULTIPOLYGONs
--and want to return a MULTILINESTRING composed of the exterior rings of each polygon
SELECT gid, ST_Collect(ST_ExteriorRing(the_geom)) AS erings
FROM (SELECT gid, (ST_Dump(the_geom)).geom As the_geom
FROM sometable) As foo
GROUP BY gid;
Create table boundaries as
Select st_union(st_exteriorRing(geom)) as geom from circles; 15.ST_Union
— Returns a geometry that represents the point set union of the Geometries.
geometry ST_Union(geometry set g1field);
geometry ST_Union(geometry g1, geometry g2);
geometry ST_Union(geometry[] g1_array);
SELECT stusps,
ST_Union(f.geom) as singlegeom
FROM sometable f
GROUP BY stusps
16.ST_Polygonize
— Aggregate. Creates a GeometryCollection containing possible polygons formed from the constituent linework
of a set of geometries.
geometry ST_Polygonize(geometry set geomfield);
geometry ST_Polygonize(geometry[] geom_array);
Creates a GeometryCollection containing possible polygons formed from the constituent linework of a set of geometries.
17.ST_Contains
返回点位所在的行政辖区
SELECT id, geom, code, name
FROM gzwg t
where ST_Contains (t.geom,ST_GeomFromText('POINT(113.678425 23.796591666)',4326));