[postgis-tickets] r16599 - [doc] dbmanagement manual pass
Darafei
komzpa at gmail.com
Fri Jun 1 04:00:25 PDT 2018
Author: komzpa
Date: 2018-06-01 04:00:24 -0700 (Fri, 01 Jun 2018)
New Revision: 16599
Modified:
trunk/doc/using_postgis_dataman.xml
Log:
[doc] dbmanagement manual pass
Removed some dust, references to PostGIS 1 and Postgres 7.
Modified: trunk/doc/using_postgis_dataman.xml
===================================================================
--- trunk/doc/using_postgis_dataman.xml 2018-05-31 10:20:24 UTC (rev 16598)
+++ trunk/doc/using_postgis_dataman.xml 2018-06-01 11:00:24 UTC (rev 16599)
@@ -6,11 +6,11 @@
<title>GIS Objects</title>
<para>The GIS objects supported by PostGIS are a superset of the "Simple
- Features" defined by the OpenGIS Consortium (OGC). As of version 0.9,
+ Features" defined by the OpenGIS Consortium (OGC).
PostGIS supports all the objects and functions specified in the OGC
"Simple Features for SQL" specification.</para>
- <para>PostGIS extends the standard with support for 3DZ,3DM and 4D
+ <para>PostGIS extends the standard with support for 3DZ, 3DM and 4D
coordinates.</para>
<sect2 id="OpenGISWKBWKT">
@@ -78,7 +78,7 @@
<sect2 id="EWKB_EWKT">
<title>PostGIS EWKB, EWKT and Canonical Forms</title>
- <para>OGC formats only support 2d geometries, and the associated SRID is
+ <para>OGC formats only support 2D geometries, and the associated SRID is
*never* embedded in the input/output representations.</para>
<para>PostGIS extended formats are currently superset of OGC one (every
@@ -86,7 +86,7 @@
specifically if OGC comes out with a new format conflicting with our
extensions. Thus you SHOULD NOT rely on this feature!</para>
- <para>PostGIS EWKB/EWKT add 3dm,3dz,4d coordinates support and embedded
+ <para>PostGIS EWKB/EWKT add 3DM, 3DZ, 4D coordinates support and embedded
SRID information.</para>
<para>Examples of the text representations (EWKT) of the extended
@@ -152,7 +152,7 @@
</listitem>
</itemizedlist>
- <para>Input/Output of these formats are available using the following
+ <para>Conversion between these formats are available using the following
interfaces:</para>
<programlisting>bytea EWKB = ST_AsEWKB(geometry);
@@ -193,10 +193,10 @@
simple features for SQL spec by defining a number of circularly
interpolated curves.</para>
- <para>The SQL-MM definitions include 3dm, 3dz and 4d coordinates, but do
+ <para>The SQL-MM definitions include 3DM, 3DZ and 4D coordinates, but do
not allow the embedding of SRID information.</para>
- <para>The well-known text extensions are not yet fully supported.
+ <para>The Well-Known Text extensions are not yet fully supported.
Examples of some simple curved geometries are shown below:</para>
<itemizedlist>
@@ -254,12 +254,6 @@
</itemizedlist>
<note>
- <para>PostGIS prior to 1.4 does not support compound curves in a curve polygon, but
- PostGIS 1.4 and above do support the use of Compound Curves in
- a Curve Polygon.</para>
- </note>
-
- <note>
<para>All floating point comparisons within the SQL-MM implementation
are performed to a specified tolerance, currently 1E-8.</para>
</note>
@@ -272,11 +266,11 @@
<para>The basis for the PostGIS geometry type is a plane. The shortest path between two points on the plane is a straight line. That means calculations on geometries (areas, distances, lengths, intersections, etc) can be calculated using cartesian mathematics and straight line vectors.</para>
- <para>The basis for the PostGIS geographic type is a sphere. The shortest path between two points on the sphere is a great circle arc. That means that calculations on geographies (areas, distances, lengths, intersections, etc) must be calculated on the sphere, using more complicated mathematics. For more accurate measurements, the calculations must take the actual spheroidal shape of the world into account, and the mathematics becomes very complicated indeed.</para>
+ <para>The basis for the PostGIS geographic type is a sphere. The shortest path between two points on the sphere is a great circle arc. That means that calculations on geographies (areas, distances, lengths, intersections, etc) must be calculated on the sphere, using more complicated mathematics. For more accurate measurements, the calculations must take the actual spheroidal shape of the world into account.</para>
<para>Because the underlying mathematics is much more complicated, there are fewer functions defined for the geography type than for the geometry type. Over time, as new algorithms are added, the capabilities of the geography type will expand.</para>
- <para>It uses a data type called <varname>geography</varname>. None of the GEOS functions support the <varname>geography</varname>
+ <para>It uses a data type called <varname>geography</varname>. None of the GEOS functions support the <varname>geography</varname>
type. As a workaround one can convert back and forth between geometry and geography types.</para>
<para>Prior to PostGIS 2.2, the geography type only supported WGS 84 long lat (SRID:4326).
@@ -285,8 +279,7 @@
<para>Regardless which spatial reference system you use, the units returned by the measurement (<xref linkend="ST_Distance" />, <xref linkend="ST_Length" />, <xref linkend="ST_Perimeter" />, <xref linkend="ST_Area" />) and for input of <xref linkend="ST_DWithin" /> are in meters.</para>
-
- <para>The geography type uses the PostgreSQL 8.3+ typmod definition format so that a table with a geography field
+ <para>The geography type uses the PostgreSQL typmod definition format so that a table with a geography field
can be added in a single step. All the standard OGC formats except for curves are supported.</para>
<sect2 id="Geography_Basics">
@@ -296,9 +289,9 @@
<itemizedlist>
<listitem>
- <para>POINT: Creating a table with 2d point geography when srid is not specified defaults to 4326 WGS 84 long lat:</para>
+ <para>POINT: Creating a table with 2D point geography when srid is not specified defaults to 4326 WGS 84 long lat:</para>
<para><programlisting>CREATE TABLE ptgeogwgs(gid serial PRIMARY KEY, geog geography(POINT) );</programlisting></para>
- <para>POINT: Creating a table with 2d point geography in NAD83 longlat:</para>
+ <para>POINT: Creating a table with 2D point geography in NAD83 longlat:</para>
<para><programlisting>CREATE TABLE ptgeognad83(gid serial PRIMARY KEY, geog geography(POINT,4269) );</programlisting></para>
<para>Creating a table with z coordinate point and explicitly specifying srid</para>
<para><programlisting>CREATE TABLE ptzgeogwgs84(gid serial PRIMARY KEY, geog geography(POINTZ,4326) );</programlisting></para>
@@ -326,9 +319,7 @@
</listitem>
<!-- TODO: Add other examples -->
</itemizedlist>
- <para>The geography fields don't get registered in the <varname>geometry_columns</varname>. They get registered in a view called
- <varname>geography_columns</varname> which is a view against the system catalogs so is always automatically kept up to date without need
- for an AddGeom... like function.</para>
+ <para>The geography fields get registered in the <varname>geography_columns</varname> system view.</para>
<para>Now, check the "geography_columns" view and see that your table is listed.</para>
@@ -354,9 +345,9 @@
<para>You can insert data into the table the same as you would if it was using a GEOMETRY column:</para>
<para><programlisting>-- Add some data into the test table
-INSERT INTO global_points (name, location) VALUES ('Town', ST_GeogFromText('SRID=4326;POINT(-110 30)') );
-INSERT INTO global_points (name, location) VALUES ('Forest', ST_GeogFromText('SRID=4326;POINT(-109 29)') );
-INSERT INTO global_points (name, location) VALUES ('London', ST_GeogFromText('SRID=4326;POINT(0 49)') );</programlisting></para>
+INSERT INTO global_points (name, location) VALUES ('Town', 'SRID=4326;POINT(-110 30)');
+INSERT INTO global_points (name, location) VALUES ('Forest', 'SRID=4326;POINT(-109 29)');
+INSERT INTO global_points (name, location) VALUES ('London', 'SRID=4326;POINT(0 49)');</programlisting></para>
<para>Creating an index works the same as GEOMETRY.
PostGIS will note that the column type is GEOGRAPHY and create an appropriate sphere-based index instead of the usual planar index used for GEOMETRY.</para>
@@ -368,20 +359,20 @@
<para>Query and measurement functions use units of meters. So distance parameters should be expressed in meters, and return values should be expected in meters (or square meters for areas).</para>
<para><programlisting>-- Show a distance query and note, London is outside the 1000km tolerance
- SELECT name FROM global_points WHERE ST_DWithin(location, ST_GeogFromText('SRID=4326;POINT(-110 29)'), 1000000);</programlisting>
+ SELECT name FROM global_points WHERE ST_DWithin(location, 'SRID=4326;POINT(-110 29)'::geography, 1000000);</programlisting>
</para>
<para>You can see the power of GEOGRAPHY in action by calculating how close a plane flying from Seattle to London (LINESTRING(-122.33 47.606, 0.0 51.5)) comes to Reykjavik (POINT(-21.96 64.15)).</para>
<para><programlisting>-- Distance calculation using GEOGRAPHY (122.2km)
- SELECT ST_Distance('LINESTRING(-122.33 47.606, 0.0 51.5)'::geography, 'POINT(-21.96 64.15)':: geography);</programlisting>
+ SELECT ST_Distance('LINESTRING(-122.33 47.606, 0.0 51.5)'::geography, 'POINT(-21.96 64.15)'::geography);</programlisting>
</para>
<para><programlisting>-- Distance calculation using GEOMETRY (13.3 "degrees")
- SELECT ST_Distance('LINESTRING(-122.33 47.606, 0.0 51.5)'::geometry, 'POINT(-21.96 64.15)':: geometry);</programlisting>
+ SELECT ST_Distance('LINESTRING(-122.33 47.606, 0.0 51.5)'::geometry, 'POINT(-21.96 64.15)'::geometry);</programlisting>
</para>
-<para>Testing different lon/lat projects, requires PostGIS 2.2+.
+<para>Testing different lon/lat projects.
Any long lat spatial reference system listed in <varname>spatial_ref_sys</varname> table is allowed.</para>
<para> <programlisting>-- NAD 83 lon/lat
SELECT 'SRID=4269;POINT(-123 34)'::geography;
@@ -475,7 +466,7 @@
and tests (distance, containment) have to traverse the vertex list at least once and sometimes N times
(with N being the number of vertices in the other candidate feature).
</para>
- <para>As with GEOMETRY, we recommend that when you have very large polygons, but are doing queries in small areas, you "denormalize" your geometric data into smaller chunks so that the index can effectively subquery parts of the object and so queries don't have to pull out the whole object every time.
+ <para>As with GEOMETRY, we recommend that when you have very large polygons, but are doing queries in small areas, you "denormalize" your geometric data into smaller chunks so that the index can effectively subquery parts of the object and so queries don't have to pull out the whole object every time. Please consult <xref linkend="ST_Subdivide" /> function documentation.
Just because you *can* store all of Europe in one polygon doesn't mean you *should*.</para>
</answer>
</qandaentry>
@@ -505,8 +496,7 @@
known <ulink url="http://www.sharpgis.net/post/2007/05/Spatial-references2c-coordinate-systems2c-projections2c-datums2c-ellipsoids-e28093-confusing.aspx">spatial reference systems</ulink>
and details needed to transform/reproject between them.</para>
- <para>Although the PostGIS spatial_ref_sys table contains over 3000 of the more commonly used spatial reference system definitions that can be handled by the proj library, it does not contain all known to man and
- you can even define your own custom projection if you are familiar with proj4 constructs. Keep in mind that most spatial reference systems are regional and have no meaning when used outside of the bounds they were intended for.</para>
+ <para>Although the PostGIS spatial_ref_sys table contains over 3000 of the more commonly used spatial reference system definitions that can be handled by the proj library, it does not contain all known to man and you can define your own custom projection if you are familiar with proj4 constructs. Keep in mind that most spatial reference systems are regional and have no meaning when used outside of the bounds they were intended for.</para>
<para>An excellent resource for finding spatial reference systems not defined in the core set is <ulink url="http://spatialreference.org/">http://spatialreference.org/</ulink></para>
@@ -628,8 +618,7 @@
<sect2 id="geometry_columns">
<title>The GEOMETRY_COLUMNS VIEW</title>
- <para>In versions of PostGIS prior to 2.0.0, geometry_columns was a table that could be directly edited, and sometimes got out of synch with the actual definition of the geometry columns.
- In PostGIS 2.0.0, <varname>GEOMETRY_COLUMNS</varname> became a view with the same front-facing structure as prior versions, but reading from database system catalogs
+ <para><varname>GEOMETRY_COLUMNS</varname> is a view reading from database system catalogs.
Its structure is as follows:</para>
<programlisting>\d geometry_columns</programlisting>
@@ -644,7 +633,7 @@
srid | integer |
type | character varying(30) |</screen>
- <para>The column meanings have not changed from prior versions and are:</para>
+ <para>The column meanings are:</para>
<variablelist>
<varlistentry>
@@ -719,89 +708,25 @@
<para>We can add additional columns using standard ALTER TABLE command as we do in this next example where we add a 3-D linestring.</para>
<programlisting>ALTER TABLE roads ADD COLUMN geom2 geometry(LINESTRINGZ,4326);</programlisting>
- <para>For backwards compability, you can still create a spatial table in two stages using the management functions.</para>
-
- <itemizedlist>
- <listitem>
- <para>Create a normal non-spatial table.</para>
-
- <para>For example: <command>CREATE TABLE ROADS ( ID int4, ROAD_NAME
- varchar(25) )</command></para>
- </listitem>
-
- <listitem>
- <para>Add a spatial column to the table using the OpenGIS
- "AddGeometryColumn" function. Refer to <xref linkend="AddGeometryColumn" /> for more details.</para>
-
- <para>The syntax is: <programlisting>AddGeometryColumn(
- <schema_name>,
- <table_name>,
- <column_name>,
- <srid>,
- <type>,
- <dimension>
-)</programlisting> Or, using current schema: <programlisting>AddGeometryColumn(
- <table_name>,
- <column_name>,
- <srid>,
- <type>,
- <dimension>
-)</programlisting></para>
-
- <para>Example1: <command>SELECT AddGeometryColumn('public',
- 'roads', 'geom', 423, 'LINESTRING', 2)</command></para>
-
- <para>Example2: <command>SELECT AddGeometryColumn( 'roads',
- 'geom', 423, 'LINESTRING', 2)</command></para>
- </listitem>
- </itemizedlist>
-
- <para>Here is an example of SQL used to create a table and add a spatial
- column (assuming that an SRID of 128 exists already):</para>
-
- <programlisting>CREATE TABLE parks (
- park_id INTEGER,
- park_name VARCHAR,
- park_date DATE,
- park_type VARCHAR
-);
-SELECT AddGeometryColumn('parks', 'park_geom', 128, 'MULTIPOLYGON', 2 );</programlisting>
-
- <para>Here is another example, using the generic "geometry" type and the
- undefined SRID value of 0:</para>
-
- <programlisting>CREATE TABLE roads (
- road_id INTEGER,
- road_name VARCHAR
-);
-SELECT AddGeometryColumn( 'roads', 'roads_geom', 0, 'GEOMETRY', 3 );</programlisting>
- </sect2>
-
- <sect2 id="Manual_Register_Spatial_Column">
+ <sect2 id="Manual_Register_Spatial_Column">
<title>Manually Registering Geometry Columns in geometry_columns</title>
- <para>The AddGeometryColumn() approach creates a geometry column of specified type.
- This type and dimension are queryable from the <varname>geometry_columns</varname> view.
- Starting with PostGIS 2.0, geometry_columns is no longer editable and all geometry columns are autoregistered.</para>
- <para>If your geometry columns were created as generic in a table or view and no constraints applied, they will not have a dimension, type or srid in geometry_columns views, but will still be listed.</para>
- <para>Two of the cases
- where this may happen, but you can't use
- AddGeometryColumn, is in the case of SQL Views and bulk inserts. For bulk insert case, you can correct the registration in the geometry_columns table
+ <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 in PostGIS 2.0+ if your column is typmod based, the creation process would register it correctly, so no need to do anything.
+ 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>--Lets say you have a view created like this
-CREATE VIEW public.vwmytablemercator AS
- SELECT gid, ST_Transform(geom,3395) As geom, f_name
+ <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;
--- For it to register correctly in PostGIS 2.0+
+-- 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
+ SELECT gid, ST_Transform(geom, 3395)::geometry(Geometry, 3395) As geom, f_name
FROM public.mytable;
-- If you know the geometry type for sure is a 2D POLYGON then you could do
@@ -814,35 +739,30 @@
INTO myschema.my_special_pois
FROM poi INNER JOIN citybounds ON ST_Intersects(citybounds.geom, poi.geom);
---Create 2d index on new table
+-- Create 2d index on new table
CREATE INDEX idx_myschema_myspecialpois_geom_gist
ON myschema.my_special_pois USING gist(geom);
-- If your points are 3D points or 3M points,
-- then you might want to create an nd index instead of a 2d index
--- like so
CREATE INDEX my_special_pois_geom_gist_nd
ON my_special_pois USING gist(geom gist_geometry_ops_nd);
---To manually register this new table's geometry column in geometry_columns
--- Note that this approach will work for both PostGIS 2.0+ and PostGIS 1.4+
--- For PostGIS 2.0 it will also change the underlying structure of the table to
+-- 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.
--- For PostGIS prior to 2.0, this technique can also be used to register views
SELECT populate_geometry_columns('myschema.my_special_pois'::regclass);
---If you are using PostGIS 2.0 and for whatever reason, you
--- you need the old constraint based definition behavior
+-- 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 new optional use_typmod argument to false
+-- set optional use_typmod argument to false
SELECT populate_geometry_columns('myschema.my_special_pois'::regclass, false); </programlisting>
<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 varchar(20)
- , geom geometry(POINT,4326) );
+<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>
@@ -891,11 +811,11 @@
To force the constraint based view column to register correctly, we 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
+SELECT gid, poi_name, cat,
+ geom,
+ geom_2160::geometry(POINT,2160) As geom_2160
FROM pois_ny
- WHERE cat='park';
+ WHERE cat = 'park';
SELECT f_table_name, f_geometry_column, srid, type
FROM geometry_columns
WHERE f_table_name = 'vw_pois_ny_parks';</programlisting>
@@ -1053,7 +973,7 @@
</informaltable>
<para>By definition, a <varname>POLYGON</varname> is always
- <emphasis>simple</emphasis>. It is <emphasis>valid</emphasis> if no two
+ <emphasis>simple</emphasis>. It is <emphasis>valid</emphasis> if no two
rings in the boundary (made up of an exterior ring and interior rings)
cross. The boundary of a <varname>POLYGON</varname> may intersect at a
<varname>POINT</varname> but only as a tangent (i.e. not on a line).
@@ -1145,7 +1065,7 @@
<para>A <varname>MULTIPOLYGON</varname> is <emphasis>valid</emphasis>
if and only if all of its elements are valid and the interiors of no two
- elements intersect. The boundaries of any two elements may touch, but
+ elements intersect. The boundaries of any two elements may touch, but
only at a finite number of <varname>POINT</varname>s.</para>
<informaltable border="0" frame="none">
@@ -1225,11 +1145,11 @@
CHECK (ST_IsValid(the_geom));</programlisting>
<para>If you encounter any strange error messages such as "GEOS
- Intersection() threw an error!" or "JTS 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>
+ 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>
<note>
<para>Strictly compliant OGC geometries cannot have Z or M values. The
@@ -1245,8 +1165,8 @@
<title>Dimensionally Extended 9 Intersection Model (DE-9IM)</title>
<para>It is sometimes the case that the typical spatial predicates
- (<xref linkend="ST_Contains" />, <xref linkend="ST_Crosses" />,
- <xref linkend="ST_Intersects" />, <xref linkend="ST_Touches" />, ...) are
+ (<xref linkend="ST_Intersects" />, <xref linkend="ST_Contains" />,
+ <xref linkend="ST_Crosses" />, <xref linkend="ST_Touches" />, ...) are
insufficient in and of themselves to adequately provide that desired
spatial filter.</para>
@@ -1640,17 +1560,17 @@
<programlisting>BEGIN;
INSERT INTO roads (road_id, roads_geom, road_name)
- VALUES (1,ST_GeomFromText('LINESTRING(191232 243118,191108 243242)',-1),'Jeff Rd');
+ VALUES (1,'LINESTRING(191232 243118,191108 243242)','Jeff Rd');
INSERT INTO roads (road_id, roads_geom, road_name)
- VALUES (2,ST_GeomFromText('LINESTRING(189141 244158,189265 244817)',-1),'Geordie Rd');
+ VALUES (2,'LINESTRING(189141 244158,189265 244817)','Geordie Rd');
INSERT INTO roads (road_id, roads_geom, road_name)
- VALUES (3,ST_GeomFromText('LINESTRING(192783 228138,192612 229814)',-1),'Paul St');
+ VALUES (3,'LINESTRING(192783 228138,192612 229814)','Paul St');
INSERT INTO roads (road_id, roads_geom, road_name)
- VALUES (4,ST_GeomFromText('LINESTRING(189412 252431,189631 259122)',-1),'Graeme Ave');
+ VALUES (4,'LINESTRING(189412 252431,189631 259122)','Graeme Ave');
INSERT INTO roads (road_id, roads_geom, road_name)
- VALUES (5,ST_GeomFromText('LINESTRING(190131 224148,190871 228134)',-1),'Phil Tce');
+ VALUES (5,'LINESTRING(190131 224148,190871 228134)','Phil Tce');
INSERT INTO roads (road_id, roads_geom, road_name)
- VALUES (6,ST_GeomFromText('LINESTRING(198231 263418,198213 268322)',-1),'Dave Cres');
+ VALUES (6,'LINESTRING(198231 263418,198213 268322)','Dave Cres');
COMMIT;</programlisting>
<para>The data file can be piped into PostgreSQL very easily using the
@@ -1960,63 +1880,55 @@
<variablelist>
<varlistentry>
- <term>&&</term>
+ <term>ST_Intersects</term>
<listitem>
- <para>This operator tells whether the bounding box of one geometry
- intersects the bounding box of another.</para>
+ <para>This function tells whether two geometries share any space.</para>
</listitem>
</varlistentry>
<varlistentry>
- <term>ST_OrderingEquals</term>
+ <term>=</term>
<listitem>
<para>This tests whether two geometries are
geometrically identical. For example, if 'POLYGON((0 0,1 1,1 0,0
- 0))' is the same as 'POLYGON((0 0,1 1,1 0,0 0))' (it is).</para>
+ 0))' is the same as 'POLYGON((0 0,1 1,1 0,0 0))' (it is).
+ </para>
+ <para>Note: before PostGIS 2.4 this compared only boxes of geometries.</para>
</listitem>
</varlistentry>
-
- <varlistentry>
- <term>=</term>
-
- <listitem>
- <para>This operator is a little more naive, it only tests whether
- the bounding boxes of two geometries are the same.</para>
- </listitem>
- </varlistentry>
</variablelist>
<para>Next, you can use these operators in queries. Note that when
specifying geometries and boxes on the SQL command line, you must
- explicitly turn the string representations into geometries by using the
- "ST_GeomFromText()" function. The 312 is a fictitious spatial reference system that matches our data.
+ explicitly turn the string representations into geometries function.
+ The 312 is a fictitious spatial reference system that matches our data.
So, for example:</para>
<programlisting>SELECT road_id, road_name
FROM roads
- WHERE ST_OrderingEquals(roads_geom , ST_GeomFromText('LINESTRING(191232 243118,191108 243242)',312) ) ;</programlisting>
+ WHERE roads_geom='SRID=312;LINESTRING(191232 243118,191108 243242)'::geometry;</programlisting>
<para>The above query would return the single record from the
"ROADS_GEOM" table in which the geometry was equal to that value.</para>
- <para>When using the "&&" operator, you can specify either a
- BOX3D as the comparison feature or a GEOMETRY. When you specify a
- GEOMETRY, however, its bounding box will be used for the
- comparison.</para>
+ <para>To check whether some of the roads passes in the area defined by a polygon:</para>
<programlisting>SELECT road_id, road_name
FROM roads
-WHERE roads_geom && ST_GeomFromText('POLYGON((...))',312);</programlisting>
+WHERE ST_Intersects(roads_geom, 'SRID=312;POLYGON((...))');</programlisting>
- <para>The above query will use the bounding box of the polygon for
- comparison purposes.</para>
+
<para>The most common spatial query will probably be a "frame-based"
query, used by client software, like data browsers and web mappers, to
- grab a "map frame" worth of data for display. Using a "BOX3D" object for
- the frame, such a query looks like this:</para>
+ grab a "map frame" worth of data for display. </para>
+ <para>When using the "&&" operator, you can specify either a
+ BOX3D as the comparison feature or a GEOMETRY. When you specify a
+ GEOMETRY, however, its bounding box will be used for the
+ comparison.</para>
+ <para>Using a "BOX3D" object for the frame, such a query looks like this:</para>
<programlisting>SELECT ST_AsText(roads_geom) AS geom
FROM roads
@@ -2024,6 +1936,8 @@
roads_geom && ST_MakeEnvelope(191232, 243117,191232, 243119,312);</programlisting>
<para>Note the use of the SRID 312, to specify the projection of the envelope.</para>
+
+
</sect2>
<sect2>
@@ -2109,17 +2023,6 @@
</varlistentry>
<varlistentry>
- <term>-d</term>
-
- <listitem>
- <para>For backward compatibility: write a 3-dimensional shape file
- when dumping from old (pre-1.0.0) postgis databases (the default
- is to write a 2-dimensional shape file in that case). Starting
- from postgis-1.0.0+, dimensions are fully encoded.</para>
- </listitem>
- </varlistentry>
-
- <varlistentry>
<term>-m <varname>filename</varname></term>
<listitem>
<para> Remap identifiers to ten character names.
@@ -2142,8 +2045,7 @@
"sequential scan" of every record in the database. Indexing speeds up
searching by organizing the data into a search tree which can be quickly
traversed to find a particular record. PostgreSQL supports three kinds of
- indexes by default: B-Tree indexes, R-Tree indexes, and GiST
- indexes.</para>
+ indexes by default: B-Tree indexes, SP-GiST and GiST indexes.</para>
<itemizedlist>
<listitem>
@@ -2154,13 +2056,6 @@
</listitem>
<listitem>
- <para>R-Trees break up data into rectangles, and sub-rectangles, and
- sub-sub rectangles, etc. R-Trees are used by some spatial databases to
- index GIS data, but the PostgreSQL R-Tree implementation is not as
- robust as the GiST implementation.</para>
- </listitem>
-
- <listitem>
<para>GiST (Generalized Search Trees) indexes break up data into
"things to one side", "things which overlap", "things which are
inside" and can be used on a wide range of data-types, including GIS
@@ -2185,46 +2080,37 @@
<para>The syntax for building a GiST index on a "geometry" column is as
follows:</para>
+
<para><programlisting>CREATE INDEX [indexname] ON [tablename] USING GIST ( [geometryfield] ); </programlisting></para>
- <para>The above syntax will always build a 2D-index. To get the an n-dimensional index supported in PostGIS 2.0+ for the geometry type, you can create one using this syntax</para>
+
+ <para>The above syntax will always build a 2D-index. To get the an n-dimensional index for the geometry type, you can create one using this syntax:</para>
<programlisting>CREATE INDEX [indexname] ON [tablename] USING GIST ([geometryfield] gist_geometry_ops_nd);</programlisting>
- <para>Building a spatial index is a computationally intensive exercise:
- on tables of around 1 million rows, on a 300MHz Solaris machine, we have
- found building a GiST index takes about 1 hour. After building an index,
- it is important to force PostgreSQL to collect table statistics, which
- are used to optimize query plans:</para>
+ <para>Building a spatial index is a computationally intensive exercise. It also blocks write access to your table for the time it creates, so on a production system you may want to do in in a slower CONCURRENTLY-aware way:</para>
+ <para><programlisting>CREATE INDEX CONCURRENTLY [indexname] ON [tablename] USING GIST ( [geometryfield] ); </programlisting></para>
- <para><programlisting>VACUUM ANALYZE [table_name] [(column_name)];
--- This is only needed for PostgreSQL 7.4 installations and below
-SELECT UPDATE_GEOMETRY_STATS([table_name], [column_name]);</programlisting></para>
+ <para>After building an index, it is sometimes helpful to force PostgreSQL to collect
+ table statistics, which are used to optimize query plans:</para>
- <para>GiST indexes have two advantages over R-Tree indexes in
- PostgreSQL. Firstly, GiST indexes are "null safe", meaning they can
- index columns which include null values. Secondly, GiST indexes support
- the concept of "lossiness" which is important when dealing with GIS
- objects larger than the PostgreSQL 8K page size. Lossiness allows
- PostgreSQL to store only the "important" part of an object in an index
- -- in the case of GIS objects, just the bounding box. GIS objects larger
- than 8K will cause R-Tree indexes to fail in the process of being
- built.</para>
+ <para><programlisting>VACUUM ANALYZE [table_name] [(column_name)];</programlisting></para>
+
</sect2>
<sect2 id="brin_indexes">
<title>BRIN Indexes</title>
- <para>BRIN stands for "Block Range Index" and is a generic form of
- indexing that has been introduced in PostgreSQL 9.5. BRIN is a lossy kind
- of index, and its main usage is to provide a compromise for both read and
- write performance. Its primary goal is to handle very large tables for
- which some of the columns have some natural correlation with their
- physical location within the table. In addition to GIS indexing, BRIN is
- used to speed up searches on various kinds of regular or irregular data
- structures (integer, arrays etc).</para>
+ <para>BRIN stands for "Block Range Index" and is a generic form of
+ indexing that has been introduced in PostgreSQL 9.5. BRIN is a lossy kind
+ of index, and its main usage is to provide a compromise for both read and
+ write performance. Its primary goal is to handle very large tables for
+ which some of the columns have some natural correlation with their
+ physical location within the table. In addition to GIS indexing, BRIN is
+ used to speed up searches on various kinds of regular or irregular data
+ structures (integer, arrays etc).</para>
- <para>Once a GIS data table exceeds a few thousand rows, you will want
- to build an index to speed up spatial searches of the data (unless all
- your searches are based on attributes, in which case you'll want to
+ <para>Once a GIS data table exceeds a few thousand rows, you will want
+ to build an index to speed up spatial searches of the data (unless all
+ your searches are based on attributes, in which case you'll want to
build a normal index on the attribute fields). GiST indexes are really
performant as long as their size doesn't exceed the amount of RAM
available for the database, and as long as you can afford the storage
@@ -2279,10 +2165,6 @@
"geometry" and for "geography"), and just the <varname>&&&</varname>
operator can be used for the 3D geometries. There is no support
for kNN searches at the moment.</para>
-
- <para><programlisting>VACUUM ANALYZE [table_name] [(column_name)];
--- This is only needed for PostgreSQL 7.4 installations and below
-SELECT UPDATE_GEOMETRY_STATS([table_name], [column_name]);</programlisting></para>
</sect2>
<sect2>
@@ -2291,7 +2173,7 @@
<para>Ordinarily, indexes invisibly speed up data access: once the index
is built, the query planner transparently decides when to use index
information to speed up a query plan. Unfortunately, the PostgreSQL
- query planner does not optimize the use of GiST indexes well, so
+ query planner does not currently optimize the use of GiST indexes well, so
sometimes searches which should use a spatial index instead default to a
sequence scan of the whole table.</para>
@@ -2303,13 +2185,10 @@
<listitem>
<para>Firstly, make sure statistics are gathered about the number
and distributions of values in a table, to provide the query planner
- with better information to make decisions around index usage. For
- PostgreSQL 7.4 installations and below this is done by running
- <command>update_geometry_stats([table_name, column_name])</command>
- (compute distribution) and <command>VACUUM ANALYZE [table_name]
- [column_name]</command> (compute number of values). Starting with
- PostgreSQL 8.0 running <command>VACUUM ANALYZE</command> will do
- both operations. You should regularly vacuum your databases anyways
+ with better information to make decisions around index usage.
+ <command>VACUUM ANALYZE</command> will compute both spatial distribution and number of values.
+
+ You should regularly vacuum your databases anyways
-- many PostgreSQL DBAs have <command>VACUUM</command> run as an
off-peak cron job on a regular basis.</para>
</listitem>
@@ -2317,17 +2196,12 @@
<listitem>
<para>If vacuuming does not work, you can force the planner to use
the index information by using the <command>SET
- ENABLE_SEQSCAN=OFF</command> command. You should only use this
+ ENABLE_SEQSCAN=OFF;</command> command. You should only use this
command sparingly, and only on spatially indexed queries: generally
speaking, the planner knows better than you do about when to use
normal B-Tree indexes. Once you have run your query, you should
consider setting <varname>ENABLE_SEQSCAN</varname> back on, so that
other queries will utilize the planner as normal.</para>
-
- <note>
- <para>As of version 0.6, it should not be necessary to force the
- planner to use the index with
- <varname>ENABLE_SEQSCAN</varname>.</para>
</note>
</listitem>
@@ -2335,8 +2209,8 @@
<para>If you find the planner wrong about the cost of sequential vs
index scans try reducing the value of random_page_cost in
postgresql.conf or using SET random_page_cost=#. Default value for
- the parameter is 4, try setting it to 1 or 2. Decrementing the value
- makes the planner more inclined of using Index scans.</para>
+ the parameter is 4, try setting it to 1 (on SSD) or 2 (on fast magnetic disks).
+ Decrementing the value makes the planner more inclined of using Index scans.</para>
</listitem>
</itemizedlist>
</sect2>
@@ -2359,7 +2233,7 @@
structures.</para>
<para>Even in the case where you are planning a Mars expedition to transport the human race in the event of a nuclear holocaust
and you want to map out the Mars planet for rehabitation, you can use a non-earthly coordinate system such as <ulink url="http://spatialreference.org/ref/iau2000/mars-2000/">Mars 2000</ulink>
- make one up and insert it in the <varname>spatial_ref_sys</varname> table. Though this Mars coordinate system is a non-planar one (it's in degrees spheroidal),
+ make one up and insert it in the <varname>spatial_ref_sys</varname> table. Though this Mars coordinate system is a non-planar one (it's in degrees spheroidal),
you can use it with the geography type to have your length and proximity measurements in meters instead of degrees.</para>
<sect2>
@@ -2374,24 +2248,25 @@
<programlisting>SELECT the_geom
FROM geom_table
-WHERE ST_Distance(the_geom, ST_GeomFromText('POINT(100000 200000)', 312)) < 100</programlisting>
+WHERE ST_Distance(the_geom, 'SRID=312;POINT(100000 200000)') < 100</programlisting>
<para>This query is selecting all the geometries in geom_table which are
within 100 units of the point (100000, 200000). It will be slow because
it is calculating the distance between each point in the table and our
specified point, ie. one <varname>ST_Distance()</varname> calculation
- for each row in the table. We can avoid this by using the &&
- operator to reduce the number of distance calculations required:</para>
+ for each row in the table. We can avoid this by using the single step
+ index accelerated function ST_DWithin to reduce the number of distance
+ calculations required:</para>
<programlisting>SELECT the_geom
FROM geom_table
-WHERE ST_DWithin(the_geom, ST_MakeEnvelope(90900, 190900, 100100, 200100,312), 100)
+WHERE ST_DWithin(the_geom, 'SRID=312;POINT(100000 200000)', 100)
</programlisting>
<para>This query selects the same geometries, but it does it in a more
efficient way. Assuming there is a GiST index on the_geom, the query
planner will recognize that it can use the index to reduce the number of
- rows before calculating the result of the <varname>ST_distance()</varname>
+ rows before calculating the result of the <varname>ST_Distance()</varname>
function. Notice that the <varname>ST_MakeEnvelope</varname> geometry which is
used in the && operation is a 200 unit square box centered on
the original point - this is our "query box". The && operator
@@ -2400,14 +2275,6 @@
Assuming that our query box is much smaller than the extents of the
entire geometry table, this will drastically reduce the number of
distance calculations that need to be done.</para>
-
- <note>
- <title>Change in Behavior</title>
-
- <para>As of PostGIS 1.3.0, most of the Geometry Relationship
- Functions, with the notable exceptions of ST_Disjoint and ST_Relate,
- include implicit bounding box overlap operators.</para>
- </note>
</sect2>
<sect2 id="examples_spatial_sql">
@@ -2529,7 +2396,7 @@
bc_roads AS r,
bc_municipality AS m
WHERE
- ST_Contains(m.the_geom,r.the_geom)
+ ST_Contains(m.the_geom, r.the_geom)
GROUP BY m.name
ORDER BY roads_km;
@@ -2571,7 +2438,9 @@
FROM
bc_roads AS r,
bc_municipality AS m
-WHERE m.name = 'PRINCE GEORGE' AND ST_Intersects(r.the_geom, m.the_geom);</programlisting>
+WHERE
+ m.name = 'PRINCE GEORGE'
+ AND ST_Intersects(r.the_geom, m.the_geom);</programlisting>
</answer>
</qandaentry>
@@ -2587,8 +2456,10 @@
FROM
bc_roads r,
bc_municipality m
-WHERE r.name = 'Douglas St' AND m.name = 'VICTORIA'
- AND ST_Contains(m.the_geom, r.the_geom) ;
+WHERE
+ r.name = 'Douglas St'
+ AND m.name = 'VICTORIA'
+ AND ST_Intersects(m.the_geom, r.the_geom);
kilometers
------------------
More information about the postgis-tickets
mailing list