Bienvenue sur PostGIS.fr

Bienvenue sur PostGIS.fr , le site de la communauté des utilisateurs francophones de PostGIS.

PostGIS ajoute le support d'objets géographique à la base de données PostgreSQL. En effet, PostGIS "spatialise" le serverur PostgreSQL, ce qui permet de l'utiliser comme une base de données SIG.

Maintenu à jour, en fonction de nos disponibilités et des diverses sorties des outils que nous testons, nous vous proposons l'ensemble de nos travaux publiés en langue française.

source: trunk/workshop-foss4g/geometry_returning.rst @ 10

Revision 1, 8.3 KB checked in by djay, 13 years ago (diff)

Initial import of the svn tree

Section 18: Geometry Constructing Functions

All the functions we have seen so far work with geometries "as they are" and returns

"Geometry constructing functions" take geometries as inputs and output new shapes.

ST_Centroid / ST_PointOnSurface

A common need when composing a spatial query is to replace a polygon feature with a point representation of the feature. This is useful for spatial joins (as discussed in :ref:`polypolyjoins`) because using :command:`ST_Intersects(geometry,geometry)` on two polygon layers often results in double-counting: a polygon on a boundary will intersect an object on both sides; replacing it with a point forces it to be on one side or the other, not both.

System Message: ERROR/3 (<string>, line 19); backlink

Unknown interpreted text role "ref".

System Message: ERROR/3 (<string>, line 19); backlink

Unknown interpreted text role "command".
  • :command:`ST_Centroid(geometry)` returns a point that is approximately on the center of mass of the input argument. This simple calculation is very fast, but sometimes not desirable, because the returned point is not necessarily in the feature itself. If the input feature has a convexity (imagine the letter 'C') the returned centroid might not be in the interior of the feature.

    System Message: ERROR/3 (<string>, line 21); backlink

    Unknown interpreted text role "command".

  • :command:`ST_PointOnSurface(geometry)` returns a point that is guaranteed to be inside the input argument. It is substantially more computationally expensive than the centroid operation.

    System Message: ERROR/3 (<string>, line 22); backlink

    Unknown interpreted text role "command".

./geometry_returning/centroid.jpg

ST_Buffer

The buffering operation is common in GIS workflows, and is also available in PostGIS. :command:`ST_Buffer(geometry,distance)` takes in a buffer distance and geometry type and outputs a polygon with a boundary the buffer distance away from the input geometry.

System Message: ERROR/3 (<string>, line 30); backlink

Unknown interpreted text role "command".
./geometry_returning/st_buffer.png

For example, if the US Park Service wanted to enforce a marine traffic zone around Liberty Island, they might build a 500 meter buffer polygon around the island. Liberty Island is a single census block in our nyc_census_blocks table, so we can easily extract and buffer it.

-- Make a new table with a Liberty Island 500m buffer zone
CREATE TABLE libery_island_zone AS
SELECT ST_Buffer(the_geom,500) AS the_geom
FROM nyc_census_blocks
WHERE blkid = '360610001009000';
-- Update the geometry_columns table
SELECT Populate_Geometry_Columns();
./geometry_returning/liberty_positive.jpg

The :command:`ST_Buffer` function also accepts negative distances and builds inscribed polygons within polygonal inputs. For lines and points you will just get an empty return.

System Message: ERROR/3 (<string>, line 49); backlink

Unknown interpreted text role "command".
./geometry_returning/liberty_negative.jpg

ST_Intersection

Another classic GIS operation -- the "overlay" -- creates a new coverage by calculating the intersection of two superimposed polygons. The resultant has the property that any polygon in either of the parents can be built by merging polygons in the resultant.

The :command:`ST_Intersection(geometry A, geometry B)` function returns the spatial area (or line, or point) that both arguments have in common. If the arguments are disjoint, the function returns an empty geometry.

System Message: ERROR/3 (<string>, line 59); backlink

Unknown interpreted text role "command".
-- What is the area these two circles have in common?
-- Using ST_Buffer to make the circles!

SELECT ST_AsText(ST_Intersection(
  ST_Buffer('POINT(0 0)', 2),
  ST_Buffer('POINT(3 0)', 2)
));
./geometry_returning/intersection.jpg

ST_Union

