[postgis-tickets] [SCM] PostGIS branch master updated. 3.2.0-471-g58a27e141
git at osgeo.org
git at osgeo.org
Thu Feb 3 09:57:27 PST 2022
This is an automated email from the git hooks/post-receive script. It was
generated because a ref change was pushed to the repository containing
the project "PostGIS".
The branch, master has been updated
via 58a27e141d9d681ecb955860a179d32a1a274a38 (commit)
from 64abce15057e9606f3d8399ea22dc31a865a52e6 (commit)
Those revisions listed above that are new to this repository have
not appeared on any other notification email; so we list those
revisions in full, below.
- Log -----------------------------------------------------------------
commit 58a27e141d9d681ecb955860a179d32a1a274a38
Author: Martin Davis <mtnclimb at gmail.com>
Date: Thu Feb 3 09:57:23 2022 -0800
Improve doc Validity section
diff --git a/doc/using_postgis_dataman.xml b/doc/using_postgis_dataman.xml
index 04bb341cb..1629f4527 100644
--- a/doc/using_postgis_dataman.xml
+++ b/doc/using_postgis_dataman.xml
@@ -874,279 +874,460 @@ SELECT ST_Distance('LINESTRING(-122.33 47.606, 0.0 51.5)'::geometry, 'POINT(-21.
</qandaset>
</sect2>
</sect1>
+<!-- ============================================================== -->
+<sect1 id="OGC_Validity">
+ <title>Geometry Validation</title>
- <sect1>
- <title>Spatial Tables</title>
-
- <sect2 id="Create_Spatial_Table">
- <title>Creating a Spatial Table</title>
+ <para>PostGIS is compliant with the Open Geospatial Consortium’s (OGC)
+ Simple Features Specification.
+ It defines the concepts of geometry being
+ <emphasis>simple</emphasis> and <emphasis>valid</emphasis>.
+ These definitions allow the Simple Features geometry model
+ to represent spatial objects in a consistent and unambiguous way
+ that supports efficient computation.
+ </para>
- <para>You can create a table to store geometry data using the
- <ulink url="https://www.postgresql.org/docs/current/sql-createtable.html">CREATE TABLE</ulink>
- SQL statement with a column of type <varname>geometry</varname>.
- The following example creates a table with a geometry column storing 2D (XY) LineStrings
- in the BC-Albers coordinate system (SRID 3005):</para>
+ <sect2 id="Simple_Geometry">
+ <title>Simple Geometry</title>
-<programlisting>CREATE TABLE roads (
- id SERIAL PRIMARY KEY,
- name VARCHAR(64),
- geom geometry(LINESTRING,3005)
- );</programlisting>
+ <para>A <emphasis>simple</emphasis>
+ geometry is one that has no anomalous geometric points, such as self
+ intersection or self tangency.
+ </para>
- <para>The <varname>geometry</varname> type supports two optional <emphasis role="bold">type modifiers</emphasis>:</para>
+ <para>A <varname>POINT</varname> is inherently <emphasis>simple</emphasis>
+ as a 0-dimensional geometry object.</para>
- <itemizedlist>
- <listitem>
- <para>the <emphasis role="bold">spatial type modifier</emphasis>
- restricts the kind of shapes and dimensions allowed in the column.
- The value can be any of the supported
- <link linkend="RefObject">geometry subtypes</link>
- (e.g. POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION, etc).
- The modifier supports coordinate dimensionality restrictions by adding suffixes: Z, M and ZM.
- For example, a modifier of 'LINESTRINGM' allows only linestrings with three dimensions, and treats the third dimension as a measure.
- Similarly, 'POINTZM' requires four dimensional (XYZM) data.
- </para>
- </listitem>
- <listitem>
- <para>the <emphasis role="bold">SRID modifier</emphasis> restricts the
- <link linkend="spatial_ref_sys">spatial reference system</link> SRID to a particular number.
- If omitted, the SRID defaults to 0.
- </para>
- </listitem>
- </itemizedlist>
+ <para><varname>MULTIPOINT</varname>s are <emphasis>simple</emphasis> if
+ no two coordinates (<varname>POINT</varname>s) are equal (have identical
+ coordinate values).</para>
- <para></para>
+ <para>A <varname>LINESTRING</varname> is <emphasis>simple</emphasis> if
+ it does not pass through the same point twice, except for the endpoints.
+ If the endpoints of a simple LineString are identical it is called <emphasis>closed</emphasis>
+ and referred to as a Linear Ring.</para>
- <para>Examples of creating tables with geometry columns:</para>
- <itemizedlist>
- <listitem>
- <para>Create a table holding any kind of geometry with the default SRID:</para>
- <para><programlisting>CREATE TABLE geoms(gid serial PRIMARY KEY, geom geometry );</programlisting></para>
- </listitem>
- <listitem>
- <para>Create a table with 2D POINT geometry with the default SRID:</para>
- <para><programlisting>CREATE TABLE pts(gid serial PRIMARY KEY, geom geometry(POINT) );</programlisting></para>
- </listitem>
- <listitem>
- <para>Create a table with 3D (XYZ) POINTs and an explicit SRID of 3005:</para>
- <para><programlisting>CREATE TABLE pts(gid serial PRIMARY KEY, geom geometry(POINTZ,3005) );</programlisting></para>
- </listitem>
- <listitem>
- <para>Create a table with 4D (XYZM) LINESTRING geometry with the default SRID:</para>
- <para><programlisting>CREATE TABLE lines(gid serial PRIMARY KEY, geom geometry(LINESTRINGZM) );</programlisting></para>
- </listitem>
- <listitem>
- <para>Create a table with 2D POLYGON geometry with the SRID 4267 (NAD 1927 long lat):</para>
- <para><programlisting>CREATE TABLE polys(gid serial PRIMARY KEY, geom geometry(POLYGON,4267) );</programlisting></para>
- </listitem>
- </itemizedlist>
+ <informaltable border="0" frame="none">
+ <tgroup cols="1">
+ <tbody>
+ <row>
+ <entry><para><emphasis>
+ <emphasis role="bold">(a)</emphasis> and
+ <emphasis role="bold">(c)</emphasis> are simple <varname>LINESTRING</varname>s.
+ <emphasis role="bold">(b)</emphasis> and <emphasis role="bold">(d)</emphasis> are not simple.
+ <emphasis role="bold">(c)</emphasis> is a closed Linear Ring.
+ </emphasis></para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ <tgroup cols="2" align="center">
+ <tbody>
+ <row>
+ <entry><para><informalfigure>
+ <mediaobject>
+ <imageobject>
+ <imagedata fileref="images/st_issimple01.png" />
+ </imageobject>
- <para>It is possible to have more than one geometry column in a table.
- This can be specified when the table is created, or a column can be added using the
- <ulink url="https://www.postgresql.org/docs/current/sql-altertable.html">ALTER TABLE</ulink>
- SQL statement.
- This example adds a column that can hold 3D LineStrings:</para>
+ <caption><para><emphasis role="bold">(a)</emphasis></para></caption>
+ </mediaobject>
+ </informalfigure></para></entry>
- <programlisting>ALTER TABLE roads ADD COLUMN geom2 geometry(LINESTRINGZ,4326);</programlisting>
- </sect2>
+ <entry><para><informalfigure>
+ <mediaobject>
+ <imageobject>
+ <imagedata fileref="images/st_issimple02.png" />
+ </imageobject>
- <sect2 id="geometry_columns">
- <title>GEOMETRY_COLUMNS View</title>
+ <caption><para><emphasis role="bold">(b)</emphasis></para></caption>
+ </mediaobject>
+ </informalfigure></para></entry>
+ </row>
- <para>The OGC <emphasis>Simple Features Specification for SQL</emphasis> defines
- the <varname>GEOMETRY_COLUMNS</varname> metadata table to describe geometry table structure.
- In PostGIS <varname>geometry_columns</varname> is a view reading from database system catalog tables.
- This ensures that the spatial metadata information is always consistent with the currently defined tables and views.
- The view structure is:</para>
+ <row>
+ <entry><para><informalfigure>
+ <mediaobject>
+ <imageobject>
+ <imagedata fileref="images/st_issimple03.png" />
+ </imageobject>
- <programlisting>\d geometry_columns</programlisting>
-<screen> View "public.geometry_columns"
- Column | Type | Modifiers
--------------------+------------------------+-----------
- f_table_catalog | character varying(256) |
- f_table_schema | character varying(256) |
- f_table_name | character varying(256) |
- f_geometry_column | character varying(256) |
- coord_dimension | integer |
- srid | integer |
- type | character varying(30) |</screen>
+ <caption><para><emphasis role="bold">(c)</emphasis></para></caption>
+ </mediaobject>
+ </informalfigure></para></entry>
- <para>The columns are:</para>
+ <entry><para><informalfigure>
+ <mediaobject>
+ <imageobject>
+ <imagedata fileref="images/st_issimple04.png" />
+ </imageobject>
- <variablelist>
- <varlistentry>
- <term>f_table_catalog, f_table_schema, f_table_name</term>
+ <caption><para><emphasis role="bold">(d)</emphasis></para></caption>
+ </mediaobject>
+ </informalfigure></para></entry>
+ </row>
+ </tbody>
+ </tgroup>
- <listitem>
- <para>The fully qualified name of the feature table containing the
- geometry column. There is no PostgreSQL analogue of "catalog" so that
- column is left blank. For "schema" the PostgreSQL schema name is
- used (<varname>public</varname> is the default).</para>
- </listitem>
- </varlistentry>
+ </informaltable>
- <varlistentry>
- <term>f_geometry_column</term>
+ <para>A <varname>MULTILINESTRING</varname> is <emphasis>simple</emphasis>
+ only if all of its elements are simple and the only intersection between
+ any two elements occurs at points that are on the
+ boundaries of both elements. </para>
- <listitem>
- <para>The name of the geometry column in the feature table.</para>
- </listitem>
- </varlistentry>
+ <informaltable border="0" frame="none">
+ <tgroup cols="1">
+ <tbody>
+ <row>
+ <entry><para><emphasis>
+ <emphasis role="bold">(e)</emphasis> and
+ <emphasis role="bold">(f)</emphasis> are simple
+ <varname>MULTILINESTRING</varname>s.
+ <emphasis role="bold">(g)</emphasis> is not simple.
+ </emphasis></para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ <tgroup cols="3" align="center">
+ <tbody>
+ <row>
+ <entry><para><informalfigure>
+ <mediaobject>
+ <imageobject>
+ <imagedata fileref="images/st_issimple05.png" />
+ </imageobject>
- <varlistentry>
- <term>coord_dimension</term>
+ <caption><para><emphasis role="bold">(e)</emphasis></para></caption>
+ </mediaobject>
+ </informalfigure></para></entry>
- <listitem>
- <para>The coordinate dimension (2, 3 or 4) of the column.</para>
- </listitem>
- </varlistentry>
+ <entry><para><informalfigure>
+ <mediaobject>
+ <imageobject>
+ <imagedata fileref="images/st_issimple06.png" />
+ </imageobject>
- <varlistentry>
- <term>srid</term>
+ <caption><para><emphasis role="bold">(f)</emphasis></para></caption>
+ </mediaobject>
+ </informalfigure></para></entry>
- <listitem>
- <para>The ID of the spatial reference system used for the
- coordinate geometry in this table. It is a foreign key reference
- to the <varname>spatial_ref_sys</varname> table
- (see <xref linkend="spatial_ref_sys_table" />).</para>
- </listitem>
- </varlistentry>
+ <entry><para><informalfigure>
+ <mediaobject>
+ <imageobject>
+ <imagedata fileref="images/st_issimple07.png" />
+ </imageobject>
- <varlistentry>
- <term>type</term>
+ <caption><para><emphasis role="bold">(g)</emphasis></para></caption>
+ </mediaobject>
+ </informalfigure></para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </informaltable>
- <listitem>
- <para>The type of the spatial object. To restrict the spatial
- column to a single type, use one of: POINT, LINESTRING, POLYGON,
- MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION or
- corresponding XYM versions POINTM, LINESTRINGM, POLYGONM,
- MULTIPOINTM, MULTILINESTRINGM, MULTIPOLYGONM, GEOMETRYCOLLECTIONM.
- For heterogeneous (mixed-type) collections, you can use "GEOMETRY"
- as the type.</para>
- </listitem>
- </varlistentry>
- </variablelist>
- </sect2>
+ <para>As described in the next section, the linear rings forming a
+ <varname>POLYGON</varname> must be simple for it to be valid.</para>
- <sect2 id="Manual_Register_Spatial_Column">
- <title>Manually Registering Geometry Columns</title>
+ <para>To test if a geometry is simple
+ use the <xref linkend="ST_IsSimple" /> function:</para>
- <para>Two of the cases where you may need this are the case of SQL Views and bulk inserts. For bulk insert case, you can correct the registration in the geometry_columns table
- by constraining the column or doing an alter table. For views, you could expose using a CAST operation.
- Note, if your column is typmod based, the creation process would register it correctly, so no need to do anything.
- Also views that have no spatial function applied to the geometry will register the same as the underlying table geometry column.</para>
+ <programlisting>
+SELECT
+ ST_IsSimple('LINESTRING(0 0, 100 100)') AS straight,
+ ST_IsSimple('LINESTRING(0 0, 100 100, 100 0, 0 100)') AS crossing;
- <programlisting>-- Lets say you have a view created like this
-CREATE VIEW public.vwmytablemercator AS
- SELECT gid, ST_Transform(geom, 3395) As geom, f_name
- FROM public.mytable;
+ straight | crossing
+----------+----------
+ t | f
+</programlisting>
--- For it to register correctly
--- You need to cast the geometry
---
-DROP VIEW public.vwmytablemercator;
-CREATE VIEW public.vwmytablemercator AS
- SELECT gid, ST_Transform(geom, 3395)::geometry(Geometry, 3395) As geom, f_name
- FROM public.mytable;
+ <para>Generally, PostGIS functions do not require geometric arguments to be simple.
+ Simplicity is primarily used as a basis for defining geometric validity.
+ It is also a requirement for some kinds of spatial data models
+ (for example, linear networks often disallow lines that cross).
+ </para>
+ </sect2>
--- If you know the geometry type for sure is a 2D POLYGON then you could do
-DROP VIEW public.vwmytablemercator;
-CREATE VIEW public.vwmytablemercator AS
- SELECT gid, ST_Transform(geom,3395)::geometry(Polygon, 3395) As geom, f_name
- FROM public.mytable;</programlisting>
- <programlisting>--Lets say you created a derivative table by doing a bulk insert
-SELECT poi.gid, poi.geom, citybounds.city_name
-INTO myschema.my_special_pois
-FROM poi INNER JOIN citybounds ON ST_Intersects(citybounds.geom, poi.geom);
+ <sect2 id="Valid_Geometry">
+ <title>Valid Geometry</title>
--- Create 2D index on new table
-CREATE INDEX idx_myschema_myspecialpois_geom_gist
- ON myschema.my_special_pois USING gist(geom);
+ <para>Geometry validity primarily applies to 2-dimensional
+ geometries (<varname>POLYGON</varname>s and <varname>MULTIPOLYGON</varname>s) .
+ Validity is defined by rules that allow polygonal geometry
+ to model planar areas unambiguously.
+ </para>
--- If your points are 3D points or 3M points,
--- then you might want to create an nd index instead of a 2D index
-CREATE INDEX my_special_pois_geom_gist_nd
- ON my_special_pois USING gist(geom gist_geometry_ops_nd);
+ <para>A <varname>POLYGON</varname> is <emphasis>valid</emphasis> if:
+ </para>
--- To manually register this new table's geometry column in geometry_columns.
--- Note it will also change the underlying structure of the table to
--- to make the column typmod based.
-SELECT populate_geometry_columns('myschema.my_special_pois'::regclass);
+ <orderedlist>
+ <listitem><para>
+ the polygon boundary rings (the exterior shell ring and interior hole rings)
+ are <emphasis>simple</emphasis> (do not cross or self-touch).
+ Because of this a polygon cannnot have cut lines, spikes or loops.
+ This implies that polygon holes must be represented as interior rings,
+ rather than by the exterior ring self-touching (a so-called "inverted hole").
+ </para></listitem>
+ <listitem><para>
+ boundary rings do not cross
+ </para></listitem>
+ <listitem><para>
+ boundary rings may touch at points but only as a tangent (i.e. not in a line)
+ </para></listitem>
+ <listitem><para>
+ interior rings are contained in the exterior ring
+ </para></listitem>
+ <listitem><para>
+ the polygon interior is simply connected
+ (i.e. the rings must not touch in a way that splits the polygon into more than one part)
+ </para></listitem>
+ </orderedlist>
--- If you are using PostGIS 2.0 and for whatever reason, you
--- you need the constraint based definition behavior
--- (such as case of inherited tables where all children do not have the same type and srid)
--- set optional use_typmod argument to false
-SELECT populate_geometry_columns('myschema.my_special_pois'::regclass, false); </programlisting>
+ <informaltable border="0" frame="none">
+ <tgroup cols="1">
+ <tbody>
+ <row>
+ <entry><para><emphasis>
+ <emphasis role="bold">(h)</emphasis> and
+ <emphasis role="bold">(i)</emphasis> are valid <varname>POLYGON</varname>s.
+ <emphasis role="bold">(j-m)</emphasis> are invalid.
+ <emphasis role="bold">(j)</emphasis>
+ can be represented as a valid <varname>MULTIPOLYGON</varname>.
+ </emphasis></para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ <tgroup cols="3" align="center">
+ <tbody>
+ <row>
+ <entry><para><informalfigure>
+ <mediaobject>
+ <imageobject>
+ <imagedata fileref="images/st_isvalid01.png" />
+ </imageobject>
-<para>Although the old-constraint based method is still supported, a constraint-based geometry column used directly
-in a view, will not register correctly in geometry_columns, as will a typmod one.
-In this example we define a column using typmod and another using constraints.</para>
-<programlisting>CREATE TABLE pois_ny(gid SERIAL PRIMARY KEY, poi_name text, cat text, geom geometry(POINT,4326));
-SELECT AddGeometryColumn('pois_ny', 'geom_2160', 2160, 'POINT', 2, false);</programlisting>
-<para>If we run in psql</para>
-<programlisting>\d pois_ny;</programlisting>
-<para>We observe they are defined differently -- one is typmod, one is constraint</para>
-<screen> Table "public.pois_ny"
- Column | Type | Modifiers
+ <caption><para><emphasis role="bold">(h)</emphasis></para></caption>
+ </mediaobject>
+ </informalfigure></para></entry>
------------+-----------------------+------------------------------------------------------
- gid | integer | not null default nextval('pois_ny_gid_seq'::regclass)
- poi_name | text |
- cat | character varying(20) |
- geom | geometry(Point,4326) |
- geom_2160 | geometry |
-Indexes:
- "pois_ny_pkey" PRIMARY KEY, btree (gid)
-Check constraints:
- "enforce_dims_geom_2160" CHECK (st_ndims(geom_2160) = 2)
- "enforce_geotype_geom_2160" CHECK (geometrytype(geom_2160) = 'POINT'::text
- OR geom_2160 IS NULL)
- "enforce_srid_geom_2160" CHECK (st_srid(geom_2160) = 2160)</screen>
-<para>In geometry_columns, they both register correctly</para>
-<programlisting>SELECT f_table_name, f_geometry_column, srid, type
- FROM geometry_columns
- WHERE f_table_name = 'pois_ny';</programlisting>
-<screen>f_table_name | f_geometry_column | srid | type
--------------+-------------------+------+-------
-pois_ny | geom | 4326 | POINT
-pois_ny | geom_2160 | 2160 | POINT</screen>
-<para>However -- if we were to create a view like this</para>
-<programlisting>CREATE VIEW vw_pois_ny_parks AS
-SELECT *
- FROM pois_ny
- WHERE cat='park';
+ <entry><para><informalfigure>
+ <mediaobject>
+ <imageobject>
+ <imagedata fileref="images/st_isvalid02.png" />
+ </imageobject>
-SELECT f_table_name, f_geometry_column, srid, type
- FROM geometry_columns
- WHERE f_table_name = 'vw_pois_ny_parks';</programlisting>
-<para>The typmod based geom view column registers correctly,
-but the constraint based one does not.</para>
-<screen> f_table_name | f_geometry_column | srid | type
-------------------+-------------------+------+----------
- vw_pois_ny_parks | geom | 4326 | POINT
- vw_pois_ny_parks | geom_2160 | 0 | GEOMETRY</screen>
+ <caption><para><emphasis role="bold">(i)</emphasis></para></caption>
+ </mediaobject>
+ </informalfigure></para></entry>
+
+ <entry><para><informalfigure>
+ <mediaobject>
+ <imageobject>
+ <imagedata fileref="images/st_isvalid03.png" />
+ </imageobject>
+
+ <caption><para><emphasis role="bold">(j)</emphasis></para></caption>
+ </mediaobject>
+ </informalfigure></para></entry>
+ </row>
+ <row>
+
+ <entry><para><informalfigure>
+ <mediaobject>
+ <imageobject>
+ <imagedata fileref="images/st_isvalid04.png" />
+ </imageobject>
+
+ <caption><para><emphasis role="bold">(k)</emphasis></para></caption>
+ </mediaobject>
+ </informalfigure></para></entry>
+
+ <entry><para><informalfigure>
+ <mediaobject>
+ <imageobject>
+ <imagedata fileref="images/st_isvalid05.png" />
+ </imageobject>
+
+ <caption><para><emphasis role="bold">(l)</emphasis></para></caption>
+ </mediaobject>
+ </informalfigure></para></entry>
+
+ <entry><para><informalfigure>
+ <mediaobject>
+ <imageobject>
+ <imagedata fileref="images/st_isvalid06.png" />
+ </imageobject>
+
+ <caption><para><emphasis role="bold">(m)</emphasis></para></caption>
+ </mediaobject>
+ </informalfigure></para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </informaltable>
+
+ <para>A <varname>MULTIPOLYGON</varname> is <emphasis>valid</emphasis> if:
+ </para>
+
+ <orderedlist>
+ <listitem><para>
+ its element <varname>POLYGON</varname>s are valid
+ </para></listitem>
+ <listitem><para>
+ elements do not overlap (i.e. their interiors must not intersect)
+ </para></listitem>
+ <listitem><para>
+ elements touch only at points (i.e. not along a line)
+ </para></listitem>
+ </orderedlist>
+
+ <informaltable border="0" frame="none">
+ <tgroup cols="1">
+ <tbody>
+ <row>
+ <entry><para><emphasis>
+ <emphasis role="bold">(n)</emphasis> is a valid <varname>MULTIPOLYGON</varname>.
+ <emphasis role="bold">(o)</emphasis> and <emphasis role="bold">(p)</emphasis> are invalid.
+ </emphasis></para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ <tgroup cols="3" align="center">
+ <tbody>
+ <row>
+ <entry><para><informalfigure>
+ <mediaobject>
+ <imageobject>
+ <imagedata fileref="images/st_isvalid09.png" />
+ </imageobject>
+
+ <caption><para><emphasis role="bold">(n)</emphasis></para></caption>
+ </mediaobject>
+ </informalfigure></para></entry>
+
+ <entry><para><informalfigure>
+ <mediaobject>
+ <imageobject>
+ <imagedata fileref="images/st_isvalid07.png" />
+ </imageobject>
+
+ <caption><para><emphasis role="bold">(o)</emphasis></para></caption>
+ </mediaobject>
+ </informalfigure></para></entry>
+
+ <entry><para><informalfigure>
+ <mediaobject>
+ <imageobject>
+ <imagedata fileref="images/st_isvalid08.png" />
+ </imageobject>
+
+ <caption><para><emphasis role="bold">(p)</emphasis></para></caption>
+ </mediaobject>
+ </informalfigure></para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </informaltable>
+
+ <para>These rules mean that valid polygonal geometry is also <emphasis>simple</emphasis>.
+ </para>
+
+ <para>For linear geometry the only validity rule is that <varname>LINESTRING</varname>s must
+ have at least two points and have non-zero length
+ (or equivalently, have at least two distinct points.)
+ Note that non-simple (self-intersecting) lines are valid.
+ </para>
+
+<programlisting>
+SELECT
+ ST_IsValid('LINESTRING(0 0, 1 1)') AS len_nonzero,
+ ST_IsValid('LINESTRING(0 0, 0 0, 0 0)') AS len_zero,
+ ST_IsValid('LINESTRING(10 10, 150 150, 180 50, 20 130)') AS self_int;
+
+ len_nonzero | len_zero | self_int
+-------------+----------+----------
+ t | f | t
+</programlisting>
+
+ <para><varname>POINT</varname> and <varname>MULTIPOINT</varname> geometries
+ have no validity rules.
+ </para>
+ </sect2>
+
+ <sect2 id="Managing_Validity">
+ <title>Managing Validity</title>
+
+ <para>PostGIS allows creating and storing both valid and invalid Geometry.
+ This allows invalid geometry to be detected and flagged or fixed.
+ There are also situations where the OGC validity rules are stricter than desired
+ (examples of this are zero-length linestrings and polygons with inverted holes.)
+ </para>
+
+ <para>Many of the functions provided by PostGIS rely on the
+ assumption that geometry arguments are valid.
+ For example, it does not make sense to calculate the area of
+ a polygon that has a hole defined outside of the polygon, or to construct
+ a polygon from a non-simple boundary line.
+ Assuming valid geometric inputs allows functions to operate more efficiently,
+ since they do not need to check for topological correctness.
+ (Notable exceptions are that zero-length lines
+ and polygons with inversions are generally handled correctly.)
+ Also, most PostGIS functions produce valid geometry output if the inputs are valid.
+ This allows PostGIS functions to be chained together safely.
+ </para>
+
+ <para>If you encounter unexpected error messages when calling PostGIS functions
+ (such as "GEOS Intersection() threw an error!"),
+ you should first confirm that the function arguments are valid.
+ If they are not, then consider using one of the techniques below to ensure
+ the data you are processing is valid.
+ </para>
+
+ <note><para>
+ If a function reports an error with valid inputs,
+ then you may have found an error in either PostGIS or one of
+ the libraries it uses, and you should report this to the PostGIS project.
+ The same is true if a PostGIS function returns an invalid geometry for
+ valid input.</para></note>
+
+ <para>To test if a geometry is valid use the
+ <xref linkend="ST_IsValid" /> function:
+ </para>
+<programlisting>
+SELECT ST_IsValid('POLYGON ((20 180, 180 180, 180 20, 20 20, 20 180))');
+-----------------
+ t
+</programlisting>
+ <para>Information about the nature and location of an geometry invalidity are provided by
+ the <xref linkend="ST_IsValidDetail" /> function:
+ </para>
+<programlisting>
+SELECT valid, reason, ST_AsText(location) AS location
+ FROM ST_IsValidDetail('POLYGON ((20 20, 120 190, 50 190, 170 50, 20 20))') AS t;
+
+ valid | reason | location
+-------+-------------------+---------------------------------------------
+ f | Self-intersection | POINT(91.51162790697674 141.56976744186045)
+</programlisting>
+
+ <para>In some situations it is desirable to correct invalid geometry automatically.
+ Use the <xref linkend="ST_MakeValid" /> function to do this.
+ (<code>ST_MakeValid</code> is a case of a spatial function that <emphasis>does</emphasis> allow invalid input!)
+ </para>
+
+ <para>By default, PostGIS does not check for validity when loading geometry,
+ because validity testing can take a lot of CPU time for complex
+ geometries. If you do not trust your data sources,
+ you can enforce a validity check on your tables by adding a check
+ constraint:</para>
+
+ <programlisting>ALTER TABLE mytable
+ ADD CONSTRAINT geometry_valid_check
+ CHECK (ST_IsValid(geom));</programlisting>
-<para>This may change in future versions of PostGIS, but for now
-to force the constraint-based view column to register correctly, you need to do this:</para>
-<programlisting>DROP VIEW vw_pois_ny_parks;
-CREATE VIEW vw_pois_ny_parks AS
-SELECT gid, poi_name, cat,
- geom,
- geom_2160::geometry(POINT,2160) As geom_2160
- FROM pois_ny
- WHERE cat = 'park';
-SELECT f_table_name, f_geometry_column, srid, type
- FROM geometry_columns
- WHERE f_table_name = 'vw_pois_ny_parks';</programlisting>
-<screen> f_table_name | f_geometry_column | srid | type
-------------------+-------------------+------+-------
- vw_pois_ny_parks | geom | 4326 | POINT
- vw_pois_ny_parks | geom_2160 | 2160 | POINT</screen>
</sect2>
</sect1>
<!-- ============================================================== -->
-
<sect1 id="spatial_ref_sys">
<title>Spatial Reference Systems</title>
@@ -1326,375 +1507,280 @@ VALUES ( 990000,
</sect2>
</sect1>
-
<!-- ============================================================== -->
-<sect1 id="OGC_Validity">
- <title>Geometry Validation</title>
-
- <para>PostGIS is compliant with the Open Geospatial Consortium’s (OGC)
- OpenGIS Specifications. As such, many PostGIS methods require, or more
- accurately, assume that geometries that are operated on are both simple
- and valid. For example, it does not make sense to calculate the area of
- a polygon that has a hole defined outside of the polygon, or to construct
- a polygon from a non-simple boundary line.</para>
-
- <para>According to the OGC Specifications, a <emphasis>simple</emphasis>
- geometry is one that has no anomalous geometric points, such as self
- intersection or self tangency and primarily refers to 0 or 1-dimensional
- geometries (i.e. <varname>[MULTI]POINT, [MULTI]LINESTRING</varname>).
- Geometry validity, on the other hand, primarily refers to 2-dimensional
- geometries (i.e. <varname>[MULTI]POLYGON)</varname> and defines the set
- of assertions that characterizes a valid polygon. The description of each
- geometric class includes specific conditions that further detail geometric
- simplicity and validity.</para>
-
- <para>A <varname>POINT</varname> is inherently <emphasis>simple</emphasis>
- as a 0-dimensional geometry object.</para>
-
- <para><varname>MULTIPOINT</varname>s are <emphasis>simple</emphasis> if
- no two coordinates (<varname>POINT</varname>s) are equal (have identical
- coordinate values).</para>
-
- <para>A <varname>LINESTRING</varname> is <emphasis>simple</emphasis> if
- it does not pass through the same point twice (except
- for the endpoints, in which case it is referred to as a Linear Ring and
- additionally considered <emphasis>closed</emphasis>).</para>
-
- <informaltable border="0" frame="none">
- <tgroup cols="1">
- <tbody>
- <row>
- <entry><para><emphasis>
- <emphasis role="bold">(a)</emphasis> and
- <emphasis role="bold">(c)</emphasis> are simple <varname>LINESTRING</varname>s.
- <emphasis role="bold">(b)</emphasis> and <emphasis role="bold">(d)</emphasis> are not simple.
- <emphasis role="bold">(c)</emphasis> is a closed Linear Ring.
- </emphasis></para></entry>
- </row>
- </tbody>
- </tgroup>
- <tgroup cols="2" align="center">
- <tbody>
- <row>
- <entry><para><informalfigure>
- <mediaobject>
- <imageobject>
- <imagedata fileref="images/st_issimple01.png" />
- </imageobject>
-
- <caption><para><emphasis role="bold">(a)</emphasis></para></caption>
- </mediaobject>
- </informalfigure></para></entry>
-
- <entry><para><informalfigure>
- <mediaobject>
- <imageobject>
- <imagedata fileref="images/st_issimple02.png" />
- </imageobject>
-
- <caption><para><emphasis role="bold">(b)</emphasis></para></caption>
- </mediaobject>
- </informalfigure></para></entry>
- </row>
-
- <row>
- <entry><para><informalfigure>
- <mediaobject>
- <imageobject>
- <imagedata fileref="images/st_issimple03.png" />
- </imageobject>
-
- <caption><para><emphasis role="bold">(c)</emphasis></para></caption>
- </mediaobject>
- </informalfigure></para></entry>
-
- <entry><para><informalfigure>
- <mediaobject>
- <imageobject>
- <imagedata fileref="images/st_issimple04.png" />
- </imageobject>
-
- <caption><para><emphasis role="bold">(d)</emphasis></para></caption>
- </mediaobject>
- </informalfigure></para></entry>
- </row>
- </tbody>
- </tgroup>
+<sect1>
+ <title>Spatial Tables</title>
- </informaltable>
+ <sect2 id="Create_Spatial_Table">
+ <title>Creating a Spatial Table</title>
- <para>A <varname>MULTILINESTRING</varname> is <emphasis>simple</emphasis>
- only if all of its elements are simple and the only intersection between
- any two elements occurs at points that are on the
- boundaries of both elements. </para>
+ <para>You can create a table to store geometry data using the
+ <ulink url="https://www.postgresql.org/docs/current/sql-createtable.html">CREATE TABLE</ulink>
+ SQL statement with a column of type <varname>geometry</varname>.
+ The following example creates a table with a geometry column storing 2D (XY) LineStrings
+ in the BC-Albers coordinate system (SRID 3005):</para>
- <informaltable border="0" frame="none">
- <tgroup cols="1">
- <tbody>
- <row>
- <entry><para><emphasis>
- <emphasis role="bold">(e)</emphasis> and
- <emphasis role="bold">(f)</emphasis> are simple
- <varname>MULTILINESTRING</varname>s.
- <emphasis role="bold">(g)</emphasis> is not simple.
- </emphasis></para></entry>
- </row>
- </tbody>
- </tgroup>
- <tgroup cols="3" align="center">
- <tbody>
- <row>
- <entry><para><informalfigure>
- <mediaobject>
- <imageobject>
- <imagedata fileref="images/st_issimple05.png" />
- </imageobject>
+<programlisting>CREATE TABLE roads (
+ id SERIAL PRIMARY KEY,
+ name VARCHAR(64),
+ geom geometry(LINESTRING,3005)
+ );</programlisting>
- <caption><para><emphasis role="bold">(e)</emphasis></para></caption>
- </mediaobject>
- </informalfigure></para></entry>
+ <para>The <varname>geometry</varname> type supports two optional <emphasis role="bold">type modifiers</emphasis>:</para>
- <entry><para><informalfigure>
- <mediaobject>
- <imageobject>
- <imagedata fileref="images/st_issimple06.png" />
- </imageobject>
+ <itemizedlist>
+ <listitem>
+ <para>the <emphasis role="bold">spatial type modifier</emphasis>
+ restricts the kind of shapes and dimensions allowed in the column.
+ The value can be any of the supported
+ <link linkend="RefObject">geometry subtypes</link>
+ (e.g. POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION, etc).
+ The modifier supports coordinate dimensionality restrictions by adding suffixes: Z, M and ZM.
+ For example, a modifier of 'LINESTRINGM' allows only linestrings with three dimensions, and treats the third dimension as a measure.
+ Similarly, 'POINTZM' requires four dimensional (XYZM) data.
+ </para>
+ </listitem>
+ <listitem>
+ <para>the <emphasis role="bold">SRID modifier</emphasis> restricts the
+ <link linkend="spatial_ref_sys">spatial reference system</link> SRID to a particular number.
+ If omitted, the SRID defaults to 0.
+ </para>
+ </listitem>
+ </itemizedlist>
- <caption><para><emphasis role="bold">(f)</emphasis></para></caption>
- </mediaobject>
- </informalfigure></para></entry>
+ <para></para>
- <entry><para><informalfigure>
- <mediaobject>
- <imageobject>
- <imagedata fileref="images/st_issimple07.png" />
- </imageobject>
+ <para>Examples of creating tables with geometry columns:</para>
+ <itemizedlist>
+ <listitem>
+ <para>Create a table holding any kind of geometry with the default SRID:</para>
+ <para><programlisting>CREATE TABLE geoms(gid serial PRIMARY KEY, geom geometry );</programlisting></para>
+ </listitem>
+ <listitem>
+ <para>Create a table with 2D POINT geometry with the default SRID:</para>
+ <para><programlisting>CREATE TABLE pts(gid serial PRIMARY KEY, geom geometry(POINT) );</programlisting></para>
+ </listitem>
+ <listitem>
+ <para>Create a table with 3D (XYZ) POINTs and an explicit SRID of 3005:</para>
+ <para><programlisting>CREATE TABLE pts(gid serial PRIMARY KEY, geom geometry(POINTZ,3005) );</programlisting></para>
+ </listitem>
+ <listitem>
+ <para>Create a table with 4D (XYZM) LINESTRING geometry with the default SRID:</para>
+ <para><programlisting>CREATE TABLE lines(gid serial PRIMARY KEY, geom geometry(LINESTRINGZM) );</programlisting></para>
+ </listitem>
+ <listitem>
+ <para>Create a table with 2D POLYGON geometry with the SRID 4267 (NAD 1927 long lat):</para>
+ <para><programlisting>CREATE TABLE polys(gid serial PRIMARY KEY, geom geometry(POLYGON,4267) );</programlisting></para>
+ </listitem>
+ </itemizedlist>
- <caption><para><emphasis role="bold">(g)</emphasis></para></caption>
- </mediaobject>
- </informalfigure></para></entry>
- </row>
- </tbody>
- </tgroup>
+ <para>It is possible to have more than one geometry column in a table.
+ This can be specified when the table is created, or a column can be added using the
+ <ulink url="https://www.postgresql.org/docs/current/sql-altertable.html">ALTER TABLE</ulink>
+ SQL statement.
+ This example adds a column that can hold 3D LineStrings:</para>
- </informaltable>
+ <programlisting>ALTER TABLE roads ADD COLUMN geom2 geometry(LINESTRINGZ,4326);</programlisting>
+ </sect2>
- <para>A <varname>POLYGON</varname> is <emphasis>valid</emphasis> if:
- </para>
+ <sect2 id="geometry_columns">
+ <title>GEOMETRY_COLUMNS View</title>
- <orderedlist>
- <listitem><para>
- the polygon boundary rings (the exterior shell ring and interior hole rings)
- are <emphasis>simple</emphasis> (do not cross or self-touch).
- Because of this a polygon cannnot have cut lines or spikes.
- </para></listitem>
- <listitem><para>
- boundary rings do not cross
- </para></listitem>
- <listitem><para>
- boundary rings may touch at points but only as a tangent (i.e. not in a line)
- </para></listitem>
- <listitem><para>
- the interior rings are contained in the exterior ring
- </para></listitem>
- <listitem><para>
- the polygon interior is simply connected
- (i.e. the interior rings must not split the polygon into more than one part)
- </para></listitem>
- </orderedlist>
+ <para>The OGC <emphasis>Simple Features Specification for SQL</emphasis> defines
+ the <varname>GEOMETRY_COLUMNS</varname> metadata table to describe geometry table structure.
+ In PostGIS <varname>geometry_columns</varname> is a view reading from database system catalog tables.
+ This ensures that the spatial metadata information is always consistent with the currently defined tables and views.
+ The view structure is:</para>
- <para>These rules imply that valid <varname>POLYGON</varname>s are
- <emphasis>simple</emphasis> as well.
- </para>
+ <programlisting>\d geometry_columns</programlisting>
+<screen> View "public.geometry_columns"
+ Column | Type | Modifiers
+-------------------+------------------------+-----------
+ f_table_catalog | character varying(256) |
+ f_table_schema | character varying(256) |
+ f_table_name | character varying(256) |
+ f_geometry_column | character varying(256) |
+ coord_dimension | integer |
+ srid | integer |
+ type | character varying(30) |</screen>
- <informaltable border="0" frame="none">
- <tgroup cols="1">
- <tbody>
- <row>
- <entry><para><emphasis>
- <emphasis role="bold">(h)</emphasis> and
- <emphasis role="bold">(i)</emphasis> are valid <varname>POLYGON</varname>s.
- <emphasis role="bold">(j-m)</emphasis> are invalid.
- <emphasis role="bold">(j)</emphasis>
- could be represented as a valid <varname>MULTIPOLYGON</varname>.
- </emphasis></para></entry>
- </row>
- </tbody>
- </tgroup>
- <tgroup cols="3" align="center">
- <tbody>
- <row>
- <entry><para><informalfigure>
- <mediaobject>
- <imageobject>
- <imagedata fileref="images/st_isvalid01.png" />
- </imageobject>
+ <para>The columns are:</para>
- <caption><para><emphasis role="bold">(h)</emphasis></para></caption>
- </mediaobject>
- </informalfigure></para></entry>
+ <variablelist>
+ <varlistentry>
+ <term>f_table_catalog, f_table_schema, f_table_name</term>
- <entry><para><informalfigure>
- <mediaobject>
- <imageobject>
- <imagedata fileref="images/st_isvalid02.png" />
- </imageobject>
+ <listitem>
+ <para>The fully qualified name of the feature table containing the
+ geometry column. There is no PostgreSQL analogue of "catalog" so that
+ column is left blank. For "schema" the PostgreSQL schema name is
+ used (<varname>public</varname> is the default).</para>
+ </listitem>
+ </varlistentry>
- <caption><para><emphasis role="bold">(i)</emphasis></para></caption>
- </mediaobject>
- </informalfigure></para></entry>
+ <varlistentry>
+ <term>f_geometry_column</term>
- <entry><para><informalfigure>
- <mediaobject>
- <imageobject>
- <imagedata fileref="images/st_isvalid03.png" />
- </imageobject>
+ <listitem>
+ <para>The name of the geometry column in the feature table.</para>
+ </listitem>
+ </varlistentry>
- <caption><para><emphasis role="bold">(j)</emphasis></para></caption>
- </mediaobject>
- </informalfigure></para></entry>
- </row>
- <row>
+ <varlistentry>
+ <term>coord_dimension</term>
- <entry><para><informalfigure>
- <mediaobject>
- <imageobject>
- <imagedata fileref="images/st_isvalid04.png" />
- </imageobject>
+ <listitem>
+ <para>The coordinate dimension (2, 3 or 4) of the column.</para>
+ </listitem>
+ </varlistentry>
- <caption><para><emphasis role="bold">(k)</emphasis></para></caption>
- </mediaobject>
- </informalfigure></para></entry>
+ <varlistentry>
+ <term>srid</term>
- <entry><para><informalfigure>
- <mediaobject>
- <imageobject>
- <imagedata fileref="images/st_isvalid05.png" />
- </imageobject>
+ <listitem>
+ <para>The ID of the spatial reference system used for the
+ coordinate geometry in this table. It is a foreign key reference
+ to the <varname>spatial_ref_sys</varname> table
+ (see <xref linkend="spatial_ref_sys_table" />).</para>
+ </listitem>
+ </varlistentry>
- <caption><para><emphasis role="bold">(l)</emphasis></para></caption>
- </mediaobject>
- </informalfigure></para></entry>
+ <varlistentry>
+ <term>type</term>
- <entry><para><informalfigure>
- <mediaobject>
- <imageobject>
- <imagedata fileref="images/st_isvalid06.png" />
- </imageobject>
+ <listitem>
+ <para>The type of the spatial object. To restrict the spatial
+ column to a single type, use one of: POINT, LINESTRING, POLYGON,
+ MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION or
+ corresponding XYM versions POINTM, LINESTRINGM, POLYGONM,
+ MULTIPOINTM, MULTILINESTRINGM, MULTIPOLYGONM, GEOMETRYCOLLECTIONM.
+ For heterogeneous (mixed-type) collections, you can use "GEOMETRY"
+ as the type.</para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </sect2>
- <caption><para><emphasis role="bold">(m)</emphasis></para></caption>
- </mediaobject>
- </informalfigure></para></entry>
- </row>
- </tbody>
- </tgroup>
- </informaltable>
+ <sect2 id="Manual_Register_Spatial_Column">
+ <title>Manually Registering Geometry Columns</title>
- <para>A <varname>MULTIPOLYGON</varname> is <emphasis>valid</emphasis> if:
- </para>
+ <para>Two of the cases where you may need this are the case of SQL Views and bulk inserts. For bulk insert case, you can correct the registration in the geometry_columns table
+ by constraining the column or doing an alter table. For views, you could expose using a CAST operation.
+ Note, if your column is typmod based, the creation process would register it correctly, so no need to do anything.
+ Also views that have no spatial function applied to the geometry will register the same as the underlying table geometry column.</para>
- <orderedlist>
- <listitem><para>
- its element <varname>POLYGON</varname>s are valid
- </para></listitem>
- <listitem><para>
- elements do not overlap (have intersecting interiors)
- </para></listitem>
- <listitem><para>
- elements touch only at points (i.e. not along a line)
- </para></listitem>
- </orderedlist>
+ <programlisting>-- Lets say you have a view created like this
+CREATE VIEW public.vwmytablemercator AS
+ SELECT gid, ST_Transform(geom, 3395) As geom, f_name
+ FROM public.mytable;
- <informaltable border="0" frame="none">
- <tgroup cols="1">
- <tbody>
- <row>
- <entry><para><emphasis>
- <emphasis role="bold">(n)</emphasis> is a valid <varname>MULTIPOLYGON</varname>.
- <emphasis role="bold">(o)</emphasis> and <emphasis role="bold">(p)</emphasis> are not valid.
- </emphasis></para></entry>
- </row>
- </tbody>
- </tgroup>
- <tgroup cols="3" align="center">
- <tbody>
- <row>
- <entry><para><informalfigure>
- <mediaobject>
- <imageobject>
- <imagedata fileref="images/st_isvalid09.png" />
- </imageobject>
+-- For it to register correctly
+-- You need to cast the geometry
+--
+DROP VIEW public.vwmytablemercator;
+CREATE VIEW public.vwmytablemercator AS
+ SELECT gid, ST_Transform(geom, 3395)::geometry(Geometry, 3395) As geom, f_name
+ FROM public.mytable;
- <caption><para><emphasis role="bold">(n)</emphasis></para></caption>
- </mediaobject>
- </informalfigure></para></entry>
+-- If you know the geometry type for sure is a 2D POLYGON then you could do
+DROP VIEW public.vwmytablemercator;
+CREATE VIEW public.vwmytablemercator AS
+ SELECT gid, ST_Transform(geom,3395)::geometry(Polygon, 3395) As geom, f_name
+ FROM public.mytable;</programlisting>
+ <programlisting>--Lets say you created a derivative table by doing a bulk insert
+SELECT poi.gid, poi.geom, citybounds.city_name
+INTO myschema.my_special_pois
+FROM poi INNER JOIN citybounds ON ST_Intersects(citybounds.geom, poi.geom);
- <entry><para><informalfigure>
- <mediaobject>
- <imageobject>
- <imagedata fileref="images/st_isvalid07.png" />
- </imageobject>
+-- Create 2D index on new table
+CREATE INDEX idx_myschema_myspecialpois_geom_gist
+ ON myschema.my_special_pois USING gist(geom);
- <caption><para><emphasis role="bold">(o)</emphasis></para></caption>
- </mediaobject>
- </informalfigure></para></entry>
+-- If your points are 3D points or 3M points,
+-- then you might want to create an nd index instead of a 2D index
+CREATE INDEX my_special_pois_geom_gist_nd
+ ON my_special_pois USING gist(geom gist_geometry_ops_nd);
- <entry><para><informalfigure>
- <mediaobject>
- <imageobject>
- <imagedata fileref="images/st_isvalid08.png" />
- </imageobject>
+-- To manually register this new table's geometry column in geometry_columns.
+-- Note it will also change the underlying structure of the table to
+-- to make the column typmod based.
+SELECT populate_geometry_columns('myschema.my_special_pois'::regclass);
- <caption><para><emphasis role="bold">(p)</emphasis></para></caption>
- </mediaobject>
- </informalfigure></para></entry>
- </row>
- </tbody>
- </tgroup>
- </informaltable>
+-- If you are using PostGIS 2.0 and for whatever reason, you
+-- you need the constraint based definition behavior
+-- (such as case of inherited tables where all children do not have the same type and srid)
+-- set optional use_typmod argument to false
+SELECT populate_geometry_columns('myschema.my_special_pois'::regclass, false); </programlisting>
- <para>Most of the functions implemented by the GEOS library rely on the
- assumption that input geometries are valid as specified by the OpenGIS
- Simple Feature Specification. To check simplicity or validity of
- geometries you can use the <link linkend="ST_IsSimple">ST_IsSimple()</link> and
- <link linkend="ST_IsValid">ST_IsValid()</link></para>
-
- <programlisting>-- Typically, it doesn't make sense to check
--- for validity on linear features since it will always return TRUE.
--- But in this example, PostGIS extends the definition of the OGC IsValid
--- by returning false if a LineString has less than 2 *distinct* vertices.
-gisdb=# SELECT
- ST_IsValid('LINESTRING(0 0, 1 1)'),
- ST_IsValid('LINESTRING(0 0, 0 0, 0 0)');
-
- st_isvalid | st_isvalid
-------------+-----------
- t | f</programlisting>
-
- <para>By default, PostGIS does not apply this validity check on geometry
- input, because testing for validity needs lots of CPU time for complex
- geometries, especially polygons. If you do not trust your data sources,
- you can manually enforce such a check to your tables by adding a check
- constraint:</para>
+<para>Although the old-constraint based method is still supported, a constraint-based geometry column used directly
+in a view, will not register correctly in geometry_columns, as will a typmod one.
+In this example we define a column using typmod and another using constraints.</para>
+<programlisting>CREATE TABLE pois_ny(gid SERIAL PRIMARY KEY, poi_name text, cat text, geom geometry(POINT,4326));
+SELECT AddGeometryColumn('pois_ny', 'geom_2160', 2160, 'POINT', 2, false);</programlisting>
+<para>If we run in psql</para>
+<programlisting>\d pois_ny;</programlisting>
+<para>We observe they are defined differently -- one is typmod, one is constraint</para>
+<screen> Table "public.pois_ny"
+ Column | Type | Modifiers
- <programlisting>ALTER TABLE mytable
- ADD CONSTRAINT geometry_valid_check
- CHECK (ST_IsValid(geom));</programlisting>
+-----------+-----------------------+------------------------------------------------------
+ gid | integer | not null default nextval('pois_ny_gid_seq'::regclass)
+ poi_name | text |
+ cat | character varying(20) |
+ geom | geometry(Point,4326) |
+ geom_2160 | geometry |
+Indexes:
+ "pois_ny_pkey" PRIMARY KEY, btree (gid)
+Check constraints:
+ "enforce_dims_geom_2160" CHECK (st_ndims(geom_2160) = 2)
+ "enforce_geotype_geom_2160" CHECK (geometrytype(geom_2160) = 'POINT'::text
+ OR geom_2160 IS NULL)
+ "enforce_srid_geom_2160" CHECK (st_srid(geom_2160) = 2160)</screen>
+<para>In geometry_columns, they both register correctly</para>
+<programlisting>SELECT f_table_name, f_geometry_column, srid, type
+ FROM geometry_columns
+ WHERE f_table_name = 'pois_ny';</programlisting>
+<screen>f_table_name | f_geometry_column | srid | type
+-------------+-------------------+------+-------
+pois_ny | geom | 4326 | POINT
+pois_ny | geom_2160 | 2160 | POINT</screen>
+<para>However -- if we were to create a view like this</para>
+<programlisting>CREATE VIEW vw_pois_ny_parks AS
+SELECT *
+ FROM pois_ny
+ WHERE cat='park';
- <para>If you encounter any strange error messages such as "GEOS
- Intersection() threw an error!" when calling PostGIS functions with valid
- input geometries, you likely found an error in either PostGIS or one of
- the libraries it uses, and you should contact the PostGIS developers.
- The same is true if a PostGIS function returns an invalid geometry for
- valid input.</para>
+SELECT f_table_name, f_geometry_column, srid, type
+ FROM geometry_columns
+ WHERE f_table_name = 'vw_pois_ny_parks';</programlisting>
+<para>The typmod based geom view column registers correctly,
+but the constraint based one does not.</para>
+<screen> f_table_name | f_geometry_column | srid | type
+------------------+-------------------+------+----------
+ vw_pois_ny_parks | geom | 4326 | POINT
+ vw_pois_ny_parks | geom_2160 | 0 | GEOMETRY</screen>
- <note>
- <para>The <link linkend="ST_IsValid">ST_IsValid()</link>
- function does not check the Z and M dimensions.
- </para>
- </note>
+<para>This may change in future versions of PostGIS, but for now
+to force the constraint-based view column to register correctly, you need to do this:</para>
+<programlisting>DROP VIEW vw_pois_ny_parks;
+CREATE VIEW vw_pois_ny_parks AS
+SELECT gid, poi_name, cat,
+ geom,
+ geom_2160::geometry(POINT,2160) As geom_2160
+ FROM pois_ny
+ WHERE cat = 'park';
+SELECT f_table_name, f_geometry_column, srid, type
+ FROM geometry_columns
+ WHERE f_table_name = 'vw_pois_ny_parks';</programlisting>
+<screen> f_table_name | f_geometry_column | srid | type
+------------------+-------------------+------+-------
+ vw_pois_ny_parks | geom | 4326 | POINT
+ vw_pois_ny_parks | geom_2160 | 2160 | POINT</screen>
+ </sect2>
</sect1>
+<!-- ============================================================== -->
+
<sect1 id="loading-data">
<title>Loading Spatial Data</title>
-----------------------------------------------------------------------
Summary of changes:
doc/using_postgis_dataman.xml | 1214 ++++++++++++++++++++++-------------------
1 file changed, 650 insertions(+), 564 deletions(-)
hooks/post-receive
--
PostGIS
More information about the postgis-tickets
mailing list