# SOME DESCRIPTIVE TITLE. # FIRST AUTHOR , YEAR. # #, fuzzy msgid "" msgstr "" "Project-Id-Version: PACKAGE VERSION\n" "Report-Msgid-Bugs-To: http://bugs.kde.org\n" "POT-Creation-Date: 2018-07-21 17:36+0000\n" "PO-Revision-Date: YEAR-MO-DA HO:MI+ZONE\n" "Last-Translator: FULL NAME \n" "Language-Team: LANGUAGE \n" "MIME-Version: 1.0\n" "Content-Type: text/plain; charset=UTF-8\n" "Content-Transfer-Encoding: 8bit\n" #. Tag: title #: using_postgis_dataman.xml:3 #, no-c-format msgid "Using PostGIS: Data Management and Queries" msgstr "" #. Tag: title #: using_postgis_dataman.xml:6 #, no-c-format msgid "GIS Objects" msgstr "" #. Tag: para #: using_postgis_dataman.xml:8 #, no-c-format msgid "" "The GIS objects supported by PostGIS are a superset of the \"Simple Features" "\" defined by the OpenGIS Consortium (OGC). PostGIS supports all the objects " "and functions specified in the OGC \"Simple Features for SQL\" specification." msgstr "" #. Tag: para #: using_postgis_dataman.xml:13 #, no-c-format msgid "" "PostGIS extends the standard with support for 3DZ, 3DM and 4D coordinates." msgstr "" #. Tag: title #: using_postgis_dataman.xml:17 #, no-c-format msgid "OpenGIS WKB and WKT" msgstr "" #. Tag: para #: using_postgis_dataman.xml:19 #, no-c-format msgid "" "The OpenGIS specification defines two standard ways of expressing spatial " "objects: the Well-Known Text (WKT) form and the Well-Known Binary (WKB) " "form. Both WKT and WKB include information about the type of the object and " "the coordinates which form the object." msgstr "" #. Tag: para #: using_postgis_dataman.xml:24 #, no-c-format msgid "" "Examples of the text representations (WKT) of the spatial objects of the " "features are as follows:" msgstr "" #. Tag: para #: using_postgis_dataman.xml:29 #, no-c-format msgid "POINT(0 0)" msgstr "" #. Tag: para #: using_postgis_dataman.xml:33 #, no-c-format msgid "LINESTRING(0 0,1 1,1 2)" msgstr "" #. Tag: para #: using_postgis_dataman.xml:37 #, no-c-format msgid "POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))" msgstr "" #. Tag: para #: using_postgis_dataman.xml:41 #, no-c-format msgid "MULTIPOINT((0 0),(1 2))" msgstr "" #. Tag: para #: using_postgis_dataman.xml:45 #, no-c-format msgid "MULTILINESTRING((0 0,1 1,1 2),(2 3,3 2,5 4))" msgstr "" #. Tag: para #: using_postgis_dataman.xml:49 #, no-c-format msgid "" "MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0),(1 1,2 1,2 2,1 2,1 1)), ((-1 -1,-1 -2,-2 " "-2,-2 -1,-1 -1)))" msgstr "" #. Tag: para #: using_postgis_dataman.xml:54 #, no-c-format msgid "GEOMETRYCOLLECTION(POINT(2 3),LINESTRING(2 3,3 4))" msgstr "" #. Tag: para #: using_postgis_dataman.xml:58 #, no-c-format msgid "" "The OpenGIS specification also requires that the internal storage format of " "spatial objects include a spatial referencing system identifier (SRID). The " "SRID is required when creating spatial objects for insertion into the " "database." msgstr "" #. Tag: para #: using_postgis_dataman.xml:63 #, no-c-format msgid "" "Input/Output of these formats are available using the following interfaces:" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:66 #, no-c-format msgid "" "bytea WKB = ST_AsBinary(geometry);\n" "text WKT = ST_AsText(geometry);\n" "geometry = ST_GeomFromWKB(bytea WKB, SRID);\n" "geometry = ST_GeometryFromText(text WKT, SRID);" msgstr "" #. Tag: para #: using_postgis_dataman.xml:68 #, no-c-format msgid "" "For example, a valid insert statement to create and insert an OGC spatial " "object would be:" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:71 #, no-c-format msgid "" "INSERT INTO geotable ( the_geom, the_name )\n" " VALUES ( ST_GeomFromText('POINT(-126.4 45.32)', 312), 'A Place');" msgstr "" #. Tag: title #: using_postgis_dataman.xml:75 #, no-c-format msgid "PostGIS EWKB, EWKT and Canonical Forms" msgstr "" #. Tag: para #: using_postgis_dataman.xml:77 #, no-c-format msgid "" "OGC formats only support 2D geometries, and the associated SRID is *never* " "embedded in the input/output representations." msgstr "" #. Tag: para #: using_postgis_dataman.xml:80 #, no-c-format msgid "" "PostGIS extended formats are currently superset of OGC one (every valid WKB/" "WKT is a valid EWKB/EWKT) but this might vary in the future, specifically if " "OGC comes out with a new format conflicting with our extensions. Thus you " "SHOULD NOT rely on this feature!" msgstr "" #. Tag: para #: using_postgis_dataman.xml:85 #, no-c-format msgid "" "PostGIS EWKB/EWKT add 3DM, 3DZ, 4D coordinates support and embedded SRID " "information." msgstr "" #. Tag: para #: using_postgis_dataman.xml:88 #, no-c-format msgid "" "Examples of the text representations (EWKT) of the extended spatial objects " "of the features are as follows." msgstr "" #. Tag: para #: using_postgis_dataman.xml:93 #, no-c-format msgid "POINT(0 0 0) -- XYZ" msgstr "" #. Tag: para #: using_postgis_dataman.xml:97 #, no-c-format msgid "SRID=32632;POINT(0 0) -- XY with SRID" msgstr "" #. Tag: para #: using_postgis_dataman.xml:101 #, no-c-format msgid "POINTM(0 0 0) -- XYM" msgstr "" #. Tag: para #: using_postgis_dataman.xml:105 #, no-c-format msgid "POINT(0 0 0 0) -- XYZM" msgstr "" #. Tag: para #: using_postgis_dataman.xml:109 #, no-c-format msgid "SRID=4326;MULTIPOINTM(0 0 0,1 2 1) -- XYM with SRID" msgstr "" #. Tag: para #: using_postgis_dataman.xml:113 #, no-c-format msgid "MULTILINESTRING((0 0 0,1 1 0,1 2 1),(2 3 1,3 2 1,5 4 1))" msgstr "" #. Tag: para #: using_postgis_dataman.xml:118 #, no-c-format msgid "" "POLYGON((0 0 0,4 0 0,4 4 0,0 4 0,0 0 0),(1 1 0,2 1 0,2 2 0,1 2 0,1 1 0))" msgstr "" #. Tag: para #: using_postgis_dataman.xml:123 #, no-c-format msgid "" "MULTIPOLYGON(((0 0 0,4 0 0,4 4 0,0 4 0,0 0 0),(1 1 0,2 1 0,2 2 0,1 2 0,1 1 " "0)),((-1 -1 0,-1 -2 0,-2 -2 0,-2 -1 0,-1 -1 0)))" msgstr "" #. Tag: para #: using_postgis_dataman.xml:128 #, no-c-format msgid "GEOMETRYCOLLECTIONM( POINTM(2 3 9), LINESTRINGM(2 3 4, 3 4 5) )" msgstr "" #. Tag: para #: using_postgis_dataman.xml:132 #, no-c-format msgid "MULTICURVE( (0 0, 5 5), CIRCULARSTRING(4 0, 4 4, 8 4) )" msgstr "" #. Tag: para #: using_postgis_dataman.xml:136 #, no-c-format msgid "" "POLYHEDRALSURFACE( ((0 0 0, 0 0 1, 0 1 1, 0 1 0, 0 0 0)), ((0 0 0, 0 1 0, 1 " "1 0, 1 0 0, 0 0 0)), ((0 0 0, 1 0 0, 1 0 1, 0 0 1, 0 0 0)), ((1 1 0, 1 1 1, " "1 0 1, 1 0 0, 1 1 0)), ((0 1 0, 0 1 1, 1 1 1, 1 1 0, 0 1 0)), ((0 0 1, 1 0 " "1, 1 1 1, 0 1 1, 0 0 1)) )" msgstr "" #. Tag: para #: using_postgis_dataman.xml:142 #, no-c-format msgid "TRIANGLE ((0 0, 0 9, 9 0, 0 0))" msgstr "" #. Tag: para #: using_postgis_dataman.xml:146 #, no-c-format msgid "TIN( ((0 0 0, 0 0 1, 0 1 0, 0 0 0)), ((0 0 0, 0 1 0, 1 1 0, 0 0 0)) )" msgstr "" #. Tag: para #: using_postgis_dataman.xml:151 #, no-c-format msgid "" "Conversion between these formats is available using the following interfaces:" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:153 #, no-c-format msgid "" "bytea EWKB = ST_AsEWKB(geometry);\n" "text EWKT = ST_AsEWKT(geometry);\n" "geometry = ST_GeomFromEWKB(bytea EWKB);\n" "geometry = ST_GeomFromEWKT(text EWKT);" msgstr "" #. Tag: para #: using_postgis_dataman.xml:155 #, no-c-format msgid "" "For example, a valid insert statement to create and insert a PostGIS spatial " "object would be:" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:158 #, no-c-format msgid "" "INSERT INTO geotable ( the_geom, the_name )\n" " VALUES ( ST_GeomFromEWKT('SRID=312;POINTM(-126.4 45.32 15)'), 'A Place' )" msgstr "" #. Tag: para #: using_postgis_dataman.xml:160 #, no-c-format msgid "" "The \"canonical forms\" of a PostgreSQL type are the representations you get " "with a simple query (without any function call) and the one which is " "guaranteed to be accepted with a simple insert, update or copy. For the " "PostGIS 'geometry' type these are:" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:164 #, no-c-format msgid "" "- Output\n" " - binary: EWKB\n" " ascii: HEXEWKB (EWKB in hex form)\n" "- Input\n" " - binary: EWKB\n" " ascii: HEXEWKB|EWKT" msgstr "" #. Tag: para #: using_postgis_dataman.xml:166 #, no-c-format msgid "" "For example this statement reads EWKT and returns HEXEWKB in the process of " "canonical ascii input/output:" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:169 #, no-c-format msgid "" "=# SELECT 'SRID=4;POINT(0 0)'::geometry;\n" "\n" "geometry\n" "----------------------------------------------------\n" "01010000200400000000000000000000000000000000000000\n" "(1 row)" msgstr "" #. Tag: title #: using_postgis_dataman.xml:172 #, no-c-format msgid "SQL-MM Part 3" msgstr "" #. Tag: para #: using_postgis_dataman.xml:174 #, no-c-format msgid "" "The SQL Multimedia Applications Spatial specification extends the simple " "features for SQL spec by defining a number of circularly interpolated curves." msgstr "" #. Tag: para #: using_postgis_dataman.xml:178 #, no-c-format msgid "" "The SQL-MM definitions include 3DM, 3DZ and 4D coordinates, but do not allow " "the embedding of SRID information." msgstr "" #. Tag: para #: using_postgis_dataman.xml:181 #, no-c-format msgid "" "The Well-Known Text extensions are not yet fully supported. Examples of some " "simple curved geometries are shown below:" msgstr "" #. Tag: para #: using_postgis_dataman.xml:186 #, no-c-format msgid "CIRCULARSTRING(0 0, 1 1, 1 0)" msgstr "" #. Tag: para #: using_postgis_dataman.xml:187 #, no-c-format msgid "CIRCULARSTRING(0 0, 4 0, 4 4, 0 4, 0 0)" msgstr "" #. Tag: para #: using_postgis_dataman.xml:188 #, no-c-format msgid "" "The CIRCULARSTRING is the basic curve type, similar to a LINESTRING in the " "linear world. A single segment required three points, the start and end " "points (first and third) and any other point on the arc. The exception to " "this is for a closed circle, where the start and end points are the same. In " "this case the second point MUST be the center of the arc, ie the opposite " "side of the circle. To chain arcs together, the last point of the previous " "arc becomes the first point of the next arc, just like in LINESTRING. This " "means that a valid circular string must have an odd number of points greater " "than 1." msgstr "" #. Tag: para #: using_postgis_dataman.xml:201 #, no-c-format msgid "COMPOUNDCURVE(CIRCULARSTRING(0 0, 1 1, 1 0),(1 0, 0 1))" msgstr "" #. Tag: para #: using_postgis_dataman.xml:202 #, no-c-format msgid "" "A compound curve is a single, continuous curve that has both curved " "(circular) segments and linear segments. That means that in addition to " "having well-formed components, the end point of every component (except the " "last) must be coincident with the start point of the following component." msgstr "" #. Tag: para #: using_postgis_dataman.xml:210 #, no-c-format msgid "" "CURVEPOLYGON(CIRCULARSTRING(0 0, 4 0, 4 4, 0 4, 0 0),(1 1, 3 3, 3 1, 1 1))" msgstr "" #. Tag: para #: using_postgis_dataman.xml:212 #, no-c-format msgid "" "Example compound curve in a curve polygon: " "CURVEPOLYGON(COMPOUNDCURVE(CIRCULARSTRING(0 0,2 0, 2 1, 2 3, 4 3),(4 3, 4 5, " "1 4, 0 0)), CIRCULARSTRING(1.7 1, 1.4 0.4, 1.6 0.4, 1.6 0.5, 1.7 1) )" msgstr "" #. Tag: para #: using_postgis_dataman.xml:216 #, no-c-format msgid "" "A CURVEPOLYGON is just like a polygon, with an outer ring and zero or more " "inner rings. The difference is that a ring can take the form of a circular " "string, linear string or compound string." msgstr "" #. Tag: para #: using_postgis_dataman.xml:220 #, no-c-format msgid "As of PostGIS 1.4 PostGIS supports compound curves in a curve polygon." msgstr "" #. Tag: para #: using_postgis_dataman.xml:224 #, no-c-format msgid "MULTICURVE((0 0, 5 5),CIRCULARSTRING(4 0, 4 4, 8 4))" msgstr "" #. Tag: para #: using_postgis_dataman.xml:225 #, no-c-format msgid "" "The MULTICURVE is a collection of curves, which can include linear strings, " "circular strings or compound strings." msgstr "" #. Tag: para #: using_postgis_dataman.xml:230 #, no-c-format msgid "" "MULTISURFACE(CURVEPOLYGON(CIRCULARSTRING(0 0, 4 0, 4 4, 0 4, 0 0),(1 1, 3 3, " "3 1, 1 1)),((10 10, 14 12, 11 10, 10 10),(11 11, 11.5 11, 11 11.5, 11 11)))" msgstr "" #. Tag: para #: using_postgis_dataman.xml:233 #, no-c-format msgid "" "This is a collection of surfaces, which can be (linear) polygons or curve " "polygons." msgstr "" #. Tag: para #: using_postgis_dataman.xml:239 #, no-c-format msgid "" "All floating point comparisons within the SQL-MM implementation are " "performed to a specified tolerance, currently 1E-8." msgstr "" #. Tag: title #: using_postgis_dataman.xml:245 #, no-c-format msgid "PostGIS Geography Type" msgstr "" #. Tag: para #: using_postgis_dataman.xml:247 #, no-c-format msgid "" "The geography type provides native support for spatial features represented " "on \"geographic\" coordinates (sometimes called \"geodetic\" coordinates, or " "\"lat/lon\", or \"lon/lat\"). Geographic coordinates are spherical " "coordinates expressed in angular units (degrees)." msgstr "" #. Tag: para #: using_postgis_dataman.xml:249 #, no-c-format msgid "" "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." msgstr "" #. Tag: para #: using_postgis_dataman.xml:251 #, no-c-format msgid "" "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." msgstr "" #. Tag: para #: using_postgis_dataman.xml:253 #, no-c-format msgid "" "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." msgstr "" #. Tag: para #: using_postgis_dataman.xml:255 #, no-c-format msgid "" "It uses a data type called geography. None of the GEOS " "functions support the geography type. As a workaround one " "can convert back and forth between geometry and geography types." msgstr "" #. Tag: para #: using_postgis_dataman.xml:258 #, no-c-format msgid "" "Prior to PostGIS 2.2, the geography type only supported WGS 84 long lat " "(SRID:4326). For PostGIS 2.2 and above, any long/lat based spatial reference " "system defined in the spatial_ref_sys table can be used. " "You can even add your own custom spheroidal spatial reference system as " "described in geography type is " "not limited to earth." msgstr "" #. Tag: para #: using_postgis_dataman.xml:262 #, no-c-format msgid "" "Regardless which spatial reference system you use, the units returned by the " "measurement (, , " ", ) and for input " "of are in meters." msgstr "" #. Tag: para #: using_postgis_dataman.xml:264 #, no-c-format msgid "" "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." msgstr "" #. Tag: title #: using_postgis_dataman.xml:268 #, no-c-format msgid "Geography Basics" msgstr "" #. Tag: para #: using_postgis_dataman.xml:269 #, no-c-format msgid "" "The geography type does not support curves, TINS, or POLYHEDRALSURFACEs, but " "other geometry types are supported. Standard geometry type data will " "autocast to geography if it is of SRID 4326. You can also use the EWKT and " "EWKB conventions to insert data." msgstr "" #. Tag: para #: using_postgis_dataman.xml:274 #, no-c-format msgid "" "POINT: Creating a table with 2D point geography when srid is not specified " "defaults to 4326 WGS 84 long lat:" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:275 #, no-c-format msgid "CREATE TABLE ptgeogwgs(gid serial PRIMARY KEY, geog geography(POINT) );" msgstr "" #. Tag: para #: using_postgis_dataman.xml:276 #, no-c-format msgid "POINT: Creating a table with 2D point geography in NAD83 longlat:" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:277 #, no-c-format msgid "" "CREATE TABLE ptgeognad83(gid serial PRIMARY KEY, geog " "geography(POINT,4269) );" msgstr "" #. Tag: para #: using_postgis_dataman.xml:278 #, no-c-format msgid "Creating a table with z coordinate point and explicitly specifying srid" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:279 #, no-c-format msgid "" "CREATE TABLE ptzgeogwgs84(gid serial PRIMARY KEY, geog " "geography(POINTZ,4326) );" msgstr "" #. Tag: para #: using_postgis_dataman.xml:282 #, no-c-format msgid "LINESTRING" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:283 #, no-c-format msgid "" "CREATE TABLE lgeog(gid serial PRIMARY KEY, geog geography(LINESTRING) );" msgstr "" #. Tag: para #: using_postgis_dataman.xml:286 #, no-c-format msgid "POLYGON" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:287 #, no-c-format msgid "" "--polygon NAD 1927 long lat\n" "CREATE TABLE lgeognad27(gid serial PRIMARY KEY, geog " "geography(POLYGON,4267) );" msgstr "" #. Tag: para #: using_postgis_dataman.xml:290 #, no-c-format msgid "MULTIPOINT" msgstr "" #. Tag: para #: using_postgis_dataman.xml:293 #, no-c-format msgid "MULTILINESTRING" msgstr "" #. Tag: para #: using_postgis_dataman.xml:296 #, no-c-format msgid "MULTIPOLYGON" msgstr "" #. Tag: para #: using_postgis_dataman.xml:299 #, no-c-format msgid "GEOMETRYCOLLECTION" msgstr "" #. Tag: para #: using_postgis_dataman.xml:303 #, no-c-format msgid "" "The geography fields get registered in the geography_columns system view." msgstr "" #. Tag: para #: using_postgis_dataman.xml:305 #, no-c-format msgid "" "Now, check the \"geography_columns\" view and see that your table is listed." msgstr "" #. Tag: para #: using_postgis_dataman.xml:307 #, no-c-format msgid "" "You can create a new table with a GEOGRAPHY column using the CREATE TABLE " "syntax." msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:310 #, no-c-format msgid "" "CREATE TABLE global_points (\n" " id SERIAL PRIMARY KEY,\n" " name VARCHAR(64),\n" " location GEOGRAPHY(POINT,4326)\n" " );" msgstr "" #. Tag: para #: using_postgis_dataman.xml:313 #, no-c-format msgid "" "Note that the location column has type GEOGRAPHY and that geography type " "supports two optional modifiers: a type modifier that restricts the kind of " "shapes and dimensions allowed in the column; an SRID modifier that restricts " "the coordinate reference identifier to a particular number." msgstr "" #. Tag: para #: using_postgis_dataman.xml:314 #, no-c-format msgid "" "Allowable values for the type modifier are: POINT, LINESTRING, POLYGON, " "MULTIPOINT, MULTILINESTRING, MULTIPOLYGON. The modifier also supports " "dimensionality restrictions through suffixes: Z, M and ZM. So, for example a " "modifier of 'LINESTRINGM' would only allow line strings with three " "dimensions in, and would treat the third dimension as a measure. Similarly, " "'POINTZM' would expect four dimensional data." msgstr "" #. Tag: para #: using_postgis_dataman.xml:316 #, no-c-format msgid "" "If you do not specify an SRID, the SRID will default to 4326 WGS 84 long/lat " "will be used, and all calculations will proceed using WGS84." msgstr "" #. Tag: para #: using_postgis_dataman.xml:317 #, no-c-format msgid "" "Once you have created your table, you can see it in the GEOGRAPHY_COLUMNS " "table:" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:318 #, no-c-format msgid "" "-- See the contents of the metadata view\n" "SELECT * FROM geography_columns;" msgstr "" #. Tag: para #: using_postgis_dataman.xml:320 #, no-c-format msgid "" "You can insert data into the table the same as you would if it was using a " "GEOMETRY column:" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:322 #, no-c-format msgid "" "-- Add some data into the test table\n" "INSERT INTO global_points (name, location) VALUES ('Town', 'SRID=4326;" "POINT(-110 30)');\n" "INSERT INTO global_points (name, location) VALUES ('Forest', 'SRID=4326;" "POINT(-109 29)');\n" "INSERT INTO global_points (name, location) VALUES ('London', 'SRID=4326;" "POINT(0 49)');" msgstr "" #. Tag: para #: using_postgis_dataman.xml:324 #, no-c-format msgid "" "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." msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:327 #, no-c-format msgid "" "-- Index the test table with a spherical index\n" " CREATE INDEX global_points_gix ON global_points USING GIST ( location );" msgstr "" #. Tag: para #: using_postgis_dataman.xml:330 #, no-c-format msgid "" "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)." msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:332 #, no-c-format msgid "" "-- Show a distance query and note, London is outside the 1000km tolerance\n" " SELECT name FROM global_points WHERE ST_DWithin(location, 'SRID=4326;" "POINT(-110 29)'::geography, 1000000);" msgstr "" #. Tag: para #: using_postgis_dataman.xml:335 #, no-c-format msgid "" "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))." msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:337 #, no-c-format msgid "" "-- Distance calculation using GEOGRAPHY (122.2km)\n" " SELECT ST_Distance('LINESTRING(-122.33 47.606, 0.0 51.5)'::geography, " "'POINT(-21.96 64.15)'::geography);" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:340 #, no-c-format msgid "" "-- Distance calculation using GEOMETRY (13.3 \"degrees\")\n" " SELECT ST_Distance('LINESTRING(-122.33 47.606, 0.0 51.5)'::geometry, " "'POINT(-21.96 64.15)'::geometry);" msgstr "" #. Tag: para #: using_postgis_dataman.xml:343 #, no-c-format msgid "" "Testing different lon/lat projects. Any long lat spatial reference system " "listed in spatial_ref_sys table is allowed." msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:345 #, no-c-format msgid "" "-- NAD 83 lon/lat\n" "SELECT 'SRID=4269;POINT(-123 34)'::geography;\n" " geography\n" "----------------------------------------------------\n" " 0101000020AD1000000000000000C05EC00000000000004140\n" "(1 row)" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:347 #, no-c-format msgid "" "-- NAD27 lon/lat\n" "SELECT 'SRID=4267;POINT(-123 34)'::geography;\n" "\n" " geography\n" "----------------------------------------------------\n" " 0101000020AB1000000000000000C05EC00000000000004140\n" "(1 row)" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:349 #, no-c-format msgid "" "-- NAD83 UTM zone meters, yields error since its a meter based projection\n" "SELECT 'SRID=26910;POINT(-123 34)'::geography;\n" "\n" "ERROR: Only lon/lat coordinate systems are supported in geography.\n" "LINE 1: SELECT 'SRID=26910;POINT(-123 34)'::geography;" msgstr "" #. Tag: para #: using_postgis_dataman.xml:351 #, no-c-format msgid "" "The GEOGRAPHY type calculates the true shortest distance over the sphere " "between Reykjavik and the great circle flight path between Seattle and " "London." msgstr "" #. Tag: para #: using_postgis_dataman.xml:353 #, no-c-format msgid "" "Great Circle " "mapper The GEOMETRY type calculates a meaningless cartesian distance " "between Reykjavik and the straight line path from Seattle to London plotted " "on a flat map of the world. The nominal units of the result might be called " "\"degrees\", but the result doesn't correspond to any true angular " "difference between the points, so even calling them \"degrees\" is " "inaccurate." msgstr "" #. Tag: title #: using_postgis_dataman.xml:357 #, no-c-format msgid "When to use Geography Data type over Geometry data type" msgstr "" #. Tag: para #: using_postgis_dataman.xml:358 #, no-c-format msgid "" "The geography type allows you to store data in longitude/latitude " "coordinates, but at a cost: there are fewer functions defined on GEOGRAPHY " "than there are on GEOMETRY; those functions that are defined take more CPU " "time to execute." msgstr "" #. Tag: para #: using_postgis_dataman.xml:359 #, no-c-format msgid "" "The type you choose should be conditioned on the expected working area of " "the application you are building. Will your data span the globe or a large " "continental area, or is it local to a state, county or municipality?" msgstr "" #. Tag: para #: using_postgis_dataman.xml:361 #, no-c-format msgid "" "If your data is contained in a small area, you might find that choosing an " "appropriate projection and using GEOMETRY is the best solution, in terms of " "performance and functionality available." msgstr "" #. Tag: para #: using_postgis_dataman.xml:362 #, no-c-format msgid "" "If your data is global or covers a continental region, you may find that " "GEOGRAPHY allows you to build a system without having to worry about " "projection details. You store your data in longitude/latitude, and use the " "functions that have been defined on GEOGRAPHY." msgstr "" #. Tag: para #: using_postgis_dataman.xml:364 #, no-c-format msgid "" "If you don't understand projections, and you don't want to learn about them, " "and you're prepared to accept the limitations in functionality available in " "GEOGRAPHY, then it might be easier for you to use GEOGRAPHY than GEOMETRY. " "Simply load your data up as longitude/latitude and go from there." msgstr "" #. Tag: para #: using_postgis_dataman.xml:367 #, no-c-format msgid "" "Refer to for compare between " "what is supported for Geography vs. Geometry. For a brief listing and " "description of Geography functions, refer to " msgstr "" #. Tag: title #: using_postgis_dataman.xml:373 #, no-c-format msgid "Geography Advanced FAQ" msgstr "" #. Tag: para #: using_postgis_dataman.xml:377 #, no-c-format msgid "Do you calculate on the sphere or the spheroid?" msgstr "" #. Tag: para #: using_postgis_dataman.xml:381 #, no-c-format msgid "" "By default, all distance and area calculations are done on the spheroid. You " "should find that the results of calculations in local areas match up will " "with local planar results in good local projections. Over larger areas, the " "spheroidal calculations will be more accurate than any calculation done on a " "projected plane." msgstr "" #. Tag: para #: using_postgis_dataman.xml:384 #, no-c-format msgid "" "All the geography functions have the option of using a sphere calculation, " "by setting a final boolean parameter to 'FALSE'. This will somewhat speed up " "calculations, particularly for cases where the geometries are very simple." msgstr "" #. Tag: para #: using_postgis_dataman.xml:390 #, no-c-format msgid "What about the date-line and the poles?" msgstr "" #. Tag: para #: using_postgis_dataman.xml:394 #, no-c-format msgid "" "All the calculations have no conception of date-line or poles, the " "coordinates are spherical (longitude/latitude) so a shape that crosses the " "dateline is, from a calculation point of view, no different from any other " "shape." msgstr "" #. Tag: para #: using_postgis_dataman.xml:402 #, no-c-format msgid "What is the longest arc you can process?" msgstr "" #. Tag: para #: using_postgis_dataman.xml:406 #, no-c-format msgid "" "We use great circle arcs as the \"interpolation line\" between two points. " "That means any two points are actually joined up two ways, depending on " "which direction you travel along the great circle. All our code assumes that " "the points are joined by the *shorter* of the two paths along the great " "circle. As a consequence, shapes that have arcs of more than 180 degrees " "will not be correctly modelled." msgstr "" #. Tag: para #: using_postgis_dataman.xml:413 #, no-c-format msgid "" "Why is it so slow to calculate the area of Europe / Russia / insert big " "geographic region here ?" msgstr "" #. Tag: para #: using_postgis_dataman.xml:417 #, no-c-format msgid "" "Because the polygon is so darned huge! Big areas are bad for two reasons: " "their bounds are huge, so the index tends to pull the feature no matter what " "query you run; the number of vertices is huge, 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)." msgstr "" #. Tag: para #: using_postgis_dataman.xml:422 #, no-c-format msgid "" "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 function documentation. Just " "because you *can* store all of Europe in one polygon doesn't mean you " "*should*." msgstr "" #. Tag: title #: using_postgis_dataman.xml:431 #, no-c-format msgid "Using OpenGIS Standards" msgstr "" #. Tag: para #: using_postgis_dataman.xml:433 #, no-c-format msgid "" "The OpenGIS \"Simple Features Specification for SQL\" defines standard GIS " "object types, the functions required to manipulate them, and a set of meta-" "data tables. In order to ensure that meta-data remain consistent, operations " "such as creating and removing a spatial column are carried out through " "special procedures defined by OpenGIS." msgstr "" #. Tag: para #: using_postgis_dataman.xml:439 #, no-c-format msgid "" "There are two OpenGIS meta-data tables: SPATIAL_REF_SYS " "and GEOMETRY_COLUMNS. The SPATIAL_REF_SYS table holds the numeric IDs and textual descriptions of coordinate " "systems used in the spatial database." msgstr "" #. Tag: title #: using_postgis_dataman.xml:446 #, no-c-format msgid "The SPATIAL_REF_SYS Table and Spatial Reference Systems" msgstr "" #. Tag: para #: using_postgis_dataman.xml:448 #, no-c-format msgid "" "The spatial_ref_sys table is a PostGIS included and OGC compliant database " "table that lists over 3000 known spatial reference systems and details needed to transform/reproject between them." msgstr "" #. Tag: para #: using_postgis_dataman.xml:452 #, no-c-format msgid "" "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." msgstr "" #. Tag: para #: using_postgis_dataman.xml:454 #, no-c-format msgid "" "An excellent resource for finding spatial reference systems not defined in " "the core set is http://" "spatialreference.org/" msgstr "" #. Tag: para #: using_postgis_dataman.xml:456 #, no-c-format msgid "" "Some of the more commonly used spatial reference systems are: 4326 - WGS 84 Long Lat, 4269 - NAD " "83 Long Lat, 3395 - WGS 84 World Mercator, 2163 - US National Atlas Equal Area, Spatial reference systems for each NAD 83, WGS 84 UTM zone - UTM " "zones are one of the most ideal for measurement, but only cover 6-degree " "regions." msgstr "" #. Tag: para #: using_postgis_dataman.xml:462 #, no-c-format msgid "" "Various US state plane spatial reference systems (meter or feet based) - " "usually one or 2 exists per US state. Most of the meter ones are in the core " "set, but many of the feet based ones or ESRI created ones you will need to " "pull from spatialreference.org." msgstr "" #. Tag: para #: using_postgis_dataman.xml:466 #, no-c-format msgid "" "For details on determining which UTM zone to use for your area of interest, " "check out the utmzone PostGIS plpgsql helper function." msgstr "" #. Tag: para #: using_postgis_dataman.xml:470 #, no-c-format msgid "The SPATIAL_REF_SYS table definition is as follows:" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:473 #, no-c-format msgid "" "CREATE TABLE spatial_ref_sys (\n" " srid INTEGER NOT NULL PRIMARY KEY,\n" " auth_name VARCHAR(256),\n" " auth_srid INTEGER,\n" " srtext VARCHAR(2048),\n" " proj4text VARCHAR(2048)\n" ")" msgstr "" #. Tag: para #: using_postgis_dataman.xml:475 #, no-c-format msgid "The SPATIAL_REF_SYS columns are as follows:" msgstr "" #. Tag: ulink #: using_postgis_dataman.xml:480 #, no-c-format msgid "SRID" msgstr "" #. Tag: para #: using_postgis_dataman.xml:483 #, no-c-format msgid "" "An integer value that uniquely identifies the Spatial Referencing System " "(SRS) within the database." msgstr "" #. Tag: term #: using_postgis_dataman.xml:489 #, no-c-format msgid "AUTH_NAME" msgstr "" #. Tag: para #: using_postgis_dataman.xml:492 #, no-c-format msgid "" "The name of the standard or standards body that is being cited for this " "reference system. For example, \"EPSG\" would be a valid AUTH_NAME." msgstr "" #. Tag: term #: using_postgis_dataman.xml:499 #, no-c-format msgid "AUTH_SRID" msgstr "" #. Tag: para #: using_postgis_dataman.xml:502 #, no-c-format msgid "" "The ID of the Spatial Reference System as defined by the Authority cited in " "the AUTH_NAME. In the case of EPSG, this is where the " "EPSG projection code would go." msgstr "" #. Tag: term #: using_postgis_dataman.xml:509 #, no-c-format msgid "SRTEXT" msgstr "" #. Tag: para #: using_postgis_dataman.xml:512 #, no-c-format msgid "" "The Well-Known Text representation of the Spatial Reference System. An " "example of a WKT SRS representation is:" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:515 #, no-c-format msgid "" "PROJCS[\"NAD83 / UTM Zone 10N\",\n" " GEOGCS[\"NAD83\",\n" " DATUM[\"North_American_Datum_1983\",\n" " SPHEROID[\"GRS 1980\",6378137,298.257222101]\n" " ],\n" " PRIMEM[\"Greenwich\",0],\n" " UNIT[\"degree\",0.0174532925199433]\n" " ],\n" " PROJECTION[\"Transverse_Mercator\"],\n" " PARAMETER[\"latitude_of_origin\",0],\n" " PARAMETER[\"central_meridian\",-123],\n" " PARAMETER[\"scale_factor\",0.9996],\n" " PARAMETER[\"false_easting\",500000],\n" " PARAMETER[\"false_northing\",0],\n" " UNIT[\"metre\",1]\n" "]" msgstr "" #. Tag: para #: using_postgis_dataman.xml:517 #, no-c-format msgid "" "For a listing of EPSG projection codes and their corresponding WKT " "representations, see http://" "www.opengeospatial.org/. For a discussion of WKT in general, see the " "OpenGIS \"Coordinate Transformation Services Implementation Specification\" " "at http://www." "opengeospatial.org/standards. For information on the European " "Petroleum Survey Group (EPSG) and their database of spatial reference " "systems, see http://www.epsg.org." msgstr "" #. Tag: term #: using_postgis_dataman.xml:530 #, no-c-format msgid "PROJ4TEXT" msgstr "" #. Tag: para #: using_postgis_dataman.xml:533 #, no-c-format msgid "" "PostGIS uses the Proj4 library to provide coordinate transformation " "capabilities. The PROJ4TEXT column contains the Proj4 " "coordinate definition string for a particular SRID. For example:" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:538 #, no-c-format msgid "+proj=utm +zone=10 +ellps=clrk66 +datum=NAD27 +units=m" msgstr "" #. Tag: para #: using_postgis_dataman.xml:540 #, no-c-format msgid "" "For more information about, see the Proj4 web site at http://trac.osgeo.org/proj/. The " "spatial_ref_sys.sql file contains both SRTEXT and PROJ4TEXT definitions for all EPSG " "projections." msgstr "" #. Tag: title #: using_postgis_dataman.xml:551 #, no-c-format msgid "The GEOMETRY_COLUMNS VIEW" msgstr "" #. Tag: para #: using_postgis_dataman.xml:553 #, no-c-format msgid "" "GEOMETRY_COLUMNS is a view reading from database system " "catalogs. Its structure is as follows:" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:556 #, no-c-format msgid "\\d geometry_columns" msgstr "" #. Tag: screen #: using_postgis_dataman.xml:557 #, no-c-format msgid "" "View \"public.geometry_columns\"\n" " Column | Type | Modifiers\n" "-------------------+------------------------+-----------\n" " f_table_catalog | character varying(256) |\n" " f_table_schema | character varying(256) |\n" " f_table_name | character varying(256) |\n" " f_geometry_column | character varying(256) |\n" " coord_dimension | integer |\n" " srid | integer |\n" " type | character varying(30) |" msgstr "" #. Tag: para #: using_postgis_dataman.xml:559 #, no-c-format msgid "The column meanings are:" msgstr "" #. Tag: term #: using_postgis_dataman.xml:563 #, no-c-format msgid "F_TABLE_CATALOG, F_TABLE_SCHEMA, F_TABLE_NAME" msgstr "" #. Tag: para #: using_postgis_dataman.xml:566 #, no-c-format msgid "" "The fully qualified name of the feature table containing the geometry " "column. Note that the terms \"catalog\" and \"schema\" are Oracle-ish. There " "is not PostgreSQL analogue of \"catalog\" so that column is left blank -- " "for \"schema\" the PostgreSQL schema name is used (public " "is the default)." msgstr "" #. Tag: term #: using_postgis_dataman.xml:575 #, no-c-format msgid "F_GEOMETRY_COLUMN" msgstr "" #. Tag: para #: using_postgis_dataman.xml:578 #, no-c-format msgid "The name of the geometry column in the feature table." msgstr "" #. Tag: term #: using_postgis_dataman.xml:583 #, no-c-format msgid "COORD_DIMENSION" msgstr "" #. Tag: para #: using_postgis_dataman.xml:586 #, no-c-format msgid "The spatial dimension (2, 3 or 4 dimensional) of the column." msgstr "" #. Tag: term #: using_postgis_dataman.xml:592 #, no-c-format msgid "SRID" msgstr "" #. Tag: para #: using_postgis_dataman.xml:595 #, no-c-format msgid "" "The ID of the spatial reference system used for the coordinate geometry in " "this table. It is a foreign key reference to the SPATIAL_REF_SYS." msgstr "" #. Tag: term #: using_postgis_dataman.xml:602 #, no-c-format msgid "TYPE" msgstr "" #. Tag: para #: using_postgis_dataman.xml:605 #, no-c-format msgid "" "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." msgstr "" #. Tag: para #: using_postgis_dataman.xml:614 #, no-c-format msgid "" "This attribute is (probably) not part of the OpenGIS specification, but is " "required for ensuring type homogeneity." msgstr "" #. Tag: title #: using_postgis_dataman.xml:624 #, no-c-format msgid "Creating a Spatial Table" msgstr "" #. Tag: para #: using_postgis_dataman.xml:626 #, no-c-format msgid "" "Creating a table with spatial data, can be done in one step. As shown in the " "following example which creates a roads table with a 2D linestring geometry " "column in WGS84 long lat" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:628 #, no-c-format msgid "" "CREATE TABLE ROADS (ID serial, ROAD_NAME text, geom " "geometry(LINESTRING,4326) );" msgstr "" #. Tag: para #: using_postgis_dataman.xml:630 #, no-c-format msgid "" "We can add additional columns using standard ALTER TABLE command as we do in " "this next example where we add a 3-D linestring." msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:631 #, no-c-format msgid "ALTER TABLE roads ADD COLUMN geom2 geometry(LINESTRINGZ,4326);" msgstr "" #. Tag: title #: using_postgis_dataman.xml:635 #, no-c-format msgid "Manually Registering Geometry Columns in geometry_columns" msgstr "" #. Tag: para #: using_postgis_dataman.xml:637 #, no-c-format msgid "" "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." msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:642 #, no-c-format msgid "" "-- Lets say you have a view created like this\n" "CREATE VIEW public.vwmytablemercator AS\n" " SELECT gid, ST_Transform(geom, 3395) As geom, f_name\n" " FROM public.mytable;\n" "\n" "-- For it to register correctly\n" "-- You need to cast the geometry\n" "--\n" "DROP VIEW public.vwmytablemercator;\n" "CREATE VIEW public.vwmytablemercator AS\n" " SELECT gid, ST_Transform(geom, 3395)::geometry(Geometry, 3395) As " "geom, f_name\n" " FROM public.mytable;\n" "\n" "-- If you know the geometry type for sure is a 2D POLYGON then you could do\n" "DROP VIEW public.vwmytablemercator;\n" "CREATE VIEW public.vwmytablemercator AS\n" " SELECT gid, ST_Transform(geom,3395)::geometry(Polygon, 3395) As " "geom, f_name\n" " FROM public.mytable;" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:643 #, no-c-format msgid "" "--Lets say you created a derivative table by doing a bulk insert\n" "SELECT poi.gid, poi.geom, citybounds.city_name\n" "INTO myschema.my_special_pois\n" "FROM poi INNER JOIN citybounds ON ST_Intersects(citybounds.geom, poi.geom);\n" "\n" "-- Create 2D index on new table\n" "CREATE INDEX idx_myschema_myspecialpois_geom_gist\n" " ON myschema.my_special_pois USING gist(geom);\n" "\n" "-- If your points are 3D points or 3M points,\n" "-- then you might want to create an nd index instead of a 2D index\n" "CREATE INDEX my_special_pois_geom_gist_nd\n" " ON my_special_pois USING gist(geom gist_geometry_ops_nd);\n" "\n" "-- To manually register this new table's geometry column in " "geometry_columns.\n" "-- Note it will also change the underlying structure of the table to\n" "-- to make the column typmod based.\n" "SELECT populate_geometry_columns('myschema.my_special_pois'::regclass);\n" "\n" "-- If you are using PostGIS 2.0 and for whatever reason, you\n" "-- you need the constraint based definition behavior\n" "-- (such as case of inherited tables where all children do not have the same " "type and srid)\n" "-- set optional use_typmod argument to false\n" "SELECT populate_geometry_columns('myschema.my_special_pois'::regclass, " "false);" msgstr "" #. Tag: para #: using_postgis_dataman.xml:645 #, no-c-format msgid "" "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." msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:648 #, no-c-format msgid "" "CREATE TABLE pois_ny(gid SERIAL PRIMARY KEY, poi_name text, cat text, geom " "geometry(POINT,4326));\n" "SELECT AddGeometryColumn('pois_ny', 'geom_2160', 2160, 'POINT', 2, false);" msgstr "" #. Tag: para #: using_postgis_dataman.xml:649 #, no-c-format msgid "If we run in psql" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:650 #, no-c-format msgid "\\d pois_ny;" msgstr "" #. Tag: para #: using_postgis_dataman.xml:651 #, no-c-format msgid "" "We observe they are defined differently -- one is typmod, one is constraint" msgstr "" #. Tag: screen #: using_postgis_dataman.xml:652 #, no-c-format msgid "" "Table \"public.pois_ny\"\n" " Column | Type | Modifiers\n" "\n" "-----------+-----------------------" "+------------------------------------------------------\n" " gid | integer | not null default " "nextval('pois_ny_gid_seq'::regclass)\n" " poi_name | text |\n" " cat | character varying(20) |\n" " geom | geometry(Point,4326) |\n" " geom_2160 | geometry |\n" "Indexes:\n" " \"pois_ny_pkey\" PRIMARY KEY, btree (gid)\n" "Check constraints:\n" " \"enforce_dims_geom_2160\" CHECK (st_ndims(geom_2160) = 2)\n" " \"enforce_geotype_geom_2160\" CHECK (geometrytype(geom_2160) = 'POINT'::" "text\n" " OR geom_2160 IS NULL)\n" " \"enforce_srid_geom_2160\" CHECK (st_srid(geom_2160) = 2160)" msgstr "" #. Tag: para #: using_postgis_dataman.xml:653 #, no-c-format msgid "In geometry_columns, they both register correctly" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:654 #, no-c-format msgid "" "SELECT f_table_name, f_geometry_column, srid, type\n" " FROM geometry_columns\n" " WHERE f_table_name = 'pois_ny';" msgstr "" #. Tag: screen #: using_postgis_dataman.xml:655 #, no-c-format msgid "" "f_table_name | f_geometry_column | srid | type\n" "-------------+-------------------+------+-------\n" "pois_ny | geom | 4326 | POINT\n" "pois_ny | geom_2160 | 2160 | POINT" msgstr "" #. Tag: para #: using_postgis_dataman.xml:656 #, no-c-format msgid "However -- if we were to create a view like this" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:657 #, no-c-format msgid "" "CREATE VIEW vw_pois_ny_parks AS\n" "SELECT *\n" " FROM pois_ny\n" " WHERE cat='park';\n" "\n" "SELECT f_table_name, f_geometry_column, srid, type\n" " FROM geometry_columns\n" " WHERE f_table_name = 'vw_pois_ny_parks';" msgstr "" #. Tag: para #: using_postgis_dataman.xml:658 #, no-c-format msgid "" "The typmod based geom view column registers correctly, but the constraint " "based one does not." msgstr "" #. Tag: screen #: using_postgis_dataman.xml:660 #, no-c-format msgid "" "f_table_name | f_geometry_column | srid | type\n" "------------------+-------------------+------+----------\n" " vw_pois_ny_parks | geom | 4326 | POINT\n" " vw_pois_ny_parks | geom_2160 | 0 | GEOMETRY" msgstr "" #. Tag: para #: using_postgis_dataman.xml:662 #, no-c-format msgid "" "This may change in future versions of PostGIS, but for now To force the " "constraint based view column to register correctly, we need to do this:" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:664 #, no-c-format msgid "" "DROP VIEW vw_pois_ny_parks;\n" "CREATE VIEW vw_pois_ny_parks AS\n" "SELECT gid, poi_name, cat,\n" " geom,\n" " geom_2160::geometry(POINT,2160) As geom_2160\n" " FROM pois_ny\n" " WHERE cat = 'park';\n" "SELECT f_table_name, f_geometry_column, srid, type\n" " FROM geometry_columns\n" " WHERE f_table_name = 'vw_pois_ny_parks';" msgstr "" #. Tag: screen #: using_postgis_dataman.xml:665 #, no-c-format msgid "" "f_table_name | f_geometry_column | srid | type\n" "------------------+-------------------+------+-------\n" " vw_pois_ny_parks | geom | 4326 | POINT\n" " vw_pois_ny_parks | geom_2160 | 2160 | POINT" msgstr "" #. Tag: title #: using_postgis_dataman.xml:669 #, no-c-format msgid "Ensuring OpenGIS compliancy of geometries" msgstr "" #. Tag: para #: using_postgis_dataman.xml:671 #, no-c-format msgid "" "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." msgstr "" #. Tag: para #: using_postgis_dataman.xml:678 #, no-c-format msgid "" "According to the OGC Specifications, a simple 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. " "[MULTI]POINT, [MULTI]LINESTRING). Geometry validity, on " "the other hand, primarily refers to 2-dimensional geometries (i.e. " "[MULTI]POLYGON) 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." msgstr "" #. Tag: para #: using_postgis_dataman.xml:688 #, no-c-format msgid "" "A POINT is inheritably simple as a 0-" "dimensional geometry object." msgstr "" #. Tag: para #: using_postgis_dataman.xml:691 #, no-c-format msgid "" "MULTIPOINTs are simple if no two " "coordinates (POINTs) are equal (have identical coordinate " "values)." msgstr "" #. Tag: para #: using_postgis_dataman.xml:695 #, no-c-format msgid "" "A LINESTRING is simple 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 closed)." msgstr "" #. Tag: emphasis #: using_postgis_dataman.xml:710 #, no-c-format msgid "(a)" msgstr "" #. Tag: emphasis #: using_postgis_dataman.xml:720 #, no-c-format msgid "(b)" msgstr "" #. Tag: emphasis #: using_postgis_dataman.xml:732 #, no-c-format msgid "(c)" msgstr "" #. Tag: emphasis #: using_postgis_dataman.xml:742 #, no-c-format msgid "(d)" msgstr "" #. Tag: para #: using_postgis_dataman.xml:752 #, no-c-format msgid "" "(a) and (c) are simple LINESTRINGs, (b) and (d) are not." msgstr "" #. Tag: para #: using_postgis_dataman.xml:761 #, no-c-format msgid "" "A MULTILINESTRING is simple 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." msgstr "" #. Tag: emphasis #: using_postgis_dataman.xml:776 #, no-c-format msgid "(e)" msgstr "" #. Tag: emphasis #: using_postgis_dataman.xml:786 #, no-c-format msgid "(f)" msgstr "" #. Tag: emphasis #: using_postgis_dataman.xml:796 #, no-c-format msgid "(g)" msgstr "" #. Tag: para #: using_postgis_dataman.xml:806 #, no-c-format msgid "" "(e) and (f) are simple MULTILINESTRINGs, (g) is not." msgstr "" #. Tag: para #: using_postgis_dataman.xml:815 #, no-c-format msgid "" "By definition, a POLYGON is always simple. It is valid if no two rings in the boundary " "(made up of an exterior ring and interior rings) cross. The boundary of a " "POLYGON may intersect at a POINT but " "only as a tangent (i.e. not on a line). A POLYGON may not " "have cut lines or spikes and the interior rings must be contained entirely " "within the exterior ring." msgstr "" #. Tag: emphasis #: using_postgis_dataman.xml:833 #, no-c-format msgid "(h)" msgstr "" #. Tag: emphasis #: using_postgis_dataman.xml:843 #, no-c-format msgid "(i)" msgstr "" #. Tag: emphasis #: using_postgis_dataman.xml:853 #, no-c-format msgid "(j)" msgstr "" #. Tag: emphasis #: using_postgis_dataman.xml:865 #, no-c-format msgid "(k)" msgstr "" #. Tag: emphasis #: using_postgis_dataman.xml:875 #, no-c-format msgid "(l)" msgstr "" #. Tag: emphasis #: using_postgis_dataman.xml:885 #, no-c-format msgid "(m)" msgstr "" #. Tag: para #: using_postgis_dataman.xml:894 #, no-c-format msgid "" "(h) and (i) are valid POLYGONs, (j-" "m) cannot be represented as single POLYGONs, " "but (j) and (m) could be represented as a valid MULTIPOLYGON." msgstr "" #. Tag: para #: using_postgis_dataman.xml:906 #, no-c-format msgid "" "A MULTIPOLYGON is valid 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 only at a " "finite number of POINTs." msgstr "" #. Tag: emphasis #: using_postgis_dataman.xml:921 #, no-c-format msgid "(n)" msgstr "" #. Tag: emphasis #: using_postgis_dataman.xml:931 #, no-c-format msgid "(o)" msgstr "" #. Tag: emphasis #: using_postgis_dataman.xml:941 #, no-c-format msgid "(p)" msgstr "" #. Tag: para #: using_postgis_dataman.xml:950 #, no-c-format msgid "" "(n) and (o) are not valid MULTIPOLYGONs. (p), however, is valid." msgstr "" #. Tag: para #: using_postgis_dataman.xml:959 #, no-c-format msgid "" "Most of the functions implemented by the GEOS library rely on the assumption " "that your geometries are valid as specified by the OpenGIS Simple Feature " "Specification. To check simplicity or validity of geometries you can use the " "ST_IsSimple() and ST_IsValid()" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:965 #, no-c-format msgid "" "-- Typically, it doesn't make sense to check\n" "-- for validity on linear features since it will always return TRUE.\n" "-- But in this example, PostGIS extends the definition of the OGC IsValid\n" "-- by returning false if a LineString has less than 2 *distinct* vertices.\n" "gisdb=# SELECT\n" " ST_IsValid('LINESTRING(0 0, 1 1)'),\n" " ST_IsValid('LINESTRING(0 0, 0 0, 0 0)');\n" "\n" " st_isvalid | st_isvalid\n" "------------+-----------\n" " t | f" msgstr "" #. Tag: para #: using_postgis_dataman.xml:967 #, no-c-format msgid "" "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:" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:973 #, no-c-format msgid "" "ALTER TABLE mytable\n" " ADD CONSTRAINT geometry_valid_check\n" " CHECK (ST_IsValid(the_geom));" msgstr "" #. Tag: para #: using_postgis_dataman.xml:975 #, no-c-format msgid "" "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." msgstr "" #. Tag: para #: using_postgis_dataman.xml:983 #, no-c-format msgid "" "Strictly compliant OGC geometries cannot have Z or M values. The ST_IsValid() function won't consider higher " "dimensioned geometries invalid! Invocations of AddGeometryColumn() will add a constraint " "checking geometry dimensions, so it is enough to specify 2 there." msgstr "" #. Tag: title #: using_postgis_dataman.xml:993 #, no-c-format msgid "Dimensionally Extended 9 Intersection Model (DE-9IM)" msgstr "" #. Tag: para #: using_postgis_dataman.xml:995 #, no-c-format msgid "" "It is sometimes the case that the typical spatial predicates (, , , , ...) are insufficient in " "and of themselves to adequately provide that desired spatial filter." msgstr "" #. Tag: para #: using_postgis_dataman.xml:1007 #, no-c-format msgid "" "For example, consider a linear dataset representing a road network. It may " "be the task of a GIS analyst to identify all road segments that cross each " "other, not at a point, but on a line, perhaps invalidating some business " "rule. In this case, does not adequately " "provide the necessary spatial filter since, for linear features, it returns " "true only where they cross at a point." msgstr "" #. Tag: para #: using_postgis_dataman.xml:1014 #, no-c-format msgid "" "One two-step solution might be to first perform the actual intersection " "() of pairs of road segments that " "spatially intersect (), and then compare " "the intersection's with " "'LINESTRING' (properly dealing with cases that return " "GEOMETRYCOLLECTIONs of [MULTI]POINTs, " "[MULTI]LINESTRINGs, etc.)." msgstr "" #. Tag: para #: using_postgis_dataman.xml:1022 #, no-c-format msgid "A more elegant / faster solution may indeed be desirable." msgstr "" #. Tag: para #: using_postgis_dataman.xml:1036 #, no-c-format msgid "" "A second [theoretical] example may be that of a GIS analyst trying to locate " "all wharfs or docks that intersect a lake's boundary on a line and where " "only one end of the wharf is up on shore. In other words, where a wharf is " "within, but not completely within a lake, intersecting the boundary of a " "lake on a line, and where the wharf's endpoints are both completely within " "and on the boundary of the lake. The analyst may need to use a combination " "of spatial predicates to isolate the sought after features:" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1047 #, no-c-format msgid "(lake, wharf) = TRUE" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1051 #, no-c-format msgid "(lake, wharf) = FALSE" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1055 #, no-c-format msgid "((wharf, lake)) = 'LINESTRING'" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1060 #, no-c-format msgid "" "((((wharf), (lake)))) = " "1" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1063 #, no-c-format msgid "... (needless to say, this could get quite complicated)" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1072 #, no-c-format msgid "" "So enters the Dimensionally Extended 9 Intersection Model, or DE-9IM for " "short." msgstr "" #. Tag: title #: using_postgis_dataman.xml:1076 #, no-c-format msgid "Theory" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1078 #, no-c-format msgid "" "According to the OpenGIS Simple Features Implementation Specification for SQL, " "\"the basic approach to comparing two geometries is to make pair-wise tests " "of the intersections between the Interiors, Boundaries and Exteriors of the " "two geometries and to classify the relationship between the two geometries " "based on the entries in the resulting 'intersection' matrix.\"" msgstr "" #. Tag: glossterm #: using_postgis_dataman.xml:1089 #, no-c-format msgid "Boundary" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1092 #, no-c-format msgid "" "The boundary of a geometry is the set of geometries of the next lower " "dimension. For POINTs, which have a dimension of 0, the " "boundary is the empty set. The boundary of a LINESTRING " "are the two endpoints. For POLYGONs, the boundary is the " "linework that make up the exterior and interior rings." msgstr "" #. Tag: glossterm #: using_postgis_dataman.xml:1103 #, no-c-format msgid "Interior" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1106 #, no-c-format msgid "" "The interior of a geometry are those points of a geometry that are left when " "the boundary is removed. For POINTs, the interior is the " "POINT itself. The interior of a LINESTRING are the set of real points between the endpoints. For " "POLYGONs, the interior is the areal surface inside the " "polygon." msgstr "" #. Tag: glossterm #: using_postgis_dataman.xml:1117 #, no-c-format msgid "Exterior" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1120 #, no-c-format msgid "" "The exterior of a geometry is the universe, an areal surface, not on the " "interior or boundary of the geometry." msgstr "" #. Tag: para #: using_postgis_dataman.xml:1127 #, no-c-format msgid "" "Given geometry a, where the I(a), " "B(a), and E(a) are the " "Interior, Boundary, and " "Exterior of a, the mathematical representation of the " "matrix is:" msgstr "" #. Tag: emphasis #: using_postgis_dataman.xml:1139 using_postgis_dataman.xml:1149 #: using_postgis_dataman.xml:1233 using_postgis_dataman.xml:1246 #, no-c-format msgid "Interior" msgstr "" #. Tag: emphasis #: using_postgis_dataman.xml:1141 using_postgis_dataman.xml:1155 #: using_postgis_dataman.xml:1236 using_postgis_dataman.xml:1269 #, no-c-format msgid "Boundary" msgstr "" #. Tag: emphasis #: using_postgis_dataman.xml:1143 using_postgis_dataman.xml:1161 #: using_postgis_dataman.xml:1239 using_postgis_dataman.xml:1292 #, no-c-format msgid "Exterior" msgstr "" #. Tag: emphasis #: using_postgis_dataman.xml:1150 #, no-c-format msgid "dim( I(a) ∩ I(b) )" msgstr "" #. Tag: emphasis #: using_postgis_dataman.xml:1151 #, no-c-format msgid "dim( I(a) ∩ B(b) )" msgstr "" #. Tag: emphasis #: using_postgis_dataman.xml:1152 #, no-c-format msgid "dim( I(a) ∩ E(b) )" msgstr "" #. Tag: emphasis #: using_postgis_dataman.xml:1156 #, no-c-format msgid "dim( B(a) ∩ I(b) )" msgstr "" #. Tag: emphasis #: using_postgis_dataman.xml:1157 #, no-c-format msgid "dim( B(a) ∩ B(b) )" msgstr "" #. Tag: emphasis #: using_postgis_dataman.xml:1158 #, no-c-format msgid "dim( B(a) ∩ E(b) )" msgstr "" #. Tag: emphasis #: using_postgis_dataman.xml:1162 #, no-c-format msgid "dim( E(a) ∩ I(b) )" msgstr "" #. Tag: emphasis #: using_postgis_dataman.xml:1163 #, no-c-format msgid "dim( E(a) ∩ B(b) )" msgstr "" #. Tag: emphasis #: using_postgis_dataman.xml:1164 #, no-c-format msgid "dim( E(a) ∩ E(b) )" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1171 #, no-c-format msgid "" "Where dim(a) is the dimension of a " "as specified by but has the domain of " "{0,1,2,T,F,*}" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1178 #, no-c-format msgid "0 => point" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1182 #, no-c-format msgid "1 => line" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1186 #, no-c-format msgid "2 => area" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1190 #, no-c-format msgid "T => {0,1,2}" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1195 #, no-c-format msgid "F => empty set" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1199 #, no-c-format msgid "* => don't care" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1203 #, no-c-format msgid "Visually, for two overlapping polygonal geometries, this looks like:" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1250 using_postgis_dataman.xml:1262 #: using_postgis_dataman.xml:1296 using_postgis_dataman.xml:1308 #, no-c-format msgid "dim(...) = 2" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1256 using_postgis_dataman.xml:1273 #: using_postgis_dataman.xml:1285 using_postgis_dataman.xml:1302 #, no-c-format msgid "dim(...) = 1" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1279 #, no-c-format msgid "dim(...) = 0" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1320 #, no-c-format msgid "" "Read from left to right and from top to bottom, the dimensional matrix is " "represented, '212101212'." msgstr "" #. Tag: para #: using_postgis_dataman.xml:1323 #, no-c-format msgid "" "A relate matrix that would therefore represent our first example of two " "lines that intersect on a line would be: '1*1***1**'" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:1327 #, no-c-format msgid "" "-- Identify road segments that cross on a line\n" "SELECT a.id\n" "FROM roads a, roads b\n" "WHERE a.id != b.id\n" "AND a.geom && b.geom\n" "AND ST_Relate(a.geom, b.geom, '1*1***1**');" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1329 #, no-c-format msgid "" "A relate matrix that represents the second example of wharfs partly on the " "lake's shoreline would be '102101FF2'" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:1333 #, no-c-format msgid "" "-- Identify wharfs partly on a lake's shoreline\n" "SELECT a.lake_id, b.wharf_id\n" "FROM lakes a, wharfs b\n" "WHERE a.geom && b.geom\n" "AND ST_Relate(a.geom, b.geom, '102101FF2');" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1335 #, no-c-format msgid "For more information or reading, see:" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1339 #, no-c-format msgid "" "OpenGIS Simple " "Features Implementation Specification for SQL (version 1.1, section " "2.1.13.2)" msgstr "" #. Tag: ulink #: using_postgis_dataman.xml:1344 #, no-c-format msgid "Dimensionally Extended Nine-Intersection Model (DE-9IM)" msgstr "" #. Tag: ulink #: using_postgis_dataman.xml:1348 #, no-c-format msgid "GeoTools: Point Set Theory and the DE-9IM Matrix" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1351 #, no-c-format msgid "Encyclopedia of GIS By Hui Xiong" msgstr "" #. Tag: title #: using_postgis_dataman.xml:1361 #, no-c-format msgid "Loading GIS (Vector) Data" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1363 #, no-c-format msgid "" "Once you have created a spatial table, you are ready to upload GIS data to " "the database. Currently, there are two ways to get data into a PostGIS/" "PostgreSQL database: using formatted SQL statements or using the Shape file " "loader/dumper." msgstr "" #. Tag: title #: using_postgis_dataman.xml:1369 #, no-c-format msgid "Loading Data Using SQL" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1371 #, no-c-format msgid "" "If you can convert your data to a text representation, then using formatted " "SQL might be the easiest way to get your data into PostGIS. As with Oracle " "and other SQL databases, data can be bulk loaded by piping a large text file " "full of SQL \"INSERT\" statements into the SQL terminal monitor." msgstr "" #. Tag: para #: using_postgis_dataman.xml:1377 #, no-c-format msgid "" "A data upload file (roads.sql for example) might look " "like this:" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:1380 #, no-c-format msgid "" "BEGIN;\n" "INSERT INTO roads (road_id, roads_geom, road_name)\n" " VALUES (1,'LINESTRING(191232 243118,191108 243242)','Jeff Rd');\n" "INSERT INTO roads (road_id, roads_geom, road_name)\n" " VALUES (2,'LINESTRING(189141 244158,189265 244817)','Geordie Rd');\n" "INSERT INTO roads (road_id, roads_geom, road_name)\n" " VALUES (3,'LINESTRING(192783 228138,192612 229814)','Paul St');\n" "INSERT INTO roads (road_id, roads_geom, road_name)\n" " VALUES (4,'LINESTRING(189412 252431,189631 259122)','Graeme Ave');\n" "INSERT INTO roads (road_id, roads_geom, road_name)\n" " VALUES (5,'LINESTRING(190131 224148,190871 228134)','Phil Tce');\n" "INSERT INTO roads (road_id, roads_geom, road_name)\n" " VALUES (6,'LINESTRING(198231 263418,198213 268322)','Dave Cres');\n" "COMMIT;" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1382 #, no-c-format msgid "" "The data file can be piped into PostgreSQL very easily using the \"psql\" " "SQL terminal monitor:" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:1385 #, no-c-format msgid "psql -d [database] -f roads.sql" msgstr "" #. Tag: title #: using_postgis_dataman.xml:1389 #, no-c-format msgid "shp2pgsql: Using the ESRI Shapefile Loader" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1391 #, no-c-format msgid "" "The shp2pgsql data loader converts ESRI Shape files " "into SQL suitable for insertion into a PostGIS/PostgreSQL database either in " "geometry or geography format. The loader has several operating modes " "distinguished by command line flags:" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1396 #, no-c-format msgid "" "In addition to the shp2pgsql command-line loader, there is an " "shp2pgsql-gui graphical interface with most of the " "options as the command-line loader, but may be easier to use for one-off non-" "scripted loading or if you are new to PostGIS. It can also be configured as " "a plugin to PgAdminIII." msgstr "" #. Tag: term #: using_postgis_dataman.xml:1403 #, no-c-format msgid "(c|a|d|p) These are mutually exclusive options:" msgstr "" #. Tag: term #: using_postgis_dataman.xml:1408 #, no-c-format msgid "-c" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1410 #, no-c-format msgid "" "Creates a new table and populates it from the shapefile. This is " "the default mode." msgstr "" #. Tag: term #: using_postgis_dataman.xml:1418 #, no-c-format msgid "-a" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1420 #, no-c-format msgid "" "Appends data from the Shape file into the database table. Note that to use " "this option to load multiple files, the files must have the same attributes " "and same data types." msgstr "" #. Tag: term #: using_postgis_dataman.xml:1429 #, no-c-format msgid "-d" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1431 #, no-c-format msgid "" "Drops the database table before creating a new table with the data in the " "Shape file." msgstr "" #. Tag: term #: using_postgis_dataman.xml:1439 #, no-c-format msgid "-p" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1441 #, no-c-format msgid "" "Only produces the table creation SQL code, without adding any actual data. " "This can be used if you need to completely separate the table creation and " "data loading steps." msgstr "" #. Tag: term #: using_postgis_dataman.xml:1454 #, no-c-format msgid "-?" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1456 #, no-c-format msgid "Display help screen." msgstr "" #. Tag: term #: using_postgis_dataman.xml:1463 #, no-c-format msgid "-D" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1465 #, no-c-format msgid "" "Use the PostgreSQL \"dump\" format for the output data. This can be combined " "with -a, -c and -d. It is much faster to load than the default \"insert\" " "SQL format. Use this for very large data sets." msgstr "" #. Tag: term #: using_postgis_dataman.xml:1474 #, no-c-format msgid "-s [<FROM_SRID%gt;:]<SRID>" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1476 #, no-c-format msgid "" "Creates and populates the geometry tables with the specified SRID. " "Optionally specifies that the input shapefile uses the given FROM_SRID, in " "which case the geometries will be reprojected to the target SRID. FROM_SRID " "cannot be specified with -D." msgstr "" #. Tag: term #: using_postgis_dataman.xml:1487 #, no-c-format msgid "-k" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1489 #, no-c-format msgid "" "Keep identifiers' case (column, schema and attributes). Note that attributes " "in Shapefile are all UPPERCASE." msgstr "" #. Tag: term #: using_postgis_dataman.xml:1497 #, no-c-format msgid "-i" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1499 #, no-c-format msgid "" "Coerce all integers to standard 32-bit integers, do not create 64-bit " "bigints, even if the DBF header signature appears to warrant it." msgstr "" #. Tag: term #: using_postgis_dataman.xml:1507 #, no-c-format msgid "-I" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1509 #, no-c-format msgid "Create a GiST index on the geometry column." msgstr "" #. Tag: term #: using_postgis_dataman.xml:1516 #, no-c-format msgid "-m" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1518 #, no-c-format msgid "" "-m a_file_name Specify a file containing a set of " "mappings of (long) column names to 10 character DBF column names. The " "content of the file is one or more lines of two names separated by white " "space and no trailing or leading space. For example:" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:1523 #, no-c-format msgid "" "COLUMNNAME DBFFIELD1\n" "AVERYLONGCOLUMNNAME DBFFIELD2" msgstr "" #. Tag: term #: using_postgis_dataman.xml:1529 #, no-c-format msgid "-S" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1531 #, no-c-format msgid "" "Generate simple geometries instead of MULTI geometries. Will only succeed if " "all the geometries are actually single (I.E. a MULTIPOLYGON with a single " "shell, or or a MULTIPOINT with a single vertex)." msgstr "" #. Tag: term #: using_postgis_dataman.xml:1540 #, no-c-format msgid "-t <dimensionality>" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1542 #, no-c-format msgid "" "Force the output geometry to have the specified dimensionality. Use the " "following strings to indicate the dimensionality: 2D, 3DZ, 3DM, 4D." msgstr "" #. Tag: para #: using_postgis_dataman.xml:1546 #, no-c-format msgid "" "If the input has fewer dimensions that specified, the output will have those " "dimensions filled in with zeroes. If the input has more dimensions that " "specified, the unwanted dimensions will be stripped." msgstr "" #. Tag: term #: using_postgis_dataman.xml:1555 #, no-c-format msgid "-w" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1557 #, no-c-format msgid "" "Output WKT format, instead of WKB. Note that this can introduce coordinate " "drifts due to loss of precision." msgstr "" #. Tag: term #: using_postgis_dataman.xml:1565 #, no-c-format msgid "-e" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1567 #, no-c-format msgid "" "Execute each statement on its own, without using a transaction. This allows " "loading of the majority of good data when there are some bad geometries that " "generate errors. Note that this cannot be used with the -D flag as the \"dump" "\" format always uses a transaction." msgstr "" #. Tag: term #: using_postgis_dataman.xml:1577 #, no-c-format msgid "-W <encoding>" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1579 #, no-c-format msgid "" "Specify encoding of the input data (dbf file). When used, all attributes of " "the dbf are converted from the specified encoding to UTF8. The resulting SQL " "output will contain a SET CLIENT_ENCODING to UTF8 command, so " "that the backend will be able to reconvert from UTF8 to whatever encoding " "the database is configured to use internally." msgstr "" #. Tag: term #: using_postgis_dataman.xml:1589 #, no-c-format msgid "-N <policy>" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1591 #, no-c-format msgid "NULL geometries handling policy (insert*,skip,abort)" msgstr "" #. Tag: term #: using_postgis_dataman.xml:1597 #, no-c-format msgid "-n" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1599 #, no-c-format msgid "" "-n Only import DBF file. If your data has no corresponding shapefile, it " "will automatically switch to this mode and load just the dbf. So setting " "this flag is only needed if you have a full shapefile set, and you only want " "the attribute data and no geometry." msgstr "" #. Tag: term #: using_postgis_dataman.xml:1607 #, no-c-format msgid "-G" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1609 #, no-c-format msgid "" "Use geography type instead of geometry (requires lon/lat data) in WGS84 long " "lat (SRID=4326)" msgstr "" #. Tag: term #: using_postgis_dataman.xml:1615 #, no-c-format msgid "-T <tablespace>" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1617 #, no-c-format msgid "" "Specify the tablespace for the new table. Indexes will still use the default " "tablespace unless the -X parameter is also used. The PostgreSQL " "documentation has a good description on when to use custom tablespaces." msgstr "" #. Tag: term #: using_postgis_dataman.xml:1625 #, no-c-format msgid "-X <tablespace>" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1627 #, no-c-format msgid "" "Specify the tablespace for the new table's indexes. This applies to the " "primary key index, and the GIST spatial index if -I is also used." msgstr "" #. Tag: para #: using_postgis_dataman.xml:1635 #, no-c-format msgid "" "An example session using the loader to create an input file and uploading it " "might look like this:" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:1640 #, no-c-format msgid "" "# shp2pgsql -c -D -s 4269 -i -I shaperoads.shp myschema.roadstable > " "roads.sql\n" "# psql -d roadsdb -f roads.sql" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1642 #, no-c-format msgid "A conversion and upload can be done all in one step using UNIX pipes:" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:1646 #, no-c-format msgid "# shp2pgsql shaperoads.shp myschema.roadstable | psql -d roadsdb" msgstr "" #. Tag: title #: using_postgis_dataman.xml:1651 #, no-c-format msgid "Retrieving GIS Data" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1653 #, no-c-format msgid "" "Data can be extracted from the database using either SQL or the Shape file " "loader/dumper. In the section on SQL we will discuss some of the operators " "available to do comparisons and queries on spatial tables." msgstr "" #. Tag: title #: using_postgis_dataman.xml:1659 #, no-c-format msgid "Using SQL to Retrieve Data" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1661 #, no-c-format msgid "" "The most straightforward means of pulling data out of the database is to use " "a SQL select query to reduce the number of RECORDS and COLUMNS returned and " "dump the resulting columns into a parsable text file:" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:1666 #, no-c-format msgid "" "db=# SELECT road_id, ST_AsText(road_geom) AS geom, road_name FROM roads;\n" "\n" "road_id | geom | road_name\n" "--------+-----------------------------------------+-----------\n" " 1 | LINESTRING(191232 243118,191108 243242) | Jeff Rd\n" " 2 | LINESTRING(189141 244158,189265 244817) | Geordie Rd\n" " 3 | LINESTRING(192783 228138,192612 229814) | Paul St\n" " 4 | LINESTRING(189412 252431,189631 259122) | Graeme Ave\n" " 5 | LINESTRING(190131 224148,190871 228134) | Phil Tce\n" " 6 | LINESTRING(198231 263418,198213 268322) | Dave Cres\n" " 7 | LINESTRING(218421 284121,224123 241231) | Chris Way\n" "(6 rows)" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1668 #, no-c-format msgid "" "However, there will be times when some kind of restriction is necessary to " "cut down the number of fields returned. In the case of attribute-based " "restrictions, just use the same SQL syntax as normal with a non-spatial " "table. In the case of spatial restrictions, the following operators are " "available/useful:" msgstr "" #. Tag: term #: using_postgis_dataman.xml:1676 #, no-c-format msgid "ST_Intersects" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1679 #, no-c-format msgid "This function tells whether two geometries share any space." msgstr "" #. Tag: term #: using_postgis_dataman.xml:1684 #, no-c-format msgid "=" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1687 #, no-c-format msgid "" "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)." msgstr "" #. Tag: para #: using_postgis_dataman.xml:1691 #, no-c-format msgid "Note: before PostGIS 2.4 this compared only boxes of geometries." msgstr "" #. Tag: para #: using_postgis_dataman.xml:1696 #, no-c-format msgid "" "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 function. The 312 is a fictitious " "spatial reference system that matches our data. So, for example:" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:1702 #, no-c-format msgid "" "SELECT road_id, road_name\n" " FROM roads\n" " WHERE roads_geom='SRID=312;LINESTRING(191232 243118,191108 243242)'::" "geometry;" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1704 #, no-c-format msgid "" "The above query would return the single record from the \"ROADS_GEOM\" table " "in which the geometry was equal to that value." msgstr "" #. Tag: para #: using_postgis_dataman.xml:1707 #, no-c-format msgid "" "To check whether some of the roads passes in the area defined by a polygon:" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:1709 #, no-c-format msgid "" "SELECT road_id, road_name\n" "FROM roads\n" "WHERE ST_Intersects(roads_geom, 'SRID=312;POLYGON((...))');" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1713 #, no-c-format msgid "" "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." msgstr "" #. Tag: para #: using_postgis_dataman.xml:1716 #, no-c-format msgid "" "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." msgstr "" #. Tag: para #: using_postgis_dataman.xml:1720 #, no-c-format msgid "Using a \"BOX3D\" object for the frame, such a query looks like this:" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:1722 #, no-c-format msgid "" "SELECT ST_AsText(roads_geom) AS geom\n" "FROM roads\n" "WHERE\n" " roads_geom && ST_MakeEnvelope(191232, 243117,191232, 243119,312);" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1724 #, no-c-format msgid "" "Note the use of the SRID 312, to specify the projection of the envelope." msgstr "" #. Tag: title #: using_postgis_dataman.xml:1730 #, no-c-format msgid "Using the Dumper" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1732 #, no-c-format msgid "" "The pgsql2shp table dumper connects directly to the " "database and converts a table (possibly defined by a query) into a shape " "file. The basic syntax is:" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:1736 #, no-c-format msgid "" "pgsql2shp [<options>] <database> [<schema>.]<table>" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:1738 #, no-c-format msgid "pgsql2shp [<options>] <database> <query>" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1740 #, no-c-format msgid "The commandline options are:" msgstr "" #. Tag: term #: using_postgis_dataman.xml:1744 #, no-c-format msgid "-f <filename>" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1747 #, no-c-format msgid "Write the output to a particular filename." msgstr "" #. Tag: term #: using_postgis_dataman.xml:1752 #, no-c-format msgid "-h <host>" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1755 #, no-c-format msgid "The database host to connect to." msgstr "" #. Tag: term #: using_postgis_dataman.xml:1760 #, no-c-format msgid "-p <port>" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1763 #, no-c-format msgid "The port to connect to on the database host." msgstr "" #. Tag: term #: using_postgis_dataman.xml:1768 #, no-c-format msgid "-P <password>" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1771 #, no-c-format msgid "The password to use when connecting to the database." msgstr "" #. Tag: term #: using_postgis_dataman.xml:1776 #, no-c-format msgid "-u <user>" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1779 #, no-c-format msgid "The username to use when connecting to the database." msgstr "" #. Tag: term #: using_postgis_dataman.xml:1784 #, no-c-format msgid "-g <geometry column>" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1787 #, no-c-format msgid "" "In the case of tables with multiple geometry columns, the geometry column to " "use when writing the shape file." msgstr "" #. Tag: term #: using_postgis_dataman.xml:1793 #, no-c-format msgid "-b" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1796 #, no-c-format msgid "" "Use a binary cursor. This will make the operation faster, but will not work " "if any NON-geometry attribute in the table lacks a cast to text." msgstr "" #. Tag: term #: using_postgis_dataman.xml:1803 #, no-c-format msgid "-r" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1806 #, no-c-format msgid "" "Raw mode. Do not drop the gid field, or escape column " "names." msgstr "" #. Tag: term #: using_postgis_dataman.xml:1812 #, no-c-format msgid "-m filename" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1814 #, no-c-format msgid "" "Remap identifiers to ten character names. The content of the file is lines " "of two symbols separated by a single white space and no trailing or leading " "space: VERYLONGSYMBOL SHORTONE ANOTHERVERYLONGSYMBOL SHORTER etc." msgstr "" #. Tag: title #: using_postgis_dataman.xml:1827 #, no-c-format msgid "Building Indexes" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1829 #, no-c-format msgid "" "Indexes are what make using a spatial database for large data sets possible. " "Without indexing, any search for a feature would require a \"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, SP-GiST and GiST indexes." msgstr "" #. Tag: para #: using_postgis_dataman.xml:1838 #, no-c-format msgid "" "B-Trees are used for data which can be sorted along one axis; for example, " "numbers, letters, dates. Spatial data can be sorted along a space-filling " "curve, Z-order curve or Hilbert curve. This representation however does not " "allow speeding up common operations." msgstr "" #. Tag: para #: using_postgis_dataman.xml:1845 #, no-c-format msgid "" "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 data. PostGIS uses an R-" "Tree index implemented on top of GiST to index GIS data." msgstr "" #. Tag: title #: using_postgis_dataman.xml:1854 #, no-c-format msgid "GiST Indexes" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1856 #, no-c-format msgid "" "GiST stands for \"Generalized Search Tree\" and is a generic form of " "indexing. In addition to GIS indexing, GiST is used to speed up searches on " "all kinds of irregular data structures (integer arrays, spectral data, etc) " "which are not amenable to normal B-Tree indexing." msgstr "" #. Tag: para #: using_postgis_dataman.xml:1861 #, no-c-format msgid "" "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)." msgstr "" #. Tag: para #: using_postgis_dataman.xml:1866 #, no-c-format msgid "" "The syntax for building a GiST index on a \"geometry\" column is as follows:" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:1870 #, no-c-format msgid "CREATE INDEX [indexname] ON [tablename] USING GIST ( [geometryfield] );" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1872 #, no-c-format msgid "" "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:" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:1873 #, no-c-format msgid "" "CREATE INDEX [indexname] ON [tablename] USING GIST ([geometryfield] " "gist_geometry_ops_nd);" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1875 #, no-c-format msgid "" "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:" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:1876 #, no-c-format msgid "" "CREATE INDEX CONCURRENTLY [indexname] ON [tablename] USING GIST " "( [geometryfield] );" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1878 using_postgis_dataman.xml:1991 #, no-c-format msgid "" "After building an index, it is sometimes helpful to force PostgreSQL to " "collect table statistics, which are used to optimize query plans:" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:1881 using_postgis_dataman.xml:1994 #, no-c-format msgid "VACUUM ANALYZE [table_name] [(column_name)];" msgstr "" #. Tag: title #: using_postgis_dataman.xml:1886 #, no-c-format msgid "BRIN Indexes" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1888 #, no-c-format msgid "" "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)." msgstr "" #. Tag: para #: using_postgis_dataman.xml:1897 #, no-c-format msgid "" "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 size, and the penalty in write workload. " "Otherwise, BRIN index can be considered as an alternative." msgstr "" #. Tag: para #: using_postgis_dataman.xml:1906 #, no-c-format msgid "" "The idea of a BRIN index is to store only the bouding box englobing all the " "geometries contained in all the rows in a set of table blocks, called a " "range. Obviously, this indexing method will only be efficient if the data is " "physically ordered in a way where the resulting bouding boxes for block " "ranges will be mutually exclusive. The resulting index will be really small, " "but will be less efficient than a GiST index in many cases." msgstr "" #. Tag: para #: using_postgis_dataman.xml:1914 #, no-c-format msgid "" "Building a BRIN index is way less intensive than building a GiST index. It's " "quite common to build a BRIN index in more than ten time less than a GiST " "index would have required. As a BRIN index only store one bouding box for " "one to many table blocks, it's pretty common to consume up to a thousand " "time less disk space for this kind of indexes." msgstr "" #. Tag: para #: using_postgis_dataman.xml:1920 #, no-c-format msgid "" "You can choose the number of blocks to summarize in a range. If you decrease " "this number, the index will be bigger but will probably help to get better " "performance." msgstr "" #. Tag: para #: using_postgis_dataman.xml:1924 #, no-c-format msgid "" "The syntax for building a BRIN index on a \"geometry\" column is as follows:" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:1927 #, no-c-format msgid "CREATE INDEX [indexname] ON [tablename] USING BRIN ( [geometryfield] );" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1928 #, no-c-format msgid "" "The above syntax will always build a 2D-index. To get a 3D-dimensional " "index, you can create one using this syntax" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:1929 #, no-c-format msgid "" "CREATE INDEX [indexname] ON [tablename] USING BRIN ([geometryfield] " "brin_geometry_inclusion_ops_3d);" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1930 #, no-c-format msgid "You can also get a 4D-dimensional index using the 4D operator class" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:1931 #, no-c-format msgid "" "CREATE INDEX [indexname] ON [tablename] USING BRIN ([geometryfield] " "brin_geometry_inclusion_ops_4d);" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1932 #, no-c-format msgid "" "These above syntaxes will use the default number or block in a range, which " "is 128. To specify the number of blocks you want to summarise in a range, " "you can create one using this syntax" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:1933 #, no-c-format msgid "" "CREATE INDEX [indexname] ON [tablename] USING BRIN ( [geometryfield] ) WITH " "(pages_per_range = [number]);" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1934 #, no-c-format msgid "" "Also, keep in mind that a BRIN index will only store one index value for a " "large number of rows. If your table stores geometries with a mixed number of " "dimensions, it's likely that the resulting index will have poor performance. " "You can avoid this drop of performance by choosing the operator class whith " "the least number of dimensions of the stored geometries" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1942 #, no-c-format msgid "" "Also the \"geography\" datatype is supported for BRIN indexing. The syntax " "for building a BRIN index on a \"geography\" column is as follows:" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:1945 #, no-c-format msgid "" "CREATE INDEX [indexname] ON [tablename] USING BRIN ( [geographyfield] );" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1946 #, no-c-format msgid "" "The above syntax will always build a 2D-index for geospatial objects on the " "spheroid." msgstr "" #. Tag: para #: using_postgis_dataman.xml:1948 #, no-c-format msgid "" "Currently, just the \"inclusion support\" is considered here, meaning that " "just &&, ~ and @ operators can be used for the 2D cases (both for \"geometry\" and " "for \"geography\"), and just the &&& operator " "can be used for the 3D geometries. There is no support for kNN searches at " "the moment." msgstr "" #. Tag: title #: using_postgis_dataman.xml:1957 #, no-c-format msgid "SP-GiST Indexes" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1959 #, no-c-format msgid "" "SP-GiST stands for \"Space-Partitioned Generalized Search Tree\" and is a " "generic form of indexing that supports partitioned search trees, such as " "quad-trees, k-d trees, and radix trees (tries). The common feature of these " "data structures is that they repeatedly divide the search space into " "partitions that need not be of equal size. In addition to GIS indexing, SP-" "GiST is used to speed up searches on many kinds of data, such as phone " "routing, ip routing, substring search, etc." msgstr "" #. Tag: para #: using_postgis_dataman.xml:1967 #, no-c-format msgid "" "As it is the case for GiST indexes, SP-GiST indexes are lossy, in the sense " "that they store the bounding box englobing the spatial objects. SP-GiST " "indexes can be considered as an alternative to GiST indexes. The performance " "tests reveal that SP-GiST indexes are especially beneficial when there are " "many overlapping objects, that is, with so-called “spaghetti data”." msgstr "" #. Tag: para #: using_postgis_dataman.xml:1974 #, no-c-format msgid "" "Once a GIS data table exceeds a few thousand rows, an SP-GiST index may be " "used to speed up spatial searches of the data. The syntax for building an SP-" "GiST index on a \"geometry\" column is as follows:" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:1978 #, no-c-format msgid "" "CREATE INDEX [indexname] ON [tablename] USING SPGIST ( [geometryfield] );" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1980 #, no-c-format msgid "" "The above syntax will build a 2-dimensional index. A 3-dimensional index for " "the geometry type can be created using the 3D operator class:" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:1983 #, no-c-format msgid "" "CREATE INDEX [indexname] ON [tablename] USING SPGIST ([geometryfield] " "spgist_geometry_ops_3d);" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1985 #, no-c-format msgid "" "Building a spatial index is a computationally intensive operation. 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:" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:1989 #, no-c-format msgid "" "CREATE INDEX CONCURRENTLY [indexname] ON [tablename] USING SPGIST " "( [geometryfield] );" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1996 #, no-c-format msgid "" "An SP-GiST index can accelerate queries involving the following operators:" msgstr "" #. Tag: para #: using_postgis_dataman.xml:1998 #, no-c-format msgid "" "<<, &<, &>, >>, <<|, &<|, |&>, " "|>>, &&, @>, <@, and ~=, for 2-dimensional indexes," msgstr "" #. Tag: para #: using_postgis_dataman.xml:1999 #, no-c-format msgid "&/&, ~==, @>>, and <<@, for 3-dimensional indexes." msgstr "" #. Tag: para #: using_postgis_dataman.xml:2001 #, no-c-format msgid "There is no support for kNN searches at the moment." msgstr "" #. Tag: title #: using_postgis_dataman.xml:2004 #, no-c-format msgid "Using Indexes" msgstr "" #. Tag: para #: using_postgis_dataman.xml:2006 #, no-c-format msgid "" "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 sometimes " "does not optimize the use of GiST indexes well, so sometimes searches which " "should use a spatial index instead may perform a sequential scan of the " "whole table." msgstr "" #. Tag: para #: using_postgis_dataman.xml:2013 #, no-c-format msgid "" "If you find your spatial indexes are not being used (or your attribute " "indexes, for that matter) there are a couple things you can do:" msgstr "" #. Tag: para #: using_postgis_dataman.xml:2019 #, no-c-format msgid "" "Firstly, read query plan and check your query actually tries to compute the " "thing you need. A runaway JOIN condition, either forgotten or to the wrong " "table, can unexpectedly bring you all of your table multiple times. To get " "query plan, add EXPLAIN keyword in front of your query." msgstr "" #. Tag: para #: using_postgis_dataman.xml:2026 #, no-c-format msgid "" "Second, 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. VACUUM ANALYZE will " "compute both." msgstr "" #. Tag: para #: using_postgis_dataman.xml:2031 #, no-c-format msgid "" "You should regularly vacuum your databases anyways - many PostgreSQL DBAs " "have VACUUM run as an off-peak cron job on a regular " "basis." msgstr "" #. Tag: para #: using_postgis_dataman.xml:2036 #, no-c-format msgid "" "If vacuuming does not help, you can temporarily force the planner to use the " "index information by using the set enable_seqscan to off; " "command. This way you can check whether planner is at all capable to " "generate an index accelerated query plan for your query. You should only use " "this command only for debug: generally speaking, the planner knows better " "than you do about when to use indexes. Once you have run your query, do not " "forget to set ENABLE_SEQSCAN back on, so that other " "queries will utilize the planner as normal." msgstr "" #. Tag: para #: using_postgis_dataman.xml:2048 #, no-c-format msgid "" "If set enable_seqscan to off; helps your query to run, " "your Postgres is likely not tuned for your hardware. 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 to 1.1;. Default value for the parameter is 4, " "try setting it to 1 (on SSD) or 2 (on fast magnetic disks). Decreasing the " "value makes the planner more inclined of using Index scans." msgstr "" #. Tag: para #: using_postgis_dataman.xml:2058 #, no-c-format msgid "" "If set enable_seqscan to off; does not help your query, " "it may happen you use a construction Postgres is not yet able to untangle. A " "subquery with inline select is one example - you need to rewrite it to the " "form planner can optimize, say, a LATERAL JOIN." msgstr "" #. Tag: title #: using_postgis_dataman.xml:2069 #, no-c-format msgid "Complex Queries" msgstr "" #. Tag: para #: using_postgis_dataman.xml:2071 #, no-c-format msgid "" "The raison d'etre of spatial database functionality is " "performing queries inside the database which would ordinarily require " "desktop GIS functionality. Using PostGIS effectively requires knowing what " "spatial functions are available, and ensuring that appropriate indexes are " "in place to provide good performance. The SRID of 312 used in these examples " "is purely for demonstration. You should be using a REAL SRID listed in the " "the spatial_ref_sys table and one that matches the projection of your data. " "If your data has no spatial reference system specified, you should be " "THINKING very thoughtfully why it doesn't and maybe it should." msgstr "" #. Tag: para #: using_postgis_dataman.xml:2079 #, no-c-format msgid "" "If your reason is because you are modeling something that doesn't have a " "geographic spatial reference system defined such as the internals of a " "molecule or the floorplan of a not yet built amusement park then that's " "fine. If the location of the amusement park has been planned however, then " "it would make sense to use a suitable planar coordinate system for that " "location if nothing more than to ensure the amusement part is not " "trespassing on already existing structures." msgstr "" #. Tag: para #: using_postgis_dataman.xml:2083 #, no-c-format msgid "" "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 Mars 2000 make one up and insert it in the " "spatial_ref_sys 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." msgstr "" #. Tag: title #: using_postgis_dataman.xml:2089 #, no-c-format msgid "Taking Advantage of Indexes" msgstr "" #. Tag: para #: using_postgis_dataman.xml:2091 #, no-c-format msgid "" "When constructing a query it is important to remember that only the bounding-" "box-based operators such as && can take advantage of the GiST " "spatial index. Functions such as ST_Distance() cannot use " "the index to optimize their operation. For example, the following query " "would be quite slow on a large table:" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:2098 #, no-c-format msgid "" "SELECT the_geom\n" "FROM geom_table\n" "WHERE ST_Distance(the_geom, 'SRID=312;POINT(100000 200000)') < 100" msgstr "" #. Tag: para #: using_postgis_dataman.xml:2100 #, no-c-format msgid "" "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 ST_Distance() calculation 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:" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:2108 #, no-c-format msgid "" "SELECT the_geom\n" "FROM geom_table\n" "WHERE ST_DWithin(the_geom, 'SRID=312;POINT(100000 200000)', 100)" msgstr "" #. Tag: para #: using_postgis_dataman.xml:2110 #, no-c-format msgid "" "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 ST_Distance() function. " "Notice that the ST_MakeEnvelope 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 uses the index to " "quickly reduce the result set down to only those geometries which have " "bounding boxes that overlap the \"query box\". 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." msgstr "" #. Tag: title #: using_postgis_dataman.xml:2125 #, no-c-format msgid "Examples of Spatial SQL" msgstr "" #. Tag: para #: using_postgis_dataman.xml:2127 #, no-c-format msgid "" "The examples in this section will make use of two tables, a table of linear " "roads, and a table of polygonal municipality boundaries. The table " "definitions for the bc_roads table is:" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:2131 #, no-c-format msgid "" "Column | Type | Description\n" "------------+-------------------+-------------------\n" "gid | integer | Unique ID\n" "name | character varying | Road Name\n" "the_geom | geometry | Location Geometry (Linestring)" msgstr "" #. Tag: para #: using_postgis_dataman.xml:2133 #, no-c-format msgid "" "The table definition for the bc_municipality table is:" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:2136 #, no-c-format msgid "" "Column | Type | Description\n" "-----------+-------------------+-------------------\n" "gid | integer | Unique ID\n" "code | integer | Unique ID\n" "name | character varying | City / Town Name\n" "the_geom | geometry | Location Geometry (Polygon)" msgstr "" #. Tag: para #: using_postgis_dataman.xml:2141 #, no-c-format msgid "What is the total length of all roads, expressed in kilometers?" msgstr "" #. Tag: para #: using_postgis_dataman.xml:2146 #, no-c-format msgid "You can answer this question with a very simple piece of SQL:" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:2149 #, no-c-format msgid "" "SELECT sum(ST_Length(the_geom))/1000 AS km_roads FROM bc_roads;\n" "\n" "km_roads\n" "------------------\n" "70842.1243039643\n" "(1 row)" msgstr "" #. Tag: para #: using_postgis_dataman.xml:2155 #, no-c-format msgid "How large is the city of Prince George, in hectares?" msgstr "" #. Tag: para #: using_postgis_dataman.xml:2159 #, no-c-format msgid "" "This query combines an attribute condition (on the municipality name) with a " "spatial calculation (of the area):" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:2163 #, no-c-format msgid "" "SELECT\n" " ST_Area(the_geom)/10000 AS hectares\n" "FROM bc_municipality\n" "WHERE name = 'PRINCE GEORGE';\n" "\n" "hectares\n" "------------------\n" "32657.9103824927\n" "(1 row)" msgstr "" #. Tag: para #: using_postgis_dataman.xml:2169 #, no-c-format msgid "What is the largest municipality in the province, by area?" msgstr "" #. Tag: para #: using_postgis_dataman.xml:2174 #, no-c-format msgid "" "This query brings a spatial measurement into the query condition. There are " "several ways of approaching this problem, but the most efficient is below:" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:2178 #, no-c-format msgid "" "SELECT\n" " name,\n" " ST_Area(the_geom)/10000 AS hectares\n" "FROM\n" " bc_municipality\n" "ORDER BY hectares DESC\n" "LIMIT 1;\n" "\n" "name | hectares\n" "---------------+-----------------\n" "TUMBLER RIDGE | 155020.02556131\n" "(1 row)" msgstr "" #. Tag: para #: using_postgis_dataman.xml:2180 #, no-c-format msgid "" "Note that in order to answer this query we have to calculate the area of " "every polygon. If we were doing this a lot it would make sense to add an " "area column to the table that we could separately index for performance. By " "ordering the results in a descending direction, and them using the " "PostgreSQL \"LIMIT\" command we can easily pick off the largest value " "without using an aggregate function like max()." msgstr "" #. Tag: para #: using_postgis_dataman.xml:2192 #, no-c-format msgid "What is the length of roads fully contained within each municipality?" msgstr "" #. Tag: para #: using_postgis_dataman.xml:2197 #, no-c-format msgid "" "This is an example of a \"spatial join\", because we are bringing together " "data from two tables (doing a join) but using a spatial interaction " "condition (\"contained\") as the join condition rather than the usual " "relational approach of joining on a common key:" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:2203 #, no-c-format msgid "" "SELECT\n" " m.name,\n" " sum(ST_Length(r.the_geom))/1000 as roads_km\n" "FROM\n" " bc_roads AS r,\n" " bc_municipality AS m\n" "WHERE\n" " ST_Contains(m.the_geom, r.the_geom)\n" "GROUP BY m.name\n" "ORDER BY roads_km;\n" "\n" "name | roads_km\n" "----------------------------+------------------\n" "SURREY | 1539.47553551242\n" "VANCOUVER | 1450.33093486576\n" "LANGLEY DISTRICT | 833.793392535662\n" "BURNABY | 773.769091404338\n" "PRINCE GEORGE | 694.37554369147\n" "..." msgstr "" #. Tag: para #: using_postgis_dataman.xml:2205 #, no-c-format msgid "" "This query takes a while, because every road in the table is summarized into " "the final result (about 250K roads for our particular example table). For " "smaller overlays (several thousand records on several hundred) the response " "can be very fast." msgstr "" #. Tag: para #: using_postgis_dataman.xml:2214 #, no-c-format msgid "Create a new table with all the roads within the city of Prince George." msgstr "" #. Tag: para #: using_postgis_dataman.xml:2219 #, no-c-format msgid "" "This is an example of an \"overlay\", which takes in two tables and outputs " "a new table that consists of spatially clipped or cut resultants. Unlike the " "\"spatial join\" demonstrated above, this query actually creates new " "geometries. An overlay is like a turbo-charged spatial join, and is useful " "for more exact analysis work:" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:2226 #, no-c-format msgid "" "CREATE TABLE pg_roads as\n" "SELECT\n" " ST_Intersection(r.the_geom, m.the_geom) AS intersection_geom,\n" " ST_Length(r.the_geom) AS rd_orig_length,\n" " r.*\n" "FROM\n" " bc_roads AS r,\n" " bc_municipality AS m\n" "WHERE\n" " m.name = 'PRINCE GEORGE'\n" " AND ST_Intersects(r.the_geom, m.the_geom);" msgstr "" #. Tag: para #: using_postgis_dataman.xml:2232 #, no-c-format msgid "What is the length in kilometers of \"Douglas St\" in Victoria?" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:2237 #, no-c-format msgid "" "SELECT\n" " sum(ST_Length(r.the_geom))/1000 AS kilometers\n" "FROM\n" " bc_roads r,\n" " bc_municipality m\n" "WHERE\n" " r.name = 'Douglas St'\n" " AND m.name = 'VICTORIA'\n" " AND ST_Intersects(m.the_geom, r.the_geom);\n" "\n" "kilometers\n" "------------------\n" "4.89151904172838\n" "(1 row)" msgstr "" #. Tag: para #: using_postgis_dataman.xml:2243 #, no-c-format msgid "What is the largest municipality polygon that has a hole?" msgstr "" #. Tag: programlisting #: using_postgis_dataman.xml:2248 #, no-c-format msgid "" "SELECT gid, name, ST_Area(the_geom) AS area\n" "FROM bc_municipality\n" "WHERE ST_NRings(the_geom) > 1\n" "ORDER BY area DESC LIMIT 1;\n" "\n" "gid | name | area\n" "-----+--------------+------------------\n" "12 | SPALLUMCHEEN | 257374619.430216\n" "(1 row)" msgstr ""