In the previous example we intersected geometries, creating a new geometry that had lines from both the inputs. The :command:`ST_Union` does the reverse; it takes inputs and removes common lines. There are two forms of the :command:`ST_Union` function:

System Message: ERROR/3 (<string>, line 78); backlink

Unknown interpreted text role "command".

System Message: ERROR/3 (<string>, line 78); backlink

Unknown interpreted text role "command".
  • :command:`ST_Union(geometry, geometry)`: A two-argument version that takes in two geometries and returns the merged union. For example, our two-circle example from the previous section looks like this when you replace the intersection with a union.

    System Message: ERROR/3 (<string>, line 80); backlink

    Unknown interpreted text role "command".

    -- What is the total area these two circles cover?
    -- Using ST_Buffer to make the circles!
    
    SELECT ST_AsText(ST_Union(
      ST_Buffer('POINT(0 0)', 2),
      ST_Buffer('POINT(3 0)', 2)
    ));
    
    ./geometry_returning/union.jpg
  • :command:`ST_Union([geometry])`: An aggregate version that takes in a set of geometries and returns the merged geometry for the entire group. The aggregate ST_Union can be used with the GROUP BY SQL statement to create carefully merged subsets of basic geometries. It is very powerful,

    System Message: ERROR/3 (<string>, line 95); backlink

    Unknown interpreted text role "command".

As an example of :command:`ST_Union` aggregation, consider our nyc_census_blocks table. Census geography is carefully constructed so that larger geographies can be built up from smaller ones. So, we can create a census tracts map by merging the blocks that form each tract (as we do later in :ref:`creatingtractstable`). Or, we can create a county map by merging blocks that fall within each county.

System Message: ERROR/3 (<string>, line 97); backlink

Unknown interpreted text role "command".

System Message: ERROR/3 (<string>, line 97); backlink

Unknown interpreted text role "ref".

To carry out the merge, note that the unique key blkid actually embeds information about the higher level geographies. Here are the parts of the key for Liberty Island we used earlier:

360610001009000 = 36 061 00100 9000
36     = State of New York
061    = New York County (Manhattan)
000100 = Census Tract
9      = Census Block Group
000    = Census Block

So, we can create a county map by merging all geometries that share the same first 5 digits of their blkid.

-- Create a nyc_census_counties table by merging census blocks
CREATE TABLE nyc_census_counties AS
SELECT
  ST_Union(the_geom) AS the_geom,
  SubStr(blkid,1,5) AS countyid
FROM nyc_census_blocks
GROUP BY countyid;
-- Update the geometry_columns table
SELECT Populate_Geometry_Columns();
./geometry_returning/union_counties.png

An area test can confirm that our union operation did not lose any geometry. First, we calculate the area of each individual census block, and sum those areas grouping by census county id.

SELECT SubStr(blkid,1,5) AS countyid, Sum(ST_Area(the_geom)) AS area
FROM nyc_census_blocks
GROUP BY countyid;
 countyid |       area
----------+------------------
 36005    | 109807439.720947
 36047    | 184906575.839355
 36061    | 58973521.6225586
 36081    | 283764734.207275
 36085    | 149806077.958252

Then we calculate the area of each of our new county polygons from the county table:

SELECT countyid, ST_Area(the_geom) AS area
FROM nyc_census_counties;
 countyid |       area
----------+------------------
 36005    | 109807439.720947
 36047    | 184906575.839355
 36061    | 58973521.6225586
 36081    | 283764734.207275
 36085    | 149806077.958252

The same answer! We have successfully built an NYC county table from our census blocks data.

Function List

ST_AsText(text): Returns the Well-Known Text (WKT) representation of the geometry/geography without SRID metadata.

ST_Buffer(geometry, distance): For geometry: Returns a geometry that represents all points whose distance from this Geometry is less than or equal to distance. Calculations are in the Spatial Reference System of this Geometry. For geography: Uses a planar transform wrapper.

ST_Intersection(geometry A, geometry B): Returns a geometry that represents the shared portion of geomA and geomB. The geography implementation does a transform to geometry to do the intersection and then transform back to WGS84.

ST_Union(): Returns a geometry that represents the point set union of the Geometries.

substring(string [from int] [for int]): PostgreSQL string function to extract substring matching SQL regular expression.

sum(expression): PostgreSQL aggregate function that returns the sum of records in a set of records.

Note: See TracBrowser for help on using the repository browser.