From ac7969482490aa10e590f435933abb788b29c0e2 Mon Sep 17 00:00:00 2001 From: Sandro Santilli Date: Thu, 16 Jun 2016 08:46:07 +0000 Subject: [PATCH] Style only change: remove trailing and tab-leading spaces git-svn-id: http://svn.osgeo.org/postgis/trunk@14960 b70326c6-7e19-0410-871a-916f4a2858ee --- doc/extras_address_standardizer.xml | 216 +-- doc/extras_historytable.xml | 70 +- doc/extras_tigergeocoder.xml | 332 ++-- doc/extras_topology.xml | 1090 +++++------ doc/faq.xml | 72 +- doc/faq_raster.xml | 166 +- doc/installation.xml | 458 ++--- doc/introduction.xml | 66 +- doc/performance_tips.xml | 54 +- doc/postgis.xml | 40 +- doc/reference_accessor.xml | 10 +- doc/reference_constructor.xml | 84 +- doc/reference_editor.xml | 86 +- doc/reference_guc.xml | 36 +- doc/reference_lrs.xml | 26 +- doc/reference_management.xml | 78 +- doc/reference_measure.xml | 408 ++-- doc/reference_misc.xml | 12 +- doc/reference_operator.xml | 76 +- doc/reference_output.xml | 128 +- doc/reference_processing.xml | 378 ++-- doc/reference_raster.xml | 2712 +++++++++++++-------------- doc/reference_sfcgal.xml | 182 +- doc/reference_temporal.xml | 10 +- doc/reference_transaction.xml | 4 +- doc/reference_type.xml | 42 +- doc/release_notes.xml | 322 ++-- doc/template.xml | 14 +- doc/using_postgis_app.xml | 260 +-- doc/using_postgis_dataman.xml | 166 +- doc/using_raster_dataman.xml | 344 ++-- 31 files changed, 3971 insertions(+), 3971 deletions(-) diff --git a/doc/extras_address_standardizer.xml b/doc/extras_address_standardizer.xml index 0cebd21e5..51140c595 100644 --- a/doc/extras_address_standardizer.xml +++ b/doc/extras_address_standardizer.xml @@ -7,8 +7,8 @@ The code for this extension can be found in the PostGIS extensions/address_standardizer and is currently self-contained. For installation instructions refer to: . How the Parser Works - The parser works from right to left looking first at the macro elements - for postcode, state/province, city, and then looks micro elements to determine + The parser works from right to left looking first at the macro elements + for postcode, state/province, city, and then looks micro elements to determine if we are dealing with a house number street or intersection or landmark. It currently does not look for a country code or name, but that could be introduced in the future. @@ -34,9 +34,9 @@ into includes in the future for easier maintenance. - This section lists the PostgreSQL data types installed by Address Standardizer extension. Note we describe the casting behavior of these which is very - important especially when designing your own functions. - + This section lists the PostgreSQL data types installed by Address Standardizer extension. Note we describe the casting behavior of these which is very + important especially when designing your own functions. + Address Standardizer Types @@ -57,7 +57,7 @@ into includes in the future for easier maintenance. is text (token number 0): Refers to building number or name. Unparsed building identifiers and types. Generally blank for most addresses. - house_num + house_num is a text (token number 1): This is the street number on a street. Example 75 in 75 State Street. @@ -65,7 +65,7 @@ into includes in the future for easier maintenance. predir is text (token number 2): STREET NAME PRE-DIRECTIONAL such as North, South, East, West etc. - qual + qual is text (token number 3): STREET NAME PRE-MODIFIER Example OLD in 3715 OLD HIGHWAY 99. @@ -134,12 +134,12 @@ into includes in the future for easier maintenance. - + - + - This section lists the PostgreSQL table formats used by the address_standardizer for normalizing addresses. Note that these tables do not need to be named the same as what is referenced here. You can have different lex, gaz, rules tables for each country for example or for your custom geocoder. The names of these tables get passed into the address standardizer functions. - + This section lists the PostgreSQL table formats used by the address_standardizer for normalizing addresses. Note that these tables do not need to be named the same as what is referenced here. You can have different lex, gaz, rules tables for each country for example or for your custom geocoder. The names of these tables get passed into the address standardizer functions. + The packaged extension address_standardizer_data_us contains data for standardizing US addresses. @@ -152,7 +152,7 @@ into includes in the future for easier maintenance. Description A rules table must have at least the following columns, though you are allowed to add more for your own uses. - + id @@ -160,7 +160,7 @@ into includes in the future for easier maintenance. Primary key of table - rule + rule text field denoting the rule. Details at PAGC Address Standardizer Rule records. A rule consists of a set of non-negative integers representing input tokens, terminated by a -1, followed by an equal number of non-negative integers representing postal attributes, terminated by a -1, followed by an integer representing a rule type, followed by an integer representing the rank of the rule. The rules are ranked from 0 (lowest) to 17 (highest). @@ -170,7 +170,7 @@ into includes in the future for easier maintenance. - + Input Tokens Each rule starts with a set of input tokens followed by a terminator -1. Valid input tokens excerpted from PAGC Input Tokens are as follows: Form-Based Input Tokens @@ -181,56 +181,56 @@ into includes in the future for easier maintenance. (13). The ampersand (&) is frequently used to abbreviate the word "and". - + DASH (9). A punctuation character. - + DOUBLE (21). A sequence of two letters. Often used as identifiers. - + FRACT (25). Fractions are sometimes used in civic numbers or unit numbers. - + MIXED (23). An alphanumeric string that contains both letters and digits. Used for identifiers. - + NUMBER (0). A string of digits. - + ORD (15). Representations such as First or 1st. Often used in street names. - + ORD (18). A single letter. - + WORD @@ -238,7 +238,7 @@ into includes in the future for easier maintenance. - + Function-based Input Tokens @@ -247,56 +247,56 @@ into includes in the future for easier maintenance. (14). Words used to denote post office boxes. For example Box or PO Box. - + BUILDH (19). Words used to denote buildings or building complexes, usually as a prefix. For example: Tower in Tower 7A. - + BUILDT (24). Words and abbreviations used to denote buildings or building complexes, usually as a suffix. For example: Shopping Centre. - + DIRECT (22). Words used to denote directions, for example North. - + MILE (20). Words used to denote milepost addresses. - + ROAD (6). Words and abbreviations used to denote highways and roads. For example: the Interstate in Interstate 5 - + RR (8). Words and abbreviations used to denote rural routes. RR. - + TYPE (2). Words and abbreviation used to denote street typess. For example: ST or AVE. - + UNITH @@ -304,7 +304,7 @@ into includes in the future for easier maintenance. - + Postal Type Input Tokens @@ -313,21 +313,21 @@ into includes in the future for easier maintenance. (28). A 5 digit number. Identifies a Zip Code - + QUAD (29). A 4 digit number. Identifies ZIP4. - + PCH (27). A 3 character sequence of letter number letter. Identifies an FSA, the first 3 characters of a Canadian postal code. - + PCT @@ -335,7 +335,7 @@ into includes in the future for easier maintenance. - + Stopwords STOPWORDS combine with WORDS. In rules a string of multiple WORDs and STOPWORDs will be represented by a single WORD token. @@ -347,17 +347,17 @@ into includes in the future for easier maintenance. - + - + Output Tokens After the first -1 (terminator), follows the output tokens and their order, followed by a terminator -1. Numbers for corresponding output tokens are listed in . What are allowed is dependent on kind of rule. Output tokens valid for each rule type are listed in . - + Rule Types and Rank The final part of the rule is the rule type which is denoted by one of the following, followed by a rule rank. The rules are ranked from 0 (lowest) to 17 (highest). - + MACRO_C (token number = "0"). The class of rules for parsing MACRO clauses such as PLACE STATE ZIP MACRO_C output tokens (excerpted from http://www.pagcgeo.org/docs/html/pagc-12.html#--r-typ--. @@ -387,12 +387,12 @@ into includes in the future for easier maintenance. - + MICRO_C (token number = "1"). The class of rules for parsing full MICRO clauses (such as House, street, sufdir, predir, pretyp, suftype, qualif) (ie ARC_C plus CIVIC_C). These rules are not used in the build phase. MICRO_C output tokens (excerpted from http://www.pagcgeo.org/docs/html/pagc-12.html#--r-typ--. - HOUSE + HOUSE is a text (token number 1): This is the street number on a street. Example 75 in 75 State Street. @@ -400,7 +400,7 @@ into includes in the future for easier maintenance. predir is text (token number 2): STREET NAME PRE-DIRECTIONAL such as North, South, East, West etc. - qual + qual is text (token number 3): STREET NAME PRE-MODIFIER Example OLD in 3715 OLD HIGHWAY 99. @@ -426,49 +426,49 @@ into includes in the future for easier maintenance. - + ARC_C (token number = "2"). The class of rules for parsing MICRO clauses, excluding the HOUSE attribute. As such uses same set of output tokens as MICRO_C minus the HOUSE token. - + CIVIC_C (token number = "3"). The class of rules for parsing the HOUSE attribute. EXTRA_C (token number = "4"). The class of rules for parsing EXTRA attributes - attributes excluded from geocoding. These rules are not used in the build phase. - + EXTRA_C output tokens (excerpted from http://www.pagcgeo.org/docs/html/pagc-12.html#--r-typ--. - BLDNG + BLDNG (token number 0): Unparsed building identifiers and types. - BOXH + BOXH i(token number 14): The BOX in BOX 3B - BOXT + BOXT (token number 15): The 3B in BOX 3B - RR + RR (token number 8): The RR in RR 7 - UNITH + UNITH (token number 16): The APT in APT 3B - UNITT + UNITT (token number 17): The 3B in APT 3B - UNKNWN + UNKNWN (token number 9): An otherwise unclassified output. @@ -476,7 +476,7 @@ into includes in the future for easier maintenance. - + lex table @@ -485,7 +485,7 @@ into includes in the future for easier maintenance. Description A lex (short for lexicon) table is used to classify alphanumeric input and associate that input with and (b) standardized representations. Things you will find in these tables are ONE mapped to stdword: 1. - + A lex has at least the following columns in the table. You may add @@ -494,23 +494,23 @@ into includes in the future for easier maintenance. Primary key of table - seq + seq integer: definition number? - - word + + word text: the input word - stdword + stdword text: the standardized replacement word - token + token integer: the kind of word it is. Only if it is used in this context will it be replaced. Refer to PAGC Tokens. @@ -518,7 +518,7 @@ into includes in the future for easier maintenance. - + gaz table @@ -527,7 +527,7 @@ into includes in the future for easier maintenance. Description A gaz (short for gazeteer) table is used to classify place names and associate that input with and (b) standardized representations. For example if you are in US, you may load these with State Names and associated abbreviations. - + A gaz table has at least the following columns in the table. You may add more columns if you wish for your own purposes. @@ -536,42 +536,42 @@ into includes in the future for easier maintenance. Primary key of table - seq + seq integer: definition number? - identifer used for that instance of the word - word + word text: the input word - stdword + stdword text: the standardized replacement word - token + token integer: the kind of word it is. Only if it is used in this context will it be replaced. Refer to PAGC Tokens. - - - + + + - + Address Standardizer Functions - + parse_address - + Takes a 1 line address and breaks into parts - + @@ -581,10 +581,10 @@ into includes in the future for easier maintenance. - + Description - + Returns takes as input an address, and returns a record output consisting of fields num, street, street2, address1, city, state, zip, zipplus, country. @@ -592,18 +592,18 @@ into includes in the future for easier maintenance. Availability: 2.2.0 &address_standardizer_required; - - + + Examples - + Single Addresss - SELECT num, street, city, zip, zipplus + SELECT num, street, city, zip, zipplus FROM parse_address('1 Devonshire Place, Boston, MA 02109-1234') AS a; - + num | street | city | zip | zipplus -----+------------------+--------+-------+--------- - 1 | Devonshire Place | Boston | 02109 | 1234 + 1 | Devonshire Place | Boston | 02109 | 1234 Table of addresses -- basic table @@ -617,7 +617,7 @@ VALUES ('529 Main Street, Boston MA, 02129'), ('124 Mount Auburn St, Cambridge, Massachusetts 02138'), ('950 Main Street, Worcester, MA 01610'); - -- parse the addresses + -- parse the addresses -- if you want all fields you can use (a).* SELECT addid, (a).num, (a).street, (a).city, (a).state, (a).zip, (a).zipplus FROM (SELECT addid, parse_address(address) As a @@ -632,22 +632,22 @@ FROM (SELECT addid, parse_address(address) As a 6 | 950 | Main Street | Worcester | MA | 01610 | (6 rows) - + See Also - + - + standardize_address - + Returns an stdaddr form of an input address utilizing lex, gaz, and rule tables. - + @@ -657,7 +657,7 @@ FROM (SELECT addid, parse_address(address) As a text rultab text address - + stdaddr standardize_address text lextab @@ -668,39 +668,39 @@ FROM (SELECT addid, parse_address(address) As a - + Description - + Returns an form of an input address utilizing table name, , and table names and an address. - + Variant 1: Takes an address as a single line. - Variant 2: Takes an address as 2 parts. A micro consisting of standard first line of postal address e.g. house_num street, and a macro consisting of standard postal second line of an address e.g city, state postal_code country. + Variant 2: Takes an address as 2 parts. A micro consisting of standard first line of postal address e.g. house_num street, and a macro consisting of standard postal second line of an address e.g city, state postal_code country. Availability: 2.2.0 &address_standardizer_required; - - + + Examples Using address_standardizer_data_us extension CREATE EXTENSION address_standardizer_data_us; -- only needs to be done once - + Variant 1: Single line address. This doesn't work well with non-US addresses -SELECT house_num, name, suftype, city, country, state, unit FROM standardize_address('us_lex', +SELECT house_num, name, suftype, city, country, state, unit FROM standardize_address('us_lex', 'us_gaz', 'us_rules', 'One Devonshire Place, PH 301, Boston, MA 02109'); house_num | name | suftype | city | country | state | unit ----------+------------+---------+--------+---------+---------------+----------------- 1 | DEVONSHIRE | PLACE | BOSTON | USA | MASSACHUSETTS | # PENTHOUSE 301 Using tables packaged with tiger geocoder. This example only works if you installed postgis_tiger_geocoder. - SELECT * FROM standardize_address('tiger.pagc_lex', + SELECT * FROM standardize_address('tiger.pagc_lex', 'tiger.pagc_gaz', 'tiger.pagc_rules', 'One Devonshire Place, PH 301, Boston, MA 02109-1234'); - + Make easier to read we'll dump output using hstore extension CREATE EXTENSION hstore; you need to install - SELECT (each(hstore(p))).* - FROM standardize_address('tiger.pagc_lex', 'tiger.pagc_gaz', + SELECT (each(hstore(p))).* + FROM standardize_address('tiger.pagc_lex', 'tiger.pagc_gaz', 'tiger.pagc_rules', 'One Devonshire Place, PH 301, Boston, MA 02109') As p; key | value ------------+----------------- @@ -720,12 +720,12 @@ FROM (SELECT addid, parse_address(address) As a postcode | 02109 house_num | 1 ruralroute | -(16 rows) - - +(16 rows) + + Variant 2: As a two part Address -SELECT (each(hstore(p))).* - FROM standardize_address('tiger.pagc_lex', 'tiger.pagc_gaz', +SELECT (each(hstore(p))).* + FROM standardize_address('tiger.pagc_lex', 'tiger.pagc_gaz', 'tiger.pagc_rules', 'One Devonshire Place, PH 301', 'Boston, MA 02109, US') As p; key | value ------------+----------------- @@ -748,14 +748,14 @@ FROM (SELECT addid, parse_address(address) As a (16 rows) - + See Also - + , , , , - + diff --git a/doc/extras_historytable.xml b/doc/extras_historytable.xml index 61c8e9c94..6b2d4d4fa 100644 --- a/doc/extras_historytable.xml +++ b/doc/extras_historytable.xml @@ -3,19 +3,19 @@ History Tracking - Suppose you have a table of data that represents the current state of a particular geographic feature. - A parcels table, or a roads table, or a fruit trees table, whatever. - Generally, GIS tools understand a table as a single entity into which they can update, insert and delete rows from. + Suppose you have a table of data that represents the current state of a particular geographic feature. + A parcels table, or a roads table, or a fruit trees table, whatever. + Generally, GIS tools understand a table as a single entity into which they can update, insert and delete rows from. How you do allow common GIS tools to work against your data, while maintaining an audit trail of what changes have been made, by whom, and what the past state of the data is? - This history_table extra module provides some utility functions for creating and maintaining history. + This history_table extra module provides some utility functions for creating and maintaining history. - + The history_table was also packaged in PostGIS 1.5, but added to the documentation in PostGIS 2.0. This package is written in plpgsql and located in the extras/history_table of PostGIS source tar balls and source repository. If you have a table 'roads', this module will maintain a 'roads_history' side table, which contains all the columns of the parent table, and the following additional columns: -history_id | integer | not null default +history_id | integer | not null default date_added | timestamp without time zone | not null default now() - date_deleted | timestamp without time zone | + date_deleted | timestamp without time zone | last_operation | character varying(30) | not null active_user | character varying(90) | not null default "current_user"() current_version | text | not null @@ -36,16 +36,16 @@ SELECT * FROM roads_history WHERE roads_pk = 111; Or, to retrieve a view of the roads table at any point in the past: -SELECT * FROM roads_history - WHERE date_added < 'January 1, 2001' AND +SELECT * FROM roads_history + WHERE date_added < 'January 1, 2001' AND ( date_deleted >= 'January 1, 2001' OR date_deleted IS NULL ); - + Postgis_Install_History Creates a table that will hold some interesting values for managing history tables. - + @@ -54,37 +54,37 @@ - + Description - + Creates a table that will hold some interesting values for managing history tables. Creates a table called historic_information - - + + Availability: 1.5.0 - - + + Examples - + SELECT postgis_install_history(); - + - + See Also - + Postgis_Enable_History Registers a tablein the history_information table for tracking and also adds in side line history table and insert, update, delete rules on the table. - + @@ -94,37 +94,37 @@ - + Description - + Registers a table in the history_information table for tracking and also adds in side line history table with same name as table but prefixed with history in the same schema as the original table. Puts in insert, update, delete rules on the table. Any inserts,updates,deletes of the geometry are recorded in the history table. - + This function currently relies on a geometry column being registered in geometry_columns and fails if the geometry column is not present in geometry_columns table. - + Availability: 1.5.0 - - + + Examples - + CREATE TABLE roads(gid SERIAL PRIMARY KEY, road_name varchar(150)); SELECT AddGeometryColumn('roads', 'geom', 26986, 'LINESTRING', 2); - + SELECT postgis_enable_history('public', 'roads', 'geom') As register_table; register_table -------------- t -INSERT INTO roads(road_name, geom) +INSERT INTO roads(road_name, geom) VALUES('Test Street', ST_GeomFromText('LINESTRING(231660.5 832170,231647 832202,231627.5 832250.5)',26986)); -- check transaction detail -- -SELECT date_added, last_operation, current_version -FROM roads_history +SELECT date_added, last_operation, current_version +FROM roads_history WHERE road_name = 'Test Street' ORDER BY date_added DESC; date_added | last_operation | current_version @@ -132,9 +132,9 @@ WHERE road_name = 'Test Street' ORDER BY date_added DESC; 2011-02-07 12:44:36.92 | INSERT | 2 - + - + See Also diff --git a/doc/extras_tigergeocoder.xml b/doc/extras_tigergeocoder.xml index 36bf005c9..576a2f768 100644 --- a/doc/extras_tigergeocoder.xml +++ b/doc/extras_tigergeocoder.xml @@ -11,28 +11,28 @@ prefixed with the state code e.g ma_addr, ma_edges etc with constraints to enforce only that state data. Each of these tables inherits from the tables addr, faces, edges, etc located in the tiger schema. All the geocode functions only reference the base tables, so there is no requirement that the data schema be called tiger_data or that data can't be further partitioned into other schemas -- e.g a different schema for each state, as long as all the tables inherit from the tables in the tiger schema. - - + + For instructions on how enable the extension in your database and also to load data using it, refer to Refer to . - - + + If you are using tiger geocoder (tiger_2010), you can upgrade the scripts using the accompanying upgrade_geocoder.bat / .sh scripts in extras/tiger. One major change between tiger_2010 and tiger_2011+ is that the county and county and state tables are no longer broken out by state. If you have data from tiger_2010 and want replace with tiger_2015 refer to - + New in PostGIS 2.2.0 release is support for Tiger 2015 data and inclusion of Address Standardizer as part of PostGIS. New in PostGIS 2.1.0 release is ability to install tiger geocoder with PostgreSQL extension model if you are running PostgreSQL 9.1+. Refer to for details. - - The function as a drop in replacement for in-built . Refer to for compile and installation instructions. - + + The function as a drop in replacement for in-built . Refer to for compile and installation instructions. + Design: - The goal of this project is to build a fully functional geocoder that can process an arbitrary + The goal of this project is to build a fully functional geocoder that can process an arbitrary United States address string and using normalized TIGER census data, produce a point geometry and rating reflecting the location of the given address and likeliness of the location. The higher the rating number the worse the result. The reverse_geocode function, introduced in PostGIS 2.0.0 is useful for deriving the street address and cross streets of a GPS location. The geocoder should be simple for anyone familiar with PostGIS to install and use, and should be easily installable and usable on all platforms supported by PostGIS. @@ -44,7 +44,7 @@ you can upgrade the scripts using the accompanying upgrade_geocoder.bat Tiger Geocoder - + There are a couple other open source geocoders for PostGIS, that unlike tiger geocoder have the advantage of multi-country geocoding support Nominatim @@ -52,7 +52,7 @@ you can upgrade the scripts using the accompanying upgrade_geocoder.bat Just like the tiger geocoder, it has both a geocoder and a reverse geocoder component. From the documentation, it is unclear if it has a pure SQL interface like the tiger geocoder, or if a good deal of the logic is implemented in the web interface. GIS Graphy also utilizes PostGIS and like Nominatim works with OpenStreetMap (OSM) data. It comes with a loader to load OSM data and similar to Nominatim is capable of geocoding not just US. Much like Nominatim, it runs as a webservice and relies on Java 1.5, Servlet apps, Solr. GisGraphy is cross-platform and also has a reverse geocoder among some other neat features. - + Drop_Indexes_Generate_Script @@ -74,8 +74,8 @@ you can upgrade the scripts using the accompanying upgrade_geocoder.bat Generates a script that drops all non-primary key and non-unique indexes on tiger schema and user specified schema. Defaults schema to tiger_data if no schema is specified. This is useful for minimizing index bloat that may confuse the query planner or take up unnecessary space. Use in combination with to add just the indexes used by the geocoder. - - + + Availability: 2.0.0 @@ -119,7 +119,7 @@ DROP INDEX tiger_data.idx_tiger_data_ma_county_snd_name; : : - + @@ -128,7 +128,7 @@ DROP INDEX tiger_data.idx_tiger_data_ma_county_snd_name; , - + Drop_Nation_Tables_Generate_Script @@ -149,8 +149,8 @@ DROP INDEX tiger_data.idx_tiger_data_ma_county_snd_name; Description Generates a script that drops all tables in the specified schema that start with county_all, state_all or stae code followed by county or state. This is needed if you are upgrading from tiger_2010 to tiger_2011 data. - - + + Availability: 2.1.0 @@ -165,7 +165,7 @@ DROP TABLE tiger_data.county_all_lookup; DROP TABLE tiger_data.state_all; DROP TABLE tiger_data.ma_county; DROP TABLE tiger_data.ma_state; - + @@ -174,7 +174,7 @@ DROP TABLE tiger_data.ma_state; - + Drop_State_Tables_Generate_Script @@ -197,8 +197,8 @@ DROP TABLE tiger_data.ma_state; Generates a script that drops all tables in the specified schema that are prefixed with the state abbreviation. Defaults schema to tiger_data if no schema is specified. This function is useful for dropping tables of a state just before you reload a state in case something went wrong during your previous load. - - + + Availability: 2.0.0 @@ -221,7 +221,7 @@ DROP TABLE tiger_data.pa_zip_lookup_base; DROP TABLE tiger_data.pa_zip_state; DROP TABLE tiger_data.pa_zip_state_loc; - + @@ -234,7 +234,7 @@ DROP TABLE tiger_data.pa_zip_state_loc; Geocode - Takes in an address as a string (or other normalized address) and outputs a set of possible locations which include a point geometry in NAD 83 long lat, a normalized address for each, and the rating. The lower the rating the more likely the match. + Takes in an address as a string (or other normalized address) and outputs a set of possible locations which include a point geometry in NAD 83 long lat, a normalized address for each, and the rating. The lower the rating the more likely the match. Results are sorted by lowest rating first. Can optionally pass in maximum results, defaults to 10, and restrict_region (defaults to NULL) @@ -264,12 +264,12 @@ DROP TABLE tiger_data.pa_zip_state_loc; Description - Takes in an address as a string (or already normalized address) and outputs a set of possible locations which include a point geometry in NAD 83 long lat, a normalized_address (addy) for each, and the rating. The lower the rating the more likely the match. + Takes in an address as a string (or already normalized address) and outputs a set of possible locations which include a point geometry in NAD 83 long lat, a normalized_address (addy) for each, and the rating. The lower the rating the more likely the match. Results are sorted by lowest rating first. Uses Tiger data (edges,faces,addr), PostgreSQL fuzzy string matching (soundex,levenshtein) and PostGIS line interpolation functions to interpolate address along the Tiger edges. The higher the rating the less likely the geocode is right. The geocoded point is defaulted to offset 10 meters from center-line off to side (L/R) of street address is located on. Enhanced: 2.0.0 to support Tiger 2010 structured data and revised some logic to improve speed, accuracy of geocoding, and to offset point from centerline to side of street address is located on. New parameter max_results useful for specifying ot just return the best result. - + @@ -277,29 +277,29 @@ DROP TABLE tiger_data.pa_zip_state_loc; Examples: Basic The below examples timings are on a 3.0 GHZ single processor Windows 7 machine with 2GB ram running PostgreSQL 9.1rc1/PostGIS 2.0 loaded with all of MA,MN,CA, RI state Tiger data loaded. Exact matches are faster to compute (61ms) - SELECT g.rating, ST_X(g.geomout) As lon, ST_Y(g.geomout) As lat, - (addy).address As stno, (addy).streetname As street, - (addy).streettypeabbrev As styp, (addy).location As city, (addy).stateabbrev As st,(addy).zip - FROM geocode('75 State Street, Boston MA 02109') As g; - rating | lon | lat | stno | street | styp | city | st | zip + SELECT g.rating, ST_X(g.geomout) As lon, ST_Y(g.geomout) As lat, + (addy).address As stno, (addy).streetname As street, + (addy).streettypeabbrev As styp, (addy).location As city, (addy).stateabbrev As st,(addy).zip + FROM geocode('75 State Street, Boston MA 02109') As g; + rating | lon | lat | stno | street | styp | city | st | zip --------+-------------------+------------------+------+--------+------+--------+----+------- 0 | -71.0556722990239 | 42.3589914927049 | 75 | State | St | Boston | MA | 02109 Even if zip is not passed in the geocoder can guess (took about 122-150 ms) - SELECT g.rating, ST_AsText(ST_SnapToGrid(g.geomout,0.00001)) As wktlonlat, - (addy).address As stno, (addy).streetname As street, - (addy).streettypeabbrev As styp, (addy).location As city, (addy).stateabbrev As st,(addy).zip - FROM geocode('226 Hanover Street, Boston, MA',1) As g; - rating | wktlonlat | stno | street | styp | city | st | zip + SELECT g.rating, ST_AsText(ST_SnapToGrid(g.geomout,0.00001)) As wktlonlat, + (addy).address As stno, (addy).streetname As street, + (addy).streettypeabbrev As styp, (addy).location As city, (addy).stateabbrev As st,(addy).zip + FROM geocode('226 Hanover Street, Boston, MA',1) As g; + rating | wktlonlat | stno | street | styp | city | st | zip --------+---------------------------+------+---------+------+--------+----+------- 1 | POINT(-71.05528 42.36316) | 226 | Hanover | St | Boston | MA | 02113 - + Can handle misspellings and provides more than one possible solution with ratings and takes longer (500ms). -SELECT g.rating, ST_AsText(ST_SnapToGrid(g.geomout,0.00001)) As wktlonlat, - (addy).address As stno, (addy).streetname As street, - (addy).streettypeabbrev As styp, (addy).location As city, (addy).stateabbrev As st,(addy).zip - FROM geocode('31 - 37 Stewart Street, Boston, MA 02116') As g; - rating | wktlonlat | stno | street | styp | city | st | zip +SELECT g.rating, ST_AsText(ST_SnapToGrid(g.geomout,0.00001)) As wktlonlat, + (addy).address As stno, (addy).streetname As street, + (addy).streettypeabbrev As styp, (addy).location As city, (addy).stateabbrev As st,(addy).zip + FROM geocode('31 - 37 Stewart Street, Boston, MA 02116') As g; + rating | wktlonlat | stno | street | styp | city | st | zip --------+---------------------------+------+--------+------+--------+----+------- 70 | POINT(-71.06459 42.35113) | 31 | Stuart | St | Boston | MA | 02116 @@ -315,19 +315,19 @@ VALUES ('529 Main Street, Boston MA, 02129'), ('26 Capen Street, Medford, MA'), ('124 Mount Auburn St, Cambridge, Massachusetts 02138'), ('950 Main Street, Worcester, MA 01610'); - + -- only update the first 3 addresses (323-704 ms - there are caching and shared memory effects so first geocode you do is always slower) -- -- for large numbers of addresses you don't want to update all at once --- since the whole geocode must commit at once --- For this example we rejoin with LEFT JOIN --- and set to rating to -1 rating if no match --- to ensure we don't regeocode a bad address +-- since the whole geocode must commit at once +-- For this example we rejoin with LEFT JOIN +-- and set to rating to -1 rating if no match +-- to ensure we don't regeocode a bad address UPDATE addresses_to_geocode - SET (rating, new_address, lon, lat) + SET (rating, new_address, lon, lat) = ( COALESCE((g.geo).rating,-1), pprint_addy((g.geo).addy), ST_X((g.geo).geomout)::numeric(8,5), ST_Y((g.geo).geomout)::numeric(8,5) ) -FROM (SELECT addid - FROM addresses_to_geocode +FROM (SELECT addid + FROM addresses_to_geocode WHERE rating IS NULL ORDER BY addid LIMIT 3) As a LEFT JOIN (SELECT addid, (geocode(address,1)) As geo FROM addresses_to_geocode As ag @@ -340,7 +340,7 @@ Query returned successfully: 3 rows affected, 480 ms execution time. SELECT * FROM addresses_to_geocode WHERE rating is not null; - addid | address | lon | lat | new_address | rating + addid | address | lon | lat | new_address | rating -------+----------------------------------------------+-----------+----------+-------------------------------------------+-------- 1 | 529 Main Street, Boston MA, 02129 | -71.07181 | 42.38359 | 529 Main St, Boston, MA 02129 | 0 2 | 77 Massachusetts Avenue, Cambridge, MA 02139 | -71.09428 | 42.35988 | 77 Massachusetts Ave, Cambridge, MA 02139 | 0 @@ -350,13 +350,13 @@ SELECT * FROM addresses_to_geocode WHERE rating is not null; Examples: Using Geometry filter -SELECT g.rating, ST_AsText(ST_SnapToGrid(g.geomout,0.00001)) As wktlonlat, - (addy).address As stno, (addy).streetname As street, - (addy).streettypeabbrev As styp, - (addy).location As city, (addy).stateabbrev As st,(addy).zip +SELECT g.rating, ST_AsText(ST_SnapToGrid(g.geomout,0.00001)) As wktlonlat, + (addy).address As stno, (addy).streetname As street, + (addy).streettypeabbrev As styp, + (addy).location As city, (addy).stateabbrev As st,(addy).zip FROM geocode('100 Federal Street, MA', - 3, - (SELECT ST_Union(the_geom) + 3, + (SELECT ST_Union(the_geom) FROM place WHERE statefp = '25' AND name = 'Lynn')::geometry ) As g; @@ -373,12 +373,12 @@ Total query runtime: 245 ms. , , , , , - + Geocode_Intersection - Takes in 2 streets that intersect and a state, city, zip, and outputs a set of possible locations on the first cross street that is at the intersection, also includes a point geometry in NAD 83 long lat, a normalized address for each location, and the rating. The lower the rating the more likely the match. + Takes in 2 streets that intersect and a state, city, zip, and outputs a set of possible locations on the first cross street that is at the intersection, also includes a point geometry in NAD 83 long lat, a normalized address for each location, and the rating. The lower the rating the more likely the match. Results are sorted by lowest rating first. Can optionally pass in maximum results, defaults to 10 @@ -402,13 +402,13 @@ Total query runtime: 245 ms. Description - Takes in 2 streets that intersect and a state, city, zip, and outputs a set of possible locations on the first cross street that is at the intersection, also includes a point geometry in NAD 83 long lat, a normalized address for each location, and the rating. The lower the rating the more likely the match. + Takes in 2 streets that intersect and a state, city, zip, and outputs a set of possible locations on the first cross street that is at the intersection, also includes a point geometry in NAD 83 long lat, a normalized address for each location, and the rating. The lower the rating the more likely the match. Results are sorted by lowest rating first. Can optionally pass in maximum results, defaults to 10. - Returns normalized_address (addy) for each, geomout as the point location in nad 83 long lat, and the rating. The lower the rating the more likely the match. + Returns normalized_address (addy) for each, geomout as the point location in nad 83 long lat, and the rating. The lower the rating the more likely the match. Results are sorted by lowest rating first. Uses Tiger data (edges,faces,addr), PostgreSQL fuzzy string matching (soundex,levenshtein) Availability: 2.0.0 - + @@ -416,20 +416,20 @@ Total query runtime: 245 ms. Examples: Basic The below examples timings are on a 3.0 GHZ single processor Windows 7 machine with 2GB ram running PostgreSQL 9.0/PostGIS 1.5 loaded with all of MA state Tiger data loaded. Currently a bit slow (3000 ms) Testing on Windows 2003 64-bit 8GB on PostGIS 2.0 PostgreSQL 64-bit Tiger 2011 data loaded -- (41ms) - SELECT pprint_addy(addy), st_astext(geomout),rating - FROM geocode_intersection( 'Haverford St','Germania St', 'MA', 'Boston', '02130',1); + SELECT pprint_addy(addy), st_astext(geomout),rating + FROM geocode_intersection( 'Haverford St','Germania St', 'MA', 'Boston', '02130',1); pprint_addy | st_astext | rating ----------------------------------+----------------------------+-------- 98 Haverford St, Boston, MA 02130 | POINT(-71.101375 42.31376) | 0 Even if zip is not passed in the geocoder can guess (took about 3500 ms on the windows 7 box), on the windows 2003 64-bit 741 ms - SELECT pprint_addy(addy), st_astext(geomout),rating + SELECT pprint_addy(addy), st_astext(geomout),rating FROM geocode_intersection('Weld', 'School', 'MA', 'Boston'); pprint_addy | st_astext | rating -------------------------------+--------------------------+-------- 98 Weld Ave, Boston, MA 02119 | POINT(-71.099 42.314234) | 3 99 Weld Ave, Boston, MA 02119 | POINT(-71.099 42.314234) | 3 - + @@ -438,7 +438,7 @@ Total query runtime: 245 ms. , , - + Get_Geocode_Setting @@ -465,14 +465,14 @@ Total query runtime: 245 ms. debug_geocode_intersection | false | boolean | debug | outputs debug information in notice log such as queries when geocode_intersection is called if true debug_normalize_address | false | boolean | debug | outputs debug information in notice log such as queries and intermediate expressions when normalize_address is called if true debug_reverse_geocode | false | boolean | debug | if true, outputs debug information in notice log such as queries and intermediate expressions when reverse_geocode - reverse_geocode_numbered_roads | 0 | integer | rating | For state and county highways, 0 - no preference in name, + reverse_geocode_numbered_roads | 0 | integer | rating | For state and county highways, 0 - no preference in name, 1 - prefer the numbered highway name, 2 - prefer local state/county name - use_pagc_address_parser | false | boolean | normalize | If set to true, will try to use the address_standardizer extension (via pagc_normalize_address) + use_pagc_address_parser | false | boolean | normalize | If set to true, will try to use the address_standardizer extension (via pagc_normalize_address) instead of tiger normalize_address built one Changed: 2.2.0 : default settings are now kept in a table called geocode_settings_default. Use customized settingsa re in geocode_settings and only contain those that have been set by user. Availability: 2.1.0 - + @@ -482,7 +482,7 @@ Total query runtime: 245 ms. result --------- false - + @@ -491,7 +491,7 @@ false - + Get_Tract @@ -515,7 +515,7 @@ false Given a geometry will return the census tract location of that geometry. NAD 83 long lat is assumed if no spatial ref sys is specified. Availability: 2.0.0 - + @@ -530,7 +530,7 @@ tract_name SELECT get_tract(ST_Point(-71.101375, 42.31376), 'tract_id' ) As tract_id; tract_id --------- -25025120301 +25025120301 @@ -539,7 +539,7 @@ tract_id > - + Install_Missing_Indexes @@ -563,8 +563,8 @@ tract_id define the index for those tables and then execute the generated script. This is a helper function that adds new indexes needed to make queries faster that may have been missing during the load process. This function is a companion to that in addition to generating the create index script, also executes it. It is called as part of the update_geocode.sql upgrade script. - - + + Availability: 2.0.0 @@ -578,7 +578,7 @@ tract_id ------------------------- t - + @@ -623,7 +623,7 @@ tract_id loader_lookuptables each record defines a kind of table (state, county), whether to process records in it and how to load them in. Defines the steps to import data, stage data, add, removes columns, indexes, and constraints for each. Each table is prefixed with the state and inherits from a table in the tiger schema. e.g. creates tiger_data.ma_faces which inherits from tiger.faces - + Availability: 2.0.0 includes this logic, but if you installed tiger geocoder prior to PostGIS 2.0.0 alpha5, you'll need to run this on the states you have already done to get these additional tables. @@ -654,9 +654,9 @@ del %TMPDIR%\*.* /Q %PSQL% -c "DROP SCHEMA tiger_staging CASCADE;" %PSQL% -c "CREATE SCHEMA tiger_staging;" cd %STATEDIR% -for /r %%z in (*.zip) do %UNZIPTOOL% e %%z -o%TMPDIR% -cd %TMPDIR% -%PSQL% -c "CREATE TABLE tiger_data.MA_tract(CONSTRAINT pk_MA_tract PRIMARY KEY (tract_id) ) INHERITS(tiger.tract); " +for /r %%z in (*.zip) do %UNZIPTOOL% e %%z -o%TMPDIR% +cd %TMPDIR% +%PSQL% -c "CREATE TABLE tiger_data.MA_tract(CONSTRAINT pk_MA_tract PRIMARY KEY (tract_id) ) INHERITS(tiger.tract); " %SHP2PGSQL% -c -s 4269 -g the_geom -W "latin1" tl_2010_25_tract10.dbf tiger_staging.ma_tract10 | %PSQL% %PSQL% -c "ALTER TABLE tiger_staging.MA_tract10 RENAME geoid10 TO tract_id; SELECT loader_load_staged_data(lower('MA_tract10'), lower('MA_tract')); " %PSQL% -c "CREATE INDEX tiger_data_MA_tract_the_geom_gist ON tiger_data.MA_tract USING gist(the_geom);" @@ -664,7 +664,7 @@ cd %TMPDIR% %PSQL% -c "ALTER TABLE tiger_data.MA_tract ADD CONSTRAINT chk_statefp CHECK (statefp = '25');" : Generate sh script -STATEDIR="/gisdata/www2.census.gov/geo/pvs/tiger2010st/25_Massachusetts" +STATEDIR="/gisdata/www2.census.gov/geo/pvs/tiger2010st/25_Massachusetts" TMPDIR="/gisdata/temp/" UNZIPTOOL=unzip WGETTOOL="/usr/bin/wget" @@ -695,7 +695,7 @@ for z in *.zip; do $UNZIPTOOL -o -d $TMPDIR $z; done - + Loader_Generate_Script @@ -731,7 +731,7 @@ for z in *.zip; do $UNZIPTOOL -o -d $TMPDIR $z; done loader_lookuptables each record defines a kind of table (state, county), whether to process records in it and how to load them in. Defines the steps to import data, stage data, add, removes columns, indexes, and constraints for each. Each table is prefixed with the state and inherits from a table in the tiger schema. e.g. creates tiger_data.ma_faces which inherits from tiger.faces - + Availability: 2.0.0 to support Tiger 2010 structured data and load census tract (tract), block groups (bg), and blocks (tabblocks) tables . @@ -762,7 +762,7 @@ set SHP2PGSQL="%PGBIN%shp2pgsql" Generate sh script SELECT loader_generate_script(ARRAY['MA','RI'], 'sh') AS result; -- result -- -STATEDIR="/gisdata/www2.census.gov/geo/pvs/tiger2010st/44_Rhode_Island" +STATEDIR="/gisdata/www2.census.gov/geo/pvs/tiger2010st/44_Rhode_Island" TMPDIR="/gisdata/temp/" UNZIPTOOL=unzip PGPORT=5432 @@ -785,7 +785,7 @@ wget http://www2.census.gov/geo/pvs/tiger2010st/44_Rhode_Island/ --no-parent --r - + Loader_Generate_Nation_Script @@ -819,7 +819,7 @@ wget http://www2.census.gov/geo/pvs/tiger2010st/44_Rhode_Island/ --no-parent --r loader_lookuptables each record defines a kind of table (state, county), whether to process records in it and how to load them in. Defines the steps to import data, stage data, add, removes columns, indexes, and constraints for each. Each table is prefixed with the state and inherits from a table in the tiger schema. e.g. creates tiger_data.ma_faces which inherits from tiger.faces - + Availability: 2.1.0 If you were running tiger_2010 version and you want to reload as state with tiger_2011, you'll need to for the very first load generate and run drop statements before you run this script. @@ -839,7 +839,7 @@ wget http://www2.census.gov/geo/pvs/tiger2010st/44_Rhode_Island/ --no-parent --r - + Missing_Indexes_Generate_Script @@ -864,8 +864,8 @@ wget http://www2.census.gov/geo/pvs/tiger2010st/44_Rhode_Island/ --no-parent --r define the index for those tables. This is a helper function that adds new indexes needed to make queries faster that may have been missing during the load process. As the geocoder is improved, this function will be updated to accommodate new indexes being used. If this function outputs nothing, it means all your tables have what we think are the key indexes already in place. - - + + Availability: 2.0.0 @@ -886,7 +886,7 @@ CREATE INDEX idx_tiger_data_ma_cousub_countyfp ON tiger_data.ma_cousub USING btr CREATE INDEX idx_tiger_data_ma_edges_countyfp ON tiger_data.ma_edges USING btree(countyfp); CREATE INDEX idx_tiger_data_ma_faces_countyfp ON tiger_data.ma_faces USING btree(countyfp); - + @@ -895,12 +895,12 @@ CREATE INDEX idx_tiger_data_ma_faces_countyfp ON tiger_data.ma_faces USING btree , - + Normalize_Address - Given a textual street address, returns a composite norm_addy type that has road suffix, prefix and type standardized, street, streetname etc. broken into separate fields. This function + Given a textual street address, returns a composite norm_addy type that has road suffix, prefix and type standardized, street, streetname etc. broken into separate fields. This function will work with just the lookup data packaged with the tiger_geocoder (no need for tiger census data). @@ -916,7 +916,7 @@ CREATE INDEX idx_tiger_data_ma_faces_countyfp ON tiger_data.ma_faces USING btree Description - Given a textual street address, returns a composite norm_addy type that has road suffix, prefix and type standardized, street, streetname etc. broken into separate fields. This is the first step in the geocoding process to + Given a textual street address, returns a composite norm_addy type that has road suffix, prefix and type standardized, street, streetname etc. broken into separate fields. This is the first step in the geocoding process to get all addresses into normalized postal form. No other data is required aside from what is packaged with the geocoder. This function just uses the various direction/state/suffix lookup tables preloaded with the tiger_geocoder and located in the tiger schema, so it doesn't need you to download tiger census data or any other additional data to make use of it. You may find the need to add more abbreviations or alternative namings to the various lookup tables in the tiger schema. @@ -965,18 +965,18 @@ CREATE INDEX idx_tiger_data_ma_faces_countyfp ON tiger_data.ma_faces USING btree SELECT address As orig, (g.na).streetname, (g.na).streettypeabbrev FROM (SELECT address, normalize_address(address) As na FROM addresses_to_geocode) As g; - - orig | streetname | streettypeabbrev + + orig | streetname | streettypeabbrev -----------------------------------------------------+---------------+------------------ 28 Capen Street, Medford, MA | Capen | St 124 Mount Auburn St, Cambridge, Massachusetts 02138 | Mount Auburn | St 950 Main Street, Worcester, MA 01610 | Main | St 529 Main Street, Boston MA, 02129 | Main | St 77 Massachusetts Avenue, Cambridge, MA 02139 | Massachusetts | Ave - 25 Wizard of Oz, Walaford, KS 99912323 | Wizard of Oz | + 25 Wizard of Oz, Walaford, KS 99912323 | Wizard of Oz | - - + + @@ -986,12 +986,12 @@ CREATE INDEX idx_tiger_data_ma_faces_countyfp ON tiger_data.ma_faces USING btree , - + Pagc_Normalize_Address - Given a textual street address, returns a composite norm_addy type that has road suffix, prefix and type standardized, street, streetname etc. broken into separate fields. This function + Given a textual street address, returns a composite norm_addy type that has road suffix, prefix and type standardized, street, streetname etc. broken into separate fields. This function will work with just the lookup data packaged with the tiger_geocoder (no need for tiger census data). Requires address_standardizer extension. @@ -1007,7 +1007,7 @@ CREATE INDEX idx_tiger_data_ma_faces_countyfp ON tiger_data.ma_faces USING btree Description - Given a textual street address, returns a composite norm_addy type that has road suffix, prefix and type standardized, street, streetname etc. broken into separate fields. This is the first step in the geocoding process to + Given a textual street address, returns a composite norm_addy type that has road suffix, prefix and type standardized, street, streetname etc. broken into separate fields. This is the first step in the geocoding process to get all addresses into normalized postal form. No other data is required aside from what is packaged with the geocoder. This function just uses the various pagc_* lookup tables preloaded with the tiger_geocoder and located in the tiger schema, so it doesn't need you to download tiger census data or any other additional data to make use of it. You may find the need to add more abbreviations or alternative namings to the various lookup tables in the tiger schema. @@ -1019,7 +1019,7 @@ CREATE INDEX idx_tiger_data_ma_faces_countyfp ON tiger_data.ma_faces USING btree (address) [predirAbbrev] (streetName) [streetTypeAbbrev] [postdirAbbrev] [internal] [location] [stateAbbrev] [zip] The native standardaddr of address_standardizer extension is at this time a bit richer than norm_addy since its designed to support international addresses (including country). standardaddr equivalent fields are: house_num,predir, name, suftype, sufdir, unit, city, state, postcode - + address is an integer: The street number @@ -1062,15 +1062,15 @@ CREATE INDEX idx_tiger_data_ma_faces_countyfp ON tiger_data.ma_faces USING btree SELECT addy.* FROM pagc_normalize_address('9000 E ROO ST STE 999, Springfield, CO') AS addy; - + address | predirabbrev | streetname | streettypeabbrev | postdirabbrev | internal | location | stateabbrev | zip | parsed ---------+--------------+------------+------------------+---------------+-----------+-------------+-------------+-----+-------- 9000 | E | ROO | ST | | SUITE 999 | SPRINGFIELD | CO | | t - + Batch call. There are currently speed issues with the way postgis_tiger_geocoder wraps the address_standardizer. These will hopefully be resolved in later editions. To work around them, if you need speed for batch geocoding to call generate a normaddy in batch mode, you are encouraged to directly call the address_standardizer standardize_address function as shown below which is similar exercise to what we did in that uses data created in . - + WITH g AS (SELECT address, ROW((sa).house_num, (sa).predir, (sa).name , (sa).suftype, (sa).sufdir, (sa).unit , (sa).city, (sa).state, (sa).postcode, true)::norm_addy As na FROM (SELECT address, standardize_address('tiger.pagc_lex' @@ -1079,7 +1079,7 @@ to directly call the address_standardizer standardize_address function as shown FROM addresses_to_geocode) As g) SELECT address As orig, (g.na).streetname, (g.na).streettypeabbrev FROM g; - + orig | streetname | streettypeabbrev -----------------------------------------------------+---------------+------------------ 529 Main Street, Boston MA, 02129 | MAIN | ST @@ -1095,7 +1095,7 @@ SELECT address As orig, (g.na).streetname, (g.na).streettypeabbrev , - + Pprint_Addy @@ -1117,7 +1117,7 @@ SELECT address As orig, (g.na).streetname, (g.na).streettypeabbrev Given a norm_addy composite type object, returns a pretty print representation of it. No other data is required aside from what is packaged with the geocoder. Usually used in conjunction with . - + @@ -1130,11 +1130,11 @@ SELECT address As orig, (g.na).streetname, (g.na).streettypeabbrev --------------------------------------- 202 E Fremont St, Las Vegas, NV 89101 - + Pretty print address a table of addresses SELECT address As orig, pprint_addy(normalize_address(address)) As pretty_address FROM addresses_to_geocode; - + orig | pretty_address -----------------------------------------------------+------------------------------------------- 529 Main Street, Boston MA, 02129 | 529 Main St, Boston MA, 02129 @@ -1151,14 +1151,14 @@ SELECT address As orig, (g.na).streetname, (g.na).streettypeabbrev - + Reverse_Geocode - + Takes a geometry point in a known spatial ref sys and returns a record containing an array of theoretically possible addresses and an array of cross streets. If include_strnum_range = true, includes the street range in the cross streets. - + @@ -1171,18 +1171,18 @@ SELECT address As orig, (g.na).streetname, (g.na).streettypeabbrev - + Description - + Takes a geometry point in a known spatial ref and returns a record containing an array of theoretically possible addresses and an array of cross streets. If include_strnum_range = true, includes the street range in the cross streets. include_strnum_range defaults to false if not passed in. Addresses are sorted according to which road a point is closest to so first address is most likely the right one. - + Why do we say theoretical instead of actual addresses. The Tiger data doesn't have real addresses, but just street ranges. As such the theoretical address is an interpolated address based on the street ranges. Like for example interpolating one of my addresses returns a 26 Court St. and 26 Court Sq., though there is no such place as 26 Court Sq. This is because a point may be at a corner of 2 streets and thus the logic interpolates along both streets. The logic also assumes addresses are equally spaced along a street, which of course is wrong since you can have a municipal building taking up a good chunk of the street range and the rest of the buildings are clustered at the end. - + Note: Hmm this function relies on Tiger data. If you have not loaded data covering the region of this point, then hmm you will get a record filled with NULLS. Returned elements of the record are as follows: @@ -1197,29 +1197,29 @@ SELECT address As orig, (g.na).streetname, (g.na).streettypeabbrev street an array of varchar: These are cross streets (or the street) (streets that intersect or are the street the point is projected to be on). - + Availability: 2.0.0 - - + + Examples - Example of a point at the corner of two streets, but closest to one. This is approximate location of MIT: 77 Massachusetts Ave, Cambridge, MA 02139 - Note that although we don't have 3 streets, PostgreSQL will just return null for entries above our upper bound so safe to use. This includes street ranges - SELECT pprint_addy(r.addy[1]) As st1, pprint_addy(r.addy[2]) As st2, pprint_addy(r.addy[3]) As st3, - array_to_string(r.street, ',') As cross_streets + Example of a point at the corner of two streets, but closest to one. This is approximate location of MIT: 77 Massachusetts Ave, Cambridge, MA 02139 + Note that although we don't have 3 streets, PostgreSQL will just return null for entries above our upper bound so safe to use. This includes street ranges + SELECT pprint_addy(r.addy[1]) As st1, pprint_addy(r.addy[2]) As st2, pprint_addy(r.addy[3]) As st3, + array_to_string(r.street, ',') As cross_streets FROM reverse_geocode(ST_GeomFromText('POINT(-71.093902 42.359446)',4269),true) As r; - + result ------ st1 | st2 | st3 | cross_streets -------------------------------------------+-----+-----+---------------------------------------------- 67 Massachusetts Ave, Cambridge, MA 02139 | | | 67 - 127 Massachusetts Ave,32 - 88 Vassar St - - Here we choose not to include the address ranges for the cross streets and picked a location + + Here we choose not to include the address ranges for the cross streets and picked a location really really close to a corner of 2 streets thus could be known by two different addresses. -SELECT pprint_addy(r.addy[1]) As st1, pprint_addy(r.addy[2]) As st2, +SELECT pprint_addy(r.addy[1]) As st1, pprint_addy(r.addy[2]) As st2, pprint_addy(r.addy[3]) As st3, array_to_string(r.street, ',') As cross_str FROM reverse_geocode(ST_GeomFromText('POINT(-71.06941 42.34225)',4269)) As r; @@ -1231,38 +1231,38 @@ result For this one we reuse our geocoded example from and we only want the primary address and at most 2 cross streets. -SELECT actual_addr, lon, lat, pprint_addy((rg).addy[1]) As int_addr1, +SELECT actual_addr, lon, lat, pprint_addy((rg).addy[1]) As int_addr1, (rg).street[1] As cross1, (rg).street[2] As cross2 FROM (SELECT address As actual_addr, lon, lat, reverse_geocode( ST_SetSRID(ST_Point(lon,lat),4326) ) As rg FROM addresses_to_geocode WHERE rating > -1) As foo; - actual_addr | lon | lat | int_addr1 | cross1 | cross2 + actual_addr | lon | lat | int_addr1 | cross1 | cross2 -----------------------------------------------------+-----------+----------+-------------------------------------------+-----------------+------------ - 529 Main Street, Boston MA, 02129 | -71.07181 | 42.38359 | 527 Main St, Boston, MA 02129 | Medford St | - 77 Massachusetts Avenue, Cambridge, MA 02139 | -71.09428 | 42.35988 | 77 Massachusetts Ave, Cambridge, MA 02139 | Vassar St | + 529 Main Street, Boston MA, 02129 | -71.07181 | 42.38359 | 527 Main St, Boston, MA 02129 | Medford St | + 77 Massachusetts Avenue, Cambridge, MA 02139 | -71.09428 | 42.35988 | 77 Massachusetts Ave, Cambridge, MA 02139 | Vassar St | 26 Capen Street, Medford, MA | -71.12377 | 42.41101 | 9 Edison Ave, Medford, MA 02155 | Capen St | Tesla Ave - 124 Mount Auburn St, Cambridge, Massachusetts 02138 | -71.12304 | 42.37328 | 3 University Rd, Cambridge, MA 02138 | Mount Auburn St | + 124 Mount Auburn St, Cambridge, Massachusetts 02138 | -71.12304 | 42.37328 | 3 University Rd, Cambridge, MA 02138 | Mount Auburn St | 950 Main Street, Worcester, MA 01610 | -71.82368 | 42.24956 | 3 Maywood St, Worcester, MA 01603 | Main St | Maywood Pl - + See Also - + , - + Topology_Load_Tiger - + Loads a defined region of tiger data into a PostGIS Topology and transforming the tiger data to spatial reference of the topology and snapping to the precision tolerance of the topology. - + @@ -1273,18 +1273,18 @@ FROM (SELECT address As actual_addr, lon, lat, - + Description - + Loads a defined region of tiger data into a PostGIS Topology. The faces, nodes and edges are transformed to the spatial reference system of the target topology and points are snapped to the tolerance of the target topology. The created faces, nodes, edges maintain the same ids as the original Tiger data faces, nodes, edges so that datasets can be in the future be more easily reconciled with tiger data. Returns summary details about the process. - + This would be useful for example for redistricting data where you require the newly formed polygons to follow the center lines of streets and for the resulting polygons not to overlap. - + This function relies on Tiger data as well as the installation of the PostGIS topology module. For more information, refer to and . If you have not loaded data covering the region of interest, then no topology records will be created. This function will also fail if you have not created a topology using the topology functions. - - Most topology validation errors are a result of tolerance issues where after transformation the edges points don't quite line up or overlap. + + Most topology validation errors are a result of tolerance issues where after transformation the edges points don't quite line up or overlap. To remedy the situation you may want to increase or lower the precision if you get topology validation failures. Required arguments: @@ -1299,24 +1299,24 @@ FROM (SELECT address As actual_addr, lon, lat, - + Availability: 2.0.0 Example: Boston, Massachusetts Topology - Create a topology for Boston, Massachusetts in Mass State Plane Feet (2249) + Create a topology for Boston, Massachusetts in Mass State Plane Feet (2249) with tolerance 0.25 feet and then load in Boston city tiger faces, edges, nodes. SELECT topology.CreateTopology('topo_boston', 2249, 0.25); createtopology -------------- 15 --- 60,902 ms ~ 1 minute on windows 7 desktop running 9.1 (with 5 states tiger data loaded) -SELECT tiger.topology_load_tiger('topo_boston', 'place', '2507000'); +-- 60,902 ms ~ 1 minute on windows 7 desktop running 9.1 (with 5 states tiger data loaded) +SELECT tiger.topology_load_tiger('topo_boston', 'place', '2507000'); -- topology_loader_tiger -- -29722 edges holding in temporary. 11108 faces added. 1875 edges of faces added. 20576 nodes added. -19962 nodes contained in a face. 0 edge start end corrected. 31597 edges added. - +29722 edges holding in temporary. 11108 faces added. 1875 edges of faces added. 20576 nodes added. +19962 nodes contained in a face. 0 edge start end corrected. 31597 edges added. + -- 41 ms -- SELECT topology.TopologySummary('topo_boston'); -- topologysummary-- @@ -1324,25 +1324,25 @@ Topology topo_boston (15), SRID 2249, precision 0.25 20576 nodes, 31597 edges, 11109 faces, 0 topogeoms in 0 layers -- 28,797 ms to validate yeh returned no errors -- -SELECT * FROM - topology.ValidateTopology('topo_boston'); - +SELECT * FROM + topology.ValidateTopology('topo_boston'); + error | id1 | id2 -------------------+----------+----------- - + Example: Suffolk, Massachusetts Topology - Create a topology for Suffolk, Massachusetts in Mass State Plane Meters (26986) + Create a topology for Suffolk, Massachusetts in Mass State Plane Meters (26986) with tolerance 0.25 meters and then load in Suffolk county tiger faces, edges, nodes. SELECT topology.CreateTopology('topo_suffolk', 26986, 0.25); -- this took 56,275 ms ~ 1 minute on Windows 7 32-bit with 5 states of tiger loaded -- must have been warmed up after loading boston -SELECT tiger.topology_load_tiger('topo_suffolk', 'county', '25025'); +SELECT tiger.topology_load_tiger('topo_suffolk', 'county', '25025'); -- topology_loader_tiger -- - 36003 edges holding in temporary. 13518 faces added. 2172 edges of faces added. - 24761 nodes added. 24075 nodes contained in a face. 0 edge start end corrected. 38175 edges added. + 36003 edges holding in temporary. 13518 faces added. 2172 edges of faces added. + 24761 nodes added. 24075 nodes contained in a face. 0 edge start end corrected. 38175 edges added. -- 31 ms -- SELECT topology.TopologySummary('topo_suffolk'); -- topologysummary-- @@ -1350,9 +1350,9 @@ SELECT topology.TopologySummary('topo_suffolk'); 24761 nodes, 38175 edges, 13519 faces, 0 topogeoms in 0 layers -- 33,606 ms to validate -- -SELECT * FROM - topology.ValidateTopology('topo_suffolk'); - +SELECT * FROM + topology.ValidateTopology('topo_suffolk'); + error | id1 | id2 -------------------+----------+----------- coincident nodes | 81045651 | 81064553 @@ -1369,7 +1369,7 @@ SELECT * FROM , , , - + Set_Geocode_Setting @@ -1393,7 +1393,7 @@ SELECT * FROM Sets value of specific setting stored in tiger.geocode_settings table. Settings allow you to toggle debugging of functions. Later plans will be to control rating with settings. Current list of settings are listed in . Availability: 2.1.0 - + @@ -1403,7 +1403,7 @@ SELECT * FROM SELECT set_geocode_setting('debug_geocode_address', 'true') As result; result --------- -true +true diff --git a/doc/extras_topology.xml b/doc/extras_topology.xml index 10259d2b0..f5922448b 100644 --- a/doc/extras_topology.xml +++ b/doc/extras_topology.xml @@ -11,17 +11,17 @@ All functions and tables associated with this module are installed in a schema called topology. Functions that are defined in SQL/MM standard are prefixed with ST_ and functions specific to PostGIS are not prefixed. To build PostGIS 2.0 with topology support, compile with the --with-topology option as described in . Some functions depend on GEOS 3.3+ so you should compile with GEOS 3.3+ to fully utilize the topology support. - + - This section lists the PostgreSQL data types installed by PostGIS Topology. Note we describe the casting behavior of these which is very - important especially when designing your own functions. - + This section lists the PostgreSQL data types installed by PostGIS Topology. Note we describe the casting behavior of these which is very + important especially when designing your own functions. + Topology Types - + getfaceedges_returntype @@ -40,7 +40,7 @@ - + TopoGeometry @@ -64,7 +64,7 @@ - + Casting Behavior This section lists the automatic as well as explicit casts allowed for this data type @@ -89,7 +89,7 @@ - + validatetopology_returntype @@ -110,7 +110,7 @@ id2 is an integer: For errors that involve 2 objects denotes the secondary edge / or node - + @@ -119,17 +119,17 @@ - + This section lists the PostgreSQL domains installed by PostGIS Topology. Domains can be used like object types as return objects of functions or table columns. The distinction between a domain and a type is that a domain is an existing type with a check constraint bound to it. - + Topology Domains - + TopoElement @@ -191,7 +191,7 @@ ERROR: value for domain topology.topoelement violates check constraint "dimensi - + TopoElementArray @@ -237,7 +237,7 @@ ERROR: value for domain topology.topoelementarray violates check constraint "di - + @@ -250,72 +250,72 @@ ERROR: value for domain topology.topoelementarray violates check constraint "di AddTopoGeometryColumn Adds a topogeometry column to an existing table, registers this new column as a layer in topology.layer and returns the new layer_id. - + integer AddTopoGeometryColumn varchar topology_name - + varchar schema_name - + varchar table_name - + varchar column_name - + varchar feature_type integer AddTopoGeometryColumn - + varchar topology_name - + varchar schema_name - + varchar table_name - + varchar column_name - + varchar feature_type - + integer child_layer - + Description - - Each TopoGeometry object belongs to a specific Layer of a specific Topology. Before creating a TopoGeometry object you need to create its TopologyLayer. + + Each TopoGeometry object belongs to a specific Layer of a specific Topology. Before creating a TopoGeometry object you need to create its TopologyLayer. A Topology Layer is an association of a feature-table with the topology. It also contain type and hierarchy information. We create a layer using the AddTopoGeometryColumn() function: This function will both add the requested column to the table and add a record to the topology.layer table with all the given info. - If you don't specify [child_layer] (or set it to NULL) this layer would contain Basic TopoGeometries (composed by primitive topology elements). + If you don't specify [child_layer] (or set it to NULL) this layer would contain Basic TopoGeometries (composed by primitive topology elements). Otherwise this layer will contain hierarchical TopoGeometries (composed by TopoGeometries from the child_layer). - + Once the layer is created (its id is returned by the AddTopoGeometryColumn function) you're ready to construct TopoGeometry objects in it - Valid feature_types are: POINT, LINE, POLYGON, COLLECTION - + Valid feature_types are: POINT, LINE, POLYGON, COLLECTION + Availability: 1.? - + - - + + Examples - -- Note for this example we created our new table in the ma_topo schema --- though we could have created it in a different schema -- in which case topology_name and schema_name would be different + -- Note for this example we created our new table in the ma_topo schema +-- though we could have created it in a different schema -- in which case topology_name and schema_name would be different CREATE SCHEMA ma; CREATE TABLE ma.parcels(gid serial, parcel_id varchar(20) PRIMARY KEY, address text); SELECT topology.AddTopoGeometryColumn('ma_topo', 'ma', 'parcels', 'topo', 'POLYGON'); @@ -325,21 +325,21 @@ CREATE TABLE ri.roads(gid serial PRIMARY KEY, road_name text); SELECT topology.AddTopoGeometryColumn('ri_topo', 'ri', 'roads', 'topo', 'LINE'); - + See Also - + , DropTopology - + Use with caution: Drops a topology schema and deletes its reference from topology.topology table and references to tables in that schema from the geometry_columns table. - + @@ -348,24 +348,24 @@ SELECT topology.AddTopoGeometryColumn('ri_topo', 'ri', 'roads', 'topo', 'LINE'); - + Description - + Drops a topology schema and deletes its reference from topology.topology table and references to tables in that schema from the geometry_columns table. This function should be USED WITH CAUTION, as it could destroy data you care about. If the schema does not exist, it just removes reference entries the named schema. - + Availability: 1.? - - + + Examples Cascade drops the ma_topo schema and removes all references to it in topology.topology and geometry_columns. SELECT topology.DropTopology('ma_topo'); - + See Also @@ -375,10 +375,10 @@ SELECT topology.AddTopoGeometryColumn('ri_topo', 'ri', 'roads', 'topo', 'LINE'); DropTopoGeometryColumn - + Drops the topogeometry column from the table named table_name in schema schema_name and unregisters the columns from topology.layer table. - + @@ -389,37 +389,37 @@ SELECT topology.AddTopoGeometryColumn('ri_topo', 'ri', 'roads', 'topo', 'LINE'); - + Description - + Drops the topogeometry column from the table named table_name in schema schema_name and unregisters the columns from topology.layer table. Returns summary of drop status. NOTE: it first sets all values to NULL before dropping to bypass referential integrity checks. - + Availability: 1.? - - + + Examples SELECT topology.DropTopoGeometryColumn('ma_topo', 'parcel_topo', 'topo'); - + See Also - + TopologySummary - + Takes a topology name and provides summary totals of types of objects in topology - + @@ -428,21 +428,21 @@ SELECT topology.AddTopoGeometryColumn('ri_topo', 'ri', 'roads', 'topo', 'LINE'); - + Description - + Takes a topology name and provides summary totals of types of objects in topology. - + Availability: 2.0.0 - - + + Examples SELECT topology.topologysummary('city_data'); - topologysummary + topologysummary -------------------------------------------------------- Topology city_data (329), SRID 4326, precision: 0 22 nodes, 24 edges, 10 faces, 29 topogeoms in 5 layers @@ -465,14 +465,14 @@ SELECT topology.AddTopoGeometryColumn('ri_topo', 'ri', 'roads', 'topo', 'LINE'); - + ValidateTopology - + Returns a set of validatetopology_returntype objects detailing issues with topology - + @@ -481,12 +481,12 @@ SELECT topology.AddTopoGeometryColumn('ri_topo', 'ri', 'roads', 'topo', 'LINE'); - + Description - + Returns a set of objects detailing issues with topology. List of possible errors and what the returned ids represent are displayed below: - + Errorid1id2 @@ -544,24 +544,24 @@ SELECT topology.AddTopoGeometryColumn('ri_topo', 'ri', 'roads', 'topo', 'LINE'); - + Availability: 1.0.0 - + Enhanced: 2.0.0 more efficient edge crossing detection and fixes for false positives that were existent in prior versions. Changed: 2.2.0 values for id1 and id2 were swapped for 'edge crosses node' to be consistent with error description. - - + + Examples SELECT * FROM topology.ValidateTopology('ma_topo'); error | id1 | id2 -------------------+-----+----- -face without edges | 0 | +face without edges | 0 | - + See Also @@ -583,27 +583,27 @@ face without edges | 0 | CreateTopology Creates a new topology schema and registers this new schema in the topology.topology table. - + integer CreateTopology varchar topology_schema_name - + integer CreateTopology varchar topology_schema_name integer srid - + integer CreateTopology varchar topology_schema_name integer srid double precision tolerance - + integer CreateTopology varchar topology_schema_name @@ -613,38 +613,38 @@ face without edges | 0 | - + Description - + Creates a new schema with name topology_name consisting of tables (edge_data,face,node, relation - and registers this new topology in the topology.topology table. It returns the id of the topology in the topology table. The srid is the spatial reference identified as + and registers this new topology in the topology.topology table. It returns the id of the topology in the topology table. The srid is the spatial reference identified as defined in spatial_ref_sys table for that topology. Topologies must be uniquely named. The tolerance is measured in the units of the spatial reference system. If the tolerance is not specified defaults to 0. - + This is similar to the SQL/MM but a bit more functional. hasz defaults to false if not specified. - + Availability: 1.? - - + + Examples This example creates a new schema called ma_topo that will store edges, faces, and relations in Massachusetts State Plane meters. The tolerance represents 1/2 meter since the spatial reference system is a meter based spatial reference system SELECT topology.CreateTopology('ma_topo',26986, 0.5); - + Create Rhode Island topology in State Plane ft SELECT topology.CreateTopology('ri_topo',3438) As topoid; topoid ------ 2 - + See Also - + , , @@ -654,7 +654,7 @@ topoid CopyTopology Makes a copy of a topology structure (nodes, edges, faces, layers and TopoGeometries). - + @@ -664,36 +664,36 @@ topoid - + Description - + Creates a new topology with name new_topology_name and SRID and precision taken from existing_topology_name, copies all nodes, edges and faces in there, copies layers and their TopoGeometries too. - + The new rows in topology.layer will contain synthesized values for schema_name, table_name and feature_column. This is because the TopoGeometry will only exist as a definition but won't be available in any user-level table yet. - + Availability: 2.0.0 - - + + Examples -This example makes a backup of a topology called ma_topo +This example makes a backup of a topology called ma_topo SELECT topology.CopyTopology('ma_topo', 'ma_topo_bakup'); - + - + See Also - + , @@ -703,7 +703,7 @@ This example makes a backup of a topology called ma_topo ST_InitTopoGeo Creates a new topology schema and registers this new schema in the topology.topology table and details summary of process. - + @@ -712,18 +712,18 @@ This example makes a backup of a topology called ma_topo - + Description - + This is an SQL-MM equivalent of CreateTopology but lacks the spatial reference and tolerance options of CreateTopology and outputs a text description of creation instead of topology id. - + Availability: 1.? &sqlmm_compliant; SQL-MM 3 Topo-Geo and Topo-Net 3: Routine Details: X.3.17 - - + + Examples SELECT topology.ST_InitTopoGeo('topo_schema_to_create') AS topocreation; @@ -732,25 +732,25 @@ This example makes a backup of a topology called ma_topo Topology-Geometry 'topo_schema_to_create' (id:7) created. - + See Also - + - + ST_CreateTopoGeo - + -Adds a collection of geometries to a given empty topology and returns a message detailing success. +Adds a collection of geometries to a given empty topology and returns a message detailing success. - + @@ -760,28 +760,28 @@ Adds a collection of geometries to a given empty topology and returns a message - + Description - + -Adds a collection of geometries to a given empty topology and returns a message detailing success. +Adds a collection of geometries to a given empty topology and returns a message detailing success. - + Useful for populating an empty topology. - - + + Availability: 2.0 - &sqlmm_compliant; SQL-MM: Topo-Geo and Topo-Net 3: Routine Details -- X.3.18 + &sqlmm_compliant; SQL-MM: Topo-Geo and Topo-Net 3: Routine Details -- X.3.18 - - + + Examples -- Populate topology -- -SELECT topology.ST_CreateTopoGeo('ri_topo', +SELECT topology.ST_CreateTopoGeo('ri_topo', ST_GeomFromText('MULTILINESTRING((384744 236928,384750 236923,384769 236911,384799 236895,384811 236890,384833 236884, 384844 236882,384866 236881,384879 236883,384954 236898,385087 236932,385117 236938, 385167 236938,385203 236941,385224 236946,385233 236950,385241 236956,385254 236971, @@ -791,21 +791,21 @@ SELECT topology.ST_CreateTopoGeo('ri_topo', 385237 237383,385238 237399,385236 237407,385227 237419,385213 237430,385193 237439, 385174 237451,385170 237455,385169 237460,385171 237475,385181 237503,385190 237521, 385200 237533,385206 237538,385213 237541,385221 237542,385235 237540,385242 237541, - 385249 237544,385260 237555,385270 237570,385289 237584,385292 237589,385291 237596,385284 237630))',3438) + 385249 237544,385260 237555,385270 237570,385289 237584,385292 237589,385291 237596,385284 237630))',3438) ); st_createtopogeo ---------------------------- Topology ri_topo populated - - + + -- create tables and topo geometries -- CREATE TABLE ri.roads(gid serial PRIMARY KEY, road_name text); SELECT topology.AddTopoGeometryColumn('ri_topo', 'ri', 'roads', 'topo', 'LINE'); - + See Also @@ -816,12 +816,12 @@ SELECT topology.AddTopoGeometryColumn('ri_topo', 'ri', 'roads', 'topo', 'LINE'); TopoGeo_AddPoint - + Adds a point to an existing topology using a tolerance and possibly splitting an existing edge. - + @@ -832,10 +832,10 @@ Adds a point to an existing topology using a tolerance and possibly splitting an - + Description - + Adds a point to an existing topology and return its identifier. The given point will snap to existing nodes or edges within given tolerance. @@ -845,8 +845,8 @@ An existing edge may be split by the snapped point. Availability: 2.0.0 - - + + See Also @@ -862,12 +862,12 @@ An existing edge may be split by the snapped point. TopoGeo_AddLineString - + Adds a linestring to an existing topology using a tolerance and possibly splitting existing edges/faces. Returns edge identifiers - + @@ -878,10 +878,10 @@ Adds a linestring to an existing topology using a tolerance and possibly splitti - + Description - + Adds a linestring to an existing topology and return a set of edge identifiers forming it up. The given line will snap to existing nodes or edges within given tolerance. @@ -891,8 +891,8 @@ Existing edges and faces may be split by the line. Availability: 2.0.0 - - + + See Also @@ -908,12 +908,12 @@ Existing edges and faces may be split by the line. TopoGeo_AddPolygon - + Adds a polygon to an existing topology using a tolerance and possibly splitting existing edges/faces. - + @@ -924,10 +924,10 @@ Adds a polygon to an existing topology using a tolerance and possibly splitting - + Description - + Adds a polygon to an existing topology and return a set of face identifiers forming it up. The boundary of the given polygon will snap to existing nodes or edges within given tolerance. @@ -937,8 +937,8 @@ Existing edges and faces may be split by the boundary of the new polygon. Availability: 2.0.0 - - + + See Also @@ -951,7 +951,7 @@ Existing edges and faces may be split by the boundary of the new polygon. - + @@ -965,10 +965,10 @@ Existing edges and faces may be split by the boundary of the new polygon. ST_AddIsoNode - + Adds an isolated node to a face in a topology and returns the nodeid of the new node. If face is null, the node is still created. - + @@ -979,27 +979,27 @@ Existing edges and faces may be split by the boundary of the new polygon. - + Description - + Adds an isolated node with point location apoint to an existing face with faceid aface to a topology atopology and returns the nodeid of the new node. If the spatial reference system (srid) of the point geometry is not the same as the topology, the apoint is not a point geometry, the point is null, or the point intersects an existing edge (even at the boundaries) then an exception is thrown. If the point already exists as a node, an exception is thrown. If aface is not null and the apoint is not within the face, then an exception is thrown. - + Availability: 1.? - &sqlmm_compliant; SQL-MM: Topo-Net Routines: X+1.3.1 + &sqlmm_compliant; SQL-MM: Topo-Net Routines: X+1.3.1 - - + + Examples - + See Also @@ -1010,10 +1010,10 @@ Existing edges and faces may be split by the boundary of the new polygon. ST_AddIsoEdge - + Adds an isolated edge defined by geometry alinestring to a topology connecting two existing isolated nodes anode and anothernode and returns the edge id of the new edge. - + @@ -1025,42 +1025,42 @@ Existing edges and faces may be split by the boundary of the new polygon. - + Description - + Adds an isolated edge defined by geometry alinestring to a topology connecting two existing isolated nodes anode and anothernode and returns the edge id of the new edge. If the spatial reference system (srid) of the alinestring geometry is not the same as the topology, any of the input arguments are null, or the nodes are contained in more than one face, or the nodes are start or end nodes of an existing edge, then an exception is thrown. If the alinestring is not within the face of the face the anode and anothernode belong to, then an exception is thrown. If the anode and anothernode are not the start and end points of the alinestring then an exception is thrown. - + Availability: 1.? - &sqlmm_compliant; SQL-MM: Topo-Geo and Topo-Net 3: Routine Details: X.3.4 + &sqlmm_compliant; SQL-MM: Topo-Geo and Topo-Net 3: Routine Details: X.3.4 - - + + Examples - + See Also , , - - + + ST_AddEdgeNewFaces - + Add a new edge and, if in doing so it splits a face, delete the original face and replace it with two new faces. - + @@ -1072,10 +1072,10 @@ Existing edges and faces may be split by the boundary of the new polygon. - + Description - + Add a new edge and, if in doing so it splits a face, delete the original face and replace it with two new faces. @@ -1087,22 +1087,22 @@ Updates all existing joined edges and relationships accordingly. If any arguments are null, the given nodes are unknown (must already exist in the node table of the topology schema) , - the acurve is not a LINESTRING, the anode and anothernode are not the start + the acurve is not a LINESTRING, the anode and anothernode are not the start and endpoints of acurve then an error is thrown. If the spatial reference system (srid) of the acurve geometry is not the same as the topology an exception is thrown. - + Availability: 2.0 &sqlmm_compliant; SQL-MM: Topo-Geo and Topo-Net 3: Routine Details: X.3.12 - - + + Examples - + See Also @@ -1111,13 +1111,13 @@ Updates all existing joined edges and relationships accordingly. - + ST_AddEdgeModFace - + Add a new edge and, if in doing so it splits a face, modify the original face and add a new face. - + @@ -1129,17 +1129,17 @@ Updates all existing joined edges and relationships accordingly. - + Description - + Add a new edge and, if doing so splits a face, modify the original face and add a new one. -If possible, the new face will be created on left side of the new edge. +If possible, the new face will be created on left side of the new edge. This will not be possible if the face on the left side will need to be the Universe face (unbounded). @@ -1153,21 +1153,21 @@ Updates all existing joined edges and relationships accordingly. If any arguments are null, the given nodes are unknown (must already exist in the node table of the topology schema) , - the acurve is not a LINESTRING, the anode and anothernode are not the start + the acurve is not a LINESTRING, the anode and anothernode are not the start and endpoints of acurve then an error is thrown. If the spatial reference system (srid) of the acurve geometry is not the same as the topology an exception is thrown. Availability: 2.0 &sqlmm_compliant; SQL-MM: Topo-Geo and Topo-Net 3: Routine Details: X.3.13 - - + + Examples - + See Also @@ -1179,13 +1179,13 @@ Updates all existing joined edges and relationships accordingly. ST_RemEdgeNewFace - + Removes an edge and, if the removed edge separated two faces, delete the original faces and replace them with a new face. - + @@ -1195,10 +1195,10 @@ delete the original faces and replace them with a new face. - + Description - + Removes an edge and, if the removed edge separated two faces, delete the original faces and replace them with a new face. @@ -1206,7 +1206,7 @@ delete the original faces and replace them with a new face. Returns the id of a newly created face or NULL, if no new face is created. -No new face is created when the removed edge is dangling or isolated or +No new face is created when the removed edge is dangling or isolated or confined with the universe face (possibly making the universe flood into the face on the other side). @@ -1227,19 +1227,19 @@ If any arguments are null, the given edge is unknown (must already exist in the edge table of the topology schema), the topology name is invalid then an error is thrown. - + Availability: 2.0 &sqlmm_compliant; SQL-MM: Topo-Geo and Topo-Net 3: Routine Details: X.3.14 - - + + Examples - + See Also @@ -1248,16 +1248,16 @@ name is invalid then an error is thrown. - + ST_RemEdgeModFace - + Removes an edge and, if the removed edge separated two faces, delete one of the them and modify the other to take the space of both. - + @@ -1267,14 +1267,14 @@ delete one of the them and modify the other to take the space of both. - + Description - + Removes an edge and, if the removed edge separated two faces, delete one of the them and modify the other to take the space of both. -Preferentially keeps the face on the right, to be symmetric with +Preferentially keeps the face on the right, to be symmetric with ST_AddEdgeModFace also keeping it. Returns the id of the face remaining in place of the removed edge. @@ -1295,19 +1295,19 @@ If any arguments are null, the given edge is unknown (must already exist in the edge table of the topology schema), the topology name is invalid then an error is thrown. - + Availability: 2.0 &sqlmm_compliant; SQL-MM: Topo-Geo and Topo-Net 3: Routine Details: X.3.15 - - + + Examples - + See Also @@ -1315,16 +1315,16 @@ name is invalid then an error is thrown. - + ST_ChangeEdgeGeom - + Changes the shape of an edge without affecting the topology structure. - + @@ -1335,10 +1335,10 @@ Changes the shape of an edge without affecting the topology structure. - + Description - + Changes the shape of an edge without affecting the topology structure. @@ -1357,7 +1357,7 @@ change the underlying topology then an error is thrown. If moving the edge from old to new position would hit an obstacle then an error is thrown. - + Availability: 1.1.0 @@ -1368,16 +1368,16 @@ an error is thrown. &sqlmm_compliant; SQL-MM: Topo-Geo and Topo-Net 3: Routine Details X.3.6 - - + + Examples - SELECT topology.ST_ChangeEdgeGeom('ma_topo', 1, + SELECT topology.ST_ChangeEdgeGeom('ma_topo', 1, ST_GeomFromText('LINESTRING(227591.9 893900.4,227622.6 893844.3,227641.6 893816.6, 227704.5 893778.5)', 26986) ); ---- Edge 1 changed - + See Also @@ -1386,14 +1386,14 @@ an error is thrown. - + ST_ModEdgeSplit - + Split an edge by creating a new node along an existing edge, modifying the original edge and adding a new edge. - + @@ -1404,10 +1404,10 @@ an error is thrown. - + Description - + Split an edge by creating a new node along an existing edge, modifying the original edge and adding a new edge. @@ -1420,14 +1420,14 @@ Returns the identifier of the newly added node. Changed: 2.0 - In prior versions, this was misnamed ST_ModEdgesSplit &sqlmm_compliant; SQL-MM: Topo-Geo and Topo-Net 3: Routine Details: X.3.9 - - + + Examples -- Add an edge -- SELECT topology.AddEdge('ma_topo', ST_GeomFromText('LINESTRING(227592 893910, 227600 893910)', 26986) ) As edgeid; - + -- edgeid- 3 @@ -1439,7 +1439,7 @@ SELECT topology.ST_ModEdgeSplit('ma_topo', 3, ST_SetSRID(ST_Point(227594,893910 7 - + See Also @@ -1455,13 +1455,13 @@ SELECT topology.ST_ModEdgeSplit('ma_topo', 3, ST_SetSRID(ST_Point(227594,893910 ST_ModEdgeHeal - + Heal two edges by deleting the node connecting them, modifying the first edge and deleting the second edge. Returns the id of the deleted node. - + @@ -1472,10 +1472,10 @@ and deleting the second edge. Returns the id of the deleted node. - + Description - + Heal two edges by deleting the node connecting them, modifying the first edge and deleting the second edge. @@ -1487,8 +1487,8 @@ Updates all existing joined edges and relationships accordingly. Availability: 2.0 &sqlmm_compliant; SQL-MM: Topo-Geo and Topo-Net 3: Routine Details: X.3.9 - - + + See Also @@ -1502,14 +1502,14 @@ Updates all existing joined edges and relationships accordingly. ST_NewEdgeHeal - + Heal two edges by deleting the node connecting them, deleting both edges, and replacing them with an edge whose direction is the same as the first edge provided. - + @@ -1520,10 +1520,10 @@ edge provided. - + Description - + Heal two edges by deleting the node connecting them, deleting both edges, and replacing them with an edge whose direction is the same as the first @@ -1531,14 +1531,14 @@ edge provided. Returns the id of the new edge replacing the healed ones. Updates all existing joined edges and relationships accordingly. - - + + Availability: 2.0 &sqlmm_compliant; SQL-MM: Topo-Geo and Topo-Net 3: Routine Details: X.3.9 - - + + See Also @@ -1550,13 +1550,13 @@ Updates all existing joined edges and relationships accordingly. - + ST_MoveIsoNode - + Moves an isolated node in a topology from one point to another. If new apoint geometry exists as a node an error is thrown. REturns description of move. - + @@ -1567,21 +1567,21 @@ Updates all existing joined edges and relationships accordingly. - + Description - + Moves an isolated node in a topology from one point to another. If new apoint geometry exists as a node an error is thrown. If any arguments are null, the apoint is not a point, the existing node is not isolated (is a start or end point of an existing edge), new node location intersects an existing edge (even at the end points) then an exception is thrown. If the spatial reference system (srid) of the point geometry is not the same as the topology an exception is thrown. - - + + Availability: 1.? - &sqlmm_compliant; SQL-MM: Topo-Net Routines: X.3.2 + &sqlmm_compliant; SQL-MM: Topo-Net Routines: X.3.2 - - + + Examples @@ -1591,25 +1591,25 @@ SELECT topology.ST_AddIsoNode('ma_topo', NULL, ST_GeomFromText('POINT(227579 89 -------- 7 -- Move the new node -- -SELECT topology.ST_MoveIsoNode('ma_topo', 7, ST_GeomFromText('POINT(227579.5 893916.5)', 26986) ) As descrip; +SELECT topology.ST_MoveIsoNode('ma_topo', 7, ST_GeomFromText('POINT(227579.5 893916.5)', 26986) ) As descrip; descrip ---------------------------------------------------- Isolated Node 7 moved to location 227579.5,893916.5 - + See Also - + ST_NewEdgesSplit - + Split an edge by creating a new node along an existing edge, deleting the original edge and replacing it with two new edges. Returns the id of the new node created that joins the new edges. - + @@ -1620,10 +1620,10 @@ Isolated Node 7 moved to location 227579.5,893916.5 - + Description - + Split an edge with edge id anedge by creating a new node with point location apoint along current @@ -1633,14 +1633,14 @@ Updates all existing joined edges and relationships accordingly. If the spatial reference system (srid) of the point geometry is not the same as the topology, the apoint is not a point geometry, the point is null, the point already exists as a node, the edge does not correspond to an existing edge or the point is not within the edge then an exception is thrown. - - + + Availability: 1.? - &sqlmm_compliant; SQL-MM: Topo-Net Routines: X.3.8 + &sqlmm_compliant; SQL-MM: Topo-Net Routines: X.3.8 - - + + Examples @@ -1651,12 +1651,12 @@ edgeid ------ 2 -- Split the new edge -- -SELECT topology.ST_NewEdgesSplit('ma_topo', 2, ST_GeomFromText('POINT(227578.5 893913.5)', 26986) ) As newnodeid; +SELECT topology.ST_NewEdgesSplit('ma_topo', 2, ST_GeomFromText('POINT(227578.5 893913.5)', 26986) ) As newnodeid; newnodeid --------- 6 - + See Also @@ -1668,14 +1668,14 @@ SELECT topology.ST_NewEdgesSplit('ma_topo', 2, ST_GeomFromText('POINT(227578.5 - + ST_RemoveIsoNode - + Removes an isolated node and returns description of action. If the node is not isolated (is start or end of an edge), then an exception is thrown. - + @@ -1685,19 +1685,19 @@ SELECT topology.ST_NewEdgesSplit('ma_topo', 2, ST_GeomFromText('POINT(227578.5 - + Description - + Removes an isolated node and returns description of action. If the node is not isolated (is start or end of an edge), then an exception is thrown. - - + + Availability: 1.? - &sqlmm_compliant; SQL-MM: Topo-Geo and Topo-Net 3: Routine Details: X+1.3.3 + &sqlmm_compliant; SQL-MM: Topo-Geo and Topo-Net 3: Routine Details: X+1.3.3 - - + + Examples @@ -1708,7 +1708,7 @@ SELECT topology.ST_RemoveIsoNode('ma_topo', 7 ) As result; Isolated node 7 removed - + See Also @@ -1722,10 +1722,10 @@ SELECT topology.ST_RemoveIsoNode('ma_topo', 7 ) As result; GetEdgeByPoint - + Find the edge-id of an edge that intersects a given point - + @@ -1734,18 +1734,18 @@ SELECT topology.ST_RemoveIsoNode('ma_topo', 7 ) As result; geometry apoint float8 tol - + - + Retrieve the id of an edge that intersects a Point - + The function returns an integer (id-edge) given a topology, a POINT and a tolerance. If tolerance = 0 then the point has to intersect the edge. If the point doesn't intersect an edge, returns 0 (zero). If use tolerance > 0 and there is more than one edge near the point then an exception is thrown. - - + + If tolerance = 0, the function use ST_Intersects otherwise uses ST_DWithin. @@ -1753,8 +1753,8 @@ SELECT topology.ST_RemoveIsoNode('ma_topo', 7 ) As result; Availability: 2.0.0 - requires GEOS >= 3.3.0. - - + + Examples These examples use edges we created in @@ -1770,11 +1770,11 @@ FROM ST_GeomFromEWKT('SRID=26986;POINT(227591.9 893900.4)') As geom; -- get error -- ERROR: Two or more edges found - + See Also - + , , @@ -1782,7 +1782,7 @@ ERROR: Two or more edges found - + GetFaceByPoint @@ -1804,7 +1804,7 @@ ERROR: Two or more edges found The function returns an integer (id-face) given a topology, a POINT and a tolerance. If tolerance = 0 then the point has to intersect the face. If the point doesn't intersect a face, returns 0 (zero). If use tolerance > 0 and there is more than one face near the point then an exception is thrown. - + If tolerance = 0, the function uses ST_Intersects otherwise uses ST_DWithin. @@ -1817,13 +1817,13 @@ ERROR: Two or more edges found These examples use edges faces created in SELECT topology.GetFaceByPoint('ma_topo',geom, 10) As with1mtol, topology.GetFaceByPoint('ma_topo',geom,0) As withnotol FROM ST_GeomFromEWKT('POINT(234604.6 899382.0)') As geom; - + with1mtol | withnotol -----------+----------- 1 | 0 SELECT topology.GetFaceByPoint('ma_topo',geom, 1) As nearnode FROM ST_GeomFromEWKT('POINT(227591.9 893900.4)') As geom; - + -- get error -- ERROR: Two or more faces found @@ -1837,14 +1837,14 @@ ERROR: Two or more faces found - + GetNodeByPoint - + Find the id of a node at a point location - + @@ -1853,18 +1853,18 @@ ERROR: Two or more faces found geometry point float8 tol - + - + Retrieve the id of a node at a point location - + The function return an integer (id-node) given a topology, a POINT and a tolerance. If tolerance = 0 mean exactly intersection otherwise retrieve the node from an interval. If there isn't a node at the point, it return 0 (zero). If use tolerance > 0 and near the point there are more than one node it throw an exception. - - + + If tolerance = 0, the function use ST_Intersects otherwise will use ST_DWithin. @@ -1872,12 +1872,12 @@ ERROR: Two or more faces found Availability: 2.0.0 - requires GEOS >= 3.3.0. - - + + Examples These examples use edges we created in - SELECT topology.GetNodeByPoint('ma_topo',geom, 1) As nearnode + SELECT topology.GetNodeByPoint('ma_topo',geom, 1) As nearnode FROM ST_GeomFromEWKT('SRID=26986;POINT(227591.9 893900.4)') As geom; nearnode ---------- @@ -1885,16 +1885,16 @@ ERROR: Two or more faces found SELECT topology.GetNodeByPoint('ma_topo',geom, 1000) As too_much_tolerance FROM ST_GeomFromEWKT('SRID=26986;POINT(227591.9 893900.4)') As geom; - + ----get error-- ERROR: Two or more nodes found - + See Also - + , , @@ -1902,14 +1902,14 @@ ERROR: Two or more faces found - + GetTopologyID - + Returns the id of a topology in the topology.topology table given the name of the topology. - + @@ -1918,16 +1918,16 @@ ERROR: Two or more faces found - + Description - + Returns the id of a topology in the topology.topology table given the name of the topology. Availability: 1.? - - + + Examples SELECT topology.GetTopologyID('ma_topo') As topo_id; @@ -1935,7 +1935,7 @@ ERROR: Two or more faces found --------- 1 - + See Also @@ -1951,10 +1951,10 @@ ERROR: Two or more faces found GetTopologySRID - + Returns the SRID of a topology in the topology.topology table given the name of the topology. - + @@ -1963,16 +1963,16 @@ ERROR: Two or more faces found - + Description - + Returns the spatial reference id of a topology in the topology.topology table given the name of the topology. Availability: 2.0.0 - - + + Examples SELECT topology.GetTopologySRID('ma_topo') As SRID; @@ -1980,7 +1980,7 @@ ERROR: Two or more faces found ------- 4326 - + See Also @@ -1992,14 +1992,14 @@ ERROR: Two or more faces found - + GetTopologyName - + Returns the name of a topology (schema) given the id of the topology. - + @@ -2008,16 +2008,16 @@ ERROR: Two or more faces found - + Description - + Returns the topology name (schema) of a topology from the topology.topology table given the topology id of the topology. Availability: 1.? - - + + Examples SELECT topology.GetTopologyName(1) As topo_name; @@ -2025,7 +2025,7 @@ ERROR: Two or more faces found ----------- ma_topo - + See Also @@ -2037,14 +2037,14 @@ ERROR: Two or more faces found - + ST_GetFaceEdges - + Returns a set of ordered edges that bound aface. - + @@ -2054,10 +2054,10 @@ ERROR: Two or more faces found - + Description - + Returns a set of ordered edges that bound aface. Each output consists of a sequence and edgeid. Sequence numbers start with value 1. @@ -2067,10 +2067,10 @@ Order of edges follows a left-hand-rule (bound face is on the left of each direc Availability: 2.0 - &sqlmm_compliant; SQL-MM 3 Topo-Geo and Topo-Net 3: Routine Details: X.3.5 + &sqlmm_compliant; SQL-MM 3 Topo-Geo and Topo-Net 3: Routine Details: X.3.5 - - + + Examples @@ -2097,7 +2097,7 @@ FROM topology.ST_GetFaceEdges('tt',1) As t(seq,edge) INNER JOIN tt.edge AS e ON abs(t.edge) = e.edge_id; - + See Also @@ -2108,14 +2108,14 @@ FROM topology.ST_GetFaceEdges('tt',1) As t(seq,edge) - + ST_GetFaceGeometry - + Returns the polygon in the given topology with the specified face id. - + @@ -2125,19 +2125,19 @@ FROM topology.ST_GetFaceEdges('tt',1) As t(seq,edge) - + Description - + Returns the polygon in the given topology with the specified face id. Builds the polygon from the edges making up the face. - - + + Availability: 1.? - &sqlmm_compliant; SQL-MM 3 Topo-Geo and Topo-Net 3: Routine Details: X.3.16 + &sqlmm_compliant; SQL-MM 3 Topo-Geo and Topo-Net 3: Routine Details: X.3.16 - - + + Examples @@ -2149,10 +2149,10 @@ SELECT ST_AsText(topology.ST_GetFaceGeometry('ma_topo', 1)) As facegeomwkt; -------------------------------------------------------------------------------- POLYGON((234776.9 899563.7,234896.5 899456.7,234914 899436.4,234946.6 899356.9, 234872.5 899328.7,234891 899285.4,234992.5 899145,234890.6 899069, -234755.2 899255.4,234612.7 899379.4,234776.9 899563.7)) +234755.2 899255.4,234612.7 899379.4,234776.9 899563.7)) - + See Also @@ -2163,13 +2163,13 @@ SELECT ST_AsText(topology.ST_GetFaceGeometry('ma_topo', 1)) As facegeomwkt; GetRingEdges - + Returns the ordered set of signed edge identifiers met by walking on an a given edge side. - + @@ -2180,10 +2180,10 @@ a given edge side. - + Description - + Returns the ordered set of signed edge identifiers met by walking on an a given edge side. @@ -2207,12 +2207,12 @@ when dealing with possibly invalid topologies. This function uses edge ring linking metadata. - + Availability: 2.0.0 - - + + See Also @@ -2226,12 +2226,12 @@ This function uses edge ring linking metadata. GetNodeEdges - + Returns an ordered set of edges incident to the given node. - + @@ -2241,16 +2241,16 @@ Returns an ordered set of edges incident to the given node. - + Description - + Returns an ordered set of edges incident to the given node. Each output consists of a sequence and a signed edge id. Sequence numbers start with value 1. A positive edge starts at the given node. -A negative edge ends into the given node. +A negative edge ends into the given node. Closed edges will appear twice (with both signs). Order is clockwise starting from northbound. @@ -2265,7 +2265,7 @@ and is thus usable to build edge ring linking. Availability: 2.0 - + See Also @@ -2275,7 +2275,7 @@ and is thus usable to build edge ring linking. - + @@ -2299,10 +2299,10 @@ and is thus usable to build edge ring linking. - + Description - + Register all faces that can be built out a topology edge primitives. The target topology is assumed to contain no self-intersecting edges. Already known faces are recognized, so it is safe to call Polygonize multiple times on the same topology. @@ -2310,25 +2310,25 @@ and is thus usable to build edge ring linking. This function does not use nor set the next_left_edge and next_right_edge fields of the edge table. - + Availability: 2.0.0 - + See Also , - + AddNode - + Adds a point node to the node table in the specified topology schema and returns the nodeid of new node. If point already exists as node, the existing nodeid is returned. - + @@ -2340,10 +2340,10 @@ This function does not use nor set the next_left_edge and next_right_edge fields - + Description - + Adds a point node to the node table in the specified topology schema. The function automatically adds start and end @@ -2363,12 +2363,12 @@ get the correct containing face computed. If the apoint geometry already exists as a node, the node is not added but the existing nodeid is returned. - + Availability: 2.0.0 - - + + Examples SELECT topology.AddNode('ma_topo', ST_GeomFromText('POINT(227641.6 893816.5)', 26986) ) As nodeid; @@ -2376,10 +2376,10 @@ get the correct containing face computed. nodeid -------- 4 - + - + See Also @@ -2390,10 +2390,10 @@ nodeid AddEdge - + Adds a linestring edge to the edge table and associated start and end points to the point nodes table of the specified topology schema using the specified linestring geometry and returns the edgeid of the new (or existing) edge. - + @@ -2403,21 +2403,21 @@ nodeid - + Description - - Adds an edge to the edge table and associated nodes to the nodes table of the specified toponame schema using the specified linestring geometry and returns the edgeid of the new or existing record. + + Adds an edge to the edge table and associated nodes to the nodes table of the specified toponame schema using the specified linestring geometry and returns the edgeid of the new or existing record. The newly added edge has "universe" face on both sides and links to itself. If the aline geometry crosses, overlaps, contains or is contained by an existing linestring edge, then an error is thrown and the edge is not added. The geometry of aline must have the same srid as defined for the topology otherwise an invalid spatial reference sys error will be thrown. - - + + Availability: 2.0.0 requires GEOS >= 3.3.0. - - + + Examples SELECT topology.AddEdge('ma_topo', ST_GeomFromText('LINESTRING(227575.8 893917.2,227591.9 893900.4)', 26986) ) As edgeid; @@ -2425,37 +2425,37 @@ nodeid edgeid -------- 1 - + SELECT topology.AddEdge('ma_topo', ST_GeomFromText('LINESTRING(227591.9 893900.4,227622.6 893844.2,227641.6 893816.5, 227704.5 893778.5)', 26986) ) As edgeid; -- result -- edgeid -------- 2 - + SELECT topology.AddEdge('ma_topo', ST_GeomFromText('LINESTRING(227591.2 893900, 227591.9 893900.4, 227704.5 893778.5)', 26986) ) As edgeid; -- gives error -- ERROR: Edge intersects (not on endpoints) with existing edge 1 - + See Also , - - + + AddFace - + Registers a face primitive to a topology and gets its identifier. - + @@ -2466,16 +2466,16 @@ Registers a face primitive to a topology and gets its identifier. - + Description - + Registers a face primitive to a topology and gets its identifier. -For a newly added face, the edges forming its boundaries and the ones +For a newly added face, the edges forming its boundaries and the ones contained in the face will be updated to have correct values in the left_face and right_face fields. Isolated nodes contained in the face will also be updated to have a correct @@ -2504,20 +2504,20 @@ existing face record be updated. It is up to the caller to deal with that. The apolygon geometry must have the same srid as defined for the topology otherwise an invalid spatial reference sys error will be thrown. - + Availability: 2.0.0 - - + + Examples --- first add the edges we use generate_series as an iterator (the below --- will only work for polygons with < 10000 points because of our max in gs) +-- first add the edges we use generate_series as an iterator (the below +-- will only work for polygons with < 10000 points because of our max in gs) SELECT topology.AddEdge('ma_topo', ST_MakeLine(ST_PointN(geom,i), ST_PointN(geom, i + 1) )) As edgeid FROM (SELECT ST_NPoints(geom) AS npt, geom - FROM + FROM (SELECT ST_Boundary(ST_GeomFromText('POLYGON((234896.5 899456.7,234914 899436.4,234946.6 899356.9,234872.5 899328.7, 234891 899285.4,234992.5 899145, 234890.6 899069,234755.2 899255.4, 234612.7 899379.4,234776.9 899563.7,234896.5 899456.7))', 26986) ) As geom @@ -2538,8 +2538,8 @@ SELECT topology.AddEdge('ma_topo', ST_MakeLine(ST_PointN(geom,i), ST_PointN(geom 12 (10 rows) -- then add the face - - -SELECT topology.AddFace('ma_topo', + +SELECT topology.AddFace('ma_topo', ST_GeomFromText('POLYGON((234896.5 899456.7,234914 899436.4,234946.6 899356.9,234872.5 899328.7, 234891 899285.4,234992.5 899145, 234890.6 899069,234755.2 899255.4, 234612.7 899379.4,234776.9 899563.7,234896.5 899456.7))', 26986) ) As faceid; @@ -2547,10 +2547,10 @@ SELECT topology.AddFace('ma_topo', faceid -------- 1 - + - + See Also @@ -2593,7 +2593,7 @@ faceid - + @@ -2603,13 +2603,13 @@ faceid TopoGeometry Constructors - + CreateTopoGeom - + Creates a new topo geometry object from topo element array - tg_type: 1:[multi]point, 2:[multi]line, 3:[multi]poly, 4:collection - + @@ -2628,41 +2628,41 @@ faceid - + Description - + Creates a topogeometry object for layer denoted by layer_id and registers it in the relations table in the toponame schema. tg_type is an integer: 1:[multi]point (punctal), 2:[multi]line (lineal), 3:[multi]poly (areal), 4:collection. layer_id is the layer id in the topology.layer table. punctal layers are formed from set of nodes, lineal layers are formed from a set of edges, areal layers are formed from a set of faces, - and collections can be formed from a mixture of nodes, edges, and faces. + and collections can be formed from a mixture of nodes, edges, and faces. Omitting the array of components generates an empty TopoGeometry object. Availability: 1.? - - + + Examples: Form from existing edges - Create a topogeom in ri_topo schema for layer 2 (our ri_roads), of type (2) LINE, for the first edge (we loaded in ST_CreateTopoGeo. + Create a topogeom in ri_topo schema for layer 2 (our ri_roads), of type (2) LINE, for the first edge (we loaded in ST_CreateTopoGeo. INSERT INTO ri.ri_roads(road_name, topo) VALUES('Unknown', topology.CreateTopoGeom('ri_topo',2,2,'{{1,2}}'::topology.topoelementarray); - + - + Examples: Convert an areal geometry to best guess topogeometry - Lets say we have geometries that should be formed from a collection of faces. We have for example blockgroups table - and want to know the topo geometry of each block group. If our data was perfectly aligned, we could do this: + Lets say we have geometries that should be formed from a collection of faces. We have for example blockgroups table + and want to know the topo geometry of each block group. If our data was perfectly aligned, we could do this: -- create our topo geometry column -- SELECT topology.AddTopoGeometryColumn( - 'topo_boston', + 'topo_boston', 'boston', 'blockgroups', 'topo', 'POLYGON'); - + -- addtopgeometrycolumn -- 1 - --- update our column assuming + +-- update our column assuming -- everything is perfectly aligned with our edges UPDATE boston.blockgroups AS bg SET topo = topology.CreateTopoGeom('topo_boston' @@ -2673,12 +2673,12 @@ FROM (SELECT b.gid, topology.TopoElementArray_Agg(ARRAY[f.face_id,3]) As bfaces INNER JOIN topo_boston.face As f ON b.geom && f.mbr WHERE ST_Covers(b.geom, topology.ST_GetFaceGeometry('topo_boston', f.face_id)) GROUP BY b.gid) As foo -WHERE foo.gid = bg.gid; +WHERE foo.gid = bg.gid; --the world is rarely perfect allow for some error ---count the face if 50% of it falls +--count the face if 50% of it falls -- within what we think is our blockgroup boundary UPDATE boston.blockgroups AS bg SET topo = topology.CreateTopoGeom('topo_boston' @@ -2688,46 +2688,46 @@ FROM (SELECT b.gid, topology.TopoElementArray_Agg(ARRAY[f.face_id,3]) As bfaces FROM boston.blockgroups As b INNER JOIN topo_boston.face As f ON b.geom && f.mbr WHERE ST_Covers(b.geom, topology.ST_GetFaceGeometry('topo_boston', f.face_id)) - OR + OR ( ST_Intersects(b.geom, topology.ST_GetFaceGeometry('topo_boston', f.face_id)) - AND ST_Area(ST_Intersection(b.geom, topology.ST_GetFaceGeometry('topo_boston', f.face_id) ) ) > + AND ST_Area(ST_Intersection(b.geom, topology.ST_GetFaceGeometry('topo_boston', f.face_id) ) ) > ST_Area(topology.ST_GetFaceGeometry('topo_boston', f.face_id))*0.5 ) GROUP BY b.gid) As foo -WHERE foo.gid = bg.gid; +WHERE foo.gid = bg.gid; -- and if we wanted to convert our topogeometry back --- to a denomalized geometry aligned with our faces and edges +-- to a denomalized geometry aligned with our faces and edges -- cast the topo to a geometry -- The really cool thing is my new geometries -- are now aligned with my tiger street centerlines UPDATE boston.blockgroups SET new_geom = topo::geometry; - + See Also , - + , , , - + - + toTopoGeom - + Converts a simple Geometry into a topo geometry - + @@ -2746,10 +2746,10 @@ Converts a simple Geometry into a topo geometry - + Description - + Converts a simple Geometry into a . @@ -2809,9 +2809,9 @@ FROM neighborhoods WHERE gid BETWEEN 1 and 15; --use to verify what has happened -- -SELECT * FROM - topology.TopologySummary('topo_boston_test'); - +SELECT * FROM + topology.TopologySummary('topo_boston_test'); + -- summary-- Topology topo_boston_test (5), SRID 2249, precision 0 61 nodes, 87 edges, 35 faces, 15 topogeoms in 1 layers @@ -2824,7 +2824,7 @@ UPDATE nei_topo SET topo = ST_Buffer(clearTopoGeom(topo), -10); -- Get the no-one-lands left by the above operation -- I think GRASS calls this "polygon0 layer" -SELECT ST_GetFaceGeometry('topo_boston_test', f.face_id) +SELECT ST_GetFaceGeometry('topo_boston_test', f.face_id) FROM topo_boston_test.face f WHERE f.face_id > 0 -- don't consider the universe face AND NOT EXISTS ( -- check that no TopoGeometry references the face @@ -2852,7 +2852,7 @@ SELECT ST_GetFaceGeometry('topo_boston_test', f.face_id) TopoElementArray_Agg Returns a topoelementarray for a set of element_id, type arrays (topoelements) - + @@ -2861,17 +2861,17 @@ SELECT ST_GetFaceGeometry('topo_boston_test', f.face_id) - - + + Description - + Used to create a from a set of . - + Availability: 2.0.0 - - + + Examples SELECT topology.TopoElementArray_Agg(ARRAY[e,t]) As tea @@ -2895,13 +2895,13 @@ SELECT ST_GetFaceGeometry('topo_boston_test', f.face_id) TopoGeometry Editors - + clearTopoGeom - + Clears the content of a topo geometry - + @@ -2910,10 +2910,10 @@ SELECT ST_GetFaceGeometry('topo_boston_test', f.face_id) - + Description - + Clears the content a turning it into an empty one. Mostly useful in conjunction with -- Shrink all TopoGeometry polygons by 10 meters UPDATE nei_topo SET topo = ST_Buffer(clearTopoGeom(topo), -10); - + @@ -3040,24 +3040,24 @@ UPDATE mylayer SET tg = TopoGeom_remElement(tg, '{43,3}'); Description -Refer to +Refer to - + - + TopoGeometry Accessors - + GetTopoGeomElementArray - + Returns a topoelementarray (an array of topoelements) containing the topological elements and type of the given TopoGeometry (primitive elements) - + @@ -3074,38 +3074,38 @@ Refer to - + Description - + Returns a containing the topological elements and type of the given TopoGeometry (primitive elements). This is similar to GetTopoGeomElements except it returns the elements as an array rather than as a dataset. tg_id is the topogeometry id of the topogeometry object in the topology in the layer denoted by layer_id in the topology.layer table. - + Availability: 1.? - - + + Examples - + See Also , - + GetTopoGeomElements - + Returns a set of topoelement objects containing the topological element_id,element_type of the given TopoGeometry (primitive elements) - + @@ -3122,24 +3122,24 @@ Refer to - + Description - + Returns a set of element_id,element_type (topoelements) for a given topogeometry object in toponame schema. tg_id is the topogeometry id of the topogeometry object in the topology in the layer denoted by layer_id in the topology.layer table. - + Availability: 2.0.0 - - + + Examples - + See Also @@ -3153,20 +3153,20 @@ Refer to - - + + TopoGeometry Outputs - + AsGML - + Returns the GML representation of a topogeometry. - + - + text AsGML topogeometry tg @@ -3222,16 +3222,16 @@ Refer to - + Description - + Returns the GML representation of a topogeometry in version GML3 format. If no nsprefix_in is specified then gml is used. Pass in an empty string for nsprefix to get a non-qualified name space. The precision (default: 15) and options (default 1) parameters, if given, are passed untouched to the underlying call to ST_AsGML. The visitedTable parameter, if given, is used for keeping track of the visited Node and Edge elements so to use cross-references (xlink:xref) rather than duplicating definitions. The table is expected to have (at least) two integer fields: 'element_type' and 'element_id'. The calling user must have both read and write privileges on the given table. For best performance, an index should be defined on -element_type and element_id, +element_type and element_id, in that order. Such index would be created automatically by adding a unique constraint to the fields. Example: @@ -3241,23 +3241,23 @@ CREATE TABLE visited ( ); - + The idprefix parameter, if given, will be prepended to Edge and Node tag identifiers. - + The gmlver parameter, if given, will be passed to the underlying ST_AsGML. Defaults to 3. Availability: 2.0.0 - - + + Examples This uses the topo geometry we created in - SELECT topology.AsGML(topo) As rdgml - FROM ri.roads + SELECT topology.AsGML(topo) As rdgml + FROM ri.roads WHERE road_name = 'Unknown'; - + -- rdgml-- @@ -3270,14 +3270,14 @@ CREATE TABLE visited ( - 384744 236928 384750 236923 384769 236911 384799 236895 384811 236890 - 384833 236884 384844 236882 384866 236881 384879 236883 384954 236898 385087 236932 385117 236938 - 385167 236938 385203 236941 385224 236946 385233 236950 385241 236956 385254 236971 - 385260 236979 385268 236999 385273 237018 385273 237037 385271 237047 385267 237057 385225 237125 - 385210 237144 385192 237161 385167 237192 385162 237202 385159 237214 385159 237227 385162 237241 - 385166 237256 385196 237324 385209 237345 385234 237375 385237 237383 385238 237399 385236 237407 - 385227 237419 385213 237430 385193 237439 385174 237451 385170 237455 385169 237460 385171 237475 - 385181 237503 385190 237521 385200 237533 385206 237538 385213 237541 385221 237542 385235 237540 385242 237541 + 384744 236928 384750 236923 384769 236911 384799 236895 384811 236890 + 384833 236884 384844 236882 384866 236881 384879 236883 384954 236898 385087 236932 385117 236938 + 385167 236938 385203 236941 385224 236946 385233 236950 385241 236956 385254 236971 + 385260 236979 385268 236999 385273 237018 385273 237037 385271 237047 385267 237057 385225 237125 + 385210 237144 385192 237161 385167 237192 385162 237202 385159 237214 385159 237227 385162 237241 + 385166 237256 385196 237324 385209 237345 385234 237375 385237 237383 385238 237399 385236 237407 + 385227 237419 385213 237430 385193 237439 385174 237451 385170 237455 385169 237460 385171 237475 + 385181 237503 385190 237521 385200 237533 385206 237538 385213 237541 385221 237542 385235 237540 385242 237541 385249 237544 385260 237555 385270 237570 385289 237584 385292 237589 385291 237596 385284 237630 @@ -3285,13 +3285,13 @@ CREATE TABLE visited ( -]]> +]]> Same exercise as previous without namespace -SELECT topology.AsGML(topo,'') As rdgml - FROM ri.roads +SELECT topology.AsGML(topo,'') As rdgml + FROM ri.roads WHERE road_name = 'Unknown'; - + -- rdgml-- @@ -3304,14 +3304,14 @@ CREATE TABLE visited ( - 384744 236928 384750 236923 384769 236911 384799 236895 384811 236890 - 384833 236884 384844 236882 384866 236881 384879 236883 384954 236898 385087 236932 385117 236938 - 385167 236938 385203 236941 385224 236946 385233 236950 385241 236956 385254 236971 - 385260 236979 385268 236999 385273 237018 385273 237037 385271 237047 385267 237057 385225 237125 - 385210 237144 385192 237161 385167 237192 385162 237202 385159 237214 385159 237227 385162 237241 - 385166 237256 385196 237324 385209 237345 385234 237375 385237 237383 385238 237399 385236 237407 - 385227 237419 385213 237430 385193 237439 385174 237451 385170 237455 385169 237460 385171 237475 - 385181 237503 385190 237521 385200 237533 385206 237538 385213 237541 385221 237542 385235 237540 385242 237541 + 384744 236928 384750 236923 384769 236911 384799 236895 384811 236890 + 384833 236884 384844 236882 384866 236881 384879 236883 384954 236898 385087 236932 385117 236938 + 385167 236938 385203 236941 385224 236946 385233 236950 385241 236956 385254 236971 + 385260 236979 385268 236999 385273 237018 385273 237037 385271 237047 385267 237057 385225 237125 + 385210 237144 385192 237161 385167 237192 385162 237202 385159 237214 385159 237227 385162 237241 + 385166 237256 385196 237324 385209 237345 385234 237375 385237 237383 385238 237399 385236 237407 + 385227 237419 385213 237430 385193 237439 385174 237451 385170 237455 385169 237460 385171 237475 + 385181 237503 385190 237521 385200 237533 385206 237538 385213 237541 385221 237542 385235 237540 385242 237541 385249 237544 385260 237555 385270 237570 385289 237584 385292 237589 385291 237596 385284 237630 @@ -3319,10 +3319,10 @@ CREATE TABLE visited ( -]]> +]]> - + See Also @@ -3330,25 +3330,25 @@ CREATE TABLE visited ( - + AsTopoJSON - + Returns the TopoJSON representation of a topogeometry. - + - + text AsTopoJSON topogeometry tg regclass edgeMapTable - + Description - + Returns the TopoJSON representation of a topogeometry. If edgeMapTable is not null, it will be used as a lookup/storage mapping of edge identifiers to arc indices. This is to be able to allow for a compact "arcs" array in the final document. @@ -3370,13 +3370,13 @@ the actual arcs plus some headers. See the TopoJSON specification. - + Availability: 2.1.0 Enhanced: 2.2.1 added support for puntal inputs - - + + See Also @@ -3396,7 +3396,7 @@ UNION ALL SELECT '"' || feature_name || '": ' || AsTopoJSON(feature, 'edgemap') FROM features.big_parcels WHERE feature_name = 'P3P4'; -- arcs -WITH edges AS ( +WITH edges AS ( SELECT m.arc_id, e.geom FROM edgemap m, city_data.edge e WHERE e.edge_id = m.edge_id ), points AS ( @@ -3450,10 +3450,10 @@ UNION ALL SELECT ']}'::text as t; Equals - + Returns true if two topogeometries are composed of the same topology primitives. - + @@ -3463,49 +3463,49 @@ UNION ALL SELECT ']}'::text as t; - + Description - + Returns true if two topogeometries are composed of the same topology primitives: faces, edges, nodes. - + This function not supported for topogeometries that are geometry collections. It also can not compare topogeometries from different topologies. Availability: 1.1.0 - - + + &Z_support; - - + + Examples - + - + See Also - + , - + Intersects - + Returns true if any pair of primitives from the two topogeometries intersect. - + @@ -3515,15 +3515,15 @@ two topogeometries intersect. - + Description - + Returns true if any pair of primitives from the two topogeometries intersect. - + This function not supported for topogeometries that are geometry collections. It also can not compare topogeometries from different topologies. @@ -3531,23 +3531,23 @@ two topogeometries intersect. Availability: 1.1.0 - - + + &Z_support; - - + + Examples - + - + See Also - + diff --git a/doc/faq.xml b/doc/faq.xml index d64010e6a..3c1cad9fa 100644 --- a/doc/faq.xml +++ b/doc/faq.xml @@ -3,7 +3,7 @@ PostGIS Frequently Asked Questions - + Where can I find tutorials, guides and workshops on working with PostGIS @@ -22,17 +22,17 @@ A lot of deprecated functions were removed from the PostGIS code base in PostGIS 2.0. This has affected applications in addition to third-party tools such as Geoserver, MapServer, QuantumGIS, and OpenJump to name a few. There are a couple of ways to resolve this. For the third-party apps, you can try to upgrade to the latest versions of these which have many of these issues fixed. For your own code, you can change your code to not use the functions removed. Most of these functions are non ST_ aliases of ST_Union, ST_Length etc. - and as a last resort, install the whole of legacy.sql or just the + and as a last resort, install the whole of legacy.sql or just the portions of legacy.sql you need. - + The legacy.sql file is located in the same folder as postgis.sql. You can install this file after you have installed postgis.sql and spatial_ref_sys.sql to get back all the 200 some-odd old functions we removed. - + - When I load OpenStreetMap data with osm2pgsql, I'm getting an error + When I load OpenStreetMap data with osm2pgsql, I'm getting an error failed: ERROR: operator class "gist_geometry_ops" does not exist for access method "gist" Error occurred. This worked fine in PostGIS 1.5. @@ -43,10 +43,10 @@ CREATE INDEX idx_my_table_geom ON my_table USING gist(geom gist_geometry_ops); To GOOD: CREATE INDEX idx_my_table_geom ON my_table USING gist(geom); - + The only case where you WILL need to specify the operator class is if you want a 3D spatial index as follows: CREATE INDEX idx_my_super3d_geom ON my_super3d USING gist(geom gist_geometry_ops_nd); - + If you are unfortunate to be stuck with compiled code you can't change that has the old gist_geometry_ops hard-coded, then you can create the old class using the legacy_gist.sql packaged in PostGIS 2.0.2+. However if you use this fix, you are advised to at a later point drop the index and recreate it without the operator class. This will save you grief in the future when you need to upgrade again. @@ -59,20 +59,20 @@ In PostgreSQL 9.0+, the default encoding for bytea data has been changed to hex and older JDBC drivers still assume escape format. This has affected some applications such as Java applications using older JDBC drivers or .NET applications that use the older npgsql driver - that expect the old behavior of ST_AsBinary. There are two approaches to getting this to work again. - - You can upgrade your JDBC driver to the latest PostgreSQL 9.0 version which you can get from + that expect the old behavior of ST_AsBinary. There are two approaches to getting this to work again. + + You can upgrade your JDBC driver to the latest PostgreSQL 9.0 version which you can get from http://jdbc.postgresql.org/download.html If you are running a .NET app, you can use Npgsql 2.0.11 or higher which you can download from - http://pgfoundry.org/frs/?group_id=1000140 and + http://pgfoundry.org/frs/?group_id=1000140 and as described on Francisco Figueiredo's NpgSQL 2.0.11 released blog entry - + If upgrading your PostgreSQL driver is not an option, then you can set the default back to the old behavior with the following change: ALTER DATABASE mypostgisdb SET bytea_output='escape'; - + - + I tried to use PgAdmin to view my geometry column and it is blank, what gives? @@ -80,17 +80,17 @@ PgAdmin doesn't show anything for large geometries. The best ways to verify you do have data in your geometry columns are? - - - -- this should return no records if all your geom fields are filled in + + + -- this should return no records if all your geom fields are filled in SELECT somefield FROM mytable WHERE geom IS NULL; - + -- To tell just how large your geometry is do a query of the form --which will tell you the most number of points you have in any of your geometry columns SELECT MAX(ST_NPoints(geom)) FROM sometable; - + What kind of geometric objects can I store? @@ -101,13 +101,13 @@ SELECT MAX(ST_NPoints(geom)) FROM sometable; MultiPolygon, and GeometryCollection geometries. In PostGIS 2.0 and above you can also store TINS and Polyhedral Surfaces in the basic geometry type. These are specified in the Open GIS Well Known Text Format (with Z, M, and ZM extensions). There are three data types currently supported. - The standard OGC geometry data type which uses a planar coordinate system for measurement, the + The standard OGC geometry data type which uses a planar coordinate system for measurement, the geography data type which uses a geodetic coordinate system, with calculations on either a sphere or spheroid. The newest family member of the PostGIS spatial type family is raster for storing and analyzing raster data. Raster has its very own FAQ. Refer to and for more details. - + I'm all confused. Which data store should I use geometry or geography? @@ -126,11 +126,11 @@ SELECT MAX(ST_NPoints(geom)) FROM sometable; Refer to to see what is currently supported and what is not. - Long Answer: Refer to our more lengthy discussion in the and function type matrix. + Long Answer: Refer to our more lengthy discussion in the and function type matrix. - + I have more intense questions about geography, such as how big of a geographic region can I stuff in a geography column and @@ -149,7 +149,7 @@ SELECT MAX(ST_NPoints(geom)) FROM sometable; First, you need to create a table with a column of type - "geometry" or "geography" to hold your GIS data. + "geometry" or "geography" to hold your GIS data. Storing geography type data is a little different than storing geometry. Refer to for details on storing geography. @@ -167,10 +167,10 @@ SELECT MAX(ST_NPoints(geom)) FROM sometable; insert statement. The GIS object itself is formatted using the OpenGIS Consortium "well-known text" format: - INSERT INTO gtest (ID, NAME, GEOM) + INSERT INTO gtest (ID, NAME, GEOM) VALUES ( - 1, - 'First Geometry', + 1, + 'First Geometry', ST_GeomFromText('LINESTRING(2 3,4 5,6 5,7 8)') ); @@ -185,7 +185,7 @@ VALUES ( id | name | geom ----+----------------+----------------------------- - 1 | First Geometry | LINESTRING(2 3,4 5,6 5,7 8) + 1 | First Geometry | LINESTRING(2 3,4 5,6 5,7 8) (1 row) @@ -219,9 +219,9 @@ VALUES ( might meet the condition of interest. The spatial functions are then use to test the condition exactly. - SELECT id, the_geom -FROM thetable -WHERE + SELECT id, the_geom +FROM thetable +WHERE ST_Contains(the_geom,'POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))'); @@ -342,7 +342,7 @@ WHERE For example, to find all objects with 100 meters of POINT(1000 1000) the following query would work well: - SELECT * FROM geotable + SELECT * FROM geotable WHERE ST_DWithin(geocolumn, 'POINT(1000 1000)', 100.0); @@ -358,13 +358,13 @@ WHERE ST_DWithin(geocolumn, 'POINT(1000 1000)', 100.0); coordinate systems must be defined in the SPATIAL_REF_SYS table, and the geometries being reprojected must already have an SRID set on them. Once that is done, a reprojection is as simple as referring to - the desired destination SRID. The below projects a geometry to NAD 83 long lat. + the desired destination SRID. The below projects a geometry to NAD 83 long lat. The below will only work if the srid of the_geom is not -1 (not undefined spatial ref) SELECT ST_Transform(the_geom,4269) FROM geotable; - + I did an ST_AsEWKT and ST_AsText on my rather large geometry and it returned blank field. What gives? @@ -379,7 +379,7 @@ WHERE ST_DWithin(geocolumn, 'POINT(1000 1000)', 100.0); SELECT count(gid) FROM geotable WHERE the_geom IS NULL; - + When I do an ST_Intersects, it says my two geometries don't intersect when I KNOW THEY DO. What gives? @@ -387,7 +387,7 @@ WHERE ST_DWithin(geocolumn, 'POINT(1000 1000)', 100.0); This generally happens in two common cases. Your geometry is invalid -- check - or you are assuming they intersect because ST_AsText truncates the numbers and you have lots of decimals after + or you are assuming they intersect because ST_AsText truncates the numbers and you have lots of decimals after it is not showing you. diff --git a/doc/faq_raster.xml b/doc/faq_raster.xml index 7f985ac9e..cd588cb18 100644 --- a/doc/faq_raster.xml +++ b/doc/faq_raster.xml @@ -3,7 +3,7 @@ PostGIS Raster Frequently Asked Questions - + I'm getting error ERROR: RASTER_fromGDALRaster: Could not open bytea with GDAL. Check that the bytea is of a GDAL supported format. when using ST_FromGDALRaster @@ -14,10 +14,10 @@ As of PostGIS 2.1.3 and 2.0.5, a security change was made to by default disable all GDAL drivers and out of db rasters. The release notes are at PostGIS 2.0.6, 2.1.3 security release. In order to reenable specific drivers or all drivers and reenable out of database support, refer to . - - - - + + + + Where can I find out more about the PostGIS Raster Project? @@ -26,7 +26,7 @@ Refer to the PostGIS Raster home page. - + Are there any books or tutorials to get me started with this wonderful invention? @@ -37,23 +37,23 @@ Jorge has a series of blog articles on PostGIS Raster that demonstrate how to load raster data as well as cross compare to same tasks in Oracle GeoRaster. Check out Jorge's PostGIS Raster / Oracle GeoRaster Series. There is a whole chapter (more than 35 pages of content) dedicated to PostGIS Raster with free code and data downloads at PostGIS in Action - Raster chapter. - You can buy PostGIS in Action now from Manning in hard-copy (significant discounts for bulk purchases) or just the E-book format. + You can buy PostGIS in Action now from Manning in hard-copy (significant discounts for bulk purchases) or just the E-book format. You can also buy from Amazon and various other book distributors. All hard-copy books come with a free coupon to download the E-book version. Here is a review from a PostGIS Raster user PostGIS raster applied to land classification urban forestry - + - + How do I install Raster support in my PostGIS database? - The easiest is to download binaries for PostGIS and Raster which are currently available for windows and latest versions of Mac OSX. - First you need a working PostGIS 2.0.0 or above and be running PostgreSQL 8.4, 9.0, or 9.1. Note in PostGIS 2.0 PostGIS Raster is fully integrated, so it will be compiled when you compile PostGIS. - Instructions for installing and running under windows are available at How to Install and Configure PostGIS raster on windows + The easiest is to download binaries for PostGIS and Raster which are currently available for windows and latest versions of Mac OSX. + First you need a working PostGIS 2.0.0 or above and be running PostgreSQL 8.4, 9.0, or 9.1. Note in PostGIS 2.0 PostGIS Raster is fully integrated, so it will be compiled when you compile PostGIS. + Instructions for installing and running under windows are available at How to Install and Configure PostGIS raster on windows If you are on windows, you can compile yourself, or use the pre-compiled @@ -64,22 +64,22 @@ For other platforms, you generally need to compile yourself. Dependencies are PostGIS and GDAL. For more details about compiling from source, please refer to Installing PostGIS Raster from source (in prior versions of PostGIS) - + I get error could not load library "C:/Program Files/PostgreSQL/8.4/lib/rtpostgis.dll": The specified module could not be found. - or could not load library on Linux when trying to run rtpostgis.sql + or could not load library on Linux when trying to run rtpostgis.sql - rtpostgis.so/dll is built with dependency on libgdal.dll/so. Make sure for Windows you have libgdal-1.dll in the bin folder of your PostgreSQL install. + rtpostgis.so/dll is built with dependency on libgdal.dll/so. Make sure for Windows you have libgdal-1.dll in the bin folder of your PostgreSQL install. For Linux libgdal has to be in your path or bin folder. - You may also run into different errors if you don't have PostGIS installed in your database. Make sure to install PostGIS first in your + You may also run into different errors if you don't have PostGIS installed in your database. Make sure to install PostGIS first in your database before trying to install the raster support. - - + + How do I load Raster data into PostGIS? @@ -88,7 +88,7 @@ The latest version of PostGIS comes packaged with a raster2pgsql raster loader executable capable of loading many kinds of rasters and also generating lower resolution overviews without any additional software. Please refer to for more details. Pre-2.0 versions came with a raster2pgsql.py that required python with numpy and GDAL. This is no longer needed. - + What kind of raster file formats can I load into my database? @@ -96,11 +96,11 @@ Any that your GDAL library supports. GDAL supported formats are documented GDAL File Formats. - Your particular GDAL install may not support all formats. To verify the ones supported by your particular GDAL install, you can use + Your particular GDAL install may not support all formats. To verify the ones supported by your particular GDAL install, you can use raster2pgsql -G - + Can I export my PostGIS raster data to other raster formats? @@ -109,49 +109,49 @@ Yes GDAL 1.7+ has a PostGIS raster driver, but is only compiled in if you choose to compile with PostgreSQL support. - The driver currently doesn't support irregularly blocked rasters, + The driver currently doesn't support irregularly blocked rasters, although you can store irregularly blocked rasters in PostGIS raster data type. - If you are compiling from source, you need to include in your configure + If you are compiling from source, you need to include in your configure --with-pg=path/to/pg_config to enable the driver. Refer to GDAL Build Hints for tips on building GDAL against in various OS platforms. - If your version of GDAL is compiled with the PostGIS Raster driver - you should see PostGIS Raster in list when you do + If your version of GDAL is compiled with the PostGIS Raster driver + you should see PostGIS Raster in list when you do gdalinfo --formats - + To get a summary about your raster via GDAL use gdalinfo: gdalinfo "PG:host=localhost port=5432 dbname='mygisdb' user='postgres' password='whatever' schema='someschema' table=sometable" - - To export data to other raster formats, - use gdal_translate the below will export all data from a table to a PNG file at 10% size. - Depending on your pixel band types, some translations may not work if the export format does not support that Pixel type. + + To export data to other raster formats, + use gdal_translate the below will export all data from a table to a PNG file at 10% size. + Depending on your pixel band types, some translations may not work if the export format does not support that Pixel type. For example floating point band types and 32 bit unsigned ints - will not translate easily to JPG or some others. + will not translate easily to JPG or some others. Here is an example simple translation gdal_translate -of PNG -outsize 10% 10% "PG:host=localhost port=5432 dbname='mygisdb' user='postgres' password='whatever' schema='someschema' table=sometable" C:\somefile.png - You can also use SQL where clauses in your export using the where=... in your driver connection string. - Below are some using a where clause + You can also use SQL where clauses in your export using the where=... in your driver connection string. + Below are some using a where clause gdal_translate -of PNG -outsize 10% 10% "PG:host=localhost port=5432 dbname='mygisdb' user='postgres' password='whatever' schema='someschema' table=sometable where='filename=\'abcd.sid\''" " C:\somefile.png gdal_translate -of PNG -outsize 10% 10% "PG:host=localhost port=5432 dbname='mygisdb' user='postgres' password='whatever' schema='someschema' table=sometable where='ST_Intersects(rast, ST_SetSRID(ST_Point(-71.032,42.3793),4326) )' " C:\intersectregion.png To see more examples and syntax refer to Reading Raster Data of PostGIS Raster section - + - Are their binaries of GDAL available already compiled with PostGIS Raster suppport? - - Yes. Check out the page GDAL Binaries page. Any compiled with PostgreSQL - support should have PostGIS Raster in them. - PostGIS Raster is undergoing many changes. If you want to get the latest nightly build for Windows -- then check out + Are their binaries of GDAL available already compiled with PostGIS Raster suppport? + + Yes. Check out the page GDAL Binaries page. Any compiled with PostgreSQL + support should have PostGIS Raster in them. + PostGIS Raster is undergoing many changes. If you want to get the latest nightly build for Windows -- then check out the Tamas Szekeres nightly builds built with Visual Studio which contain GDAL trunk, Python Bindings and MapServer executables and PostGIS Raster driver built-in. Just click the SDK bat and run your commands from there. http://www.gisinternals.com. - Also available are VS project files. - FWTools latest stable version for Windows is compiled with Raster support. - + Also available are VS project files. + FWTools latest stable version for Windows is compiled with Raster support. + - + What tools can I use to view PostGIS raster data? @@ -160,24 +160,24 @@ You can use MapServer compiled with GDAL 1.7+ and PostGIS Raster driver support to view Raster data. QuantumGIS (QGIS) now supports viewing of PostGIS Raster if you have PostGIS raster driver installed. - In theory any tool that renders data using GDAL can support PostGIS raster data or + In theory any tool that renders data using GDAL can support PostGIS raster data or support it with fairly minimal effort. Again for Windows, Tamas' binaries http://www.gisinternals.com are a good choice if you don't want the hassle of having to setup to compile your own. - + How can I add a PostGIS raster layer to my MapServer map? - First you need GDAL 1.7 or higher compiled with PostGIS raster support. + First you need GDAL 1.7 or higher compiled with PostGIS raster support. GDAL 1.8 or above is preferred since many issues have been fixed in 1.8 and more PostGIS raster issues fixed in trunk version. - You can much like you can with any other raster. + You can much like you can with any other raster. Refer to MapServer Raster processing options for list of various processing functions you can use with MapServer raster layers. - What makes PostGIS raster data particularly interesting, is that since - each tile can have various standard database columns, you can segment it in your data source + What makes PostGIS raster data particularly interesting, is that since + each tile can have various standard database columns, you can segment it in your data source Below is an example of how you would define a PostGIS raster layer in MapServer. The mode=2 is required for tiled rasters and was added in PostGIS 2.0 and GDAL 1.8 drivers. This does not exist in GDAL 1.7 drivers. @@ -186,8 +186,8 @@ LAYER NAME coolwktraster TYPE raster STATUS ON - DATA "PG:host=localhost port=5432 dbname='somedb' user='someuser' password='whatever' - schema='someschema' table='cooltable' mode='2'" + DATA "PG:host=localhost port=5432 dbname='somedb' user='someuser' password='whatever' + schema='someschema' table='cooltable' mode='2'" PROCESSING "NODATA=0" PROCESSING "SCALE=AUTO" #... other standard raster processing functions here @@ -209,39 +209,39 @@ LAYER END END - + -- displaying raster with standard raster options and a where clause LAYER NAME soil_survey2009 TYPE raster STATUS ON - DATA "PG:host=localhost port=5432 dbname='somedb' user='someuser' password='whatever' - schema='someschema' table='cooltable' where='survey_year=2009' mode='2'" + DATA "PG:host=localhost port=5432 dbname='somedb' user='someuser' password='whatever' + schema='someschema' table='cooltable' where='survey_year=2009' mode='2'" PROCESSING "NODATA=0" #... other standard raster processing functions here #... classes are optional but useful for 1 band data END - + - - + + What functions can I currently use with my raster data? - Refer to the list of . + Refer to the list of . There are more, but this is still a work in progress. Refer to the PostGIS Raster roadmap page + url="http://trac.osgeo.org/postgis/wiki/WKTRaster/PlanningAndFunding">PostGIS Raster roadmap page for details of what you can expect in the future. - + I am getting error ERROR: function st_intersects(raster, unknown) is not unique or st_union(geometry,text) is not unique. How do I fix? @@ -253,50 +253,50 @@ END SELECT rast FROM my_raster WHERE ST_Intersects(rast, 'SRID=4326;POINT(-10 10)'); - Cast the textual geometry representation to a geometry by changing your code to this: - SELECT rast + Cast the textual geometry representation to a geometry by changing your code to this: + SELECT rast FROM my_raster WHERE ST_Intersects(rast, 'SRID=4326;POINT(-10 10)'::geometry); - + How is PostGIS Raster different from Oracle GeoRaster (SDO_GEORASTER) and SDO_RASTER types? - For a more extensive discussion on this topic, check out Jorge Arévalo Oracle GeoRaster and PostGIS Raster: First impressions - The major advantage of one-georeference-by-raster over one-georeference-by-layer is to allow: - * coverages to be not necessarily rectangular (which is often the case of raster coverage covering large extents. See the possible raster arrangements in the documentation) - * rasters to overlaps (which is necessary to implement lossless vector to raster conversion) - These arrangements are possible in Oracle as well, but they imply + For a more extensive discussion on this topic, check out Jorge Arévalo Oracle GeoRaster and PostGIS Raster: First impressions + The major advantage of one-georeference-by-raster over one-georeference-by-layer is to allow: + * coverages to be not necessarily rectangular (which is often the case of raster coverage covering large extents. See the possible raster arrangements in the documentation) + * rasters to overlaps (which is necessary to implement lossless vector to raster conversion) + These arrangements are possible in Oracle as well, but they imply the storage of multiple SDO_GEORASTER objects linked to as many SDO_RASTER tables. A complex coverage can lead to hundreds of tables in the database. With PostGIS Raster you can store a similar raster arrangement into a unique table. - It's a bit like if PostGIS would force you to store only full rectangular vector coverage without gaps or overlaps (a perfect rectangular topological layer). - This is very practical in some applications but practice has shown that it is not realistic or desirable for most geographical coverages. Vector structures needs the flexibility to store discontinuous and non-rectangular coverages. - We think it is a big advantage that raster structure should benefit as well. + It's a bit like if PostGIS would force you to store only full rectangular vector coverage without gaps or overlaps (a perfect rectangular topological layer). + This is very practical in some applications but practice has shown that it is not realistic or desirable for most geographical coverages. Vector structures needs the flexibility to store discontinuous and non-rectangular coverages. + We think it is a big advantage that raster structure should benefit as well. - - + + raster2pgsql load of large file fails with String of N bytes is too long for encoding conversion? - raster2pgsql doesn't make any connections to your database when generating the file to load. If your database has set an explicit client encoding different - from your database encoding, then when loading large raster files (above 30 MB in size), you may run into a bytes is too long for encoding conversion. - This generally happens if for example you have your database in UTF8, but to support windows apps, you have the client encoding set to WIN1252. - To work around this make sure the client encoding is the same as your database encoding during load. You can do this by explicitly setting the encoding in your load script. Example, if you are on windows: - set PGCLIENTENCODING=UTF8 - If you are on Unix/Linux - export PGCLIENTENCODING=UTF8 - Gory details of this issue are detailed in http://trac.osgeo.org/postgis/ticket/2209 + raster2pgsql doesn't make any connections to your database when generating the file to load. If your database has set an explicit client encoding different + from your database encoding, then when loading large raster files (above 30 MB in size), you may run into a bytes is too long for encoding conversion. + This generally happens if for example you have your database in UTF8, but to support windows apps, you have the client encoding set to WIN1252. + To work around this make sure the client encoding is the same as your database encoding during load. You can do this by explicitly setting the encoding in your load script. Example, if you are on windows: + set PGCLIENTENCODING=UTF8 + If you are on Unix/Linux + export PGCLIENTENCODING=UTF8 + Gory details of this issue are detailed in http://trac.osgeo.org/postgis/ticket/2209 - - + + diff --git a/doc/installation.xml b/doc/installation.xml index e0208f4b1..9a910b18c 100644 --- a/doc/installation.xml +++ b/doc/installation.xml @@ -25,14 +25,14 @@ psql -d yourdatabase -c "CREATE EXTENSION postgis_sfcgal;" psql -d yourdatabase -c "CREATE EXTENSION fuzzystrmatch" psql -d yourdatabase -c "CREATE EXTENSION postgis_tiger_geocoder;" --- if you installed with pcre +-- if you installed with pcre -- you should have address standardizer extension as well psql -d yourdatabase -c "CREATE EXTENSION address_standardizer;" - - + + Please refer to for more details about querying installed/available extensions and upgrading extensions, or switching from a non-extension install to an extension install. - + For those running who decided for some reason not to compile with raster support, or just are old-fashioned, here are longer more painful instructions for you: All the .sql files once installed will be installed in share/contrib/postgis-&last_minor_version; folder of your PostgreSQL install @@ -55,10 +55,10 @@ psql -d yourdatabase -f sfcgal_comments.sql The rest of this chapter goes into detail each of the above installation steps. - + As of PostGIS 2.1.3, out-of-db rasters and all raster drivers are disabled by default. In order to re-enable these, you need to set the following environment variables POSTGIS_GDAL_ENABLED_DRIVERS and POSTGIS_ENABLE_OUTDB_RASTERS in the server environment. For PostGIS 2.2, you can use the more cross-platform approach of setting the corresponding . - + If you want to enable offline raster: POSTGIS_ENABLE_OUTDB_RASTERS=1 Any other setting or no setting at all will disable out of db rasters. @@ -66,13 +66,13 @@ psql -d yourdatabase -f sfcgal_comments.sql POSTGIS_GDAL_ENABLED_DRIVERS=ENABLE_ALL If you want to only enable specific drivers, set your environment variable as follows: POSTGIS_GDAL_ENABLED_DRIVERS="GTiff PNG JPEG GIF XYZ" - + If you are on windows, do not quote the driver list - + Setting environment variables varies depending on OS. For PostgreSQL installed on Ubuntu or Debian via apt-postgresql, the preferred way is to edit /etc/postgresql/9.3/main/environment where 9.3 refers to version of PostgreSQL and main refers to the cluster. - - On windows, if you are running as a service, you can set via System variables which for Windows 7 you can get to by right-clicking on Computer->Properties Advanced System Settings or in explorer navigating to Control Panel\All Control Panel Items\System. + + On windows, if you are running as a service, you can set via System variables which for Windows 7 you can get to by right-clicking on Computer->Properties Advanced System Settings or in explorer navigating to Control Panel\All Control Panel Items\System. Then clicking Advanced System Settings ->Advanced->Environment Variables and adding new system variables. After you set the environment variables, you'll need to restart your PostgreSQL service for the changes to take effect. @@ -99,7 +99,7 @@ psql -d yourdatabase -f sfcgal_comments.sql . - For a full PostgreSQL / PostGIS support matrix and PostGIS/GEOS support matrix refer to + For a full PostgreSQL / PostGIS support matrix and PostGIS/GEOS support matrix refer to http://trac.osgeo.org/postgis/wiki/UsersWikiPostgreSQLPostGIS @@ -146,7 +146,7 @@ psql -d yourdatabase -f sfcgal_comments.sql LibXML2, version 2.5.x or higher. LibXML2 is currently used in some imports - functions (ST_GeomFromGML and ST_GeomFromKML). LibXML2 is available for download from + functions (ST_GeomFromGML and ST_GeomFromKML). LibXML2 is available for download from http://xmlsoft.org/downloads.html. @@ -157,7 +157,7 @@ psql -d yourdatabase -f sfcgal_comments.sql https://github.com/json-c/json-c/releases/. - + GDAL, version 1.8 or higher (1.9 or higher is strongly recommended since some things will not work well or behavior differently with lower versions). This is required for raster support and to be able to install with CREATE EXTENSION postgis so highly recommended for those running 9.1+. @@ -177,7 +177,7 @@ psql -d yourdatabase -f sfcgal_comments.sql Keep in mind other extensions may have a requires postgis extension which will prevent you from installing them unless you install postgis as an extension. So it is highly recommended you compile with GDAL support. Also make sure to enable the drivers you want to use as described in . - + GTK (requires GTK+2.0, 2.8+) to compile the shp2pgsql-gui shape file loader. @@ -187,7 +187,7 @@ psql -d yourdatabase -f sfcgal_comments.sql . - + SFCGAL, version 1.1 (or higher) could be used to provide additional 2D and 3D advanced analysis functions to PostGIS cf . And also allow to use SFCGAL rather than GEOS for some 2D functions provided by both backends (like ST_Intersection or ST_Area, for instance). A PostgreSQL configuration variable postgis.backend allow end user to control which backend he want to use if SFCGAL is installed (GEOS by default). Nota: SFCGAL 1.2 require at least CGAL 4.3 and Boost 1.54 (cf: http://oslandia.github.io/SFCGAL/installation.html) @@ -195,7 +195,7 @@ psql -d yourdatabase -f sfcgal_comments.sql https://github.com/Oslandia/SFCGAL. - + In order to build the you will also need PCRE http://www.pcre.org (which generally is already installed on nix systems). Regex::Assemble perl CPAN package is only needed if you want to rebuild the data encoded in parseaddress-stcities.h. @@ -208,7 +208,7 @@ psql -d yourdatabase -f sfcgal_comments.sql CUnit (CUnit). This is needed for regression testing. http://cunit.sourceforge.net/ - + DocBook (xsltproc) is required for building the @@ -294,7 +294,7 @@ tar -xvzf postgis-&last_release_version;.tar.gz bleeding edge versions or you are a package maintainer. This section includes general compilation instructions, if you are compiling for Windows etc - or another OS, you may find additional more detailed help at PostGIS User contributed compile guides and PostGIS Dev Wiki. + or another OS, you may find additional more detailed help at PostGIS User contributed compile guides and PostGIS Dev Wiki. Pre-Built Packages for various OS are listed in PostGIS Pre-built Packages If you are a windows user, you can get stable builds via Stackbuilder or PostGIS Windows download site We also have very bleeding-edge windows experimental builds that are built usually once or twice a week or whenever anything exciting happens. You can @@ -404,7 +404,7 @@ tar -xvzf postgis-&last_release_version;.tar.gz - + --with-gdalconfig=FILE @@ -414,7 +414,7 @@ tar -xvzf postgis-&last_release_version;.tar.gz locate the GDAL installation directory. Use this parameter (--with-gdalconfig=/path/to/gdal-config) to manually specify a particular GDAL installation that PostGIS will - build against. + build against. @@ -432,14 +432,14 @@ tar -xvzf postgis-&last_release_version;.tar.gz - + --with-xml2config=FILE LibXML is the library required for doing GeomFromKML/GML processes. It normally is found if you have libxml installed, but if not or you want - a specific version used, you'll need to point PostGIS at a specific + a specific version used, you'll need to point PostGIS at a specific xml2-config confi file to enable software installations to locate the LibXML installation directory. Use this parameter (>--with-xml2config=/path/to/xml2-config) to @@ -448,8 +448,8 @@ tar -xvzf postgis-&last_release_version;.tar.gz - - + + --with-projdir=DIR @@ -462,7 +462,7 @@ tar -xvzf postgis-&last_release_version;.tar.gz - + --with-libiconv=DIR @@ -471,7 +471,7 @@ tar -xvzf postgis-&last_release_version;.tar.gz - + --with-jsondir=DIR @@ -483,7 +483,7 @@ tar -xvzf postgis-&last_release_version;.tar.gz - + --with-pcredir=DIR @@ -495,7 +495,7 @@ tar -xvzf postgis-&last_release_version;.tar.gz - + --with-gui @@ -510,7 +510,7 @@ tar -xvzf postgis-&last_release_version;.tar.gz Compile with raster support. This will build rtpostgis-&last_release_version; library and rtpostgis.sql file. This may not - be required in final release as plan is to build in raster support by default. + be required in final release as plan is to build in raster support by default. @@ -538,7 +538,7 @@ tar -xvzf postgis-&last_release_version;.tar.gz --with-sfcgal=PATH - By default PostGIS will not install with sfcgal support without this switch. + By default PostGIS will not install with sfcgal support without this switch. PATH is an optional argument that allows to specify an alternate PATH to sfcgal-config. @@ -599,10 +599,10 @@ tar -xvzf postgis-&last_release_version;.tar.gz make comments - + Introduced in PostGIS 2.0. This generates html cheat sheets suitable for quick reference or for student handouts. - This requires xsltproc to build and will generate 4 files in doc folder topology_cheatsheet.html, tiger_geocoder_cheatsheet.html, + This requires xsltproc to build and will generate 4 files in doc folder topology_cheatsheet.html, tiger_geocoder_cheatsheet.html, raster_cheatsheet.html, postgis_cheatsheet.html You can download some pre-built ones available in html and pdf from PostGIS / PostgreSQL Study Guides @@ -611,12 +611,12 @@ tar -xvzf postgis-&last_release_version;.tar.gz make cheatsheets - + Building PostGIS Extensions and Deploying them - The PostGIS extensions are built and installed automatically if you are using PostgreSQL 9.1+. + The PostGIS extensions are built and installed automatically if you are using PostgreSQL 9.1+. If you are building from source repository, you need to build the function descriptions first. These get built if you have docbook installed. You can also manually build with the statement: @@ -629,30 +629,30 @@ tar -xvzf postgis-&last_release_version;.tar.gz cd extensions cd postgis make clean -make +make make install cd .. cd postgis_topology make clean -make +make make install cd .. cd postgis_sfcgal make clean -make +make make install cd .. cd address_standardizer make clean -make +make make install make installcheck cd .. cd postgis_tiger_geocoder make clean -make +make make install make installcheck @@ -679,20 +679,20 @@ make installcheck Once you do that, you should see postgis, postgis_topology as available extensions in PgAdmin -> extensions. If you are using psql, you can verify that the extensions are installed by running this query: - SELECT name, default_version,installed_version + SELECT name, default_version,installed_version FROM pg_available_extensions WHERE name LIKE 'postgis%' or name LIKE 'address%'; name | default_version | installed_version ------------------------------+-----------------+------------------- - address_standardizer | &last_release_version; | &last_release_version; - address_standardizer_data_us | &last_release_version; | &last_release_version; - postgis | &last_release_version; | &last_release_version; + address_standardizer | &last_release_version; | &last_release_version; + address_standardizer_data_us | &last_release_version; | &last_release_version; + postgis | &last_release_version; | &last_release_version; postgis_sfcgal | &last_release_version; | - postgis_tiger_geocoder | &last_release_version; | &last_release_version; + postgis_tiger_geocoder | &last_release_version; | &last_release_version; postgis_topology | &last_release_version; | (6 rows) -If you have the extension installed in the database you are querying, you'll see mention in the installed_version column. +If you have the extension installed in the database you are querying, you'll see mention in the installed_version column. If you get no records back, it means you don't have postgis extensions installed on the server at all. PgAdmin III 1.14+ will also provide this information in the extensions section of the database browser tree and will even allow upgrade or uninstall by right-clicking. @@ -708,7 +708,7 @@ CREATE EXTENSION postgis_topology; In psql you can use to see what versions you have installed and also what schema they are installed. \connect mygisdb -\x +\x \dx postgis* List of installed extensions @@ -731,8 +731,8 @@ Version | &last_release_version; Schema | topology Description | PostGIS topology spatial types and functions -Extension tables spatial_ref_sys, layer, topology can not be explicitly backed up. They can only -be backed up when the respective postgis or postgis_topology extension is backed up, which only seems to happen when you backup the whole database. +Extension tables spatial_ref_sys, layer, topology can not be explicitly backed up. They can only +be backed up when the respective postgis or postgis_topology extension is backed up, which only seems to happen when you backup the whole database. As of PostGIS 2.0.1, only srid records not packaged with PostGIS are backed up when the database is backed up so don't go around changing srids we package and expect your changes to be there. Put in a ticket if you find an issue. The structures of extension tables are never backed up since they are created with CREATE EXTENSION and assumed to be the same for a given version of an extension. These behaviors are built into the current PostgreSQL extension model, so nothing we can do about it. @@ -756,7 +756,7 @@ CREATE EXTENSION postgis_tiger_geocoder FROM unpackaged; make check - + The above command will run through various checks and regression tests using the generated library against an actual PostgreSQL database. @@ -1089,8 +1089,8 @@ Run Summary: Type Total Ran Passed Failed Inactive Elapsed time = 0.298 seconds -Creating database 'postgis_reg' -Loading PostGIS into 'postgis_reg' +Creating database 'postgis_reg' +Loading PostGIS into 'postgis_reg' /projects/postgis/branches/2.2/regress/00-regress-install/share/contrib/postgis/postgis.sql /projects/postgis/branches/2.2/regress/00-regress-install/share/contrib/postgis/postgis_comments.sql Loading SFCGAL into 'postgis_reg' @@ -1105,123 +1105,123 @@ PostgreSQL 9.4.4, compiled by Visual C++ build 1800, 32-bit Running tests - loader/Point .............. ok - loader/PointM .............. ok - loader/PointZ .............. ok - loader/MultiPoint .............. ok - loader/MultiPointM .............. ok - loader/MultiPointZ .............. ok - loader/Arc .............. ok - loader/ArcM .............. ok - loader/ArcZ .............. ok - loader/Polygon .............. ok - loader/PolygonM .............. ok - loader/PolygonZ .............. ok - loader/TSTPolygon ......... ok - loader/TSIPolygon ......... ok - loader/TSTIPolygon ......... ok - loader/PointWithSchema ..... ok - loader/NoTransPoint ......... ok - loader/NotReallyMultiPoint ......... ok - loader/MultiToSinglePoint ......... ok - loader/ReprojectPts ........ ok - loader/ReprojectPtsGeog ........ ok - loader/Latin1 .... ok - loader/Latin1-implicit .... ok - loader/mfile .... ok - dumper/literalsrid ....... ok - dumper/realtable ....... ok - affine .. ok - bestsrid .. ok - binary .. ok - boundary .. ok - cluster .. ok - concave_hull .. ok - ctors .. ok - dump .. ok - dumppoints .. ok - empty .. ok - forcecurve .. ok - geography .. ok - in_geohash .. ok - in_gml .. ok - in_kml .. ok - in_encodedpolyline .. ok - iscollection .. ok - legacy .. ok - long_xact .. ok - lwgeom_regress .. ok - measures .. ok - operators .. ok - out_geometry .. ok - out_geography .. ok - polygonize .. ok - polyhedralsurface .. ok - postgis_type_name .. ok - regress .. ok - regress_bdpoly .. ok - regress_index .. ok - regress_index_nulls .. ok - regress_management .. ok - regress_selectivity .. ok - regress_lrs .. ok - regress_ogc .. ok - regress_ogc_cover .. ok - regress_ogc_prep .. ok - regress_proj .. ok - relate .. ok - remove_repeated_points .. ok - removepoint .. ok - setpoint .. ok - simplify .. ok - simplifyvw .. ok - size .. ok - snaptogrid .. ok - split .. ok - sql-mm-serialize .. ok - sql-mm-circularstring .. ok - sql-mm-compoundcurve .. ok - sql-mm-curvepoly .. ok - sql-mm-general .. ok - sql-mm-multicurve .. ok - sql-mm-multisurface .. ok - swapordinates .. ok - summary .. ok - temporal .. ok - tickets .. ok - twkb .. ok - typmod .. ok - wkb .. ok - wkt .. ok - wmsservers .. ok - knn .. ok - hausdorff .. ok - regress_buffer_params .. ok - offsetcurve .. ok - relatematch .. ok - isvaliddetail .. ok - sharedpaths .. ok - snap .. ok - node .. ok - unaryunion .. ok - clean .. ok - relate_bnr .. ok - delaunaytriangles .. ok - clipbybox2d .. ok - subdivide .. ok - in_geojson .. ok - regress_sfcgal .. ok - sfcgal/empty .. ok - sfcgal/geography .. ok - sfcgal/legacy .. ok - sfcgal/measures .. ok - sfcgal/regress_ogc_prep .. ok - sfcgal/regress_ogc .. ok - sfcgal/regress .. ok - sfcgal/tickets .. ok - sfcgal/concave_hull .. ok - sfcgal/wmsservers .. ok - sfcgal/approximatemedialaxis .. ok + loader/Point .............. ok + loader/PointM .............. ok + loader/PointZ .............. ok + loader/MultiPoint .............. ok + loader/MultiPointM .............. ok + loader/MultiPointZ .............. ok + loader/Arc .............. ok + loader/ArcM .............. ok + loader/ArcZ .............. ok + loader/Polygon .............. ok + loader/PolygonM .............. ok + loader/PolygonZ .............. ok + loader/TSTPolygon ......... ok + loader/TSIPolygon ......... ok + loader/TSTIPolygon ......... ok + loader/PointWithSchema ..... ok + loader/NoTransPoint ......... ok + loader/NotReallyMultiPoint ......... ok + loader/MultiToSinglePoint ......... ok + loader/ReprojectPts ........ ok + loader/ReprojectPtsGeog ........ ok + loader/Latin1 .... ok + loader/Latin1-implicit .... ok + loader/mfile .... ok + dumper/literalsrid ....... ok + dumper/realtable ....... ok + affine .. ok + bestsrid .. ok + binary .. ok + boundary .. ok + cluster .. ok + concave_hull .. ok + ctors .. ok + dump .. ok + dumppoints .. ok + empty .. ok + forcecurve .. ok + geography .. ok + in_geohash .. ok + in_gml .. ok + in_kml .. ok + in_encodedpolyline .. ok + iscollection .. ok + legacy .. ok + long_xact .. ok + lwgeom_regress .. ok + measures .. ok + operators .. ok + out_geometry .. ok + out_geography .. ok + polygonize .. ok + polyhedralsurface .. ok + postgis_type_name .. ok + regress .. ok + regress_bdpoly .. ok + regress_index .. ok + regress_index_nulls .. ok + regress_management .. ok + regress_selectivity .. ok + regress_lrs .. ok + regress_ogc .. ok + regress_ogc_cover .. ok + regress_ogc_prep .. ok + regress_proj .. ok + relate .. ok + remove_repeated_points .. ok + removepoint .. ok + setpoint .. ok + simplify .. ok + simplifyvw .. ok + size .. ok + snaptogrid .. ok + split .. ok + sql-mm-serialize .. ok + sql-mm-circularstring .. ok + sql-mm-compoundcurve .. ok + sql-mm-curvepoly .. ok + sql-mm-general .. ok + sql-mm-multicurve .. ok + sql-mm-multisurface .. ok + swapordinates .. ok + summary .. ok + temporal .. ok + tickets .. ok + twkb .. ok + typmod .. ok + wkb .. ok + wkt .. ok + wmsservers .. ok + knn .. ok + hausdorff .. ok + regress_buffer_params .. ok + offsetcurve .. ok + relatematch .. ok + isvaliddetail .. ok + sharedpaths .. ok + snap .. ok + node .. ok + unaryunion .. ok + clean .. ok + relate_bnr .. ok + delaunaytriangles .. ok + clipbybox2d .. ok + subdivide .. ok + in_geojson .. ok + regress_sfcgal .. ok + sfcgal/empty .. ok + sfcgal/geography .. ok + sfcgal/legacy .. ok + sfcgal/measures .. ok + sfcgal/regress_ogc_prep .. ok + sfcgal/regress_ogc .. ok + sfcgal/regress .. ok + sfcgal/tickets .. ok + sfcgal/concave_hull .. ok + sfcgal/wmsservers .. ok + sfcgal/approximatemedialaxis .. ok uninstall . /projects/postgis/branches/2.2/regress/00-regress-install/share/contrib/postgis/uninstall_sfcgal.sql /projects/postgis/branches/2.2/regress/00-regress-install/share/contrib/postgis/uninstall_postgis.sql . ok (4336) @@ -1253,7 +1253,7 @@ Run Summary: Type Total Ran Passed Failed Inactive make install make installcheck - + Output should look like: ============== dropping database "contrib_regression" ============== DROP DATABASE @@ -1269,7 +1269,7 @@ test test-standardize_address_2 ... ok ===================== All 4 tests passed. ===================== - + For tiger geocoder, make sure you have postgis and fuzzystrmatch extensions available in your PostgreSQL instance. The address_standardizer tests will also kick in if you built postgis with address_standardizer support: cd extensions/postgis_tiger_geocoder make install @@ -1413,7 +1413,7 @@ All 2 tests passed. psql -d [yourdatabase] -f postgis_comments.sql - + Install raster support @@ -1421,7 +1421,7 @@ All 2 tests passed. psql -d [yourdatabase] -f rtpostgis.sql - + Install raster support comments. This will provide quick help info for each raster function using psql or PgAdmin or any other PostgreSQL tool that can show function comments @@ -1437,7 +1437,7 @@ All 2 tests passed. psql -d [yourdatabase] -f topology/topology.sql - + Install topology support comments. This will provide quick help info for each topology function / type using psql or PgAdmin or any other PostgreSQL tool that can show function comments @@ -1446,20 +1446,20 @@ All 2 tests passed. psql -d [yourdatabase] -f topology/topology_comments.sql - + If you plan to restore an old backup from prior versions in this new db, run: psql -d [yourdatabase] -f legacy.sql There is an alternative legacy_minimal.sql you can run instead which will install barebones needed to recover tables and work with apps like MapServer and GeoServer. If you have views that use things like distance / length etc, you'll need the full blown legacy.sql - + You can later run uninstall_legacy.sql to get rid of the deprecated functions after you are done with restoring and cleanup. - + Creating a spatial database using EXTENSIONS - If you are using PostgreSQL 9.1+ and have compiled and installed the extensions/ postgis modules, you + If you are using PostgreSQL 9.1+ and have compiled and installed the extensions/ postgis modules, you can create a spatial database the new way. @@ -1483,21 +1483,21 @@ All 2 tests passed. psql -d [yourdatabase] -c "CREATE EXTENSION postgis_topology;" - + If you plan to restore an old backup from prior versions in this new db, run: psql -d [yourdatabase] -f legacy.sql - + You can later run uninstall_legacy.sql to get rid of the deprecated functions after you are done with restoring and cleanup. - + Installing and Using the address standardizer - The address_standardizer extension used to be a separate package that required separate download. From PostGIS 2.2 on, it is now bundled in. - For more information about the address_standardize, what it does, and how to configure it for your needs, refer to . - This standardizer can be used in conjunction with the PostGIS packaged tiger geocoder extension as a replacement for the discussed. - To use as replacement refer to . - You can also use it as a building block for your own geocoder or use it to standardize your addresses for easier compare of addresses. - - The address standardizer relies on PCRE which is usually already installed on many Nix systems, + The address_standardizer extension used to be a separate package that required separate download. From PostGIS 2.2 on, it is now bundled in. + For more information about the address_standardize, what it does, and how to configure it for your needs, refer to . + This standardizer can be used in conjunction with the PostGIS packaged tiger geocoder extension as a replacement for the discussed. + To use as replacement refer to . + You can also use it as a building block for your own geocoder or use it to standardize your addresses for easier compare of addresses. + + The address standardizer relies on PCRE which is usually already installed on many Nix systems, but you can download the latest at: http://www.pcre.org. If during , PCRE is found, then the address standardizer extension will automatically be built. If you have a custom pcre install you want to use instead, pass to configure --with-pcredir=/path/to/pcre where /path/to/pcre is the root folder for your pcre include and lib directories. For Windows users, the PostGIS 2.1+ bundle is packaged with the address_standardizer already so no need to compile and can move straight to CREATE EXTENSION step. @@ -1505,15 +1505,15 @@ but you can download the latest at: http://www. Once you have installed, you can connect to your database and run the SQL: CREATE EXTENSION address_standardizer; - + The following test requires no rules, gaz, or lex tables - SELECT num, street, city, state, zip + SELECT num, street, city, state, zip FROM parse_address('1 Devonshire Place, Boston, MA 02109'); Output should be num | street | city | state | zip -----+------------------------+--------+-------+------- 1 | Devonshire Place PH301 | Boston | MA | 02109 - + Installing Regex::Assemble Perl Regex:Assemble is no longer needed for compiling address_standardizer extension since the files it generates are part of the source tree. However if you need to edit the usps-st-city-orig.txt or usps-st-city-orig.txt usps-st-city-adds.tx, you need to rebuild parseaddress-stcities.h which does require Regex:Assemble. cpan Regexp::Assemble @@ -1521,22 +1521,22 @@ but you can download the latest at: http://www. sudo perl -MCPAN -e "install Regexp::Assemble" - + - Installing, Upgrading Tiger Geocoder and loading data - + Installing, Upgrading Tiger Geocoder and loading data + Extras like Tiger geocoder may not be packaged in your PostGIS distribution, but will always be available in the postgis-&last_release_version;.tar.gz file. The instructions provided here are also available in the extras/tiger_geocoder/README If you are on Windows and you don't have tar installed, you can use http://www.7-zip.org/ to unzip the PostGIS tarball. - Tiger Geocoder Enabling your PostGIS database: Using Extension + Tiger Geocoder Enabling your PostGIS database: Using Extension If you are using PostgreSQL 9.1+ and PostGIS 2.1+, you can take advantage of the new extension model for installing tiger geocoder. To do so: First get binaries for PostGIS 2.1+ or compile and install as usual. This should install the necessary extension files as well for tiger geocoder. Connect to your database via psql or pgAdmin or some other tool and run the following SQL commands. Note that if you are installing in a database that already has postgis, you don't need to do the first step. If you have fuzzystrmatch extension already installed, you don't need to do the second step either. - CREATE EXTENSION postgis; + CREATE EXTENSION postgis; CREATE EXTENSION fuzzystrmatch; --this one is optional if you want to use the rules based standardizer (pagc_normalize_address) -CREATE EXTENSION address_standardizer; +CREATE EXTENSION address_standardizer; CREATE EXTENSION postgis_tiger_geocoder; If you already have postgis_tiger_geocoder extension installed, and just want to update to the latest run: @@ -1552,16 +1552,16 @@ ALTER EXTENSION postgis_tiger_geocoder UPDATE; ---------+------------+------------------+------- 1 | Devonshire | Pl | 02109 - Create a new record in tiger.loader_platform table with the paths of your executables and server. + Create a new record in tiger.loader_platform table with the paths of your executables and server. So for example to create a profile called debbie that follows sh convention. You would do: - INSERT INTO tiger.loader_platform(os, declare_sect, pgbin, wget, unzip_command, psql, path_sep, + INSERT INTO tiger.loader_platform(os, declare_sect, pgbin, wget, unzip_command, psql, path_sep, loader, environ_set_command, county_process_command) -SELECT 'debbie', declare_sect, pgbin, wget, unzip_command, psql, path_sep, +SELECT 'debbie', declare_sect, pgbin, wget, unzip_command, psql, path_sep, loader, environ_set_command, county_process_command FROM tiger.loader_platform WHERE os = 'sh'; And then edit the paths in the declare_sect column to those that fit Debbie's pg, unzip,shp2pgsql, psql, etc path locations. - + If you don't edit this loader_platform table, it will just contain common case locations of items and you'll have to edit the generated script after the script is generated. Create a folder called gisdata on root of server or your local pc if you have a fast network connection to the server. This folder is @@ -1595,34 +1595,34 @@ vacuum analyze verbose tiger.state; - + Tiger Geocoder Enabling your PostGIS database: Not Using Extensions First install PostGIS using the prior instructions. - + If you don't have an extras folder, download &postgis_download_url; - + tar xvfz postgis-&last_release_version;.tar.gz - + cd postgis-&last_release_version;/extras/tiger_geocoder - + Edit the tiger_loader_2015.sql (or latest loader file you find, unless you want to load different year) to the paths of your executables server etc or alternatively you can update the loader_platform table once installed. If you don't edit this file or the loader_platform table, it will just contain common case locations of items and you'll have to edit the generated script after the fact when you run the and SQL functions. If you are installing Tiger geocoder for the first time edit either the create_geocode.bat script If you are on windows or the create_geocode.sh if you are on Linux/Unix/Mac OSX with your PostgreSQL specific settings and run the corresponding script from the commandline. - - + + Verify that you now have a tiger schema in your database and that it is part of your database search_path. If it is not, add it with a command something along the line of: ALTER DATABASE geocoder SET search_path=public, tiger; - The normalizing address functionality works more or less without any data except for tricky addresses. Run this test and verify things look like this: + The normalizing address functionality works more or less without any data except for tricky addresses. Run this test and verify things look like this: SELECT pprint_addy(normalize_address('202 East Fremont Street, Las Vegas, Nevada 89101')) As pretty_address; pretty_address --------------------------------------- @@ -1633,12 +1633,12 @@ pretty_address Using Address Standardizer Extension with Tiger geocoder One of the many complaints of folks is the address normalizer function function that normalizes an address for prepping before geocoding. The normalizer is far from perfect and trying to patch its imperfectness takes a vast amount of resources. As such we have integrated with another project that has a much better address standardizer engine. To use this new address_standardizer, you compile the extension as described in and install as an extension in your database. - + Once you install this extension in the same database as you have installed postgis_tiger_geocoder, then the can be used instead of . This extension is tiger agnostic, so can be used with other data sources such as international addresses. The tiger geocoder extension does come packaged with its own custom versions of ( tiger.pagc_rules) , (tiger.pagc_gaz), and (tiger.pagc_lex). These you can add and update to improve your standardizing experience for your own needs. Loading Tiger Data - The instructions for loading data are available in a more detailed form in the extras/tiger_geocoder/tiger_2011/README. This just includes the general steps. + The instructions for loading data are available in a more detailed form in the extras/tiger_geocoder/tiger_2011/README. This just includes the general steps. The load process downloads data from the census website for the respective nation files, states requested, extracts the files, and then loads each state into its own separate set of state tables. Each state table inherits from the tables defined in tiger schema so that its sufficient to just query those tables to access all the data and drop a set of state tables at any time using the if you need to reload a state or just don't need a state anymore. In order to be able to load data you'll need the following tools: @@ -1654,9 +1654,9 @@ pretty_address If you are upgrading from tiger_2010, you'll need to first generate and run . Before you load any state data, you need to load the nation wide data which you do with . Which will generate a loader script for you. is a one-time step that should be done for upgrading (from 2010) and for new installs. - To load state data refer to to generate a data load script for your platform for the states you desire. + To load state data refer to to generate a data load script for your platform for the states you desire. Note that you can install these piecemeal. You don't have to load all the states you want all at once. You can load them as you need them. - + After the states you desire have been loaded, make sure to run the: SELECT install_missing_indexes(); as described in . To test that things are working as they should, try to run a geocode on an address in your state using @@ -1664,29 +1664,29 @@ pretty_address Upgrading your Tiger Geocoder Install - If you have Tiger Geocoder packaged with 2.0+ already installed, you can upgrade the functions at any time even from an interim tar ball if there are fixes you badly need. This will only work for Tiger geocoder not installed with extensions. + If you have Tiger Geocoder packaged with 2.0+ already installed, you can upgrade the functions at any time even from an interim tar ball if there are fixes you badly need. This will only work for Tiger geocoder not installed with extensions. - + If you don't have an extras folder, download &postgis_download_url; - + tar xvfz postgis-&last_release_version;.tar.gz - + cd postgis-&last_release_version;/extras/tiger_geocoder/tiger_2011 - + Locate the upgrade_geocoder.bat script If you are on windows or the upgrade_geocoder.sh if you are on Linux/Unix/Mac OSX. Edit the file to have your postgis database credentials. - + If you are upgrading from 2010 or 2011, make sure to unremark out the loader script line so you get the latest script for loading 2012 data. - + Then run th corresponding script from the commandline. - + Next drop all nation tables and load up the new ones. Generate a drop script with this SQL statement as detailed in SELECT drop_nation_tables_generate_script(); Run the generated drop SQL statements. @@ -1698,7 +1698,7 @@ pretty_address Refer to for instructions on how to run the generate script. This only needs to be done once. You can have a mix of 2010/2011 state tables and can upgrade each state separately. Before you upgrade a state to 2011, you first need to drop the 2010 tables for that state using . - + @@ -1757,7 +1757,7 @@ pretty_address If you installed your database using extensions, you'll need to upgrade using the extension model as well. If you installed using the old sql script way, then you should upgrade using the sql script way. Please refer to the appropriate. - + Soft Upgrade Pre 9.1+ or without extensions This section applies only to those who installed PostGIS not using extensions. If you have extensions and try to upgrade with this approach you'll get messages like: can't drop ... because postgis extension depends on it @@ -1791,7 +1791,7 @@ pretty_address upgrade using a "procs need upgrade" message. - + Soft Upgrade 9.1+ using extensions If you originally installed PostGIS with extensions, then you need to upgrade using extensions as well. Doing a minor upgrade with extensions, is fairly painless. ALTER EXTENSION postgis UPDATE TO "&last_release_version;"; @@ -1835,7 +1835,7 @@ ALTER EXTENSION postgis_topology UPDATE TO "&last_release_version;next"; - + Supplementary instructions for windows users are available at Windows Hard upgrade. @@ -1891,7 +1891,7 @@ ALTER EXTENSION postgis_topology UPDATE TO "&last_release_version;next"; - + @@ -1950,7 +1950,7 @@ ALTER EXTENSION postgis_topology UPDATE TO "&last_release_version;next";ALTER TABLE spatial_ref_sys ADD CONSTRAINT spatial_ref_sys_srid_check check (srid > 0 AND srid < 999000 ); ALTER TABLE spatial_ref_sys ADD PRIMARY KEY(srid)); - + @@ -1989,10 +1989,10 @@ ALTER EXTENSION postgis_topology UPDATE TO "&last_release_version;next";rpm -qa | grep postgresql - + - If your upgrade fails, make sure you are restoring into a database that already has PostGIS installed. - SELECT postgis_full_version(); + If your upgrade fails, make sure you are restoring into a database that already has PostGIS installed. + SELECT postgis_full_version(); diff --git a/doc/introduction.xml b/doc/introduction.xml index 829ee79b6..9937c5826 100644 --- a/doc/introduction.xml +++ b/doc/introduction.xml @@ -10,12 +10,12 @@ including full OpenGIS support, advanced topological constructs (coverages, surfaces, networks), desktop user interface tools for viewing and editing GIS data, and web-based access tools. - - + + PostGIS is an incubation project of the OSGeo Foundation. PostGIS is being continually improved and funded by many FOSS4G Developers as well as corporations all over the world that gain great benefit from its functionality and versatility. - - + + Project Steering Committee @@ -35,16 +35,16 @@ and new function enhancements. - + Regina Obe - Buildbot Maintenance, windows production and experimental builds, Documentation, general user support on PostGIS newsgroup, - X3D support, Tiger Geocoder Support, management functions, and + Buildbot Maintenance, windows production and experimental builds, Documentation, general user support on PostGIS newsgroup, + X3D support, Tiger Geocoder Support, management functions, and smoke testing new functionality or major code changes. - + Bborie Park @@ -55,11 +55,11 @@ Paul Ramsey (Chair) - Co-founder of PostGIS project. General bug fixing, geography support, geography and geometry index support (2D, 3D, nD index and anything spatial index), underlying geometry internal structures, PointCloud (in development), GEOS functionality integration and alignment with + Co-founder of PostGIS project. General bug fixing, geography support, geography and geometry index support (2D, 3D, nD index and anything spatial index), underlying geometry internal structures, PointCloud (in development), GEOS functionality integration and alignment with GEOS releases, loader/dumper, and Shapefile GUI loader. - + Sandro Santilli @@ -67,56 +67,56 @@ Bug fixes and maintenance and integration of new GEOS functionality and alignment with GEOS releases, Topology support, and Raster framework and low level api functions. - + - Core Contributors Present - + Core Contributors Present + Jorge Arévalo Raster development, GDAL driver support, loader - + Nicklas Avén - + Distance function enhancements (including 3D distance and relationship functions) and additions, Tiny WKB output format (TWKB) (in development) and general user support - + Dan Baston - + Geometry clustering function additions, other geometry algorithm enhancements, and general user support - + Olivier Courtin Input output XML (KML,GML)/GeoJSON functions, 3D support and bug fixes. - + Mateusz Loskot CMake support for PostGIS, built original raster loader in python and low level raster api functions - - + + Pierre Racine Raster overall architecture, prototyping, programming support - + David Zwarg @@ -135,7 +135,7 @@ Prior PSC Member. General development, site and buildbot maintenance, OSGeo incubation management - + Kevin Neufeld @@ -143,25 +143,25 @@ on PostGIS newsgroup, and PostGIS maintenance function enhancements. - + Dave Blasby - + The original developer/Co-founder of PostGIS. Dave wrote the server side objects, index bindings, and many of the server side analytical functions. - + Jeff Lounsbury Original development of the Shape file loader/dumper. Current PostGIS Project Owner representative. - - + + Mark Leslie @@ -239,7 +239,7 @@ Vincent Picavet - + Corporate Sponsors @@ -285,7 +285,7 @@ U.S Department of State (HIU), Zonar Systems - + Crowd Funding Campaigns @@ -305,16 +305,16 @@ Zonar Systems geometry operations library, and the algorithmic work of Martin Davis in making it all work, ongoing maintenance and support of Mateusz Loskot, Sandro Santilli (strk), Paul Ramsey and others. - + The GDAL - Geospatial Data Abstraction Library, by Frank Warmerdam and others is used to + Geospatial Data Abstraction Library, by Frank Warmerdam and others is used to power much of the raster functionality introduced in PostGIS 2.0.0. In kind, improvements needed in GDAL to support PostGIS are contributed back to the GDAL project. The Proj4 cartographic projection library, and the work of Gerald Evenden and Frank Warmerdam in creating and maintaining it. - + Last but not least, the PostgreSQL DBMS, The giant that PostGIS stands on. Much of the speed and flexibility of PostGIS would not be possible without the extensibility, great query planner, GIST index, and plethora of SQL features provided by PostgreSQL. diff --git a/doc/performance_tips.xml b/doc/performance_tips.xml index 5ccb539fc..5c40057cc 100644 --- a/doc/performance_tips.xml +++ b/doc/performance_tips.xml @@ -58,14 +58,14 @@ column that "caches" the bbox, and matching against this. In our example, the commands are like: - SELECT AddGeometryColumn('myschema','mytable','bbox','4326','GEOMETRY','2'); + SELECT AddGeometryColumn('myschema','mytable','bbox','4326','GEOMETRY','2'); UPDATE mytable SET bbox = ST_Envelope(ST_Force2D(the_geom)); Now change your query to use the && operator against bbox instead of geom_column, like: - SELECT geom_column -FROM mytable + SELECT geom_column +FROM mytable WHERE bbox && ST_SetSRID('BOX3D(0 0,1 1)'::box3d,4326); Of course, if you change or add rows to mytable, you have to keep @@ -93,14 +93,14 @@ WHERE bbox && ST_SetSRID('BOX3D(0 0,1 1)'::box3d,4326); GIST indices because GIST indices simply ignores NULL values, you get an error message like: - lwgeom=# CLUSTER my_geom_index ON my_table; + lwgeom=# CLUSTER my_geom_index ON my_table; ERROR: cannot cluster when index access method does not handle null values HINT: You may be able to work around this by marking column "the_geom" NOT NULL. As the HINT message tells you, one can work around this deficiency by adding a "not null" constraint to the table: - lwgeom=# ALTER TABLE my_table ALTER COLUMN the_geom SET not null; + lwgeom=# ALTER TABLE my_table ALTER COLUMN the_geom SET not null; ALTER TABLE Of course, this will not work if you in fact need NULL values in @@ -119,7 +119,7 @@ ALTER TABLE overhead for large geometries. To avoid this overhead, it may be feasible to pre-drop those additional dimensions once and forever: - UPDATE mytable SET the_geom = ST_Force2D(the_geom); + UPDATE mytable SET the_geom = ST_Force2D(the_geom); VACUUM FULL ANALYZE mytable; Note that if you added your geometry column using @@ -147,8 +147,8 @@ VACUUM FULL ANALYZE mytable; use of PostGIS (for example, static data and complex analysis vs frequently updated data and lots of users) these changes can provide significant speedups to your queries. - - For a more tips (and better formatting), the original presentation + + For a more tips (and better formatting), the original presentation is at http://2007.foss4g.org/presentations/view.php?abstract_id=117. @@ -156,15 +156,15 @@ VACUUM FULL ANALYZE mytable; Startup - + These settings are configured in postgresql.conf: - + checkpoint_segments - + @@ -174,7 +174,7 @@ VACUUM FULL ANALYZE mytable; - Set to at least 10 or 30 for databases with heavy write activity, or + Set to at least 10 or 30 for databases with heavy write activity, or more for large database loads. Another article on the topic worth reading Greg Smith: Checkpoint and Background writer @@ -184,11 +184,11 @@ VACUUM FULL ANALYZE mytable; - + constraint_exclusion - + @@ -200,15 +200,15 @@ VACUUM FULL ANALYZE mytable; This is generally used for table partitioning. If you are running PostgreSQL versions below 8.4, set to "on" to ensure the query planner will optimize as desired. As of PostgreSQL 8.4, the default for this is set to "partition" which is ideal for PostgreSQL 8.4 and above since it will force the planner to only analyze tables for constraint consideration if they are in an inherited hierarchy - and not pay the planner penalty otherwise. + and not pay the planner penalty otherwise. - + shared_buffers - + @@ -220,16 +220,16 @@ VACUUM FULL ANALYZE mytable; Set to about 1/3 to 3/4 of available RAM - + - + Runtime - + work_mem (the memory used for sort operations and complex queries) - + @@ -243,7 +243,7 @@ VACUUM FULL ANALYZE mytable; - Adjust down for many concurrent users or low RAM. + Adjust down for many concurrent users or low RAM. @@ -255,11 +255,11 @@ VACUUM FULL ANALYZE mytable; - + maintenance_work_mem (used for VACUUM, CREATE INDEX, etc.) - + @@ -281,8 +281,8 @@ VACUUM FULL ANALYZE mytable; - + - + - + diff --git a/doc/postgis.xml b/doc/postgis.xml index 16d68510a..c1e44c5e3 100644 --- a/doc/postgis.xml +++ b/doc/postgis.xml @@ -50,43 +50,43 @@ - - This method implements the OpenGIS Simple Features Implementation Specification for SQL 1.1."> - This method implements the SQL/MM specification."> - This method is also provided by SFCGAL backend."> - This method needs SFCGAL backend."> - - This method needs address_standardizer extension."> - @@ -107,7 +107,7 @@ This function supports M coordinates."> - + @@ -115,7 +115,7 @@ This function supports Polyhedral surfaces."> - + @@ -132,36 +132,36 @@ The PostGIS Development Group Paul - + Ramsey - + clever elephant - +
Victoria British Columbia Canada pramsey@cleverelephant.ca
 - - PostGIS + + PostGIS &last_release_version; - + PostGIS is an extension to the PostgreSQL object-relational database system which allows GIS (Geographic Information Systems) objects to be stored in the database. PostGIS includes support for GiST-based R-Tree spatial indexes, and functions for analysis and processing of GIS objects. - + - + This is the manual for version &last_release_version; - This work is licensed under a Creative Commons Attribution-Share Alike 3.0 License. + This work is licensed under a Creative Commons Attribution-Share Alike 3.0 License. Feel free to use this material any way you like, but we ask that you attribute credit to the PostGIS Project - and wherever possible, a link back to http://postgis.net. + and wherever possible, a link back to http://postgis.net. diff --git a/doc/reference_accessor.xml b/doc/reference_accessor.xml index 8f5f4b04f..4a7b8a026 100644 --- a/doc/reference_accessor.xml +++ b/doc/reference_accessor.xml @@ -751,9 +751,9 @@ SELECT ST_AsEWKT(ST_GeometryN(geom,2)) as wkt Returns the type of the geometry as a string. EG: 'ST_Linestring', 'ST_Polygon','ST_MultiPolygon' etc. This function differs from GeometryType(geometry) in the case of the string and ST in front that is returned, as well as the fact that it will not indicate whether the geometry is measured. Enhanced: 2.0.0 support for Polyhedral surfaces was introduced. - &sqlmm_compliant; SQL-MM 3: 5.1.4 - &Z_support; - &P_support; + &sqlmm_compliant; SQL-MM 3: 5.1.4 + &Z_support; + &P_support; @@ -947,7 +947,7 @@ postgis=# SELECT ST_IsClosed('MULTIPOINT((0 0), (1 1))'::geometry); (1 row) - + Polyhedral Surface Examples @@ -1650,7 +1650,7 @@ SELECT * FROM ST_IsValidDetail('LINESTRING(220227 150406,2220227 150407,222020 1 Prior to 1.3.4, this function crashes if used with geometries that contain CURVES. This is fixed in 1.3.4+ &Z_support; &curve_support; - &P_support; + &P_support; diff --git a/doc/reference_constructor.xml b/doc/reference_constructor.xml index 3de17be41..9a1f7cd34 100644 --- a/doc/reference_constructor.xml +++ b/doc/reference_constructor.xml @@ -101,7 +101,7 @@ , - + ST_Box2dFromGeoHash @@ -158,7 +158,7 @@ SELECT ST_Box2dFromGeoHash('9qqj7nmxncgyy4d0dbxqz0', 0); , , - + ST_GeogFromText @@ -177,9 +177,9 @@ SELECT ST_Box2dFromGeoHash('9qqj7nmxncgyy4d0dbxqz0', 0); Returns a geography object from the well-known text or extended well-known representation. SRID 4326 is assumed if unspecified. This is an alias for ST_GeographyFromText. Points are always expressed in long lat form. - + - Examples + Examples --- converting lon lat coords to geography ALTER TABLE sometable ADD COLUMN geog geography(POINT,4326); @@ -218,7 +218,7 @@ SELECT ST_AsEWKT(ST_GeogFromText('SRID=4267;POINT(-77.0092 38.889588)')); , - + ST_GeogFromWKB @@ -508,13 +508,13 @@ SELECT ST_GeomFromEWKT('CIRCULARSTRING(220268 150415 1,220227 150505 2,220227 15 --Polyhedral Surface example -SELECT ST_GeomFromEWKT('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)) +SELECT ST_GeomFromEWKT('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)) )'); @@ -655,14 +655,14 @@ SELECT ST_AsText(ST_GeomFromGeoHash('9qqj7nmxncgyy4d0dbxqz0', 10)); &P_support; &T_support; GML allow mixed dimensions (2D and 3D inside the same MultiGeometry for instance). As PostGIS geometries don't, ST_GeomFromGML convert the whole geometry to 2D if a missing Z dimension is found once. - + GML support mixed SRS inside the same MultiGeometry. As PostGIS geometries don't, ST_GeomFromGML, in this case, reproject all subgeometries to the SRS root node. If no srsName attribute available for the GML root node, the function throw an error. - + ST_GeomFromGML function is not pedantic about an explicit GML namespace. You could avoid to mention it explicitly for common usages. But you need it if you want to use XLink feature inside GML. ST_GeomFromGML function not support SQL/MM curves geometries. - + @@ -679,7 +679,7 @@ SELECT ST_AsText(ST_GeomFromGeoHash('9qqj7nmxncgyy4d0dbxqz0', 10)); Examples - XLink usage SELECT @@ -692,7 +692,7 @@ SELECT ST_AsText(ST_GeomFromGeoHash('9qqj7nmxncgyy4d0dbxqz0', 10)); ');]]>); - + Examples - Polyhedral Surface SELECT ST_AsEWKT( - 0 0 0 0 1 0 1 1 0 1 0 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 + 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 + 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 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 + 0 0 1 1 0 1 1 1 1 0 1 1 0 0 1 @@ -747,7 +747,7 @@ SELECT ST_AsText(ST_GeomFromGeoHash('9qqj7nmxncgyy4d0dbxqz0', 10)); , , - + ST_GeomFromGeoJSON @@ -767,13 +767,13 @@ SELECT ST_AsText(ST_GeomFromGeoHash('9qqj7nmxncgyy4d0dbxqz0', 10)); Description Constructs a PostGIS geometry object from the GeoJSON representation. ST_GeomFromGeoJSON works only for JSON Geometry fragments. It throws an error if you try to use it on a whole JSON document. - + Availability: 2.0.0 requires - JSON-C >= 0.9 If you do not have JSON-C enabled, support you will get an error notice instead of seeing an output. To enable JSON-C, run configure --with-jsondir=/path/to/json-c. See for details. &Z_support; - + Examples SELECT ST_AsText(ST_GeomFromGeoJSON('{"type":"Point","coordinates":[-48.23456,20.12345]}')) As wkt; @@ -787,7 +787,7 @@ SELECT ST_AsText(ST_GeomFromGeoJSON('{"type":"LineString","coordinates":[[1,2,3] wkt ------------------- LINESTRING(1 2,4 5,7 8) - + @@ -796,7 +796,7 @@ LINESTRING(1 2,4 5,7 8) , , - + ST_GeomFromKML @@ -831,14 +831,14 @@ LINESTRING(1 2,4 5,7 8) ST_GeomFromKML function not support SQL/MM curves geometries. - + Examples - A single geometry with srsName SELECT ST_GeomFromKML(' - -71.1663,42.2614 + -71.1663,42.2614 -71.1667,42.2616 ']]>); @@ -855,19 +855,19 @@ LINESTRING(1 2,4 5,7 8) ST_GMLToSQL Return a specified ST_Geometry value from GML representation. This is an alias name for ST_GeomFromGML - + - + geometry ST_GMLToSQL text geomgml - - + + geometry ST_GMLToSQL text geomgml integer srid - + - + Description &sqlmm_compliant; SQL-MM 3: 5.1.50 (except for curves support). @@ -1007,7 +1007,7 @@ SELECT ST_GeomFromText('CIRCULARSTRING(220268 150415,220227 150505,220227 150406 Examples - --Although bytea rep contains single \, these need to be escaped when inserting into a table + --Although bytea rep contains single \, these need to be escaped when inserting into a table -- unless standard_conforming_strings is set to on. SELECT ST_AsEWKT( ST_GeomFromWKB(E'\\001\\002\\000\\000\\000\\002\\000\\000\\000\\037\\205\\353Q\\270~\\\\\\300\\323Mb\\020X\\231C@\\020X9\\264\\310~\\\\\\300)\\\\\\217\\302\\365\\230C@',4326) @@ -1490,15 +1490,15 @@ BOX3D(-989502.1875 528439.5625 10,-987121.375 529933.1875 10) in the order of the travel. --- For pre-PostgreSQL 9.0 - this usually works, +-- For pre-PostgreSQL 9.0 - this usually works, -- but the planner may on occasion choose not to respect the order of the subquery SELECT gps.gps_track, ST_MakeLine(gps.the_geom) As newgeom FROM (SELECT gps_track,gps_time, the_geom FROM gps_points ORDER BY gps_track, gps_time) As gps GROUP BY gps.gps_track; - + --- If you are using PostgreSQL 9.0+ +-- If you are using PostgreSQL 9.0+ -- (you can use the new ORDER BY support for aggregates) -- this is a guaranteed way to get a correctly ordered linestring -- Your order by part can order by more than one column if needed @@ -1577,7 +1577,7 @@ LINESTRING(1 2 3,3 4 5,6 6 6) Creates a rectangular Polygon formed from the minima and maxima. by the given shell. Input values must be in SRS specified by the SRID. If no SRID is specified the unknown spatial reference system is assumed - + Availability: 1.5 Enhanced: 2.0: Ability to specify an envelope without specifying an SRID was introduced. @@ -2082,7 +2082,7 @@ SELECt ST_MPolyFromText('MULTIPOLYGON(((-70.916 42.1002,-70.9468 42.0946,-70.976 SELECT ST_SetSRID(ST_Point(-71.1043443253471, 42.3150676015829),4326) - + Examples: Geography @@ -2102,7 +2102,7 @@ SELECT ST_Transform(ST_SetSRID(ST_Point(3637510, 3014852),2273),4326)::geography , , , - + ST_PointFromGeoHash diff --git a/doc/reference_editor.xml b/doc/reference_editor.xml index 4da6e0087..d99267e1b 100644 --- a/doc/reference_editor.xml +++ b/doc/reference_editor.xml @@ -195,9 +195,9 @@ SELECT ST_AsEWKT(ST_Affine(the_geom, cos(pi()), -sin(pi()), 0, sin(pi()), cos(pi Enhanced: 2.0.0 support for Polyhedral surfaces was introduced. Changed: 2.1.0. Up to 2.0.x this was called ST_Force_2D. - &curve_support; - &P_support; - &Z_support; + &curve_support; + &P_support; + &Z_support; @@ -250,8 +250,8 @@ SELECT ST_AsEWKT(ST_Force2D('POLYGON((0 0 2,0 5 2,5 0 2,0 0 2),(1 1 2,3 1 2,1 3 Enhanced: 2.0.0 support for Polyhedral surfaces was introduced. Changed: 2.1.0. Up to 2.0.x this was called ST_Force_3D. &P_support; - &curve_support; - &Z_support; + &curve_support; + &Z_support; @@ -306,8 +306,8 @@ SELECT ST_AsEWKT(ST_Force3D('POLYGON((0 0,0 5,5 0,0 0),(1 1,3 1,1 3,1 1))')); Enhanced: 2.0.0 support for Polyhedral surfaces was introduced. Changed: 2.1.0. Up to 2.0.x this was called ST_Force_3DZ. &P_support; - &Z_support; - &curve_support; + &Z_support; + &curve_support; @@ -361,7 +361,7 @@ SELECT ST_AsEWKT(ST_Force3DZ('POLYGON((0 0,0 5,5 0,0 0),(1 1,3 1,1 3,1 1))')); Changed: 2.1.0. Up to 2.0.x this was called ST_Force_3DM. - &curve_support; + &curve_support; @@ -415,8 +415,8 @@ SELECT ST_AsEWKT(ST_Force3DM('POLYGON((0 0 1,0 5 1,5 0 1,0 0 1),(1 1 1,3 1 1,1 Forces the geometries into XYZM mode. 0 is tacked on for missing Z and M dimensions. Changed: 2.1.0. Up to 2.0.x this was called ST_Force_4D. - &Z_support; - &curve_support; + &Z_support; + &curve_support; @@ -476,8 +476,8 @@ SELECT ST_AsEWKT(ST_Force4D('MULTILINESTRINGM((0 0 1,0 5 2,5 0 3,0 0 4),(1 1 1, Changed: 2.1.0. Up to 2.0.x this was called ST_Force_Collection. &P_support; - &Z_support; - &curve_support; + &Z_support; + &curve_support; @@ -558,8 +558,8 @@ GEOMETRYCOLLECTION( &P_support; &T_support; - &curve_support; - &Z_support; + &curve_support; + &Z_support; @@ -592,8 +592,8 @@ GEOMETRYCOLLECTION( direction. Enhanced: 2.0.0 support for Polyhedral surfaces was introduced. - &Z_support; - &P_support; + &Z_support; + &P_support; @@ -646,9 +646,9 @@ GEOMETRYCOLLECTION( Availability: 2.2.0 - &Z_support; - - &curve_support; + &Z_support; + + &curve_support; @@ -975,7 +975,7 @@ SELECT ST_AsText(ST_Normalize(ST_GeomFromText( Remove a point from a linestring, given its 0-based index. Useful for turning a closed ring into an open line string Availability: 1.1.0 - &Z_support; + &Z_support; @@ -1073,10 +1073,10 @@ LINESTRING(1 2,1 10) | LINESTRING(1 10,1 2) Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced. Enhanced: 2.0.0 additional parameters for specifying the origin of rotation were added. Availability: 1.1.2. Name changed from Rotate to ST_Rotate in 1.2.2 - &Z_support; - &curve_support; - &P_support; - &T_support; + &Z_support; + &curve_support; + &P_support; + &T_support; @@ -1144,9 +1144,9 @@ FROM (SELECT 'LINESTRING (50 160, 50 50, 100 50)'::geometry AS geom) AS foo; Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced. Availability: 1.1.2. Name changed from RotateX to ST_RotateX in 1.2.2 - &P_support; - &Z_support; - &T_support; + &P_support; + &Z_support; + &T_support; @@ -1198,9 +1198,9 @@ SELECT ST_AsEWKT(ST_RotateX(ST_GeomFromEWKT('LINESTRING(1 2 3, 1 1 1)'), pi()/2) Availability: 1.1.2. Name changed from RotateY to ST_RotateY in 1.2.2 Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced. - &P_support; - &Z_support; - &T_support; + &P_support; + &Z_support; + &T_support; @@ -1256,10 +1256,10 @@ SELECT ST_AsEWKT(ST_RotateX(ST_GeomFromEWKT('LINESTRING(1 2 3, 1 1 1)'), pi()/2) Availability: 1.1.2. Name changed from RotateZ to ST_RotateZ in 1.2.2 Prior to 1.3.4, this function crashes if used with geometries that contain CURVES. This is fixed in 1.3.4+ - &Z_support; - &curve_support; - &P_support; - &T_support; + &Z_support; + &curve_support; + &P_support; + &T_support; @@ -1345,10 +1345,10 @@ point are equivalent to no scaling the corresponding dimension. Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced. Enhanced: 2.2.0 support for scaling all dimension (geometry parameter) was introduced. &P_support; - &Z_support; - &curve_support; - &T_support; - &M_support; + &Z_support; + &curve_support; + &T_support; + &M_support; @@ -1476,7 +1476,7 @@ POLYGON((-29 28,-29.8304547985374 37.9654575824488,-30 40,-29.1695452014626 30.0 Availability: 1.1.0 Updated 2.3.0 : negative indexing - &Z_support; + &Z_support; @@ -1762,9 +1762,9 @@ SELECT ST_AsEWKT(ST_SnapToGrid(ST_GeomFromEWKT('LINESTRING(-1.1115678 2.123 3 2. Examples - - - + + + @@ -2034,7 +2034,7 @@ CREATE INDEX idx_the_geom_26986_parcels st_astext -------------------------------------------------------------------------------- POLYGON((-170 74.053793645338,-141 73.4268621378904,-141 68,-170 68,-170 74.053793645338)) - + diff --git a/doc/reference_guc.xml b/doc/reference_guc.xml index a5b0f8e16..f41807cf9 100644 --- a/doc/reference_guc.xml +++ b/doc/reference_guc.xml @@ -18,21 +18,21 @@ This GUC is only relevant if you compiled PostGIS with sfcgal support. By default geos backend is used for functions where both GEOS and SFCGAL have the same named function. This variable allows you to override and make sfcgal the backend to service the request. Availability: 2.1.0 - + - Examples - Sets backend just for life of connection - set postgis.backend = sfcgal; - - Sets backend for new connections to database - ALTER DATABASE mygisdb SET postgis.backend = sfcgal; + Examples + Sets backend just for life of connection + set postgis.backend = sfcgal; + + Sets backend for new connections to database + ALTER DATABASE mygisdb SET postgis.backend = sfcgal; See Also - + postgis.gdal_datapath @@ -73,7 +73,7 @@ SET postgis.gdal_datapath TO '/usr/local/share/gdal.hidden'; SET postgis.gdal_datapath TO default; - + Setting on windows for a particular database ALTER DATABASE gisdb SET postgis.gdal_datapath = 'C:/Program Files/PostgreSQL/9.3/gdal-data'; @@ -152,18 +152,18 @@ SET postgis.gdal_datapath = 'C:/Program Files/PostgreSQL/9.3/gdal-data';Sets backend for all new connections to database ALTER DATABASE mygisdb SET postgis.gdal_enabled_drivers TO 'GTiff PNG JPEG'; - - Sets default enabled drivers for all new connections to server. Requires super user access and PostgreSQL 9.4+. + + Sets default enabled drivers for all new connections to server. Requires super user access and PostgreSQL 9.4+. Also not that database, session, and user settings override this. ALTER SYSTEM SET postgis.gdal_enabled_drivers TO 'GTiff PNG JPEG'; SELECT pg_reload_conf(); - + SET postgis.gdal_enabled_drivers TO 'GTiff PNG JPEG'; SET postgis.gdal_enabled_drivers = default; - + Enable all GDAL Drivers SET postgis.gdal_enabled_drivers = 'ENABLE_ALL'; @@ -178,11 +178,11 @@ SET postgis.gdal_enabled_drivers = 'DISABLE_ALL'; See Also - , - , - , - , - , + , + , + , + , + , diff --git a/doc/reference_lrs.xml b/doc/reference_lrs.xml index 4e1e74660..431672007 100644 --- a/doc/reference_lrs.xml +++ b/doc/reference_lrs.xml @@ -1,7 +1,7 @@ Linear Referencing - + ST_LineInterpolatePoint @@ -29,7 +29,7 @@ See for computing the line location nearest to a Point. - + Since release 1.1.1 this function also interpolates M and Z values (when present), while prior releases set them to @@ -293,15 +293,15 @@ WHERE n*100.00/length < 1; Return a derived geometry collection value with elements that match the specified measure. Polygonal elements are not supported. - - If an offset is provided, the resultant will be offset to the + + If an offset is provided, the resultant will be offset to the left or right of the input line by the specified number of units. - A positive offset will be to the left, and a negative one to the + A positive offset will be to the left, and a negative one to the right. Semantic is specified by: ISO/IEC CD 13249-3:200x(E) - Text for Continuation CD Editing Meeting - + Availability: 1.1.0 by old name ST_Locate_Along_Measure. Changed: 2.0.0 in prior versions this used to be called ST_Locate_Along_Measure. The old name has been deprecated and will be removed in the future but is still available. Use this function only for geometries with an M component @@ -523,7 +523,7 @@ LINESTRING(6.1 7.1 6,7 8 9) Examples SELECT ST_InterpolatePoint('LINESTRING M (0 0 0, 10 0 20)', 'POINT(5 5)'); - st_interpolatepoint + st_interpolatepoint --------------------- 10 @@ -572,30 +572,30 @@ LINESTRING(6.1 7.1 6,7 8 9) SELECT ST_AsText(ST_AddMeasure( ST_GeomFromEWKT('LINESTRING(1 0, 2 0, 4 0)'),1,4)) As ewelev; - ewelev + ewelev -------------------------------- LINESTRINGM(1 0 1,2 0 2,4 0 4) SELECT ST_AsText(ST_AddMeasure( ST_GeomFromEWKT('LINESTRING(1 0 4, 2 0 4, 4 0 4)'),10,40)) As ewelev; - ewelev + ewelev ---------------------------------------- LINESTRING(1 0 4 10,2 0 4 20,4 0 4 40) SELECT ST_AsText(ST_AddMeasure( ST_GeomFromEWKT('LINESTRINGM(1 0 4, 2 0 4, 4 0 4)'),10,40)) As ewelev; - ewelev + ewelev ---------------------------------------- LINESTRINGM(1 0 10,2 0 20,4 0 40) - + SELECT ST_AsText(ST_AddMeasure( ST_GeomFromEWKT('MULTILINESTRINGM((1 0 4, 2 0 4, 4 0 4),(1 0 4, 2 0 4, 4 0 4))'),10,70)) As ewelev; - ewelev + ewelev ----------------------------------------------------------------- MULTILINESTRINGM((1 0 10,2 0 20,4 0 40),(1 0 40,2 0 50,4 0 70)) - + diff --git a/doc/reference_management.xml b/doc/reference_management.xml index a0299174b..55855b6b7 100644 --- a/doc/reference_management.xml +++ b/doc/reference_management.xml @@ -7,7 +7,7 @@ AddGeometryColumn Adds a geometry column to an existing table of - attributes. By default uses type modifier to define rather than constraints. + attributes. By default uses type modifier to define rather than constraints. Pass in false for use_typmod to get old check constraint based behavior @@ -81,7 +81,7 @@ integer dimension - + boolean use_typmod=true @@ -102,15 +102,15 @@ Changed: 2.0.0 This function no longer updates geometry_columns since geometry_columns is a view that reads from system catalogs. It by default - also does not create constraints, but instead uses the built in type modifier behavior of PostgreSQL. So for example building a wgs84 POINT column with this function is now + also does not create constraints, but instead uses the built in type modifier behavior of PostgreSQL. So for example building a wgs84 POINT column with this function is now equivalent to: ALTER TABLE some_table ADD COLUMN geom geometry(Point,4326); Changed: 2.0.0 If you require the old behavior of constraints use the default use_typmod, but set it to false. - + Changed: 2.0.0 Views can no longer be manually registered in geometry_columns, however views built against geometry typmod tables geometries and used without wrapper functions will register themselves correctly because they inherit the typmod behavior of their parent table column. - Views that use geometry functions that output other geometries will need to be cast to typmod geometries for these view geometry columns to be registered correctly + Views that use geometry functions that output other geometries will need to be cast to typmod geometries for these view geometry columns to be registered correctly in geometry_columns. Refer to . @@ -146,18 +146,18 @@ SELECT AddGeometryColumn ('my_schema','my_spatial_table','geomcp_c',4326,'CURVEP -- Describe the table again reveals the addition of a new geometry columns. \d my_schema.my_spatial_table - addgeometrycolumn + addgeometrycolumn ------------------------------------------------------------------------- - my_schema.my_spatial_table.geomcp_c SRID:4326 TYPE:CURVEPOLYGON DIMS:2 + my_schema.my_spatial_table.geomcp_c SRID:4326 TYPE:CURVEPOLYGON DIMS:2 (1 row) Table "my_schema.my_spatial_table" - Column | Type | Modifiers + Column | Type | Modifiers ----------+----------------------+------------------------------------------------------------------------- id | integer | not null default nextval('my_schema.my_spatial_table_id_seq'::regclass) - geom | geometry(Point,4326) | - geom_c | geometry | - geomcp_c | geometry | + geom | geometry(Point,4326) | + geom_c | geometry | + geomcp_c | geometry | Check constraints: "enforce_dims_geom_c" CHECK (st_ndims(geom_c) = 2) "enforce_dims_geomcp_c" CHECK (st_ndims(geomcp_c) = 2) @@ -165,13 +165,13 @@ Check constraints: "enforce_geotype_geomcp_c" CHECK (geometrytype(geomcp_c) = 'CURVEPOLYGON'::text OR geomcp_c IS NULL) "enforce_srid_geom_c" CHECK (st_srid(geom_c) = 4326) "enforce_srid_geomcp_c" CHECK (st_srid(geomcp_c) = 4326) - + -- geometry_columns view also registers the new columns -- -SELECT f_geometry_column As col_name, type, srid, coord_dimension As ndims +SELECT f_geometry_column As col_name, type, srid, coord_dimension As ndims FROM geometry_columns WHERE f_table_name = 'my_spatial_table' AND f_table_schema = 'my_schema'; - col_name | type | srid | ndims + col_name | type | srid | ndims ----------+--------------+------+------- geom | Point | 4326 | 2 geom_c | Point | 4326 | 2 @@ -261,7 +261,7 @@ SELECT f_geometry_column As col_name, type, srid, coord_dimension As ndims dropgeometrycolumn ------------------------------------------------------ my_schema.my_spatial_table.geom effectively removed. - + -- In PostGIS 2.0+ the above is also equivalent to the standard -- the standard alter table. Both will deregister from geometry_columns ALTER TABLE my_schema.my_spatial_table DROP column geom; @@ -319,7 +319,7 @@ ALTER TABLE my_schema.my_spatial_table DROP column geom; Drops a table and all its references in geometry_columns. Note: uses current_schema() on schema-aware pgsql installations if schema is not provided. - + Changed: 2.0.0 This function is provided for backward compatibility. Now that since geometry_columns is now a view against the system catalogs, you can drop a table with geometry columns like any other table using DROP TABLE @@ -332,7 +332,7 @@ ALTER TABLE my_schema.my_spatial_table DROP column geom; SELECT DropGeometryTable ('my_schema','my_spatial_table'); ----RESULT output --- my_schema.my_spatial_table dropped. - + -- The above is now equivalent to -- DROP TABLE my_schema.my_spatial_table; @@ -377,7 +377,7 @@ DROP TABLE my_schema.my_spatial_table; SELECT PostGIS_Full_Version(); postgis_full_version ---------------------------------------------------------------------------------- -POSTGIS="2.2.0dev r12699" GEOS="3.5.0dev-CAPI-1.9.0 r3989" SFCGAL="1.0.4" PROJ="Rel. 4.8.0, 6 March 2012" +POSTGIS="2.2.0dev r12699" GEOS="3.5.0dev-CAPI-1.9.0 r3989" SFCGAL="1.0.4" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.11.0, released 2014/04/16" LIBXML="2.7.8" LIBJSON="0.12" RASTER (1 row) @@ -811,7 +811,7 @@ GDAL="GDAL 1.11.0, released 2014/04/16" LIBXML="2.7.8" LIBJSON="0.12" RASTER Populate_Geometry_Columns Ensures geometry columns are defined with type modifiers or have appropriate spatial constraints - This ensures they will be registered correctly in geometry_columns view. By default will convert all geometry + This ensures they will be registered correctly in geometry_columns view. By default will convert all geometry columns with no type modifier to ones with type modifiers. To get old behavior set use_typmod=false @@ -836,8 +836,8 @@ GDAL="GDAL 1.11.0, released 2014/04/16" LIBXML="2.7.8" LIBJSON="0.12" RASTER Description Ensures geometry columns have appropriate type modifiers or spatial constraints to ensure they are registered correctly in geometry_columns table. - - For backwards compatibility and for spatial needs such as table inheritance where each child table may have different geometry type, the old check constraint behavior is still supported. + + For backwards compatibility and for spatial needs such as table inheritance where each child table may have different geometry type, the old check constraint behavior is still supported. If you need the old behavior, you need to pass in the new optional argument as false use_typmod=false. When this is done geometry columns will be created with no type modifiers but will have 3 constraints defined. In particular, this means that every geometry column belonging to a table has at least @@ -885,7 +885,7 @@ GDAL="GDAL 1.11.0, released 2014/04/16" LIBXML="2.7.8" LIBJSON="0.12" RASTER parameterized version simply returns the number of rows inserted into the geometry_columns table. Availability: 1.4.0 - Changed: 2.0.0 By default, now uses type modifiers instead of check constraints to constrain geometry types. You can still use check + Changed: 2.0.0 By default, now uses type modifiers instead of check constraints to constrain geometry types. You can still use check constraint behavior instead by using the new use_typmod and setting it to false. Enhanced: 2.0.0 use_typmod optional argument was introduced that allows controlling if columns are created with typmodifiers or with check constraints. @@ -902,18 +902,18 @@ SELECT Populate_Geometry_Columns('public.myspatial_table'::regclass); populate_geometry_columns -------------------------- 1 - - + + \d myspatial_table Table "public.myspatial_table" - Column | Type | Modifiers + Column | Type | Modifiers --------+---------------------------+--------------------------------------------------------------- gid | integer | not null default nextval('myspatial_table_gid_seq'::regclass) - geom | geometry(LineString,4326) | + geom | geometry(LineString,4326) | - - -- This will change the geometry columns to use constraints if they are not typmod or have constraints already. + + -- This will change the geometry columns to use constraints if they are not typmod or have constraints already. --For this to work, there must exist data CREATE TABLE public.myspatial_table_cs(gid serial, geom geometry); INSERT INTO myspatial_table_cs(geom) VALUES(ST_GeomFromText('LINESTRING(1 2, 3 4)',4326) ); @@ -924,10 +924,10 @@ populate_geometry_columns \d myspatial_table_cs Table "public.myspatial_table_cs" - Column | Type | Modifiers + Column | Type | Modifiers --------+----------+------------------------------------------------------------------ gid | integer | not null default nextval('myspatial_table_cs_gid_seq'::regclass) - geom | geometry | + geom | geometry | Check constraints: "enforce_dims_geom" CHECK (st_ndims(geom) = 2) "enforce_geotype_geom" CHECK (geometrytype(geom) = 'LINESTRING'::text OR geom IS NULL) @@ -1019,21 +1019,21 @@ Check constraints: Examples This will change the srid of the roads table to 4326 from whatever it was before SELECT UpdateGeometrySRID('roads','geom',4326); - + The prior example is equivalent to this DDL statement - ALTER TABLE roads - ALTER COLUMN geom TYPE geometry(MULTILINESTRING, 4326) + ALTER TABLE roads + ALTER COLUMN geom TYPE geometry(MULTILINESTRING, 4326) USING ST_SetSRID(geom,4326); - - If you got the projection wrong (or brought it in as unknown) in load and you wanted to transform to web mercator all in one shot - You can do this with - DDL but there is no equivalent PostGIS management function to do so in one go. - ALTER TABLE roads + + If you got the projection wrong (or brought it in as unknown) in load and you wanted to transform to web mercator all in one shot + You can do this with + DDL but there is no equivalent PostGIS management function to do so in one go. + ALTER TABLE roads ALTER COLUMN geom TYPE geometry(MULTILINESTRING, 3857) USING ST_Transform(ST_SetSRID(geom,4326),3857) ; See Also - + , , diff --git a/doc/reference_measure.xml b/doc/reference_measure.xml index 78fd15124..edaa9b4be 100644 --- a/doc/reference_measure.xml +++ b/doc/reference_measure.xml @@ -4,29 +4,29 @@ ST_3DClosestPoint - - Returns the 3-dimensional point on g1 that is closest to g2. This is the first point of + + Returns the 3-dimensional point on g1 that is closest to g2. This is the first point of the 3D shortest line. - + geometry ST_3DClosestPoint - + geometry g1 - + geometry g2 - + Description - - Returns the 3-dimensional point on g1 that is closest to g2. This is the first point of + + Returns the 3-dimensional point on g1 that is closest to g2. This is the first point of the 3D shortest line. The 3D length of the 3D shortest line is the 3D distance. &Z_support; @@ -35,7 +35,7 @@ Availability: 2.0.0 Changed: 2.2.0 - if 2 2D geometries are input, a 2D point is returned (instead of old behavior assuming 0 for missing Z). In case of 2D and 3D, Z is no longer assumed to be 0 for missing Z. - + Examples @@ -44,14 +44,14 @@ linestring and point -- both 3d and 2d closest point -SELECT ST_AsEWKT(ST_3DClosestPoint(line,pt)) AS cp3d_line_pt, +SELECT ST_AsEWKT(ST_3DClosestPoint(line,pt)) AS cp3d_line_pt, ST_AsEWKT(ST_ClosestPoint(line,pt)) As cp2d_line_pt - FROM (SELECT 'POINT(100 100 30)'::geometry As pt, + FROM (SELECT 'POINT(100 100 30)'::geometry As pt, 'LINESTRING (20 80 20, 98 190 1, 110 180 3, 50 75 1000)'::geometry As line ) As foo; - - - cp3d_line_pt | cp2d_line_pt + + + cp3d_line_pt | cp2d_line_pt -----------------------------------------------------------+------------------------------------------ POINT(54.6993798867619 128.935022917228 11.5475869506606) | POINT(73.0769230769231 115.384615384615) @@ -59,13 +59,13 @@ SELECT ST_AsEWKT(ST_3DClosestPoint(line,pt)) AS cp3d_line_pt, linestring and multipoint -- both 3d and 2d closest point - SELECT ST_AsEWKT(ST_3DClosestPoint(line,pt)) AS cp3d_line_pt, + SELECT ST_AsEWKT(ST_3DClosestPoint(line,pt)) AS cp3d_line_pt, ST_AsEWKT(ST_ClosestPoint(line,pt)) As cp2d_line_pt - FROM (SELECT 'MULTIPOINT(100 100 30, 50 74 1000)'::geometry As pt, + FROM (SELECT 'MULTIPOINT(100 100 30, 50 74 1000)'::geometry As pt, 'LINESTRING (20 80 20, 98 190 1, 110 180 3, 50 75 900)'::geometry As line ) As foo; - - + + cp3d_line_pt | cp2d_line_pt -----------------------------------------------------------+-------------- POINT(54.6993798867619 128.935022917228 11.5475869506606) | POINT(50 75) @@ -75,25 +75,25 @@ SELECT ST_AsEWKT(ST_3DClosestPoint(line,pt)) AS cp3d_line_pt, Multilinestring and polygon both 3d and 2d closest point SELECT ST_AsEWKT(ST_3DClosestPoint(poly, mline)) As cp3d, - ST_AsEWKT(ST_ClosestPoint(poly, mline)) As cp2d + ST_AsEWKT(ST_ClosestPoint(poly, mline)) As cp2d FROM (SELECT ST_GeomFromEWKT('POLYGON((175 150 5, 20 40 5, 35 45 5, 50 60 5, 100 100 5, 175 150 5))') As poly, ST_GeomFromEWKT('MULTILINESTRING((175 155 2, 20 40 20, 50 60 -2, 125 100 1, 175 155 1), (1 10 2, 5 20 1))') As mline ) As foo; cp3d | cp2d -------------------------------------------+-------------- - POINT(39.993580415989 54.1889925532825 5) | POINT(20 40) + POINT(39.993580415989 54.1889925532825 5) | POINT(20 40) - + - + See Also - + , , , @@ -157,13 +157,13 @@ SELECT ST_3DDistance( -- Multilinestring and polygon both 3d and 2d distance -- Same example as 3D closest point example SELECT ST_3DDistance(poly, mline) As dist3d, - ST_Distance(poly, mline) As dist2d + ST_Distance(poly, mline) As dist2d FROM (SELECT ST_GeomFromEWKT('POLYGON((175 150 5, 20 40 5, 35 45 5, 50 60 5, 100 100 5, 175 150 5))') As poly, ST_GeomFromEWKT('MULTILINESTRING((175 155 2, 20 40 20, 50 60 -2, 125 100 1, 175 155 1), (1 10 2, 5 20 1))') As mline ) As foo; dist3d | dist2d -------------------+-------- - 0.716635696066337 | 0 + 0.716635696066337 | 0 @@ -173,7 +173,7 @@ SELECT ST_3DDistance(poly, mline) As dist3d, , , , , , - + ST_3DDWithin @@ -190,7 +190,7 @@ SELECT ST_3DDistance(poly, mline) As dist3d, geometry g2 - + double precision distance_of_srid @@ -200,7 +200,7 @@ SELECT ST_3DDistance(poly, mline) As dist3d, Description - For geometry type returns true if the 3d distance between two objects is within distance_of_srid specified + For geometry type returns true if the 3d distance between two objects is within distance_of_srid specified projected units (spatial ref units). &Z_support; @@ -240,7 +240,7 @@ ST_DWithin( , , , , - + ST_3DDFullyWithin @@ -293,9 +293,9 @@ ST_DWithin( -- This compares the difference between fully within and distance within as well -- as the distance fully within for the 2D footprint of the line/point vs. the 3d fully within - SELECT ST_3DDFullyWithin(geom_a, geom_b, 10) as D3DFullyWithin10, ST_3DDWithin(geom_a, geom_b, 10) as D3DWithin10, - ST_DFullyWithin(geom_a, geom_b, 20) as D2DFullyWithin20, - ST_3DDFullyWithin(geom_a, geom_b, 20) as D3DFullyWithin20 from + SELECT ST_3DDFullyWithin(geom_a, geom_b, 10) as D3DFullyWithin10, ST_3DDWithin(geom_a, geom_b, 10) as D3DWithin10, + ST_DFullyWithin(geom_a, geom_b, 20) as D2DFullyWithin20, + ST_3DDFullyWithin(geom_a, geom_b, 20) as D3DFullyWithin20 from (select ST_GeomFromEWKT('POINT(1 1 2)') as geom_a, ST_GeomFromEWKT('LINESTRING(1 5 2, 2 7 20, 1 9 100, 14 12 3)') as geom_b) t1; d3dfullywithin10 | d3dwithin10 | d2dfullywithin20 | d3dfullywithin20 @@ -309,13 +309,13 @@ ST_DWithin( , , , - + ST_3DIntersects Returns TRUE if the Geometries "spatially - intersect" in 3d - only for points, linestrings, polygons, polyhedral surface (area). With SFCGAL backend enabled also supports TINS + intersect" in 3d - only for points, linestrings, polygons, polyhedral surface (area). With SFCGAL backend enabled also supports TINS @@ -340,14 +340,14 @@ ST_DWithin( Disjoint implies false for spatial intersection. - + Availability: 2.0.0 This function call will automatically include a bounding box comparison that will make use of any indexes that are available on the geometries. - + In order to take advantage of support for TINS, you need to enable the SFCGAL backend. This can be done at session time with: set postgis.backend = sfcgal; or at the database or system level. Database level can be done with ALTER DATABASE gisdb SET postgis.backend = sfcgal;. &Z_support; @@ -358,8 +358,8 @@ ST_DWithin( Geometry Examples -SELECT ST_3DIntersects(pt, line), ST_Intersects(pt,line) - FROM (SELECT 'POINT(0 0 2)'::geometry As pt, +SELECT ST_3DIntersects(pt, line), ST_Intersects(pt,line) + FROM (SELECT 'POINT(0 0 2)'::geometry As pt, 'LINESTRING (0 0 1, 0 2 3 )'::geometry As line) As foo; st_3dintersects | st_intersects -----------------+--------------- @@ -367,20 +367,20 @@ ST_DWithin( (1 row) - + TIN Examples set postgis.backend = sfcgal; SELECT ST_3DIntersects('TIN(((0 0,1 0,0 1,0 0)))'::geometry, 'POINT(.1 .1)'::geometry); - st_3dintersects + st_3dintersects ----------------- t - + See Also - + ST_3DLongestLine @@ -408,7 +408,7 @@ SELECT ST_3DIntersects('TIN(((0 0,1 0,0 1,0 0)))'::geometry, 'POINT(.1 .1)'::geo Returns the 3-dimensional longest line between two geometries. The function will only return the first longest line if more than one. The line returned will always start in g1 and end in g2. - The 3D length of the line this function returns will always be the same as returns for g1 and g2. + The 3D length of the line this function returns will always be the same as returns for g1 and g2. Availability: 2.0.0 @@ -426,13 +426,13 @@ SELECT ST_3DIntersects('TIN(((0 0,1 0,0 1,0 0)))'::geometry, 'POINT(.1 .1)'::geo linestring and point -- both 3d and 2d longest line -SELECT ST_AsEWKT(ST_3DLongestLine(line,pt)) AS lol3d_line_pt, +SELECT ST_AsEWKT(ST_3DLongestLine(line,pt)) AS lol3d_line_pt, ST_AsEWKT(ST_LongestLine(line,pt)) As lol2d_line_pt - FROM (SELECT 'POINT(100 100 30)'::geometry As pt, + FROM (SELECT 'POINT(100 100 30)'::geometry As pt, 'LINESTRING (20 80 20, 98 190 1, 110 180 3, 50 75 1000)'::geometry As line ) As foo; - - + + lol3d_line_pt | lol2d_line_pt -----------------------------------+---------------------------- LINESTRING(50 75 1000,100 100 30) | LINESTRING(98 190,100 100) @@ -441,13 +441,13 @@ SELECT ST_AsEWKT(ST_3DLongestLine(line,pt)) AS lol3d_line_pt, linestring and multipoint -- both 3d and 2d longest line - SELECT ST_AsEWKT(ST_3DLongestLine(line,pt)) AS lol3d_line_pt, + SELECT ST_AsEWKT(ST_3DLongestLine(line,pt)) AS lol3d_line_pt, ST_AsEWKT(ST_LongestLine(line,pt)) As lol2d_line_pt - FROM (SELECT 'MULTIPOINT(100 100 30, 50 74 1000)'::geometry As pt, + FROM (SELECT 'MULTIPOINT(100 100 30, 50 74 1000)'::geometry As pt, 'LINESTRING (20 80 20, 98 190 1, 110 180 3, 50 75 900)'::geometry As line ) As foo; - - + + lol3d_line_pt | lol2d_line_pt ---------------------------------+-------------------------- LINESTRING(98 190 1,50 74 1000) | LINESTRING(98 190,50 74) @@ -457,20 +457,20 @@ SELECT ST_AsEWKT(ST_3DLongestLine(line,pt)) AS lol3d_line_pt, Multilinestring and polygon both 3d and 2d longest line SELECT ST_AsEWKT(ST_3DLongestLine(poly, mline)) As lol3d, - ST_AsEWKT(ST_LongestLine(poly, mline)) As lol2d + ST_AsEWKT(ST_LongestLine(poly, mline)) As lol2d FROM (SELECT ST_GeomFromEWKT('POLYGON((175 150 5, 20 40 5, 35 45 5, 50 60 5, 100 100 5, 175 150 5))') As poly, ST_GeomFromEWKT('MULTILINESTRING((175 155 2, 20 40 20, 50 60 -2, 125 100 1, 175 155 1), (1 10 2, 5 20 1))') As mline ) As foo; lol3d | lol2d ------------------------------+-------------------------- - LINESTRING(175 150 5,1 10 2) | LINESTRING(175 150,1 10) + LINESTRING(175 150 5,1 10 2) | LINESTRING(175 150,1 10) - + @@ -479,7 +479,7 @@ SELECT ST_AsEWKT(ST_3DLongestLine(line,pt)) AS lol3d_line_pt, , , , , - + ST_3DMaxDistance @@ -573,7 +573,7 @@ SELECT ST_3DMaxDistance( If g1 and g2 are intersecting with more than one point the function will return a line with start and end in the same point but it can be any of the intersecting points. The line returned will always start in g1 and end in g2. - The 3D length of the line this function returns will always be the same as returns for g1 and g2. + The 3D length of the line this function returns will always be the same as returns for g1 and g2. Availability: 2.0.0 @@ -591,14 +591,14 @@ SELECT ST_3DMaxDistance( linestring and point -- both 3d and 2d shortest line -SELECT ST_AsEWKT(ST_3DShortestLine(line,pt)) AS shl3d_line_pt, +SELECT ST_AsEWKT(ST_3DShortestLine(line,pt)) AS shl3d_line_pt, ST_AsEWKT(ST_ShortestLine(line,pt)) As shl2d_line_pt - FROM (SELECT 'POINT(100 100 30)'::geometry As pt, + FROM (SELECT 'POINT(100 100 30)'::geometry As pt, 'LINESTRING (20 80 20, 98 190 1, 110 180 3, 50 75 1000)'::geometry As line ) As foo; - - - shl3d_line_pt | shl2d_line_pt + + + shl3d_line_pt | shl2d_line_pt ----------------------------------------------------------------------------+------------------------------------------------------ LINESTRING(54.6993798867619 128.935022917228 11.5475869506606,100 100 30) | LINESTRING(73.0769230769231 115.384615384615,100 100) @@ -606,13 +606,13 @@ SELECT ST_AsEWKT(ST_3DShortestLine(line,pt)) AS shl3d_line_pt, linestring and multipoint -- both 3d and 2d shortest line - SELECT ST_AsEWKT(ST_3DShortestLine(line,pt)) AS shl3d_line_pt, + SELECT ST_AsEWKT(ST_3DShortestLine(line,pt)) AS shl3d_line_pt, ST_AsEWKT(ST_ShortestLine(line,pt)) As shl2d_line_pt - FROM (SELECT 'MULTIPOINT(100 100 30, 50 74 1000)'::geometry As pt, + FROM (SELECT 'MULTIPOINT(100 100 30, 50 74 1000)'::geometry As pt, 'LINESTRING (20 80 20, 98 190 1, 110 180 3, 50 75 900)'::geometry As line ) As foo; - - + + shl3d_line_pt | shl2d_line_pt ---------------------------------------------------------------------------+------------------------ LINESTRING(54.6993798867619 128.935022917228 11.5475869506606,100 100 30) | LINESTRING(50 75,50 74) @@ -622,20 +622,20 @@ SELECT ST_AsEWKT(ST_3DShortestLine(line,pt)) AS shl3d_line_pt, Multilinestring and polygon both 3d and 2d shortest line SELECT ST_AsEWKT(ST_3DShortestLine(poly, mline)) As shl3d, - ST_AsEWKT(ST_ShortestLine(poly, mline)) As shl2d + ST_AsEWKT(ST_ShortestLine(poly, mline)) As shl2d FROM (SELECT ST_GeomFromEWKT('POLYGON((175 150 5, 20 40 5, 35 45 5, 50 60 5, 100 100 5, 175 150 5))') As poly, ST_GeomFromEWKT('MULTILINESTRING((175 155 2, 20 40 20, 50 60 -2, 125 100 1, 175 155 1), (1 10 2, 5 20 1))') As mline ) As foo; shl3d | shl2d ---------------------------------------------------------------------------------------------------+------------------------ - LINESTRING(39.993580415989 54.1889925532825 5,40.4078575708294 53.6052383805529 5.03423778139177) | LINESTRING(20 40,20 40) + LINESTRING(39.993580415989 54.1889925532825 5,40.4078575708294 53.6052383805529 5.03423778139177) | LINESTRING(20 40,20 40) - + @@ -673,13 +673,13 @@ SELECT ST_AsEWKT(ST_3DShortestLine(line,pt)) AS shl3d_line_pt, ST_MultiSurface value. For geometry, a 2D Cartesian area is determined with units specified by the SRID. For geography, by default area is determined on a spheroid with units in square meters. To measure around the faster but less accurate sphere, use ST_Area(geog,false). - Enhanced: 2.0.0 - support for 2D polyhedral surfaces was introduced. + Enhanced: 2.0.0 - support for 2D polyhedral surfaces was introduced. Enhanced: 2.2.0 - measurement on spheroid performed with GeographicLib for improved accuracy and robustness. Requires Proj >= 4.9.0 to take advantage of the new feature. - &sfs_compliant; - &sqlmm_compliant; SQL-MM 3: 8.1.2, 9.5.3 - &P_support; - For polyhedral surfaces, only supports 2D polyhedral surfaces (not 2.5D). For 2.5D, may give a non-zero answer, but only for the faces that - sit completely in XY plane. + &sfs_compliant; + &sqlmm_compliant; SQL-MM 3: 8.1.2, 9.5.3 + &P_support; + For polyhedral surfaces, only supports 2D polyhedral surfaces (not 2.5D). For 2.5D, may give a non-zero answer, but only for the faces that + sit completely in XY plane. &sfcgal_enhanced; @@ -712,7 +712,7 @@ SELECT ST_Area(the_geom) As sqft, ST_Area(ST_Transform(the_geom,26986)) As sqm Return area square feet and square meters using geography data type. Note that we transform to our geometry to geography - (before you can do that make sure your geometry is in WGS 84 long lat 4326). Geography always measures in meters. + (before you can do that make sure your geometry is in WGS 84 long lat 4326). Geography always measures in meters. This is just for demonstration to compare. Normally your table will be stored in geography data type already. @@ -855,7 +855,7 @@ SELECT degrees(ST_Azimuth(ST_Point(25, 45), ST_Point(75, 100))) AS degA_B, returned. If CIRCULARSTRING or COMPOUNDCURVE are supplied, they are converted to linestring wtih CurveToLine first, - then same than for LINESTRING + then same than for LINESTRING New in 2.3.0 : support CIRCULARSTRING and COMPOUNDCURVE (using CurveToLine) @@ -864,7 +864,7 @@ SELECT degrees(ST_Azimuth(ST_Point(25, 45), ST_Point(75, 100))) AS degA_B, contribute zero "weight" to the centroid). &sfs_compliant; - &sqlmm_compliant; SQL-MM 3: 8.1.4, 9.5.5 + &sqlmm_compliant; SQL-MM 3: 8.1.4, 9.5.5 @@ -934,7 +934,7 @@ SELECT degrees(ST_Azimuth(ST_Point(25, 45), ST_Point(75, 100))) AS degA_B, (1 row) SELECT ST_AsText(ST_centroid(g)) -FROM ST_GeomFromText('CIRCULARSTRING(0 2, -1 1,0 0, 0.5 0, 1 0, 2 1, 1 2, 0.5 2, 0 2)') AS g ; +FROM ST_GeomFromText('CIRCULARSTRING(0 2, -1 1,0 0, 0.5 0, 1 0, 2 1, 1 2, 0.5 2, 0 2)') AS g ; ------------------------------------------ POINT(0.5 1) @@ -953,12 +953,12 @@ POINT(0.5 1) - + ST_ClosestPoint - Returns the 2-dimensional point on g1 that is closest to g2. This is the first point of + Returns the 2-dimensional point on g1 that is closest to g2. This is the first point of the shortest line. @@ -979,8 +979,8 @@ POINT(0.5 1) Description - Returns the 2-dimensional point on g1 that is closest to g2. This is the first point of - the shortest line. + Returns the 2-dimensional point on g1 that is closest to g2. This is the first point of + the shortest line. If you have a 3D Geometry, you may prefer to use . Availability: 1.5.0 @@ -998,17 +998,17 @@ POINT(0.5 1) Closest between point and linestring is the point itself, but closest - point between a linestring and point is the point on line string that is closest. + point between a linestring and point is the point on line string that is closest. -SELECT ST_AsText(ST_ClosestPoint(pt,line)) AS cp_pt_line, +SELECT ST_AsText(ST_ClosestPoint(pt,line)) AS cp_pt_line, ST_AsText(ST_ClosestPoint(line,pt)) As cp_line_pt -FROM (SELECT 'POINT(100 100)'::geometry As pt, +FROM (SELECT 'POINT(100 100)'::geometry As pt, 'LINESTRING (20 80, 98 190, 110 180, 50 75 )'::geometry As line ) As foo; - + cp_pt_line | cp_line_pt ----------------+------------------------------------------ POINT(100 100) | POINT(73.0769230769231 115.384615384615) @@ -1028,9 +1028,9 @@ SELECT ST_AsText( ST_ClosestPoint( ST_GeomFromText('POLYGON((175 150, 20 40, 50 60, 125 100, 175 150))'), ST_Buffer(ST_GeomFromText('POINT(110 170)'), 20) - ) + ) ) As ptwkt; - + ptwkt ------------------------------------------ POINT(140.752120669087 125.695053378061) @@ -1078,12 +1078,12 @@ SELECT ST_AsText( Description - Returns cluster number for each input geometry, based on a 2D implementation of the - Density-based spatial clustering of applications with noise (DBSCAN) - algorithm. Unlike , it does not require the number of clusters to be specified, but instead + Returns cluster number for each input geometry, based on a 2D implementation of the + Density-based spatial clustering of applications with noise (DBSCAN) + algorithm. Unlike , it does not require the number of clusters to be specified, but instead uses the desired distance (eps) and density(minpoints) parameters to construct each cluster. - + An input geometry will be added to a cluster if it is either: @@ -1104,7 +1104,7 @@ SELECT ST_AsText( Note that border geometries may be within eps distance of core geometries in more than one cluster; in this case, either assignment would be correct, and the border geometry will be arbitrarily asssigned to one of the available clusters. In these cases, it is possible for a correct cluster to be generated with fewer than minpoints geometries. - When assignment of a border geometry is ambiguous, repeated calls to ST_ClusterDBSCAN will produce identical results if an ORDER BY + When assignment of a border geometry is ambiguous, repeated calls to ST_ClusterDBSCAN will produce identical results if an ORDER BY clause is included in the window definition, but cluster assignments may differ from other implementations of the same algorithm. @@ -1142,7 +1142,7 @@ GROUP BY cid; , , - + @@ -1228,9 +1228,9 @@ GEOMETRYCOLLECTION(LINESTRING(6 6,7 7)) Description - Returns 2D distance based - k-means - cluster number for each input geometry. The distance used for clustering is the + Returns 2D distance based + k-means + cluster number for each input geometry. The distance used for clustering is the distance between the centroids of the geometries. Availability: 2.3.0 - requires GEOS @@ -1238,20 +1238,20 @@ GEOMETRYCOLLECTION(LINESTRING(6 6,7 7)) Examples - Generate dummy set of parcels for examples - CREATE TABLE parcels AS -SELECT lpad((row_number() over())::text,3,'0') As parcel_id, geom, + Generate dummy set of parcels for examples + CREATE TABLE parcels AS +SELECT lpad((row_number() over())::text,3,'0') As parcel_id, geom, ('{residential, commercial}'::text[])[1 + mod(row_number()OVER(),2)] As type -FROM +FROM ST_Subdivide(ST_Buffer('LINESTRING(40 100, 98 100, 100 150, 60 90)'::geometry, 40, 'endcap=square'),12) As geom; - - + + - + @@ -1270,7 +1270,7 @@ FROM Parcels color-coded by cluster number (cid) - SELECT ST_ClusterKMeans(geom, 5) OVER() AS cid, parcel_id, geom + SELECT ST_ClusterKMeans(geom, 5) OVER() AS cid, parcel_id, geom FROM parcels; -- result cid | parcel_id | geom @@ -1314,7 +1314,7 @@ FROM parcels; , - + @@ -1424,9 +1424,9 @@ GEOMETRYCOLLECTION(LINESTRING(6 6,7 7)) NOTE: this is the "allowable" version that returns a boolean, not an integer. - &sfs_compliant; s2.1.1.2 // s2.1.13.3 + &sfs_compliant; s2.1.1.2 // s2.1.13.3 - same as within(geometry B, geometry A) - &sqlmm_compliant; SQL-MM 3: 5.1.31 + &sqlmm_compliant; SQL-MM 3: 5.1.31 There are certain subtleties to ST_Contains and ST_Within that are not intuitively obvious. For details check out Subtleties of OGC Covers, Contains, Within @@ -1741,11 +1741,11 @@ FROM (SELECT ST_Buffer(ST_GeomFromText('POINT(1 2)'), 10) As smallc, Geeography Example -- a point with a 300 meter buffer compared to a point, a point and its 10 meter buffer -SELECT ST_Covers(geog_poly, geog_pt) As poly_covers_pt, +SELECT ST_Covers(geog_poly, geog_pt) As poly_covers_pt, ST_Covers(ST_Buffer(geog_pt,10), geog_pt) As buff_10m_covers_cent FROM (SELECT ST_Buffer(ST_GeogFromText('SRID=4326;POINT(-99.327 31.4821)'), 300) As geog_poly, ST_GeogFromText('SRID=4326;POINT(-99.33 31.483)') As geog_pt ) As foo; - + poly_covers_pt | buff_10m_covers_cent ----------------+------------------ f | t @@ -1777,7 +1777,7 @@ SELECT ST_Covers(geog_poly, geog_pt) As poly_covers_pt, geometry geomB - + boolean ST_CoveredBy @@ -2297,7 +2297,7 @@ SELECT ST_Disjoint('POINT(0 0)'::geometry, 'LINESTRING ( 0 0, 0 2 )'::geometry); geography gg2 - + float ST_Distance @@ -2306,7 +2306,7 @@ SELECT ST_Disjoint('POINT(0 0)'::geometry, 'LINESTRING ( 0 0, 0 2 )'::geometry); geography gg2 - + boolean use_spheroid @@ -2367,7 +2367,7 @@ st_distance Geography Examples -- same as geometry example but note units in meters - use sphere for slightly faster less accurate -SELECT ST_Distance(gg1, gg2) As spheroid_dist, ST_Distance(gg1, gg2, false) As sphere_dist +SELECT ST_Distance(gg1, gg2) As spheroid_dist, ST_Distance(gg1, gg2, false) As sphere_dist FROM (SELECT ST_GeogFromText('SRID=4326;POINT(-72.1235 42.3521)') As gg1, ST_GeogFromText('SRID=4326;LINESTRING(-72.1260 42.45, -72.123 42.1546)') As gg2 @@ -2379,7 +2379,7 @@ FROM (SELECT - + See Also @@ -2408,8 +2408,8 @@ FROM (SELECT It is not uncommon to have a geometry that, while meeting the criteria for validity according to ST_IsValid (polygons) - or ST_IsSimple (lines), would become invalid if one of the vertices moved by a slight distance, as can happen during - conversion to text-based formats (such as WKT, KML, GML GeoJSON), or binary formats that do not use double-precision + or ST_IsSimple (lines), would become invalid if one of the vertices moved by a slight distance, as can happen during + conversion to text-based formats (such as WKT, KML, GML GeoJSON), or binary formats that do not use double-precision floating point coordinates (MapInfo TAB). @@ -2448,10 +2448,10 @@ FROM (SELECT Examples SELECT ST_MinimumClearance('POLYGON ((0 0, 1 0, 1 1, 0.5 3.2e-4, 0 0))'); - st_minimumclearance + st_minimumclearance --------------------- 0.00032 - + @@ -2459,7 +2459,7 @@ SELECT ST_MinimumClearance('POLYGON ((0 0, 1 0, 1 1, 0.5 3.2e-4, 0 0))'); See Also - + @@ -2498,7 +2498,7 @@ SELECT ST_MinimumClearance('POLYGON ((0 0, 1 0, 1 1, 0.5 3.2e-4, 0 0))'); Examples SELECT ST_AsText(ST_MinimumClearanceLine('POLYGON ((0 0, 1 0, 1 1, 0.5 3.2e-4, 0 0))')); -st_astext +st_astext ------------------------------- LINESTRING(0.5 0.00032,0.5 0) @@ -2508,7 +2508,7 @@ LINESTRING(0.5 0.00032,0.5 0) See Also - + @@ -2577,10 +2577,10 @@ The current implementation supports only vertices as the discrete locations. Thi Examples For each building, find the parcel that best represents it. First we require the parcel intersect with the geometry. - DISTINCT ON guarantees we get each building listed only once, the ORDER BY .. ST_HausdorffDistance gives us a preference of parcel that is most similar to the building. - SELECT DISTINCT ON(buildings.gid) buildings.gid, parcels.parcel_id - FROM buildings INNER JOIN parcels ON ST_Intersects(buildings.geom,parcels.geom) - ORDER BY buildings.gid, ST_HausdorffDistance(buildings.geom, parcels.geom); + DISTINCT ON guarantees we get each building listed only once, the ORDER BY .. ST_HausdorffDistance gives us a preference of parcel that is most similar to the building. + SELECT DISTINCT ON(buildings.gid) buildings.gid, parcels.parcel_id + FROM buildings INNER JOIN parcels ON ST_Intersects(buildings.geom,parcels.geom) + ORDER BY buildings.gid, ST_HausdorffDistance(buildings.geom, parcels.geom); postgis=# SELECT ST_HausdorffDistance( 'LINESTRING (0 0, 2 0)'::geometry, @@ -2641,7 +2641,7 @@ The current implementation supports only vertices as the discrete locations. Thi (1 row) postgis=# SELECT ST_MaxDistance('POINT(0 0)'::geometry, 'LINESTRING ( 2 2, 2 2 )'::geometry); - st_maxdistance + st_maxdistance ------------------ 2.82842712474619 (1 row) @@ -2659,7 +2659,7 @@ postgis=# SELECT ST_MaxDistance('POINT(0 0)'::geometry, 'LINESTRING ( 2 2, 2 2 ) ST_DistanceSphere Returns minimum distance in meters between two lon/lat - geometries. Uses a spherical earth and radius derived from the spheroid + geometries. Uses a spherical earth and radius derived from the spheroid defined by the SRID. Faster than ST_DistanceSpheroid , but less accurate. PostGIS versions prior to 1.5 only implemented for points. @@ -2679,11 +2679,11 @@ postgis=# SELECT ST_MaxDistance('POINT(0 0)'::geometry, 'LINESTRING ( 2 2, 2 2 ) Description Returns minimum distance in meters between two lon/lat - points. Uses a spherical earth and radius derived from the spheroid + points. Uses a spherical earth and radius derived from the spheroid defined by the SRID. Faster than , but less accurate. PostGIS Versions prior to 1.5 only implemented for points. - + Availability: 1.5 - support for other geometry types besides points was introduced. Prior versions only work with points. Changed: 2.2.0 In prior versions this used to be called ST_Distance_Sphere @@ -2744,7 +2744,7 @@ FROM This function currently does not look at the SRID of a geometry and will always assume its represented in the coordinates of the passed in spheroid. Prior versions of this function only support points. - + Availability: 1.5 - support for other geometry types besides points was introduced. Prior versions only work with points. Changed: 2.2.0 In prior versions this used to be called ST_Distance_Spheroid @@ -2821,9 +2821,9 @@ FROM Examples - postgis=# SELECT ST_DFullyWithin(geom_a, geom_b, 10) as DFullyWithin10, ST_DWithin(geom_a, geom_b, 10) as DWithin10, ST_DFullyWithin(geom_a, geom_b, 20) as DFullyWithin20 from + postgis=# SELECT ST_DFullyWithin(geom_a, geom_b, 10) as DFullyWithin10, ST_DWithin(geom_a, geom_b, 10) as DWithin10, ST_DFullyWithin(geom_a, geom_b, 20) as DFullyWithin20 from (select ST_GeomFromText('POINT(1 1)') as geom_a,ST_GeomFromText('LINESTRING(1 5, 2 7, 1 9, 14 12)') as geom_b) t1; - + ----------------- DFullyWithin10 | DWithin10 | DFullyWithin20 | ---------------+----------+---------------+ @@ -2836,7 +2836,7 @@ FROM , - + ST_DWithin @@ -2852,10 +2852,10 @@ FROM boolean ST_DWithin geometry g1 - + geometry g2 - + double precision distance_of_srid @@ -2864,25 +2864,25 @@ FROM boolean ST_DWithin geography gg1 - + geography gg2 - + double precision distance_meters - + boolean ST_DWithin geography gg1 - + geography gg2 - + double precision distance_meters - + boolean use_spheroid @@ -2898,7 +2898,7 @@ FROM spatial reference system of the geometries. For this function to make sense, the source geometries must both be of the same coordinate projection, having the same SRID. - + For geography units are in meters and measurement is defaulted to use_spheroid=true, for faster check, use_spheroid=false to measure along sphere. @@ -2914,7 +2914,7 @@ FROM From 1.3.4, ST_DWithin uses a more short-circuit distance function which should make it more efficient than prior versions for larger buffer regions. - + Use ST_3DDWithin if you have 3D geometries. &sfs_compliant; @@ -2952,7 +2952,7 @@ SELECT s.gid, s.school_name , - + ST_Equals @@ -3086,11 +3086,11 @@ SELECT ST_Equals(ST_Reverse(ST_GeomFromText('LINESTRING(0 0, 10 10)')), Computes the approximate geometric median of a MultiPoint geometry - using the Weiszfeld algorithm. The geometric median provides a - centrality measure that is less sensitive to outlier points than + using the Weiszfeld algorithm. The geometric median provides a + centrality measure that is less sensitive to outlier points than the centroid. - - The algorithm will iterate until the distance change between + + The algorithm will iterate until the distance change between successive iterations is less than the supplied tolerance parameter. If this condition has not been met after max_iterations iterations, the function will produce an error and exit, unless fail_if_not_converged @@ -3125,16 +3125,16 @@ SELECT ST_Equals(ST_Reverse(ST_GeomFromText('LINESTRING(0 0, 10 10)')), WITH test AS ( SELECT 'MULTIPOINT((0 0), (1 1), (2 2), (200 200))'::geometry geom) SELECT - ST_AsText(ST_Centroid(geom)) centroid, + ST_AsText(ST_Centroid(geom)) centroid, ST_AsText(ST_GeometricMedian(geom)) median FROM test; - centroid | median + centroid | median --------------------+---------------------------------------- POINT(50.75 50.75) | POINT(1.9761550281255 1.9761550281255) (1 row) - + See Also @@ -3246,13 +3246,13 @@ FROM test; For geography, this function has a distance tolerance of about 0.00001 meters and uses the sphere rather - than spheroid calculation. + than spheroid calculation. NOTE: this is the "allowable" version that returns a boolean, not an integer. - &sfs_compliant; s2.1.1.2 //s2.1.13.3 + &sfs_compliant; s2.1.1.2 //s2.1.13.3 - ST_Intersects(g1, g2 ) --> Not (ST_Disjoint(g1, g2 )) &sqlmm_compliant; SQL-MM 3: 5.1.27 @@ -3319,7 +3319,7 @@ t If use_spheroid=false, then calculations will approximate a sphere instead of a spheroid. Currently for geometry this is an alias for ST_Length2D, but this may change to support higher dimensions. - Changed: 2.0.0 Breaking change -- in prior versions applying this to a MULTI/POLYGON of type geography would give you the perimeter of the POLYGON/MULTIPOLYGON. In 2.0.0 + Changed: 2.0.0 Breaking change -- in prior versions applying this to a MULTI/POLYGON of type geography would give you the perimeter of the POLYGON/MULTIPOLYGON. In 2.0.0 this was changed to return 0 to be in line with geometry behavior. Please use ST_Perimeter if you want the perimeter of a polygon For geography measurement defaults spheroid measurement. To use the faster less accurate sphere use ST_Length(gg,false); &sfs_compliant; s2.1.5.1 @@ -3633,9 +3633,9 @@ CAST('SPHEROID["GRS_1980",6378137,298.257222101]' As spheroid) As sph_m) as foo Description - Returns the 2-dimensional longest line between the points of two geometries. + Returns the 2-dimensional longest line between the points of two geometries. - + Availability: 1.5.0 @@ -3656,11 +3656,11 @@ CAST('SPHEROID["GRS_1980",6378137,298.257222101]' As spheroid) As sph_m) as foo SELECT ST_AsText( - ST_LongestLine('POINT(100 100)'::geometry, + ST_LongestLine('POINT(100 100)'::geometry, 'LINESTRING (20 80, 98 190, 110 180, 50 75 )'::geometry) ) As lline; - + lline ----------------- LINESTRING(100 100,98 190) @@ -3678,12 +3678,12 @@ LINESTRING(100 100,98 190) SELECT ST_AsText( ST_LongestLine( - ST_GeomFromText('POLYGON((175 150, 20 40, + ST_GeomFromText('POLYGON((175 150, 20 40, 50 60, 125 100, 175 150))'), ST_Buffer(ST_GeomFromText('POINT(110 170)'), 20) - ) + ) ) As llinewkt; - + lline ----------------- LINESTRING(20 40,121.111404660392 186.629392246051) @@ -3697,7 +3697,7 @@ LINESTRING(20 40,121.111404660392 186.629392246051) - + @@ -3708,15 +3708,15 @@ LINESTRING(20 40,121.111404660392 186.629392246051) -SELECT ST_AsText(ST_LongestLine(c.the_geom, c.the_geom)) As llinewkt, - ST_MaxDistance(c.the_geom,c.the_geom) As max_dist, - ST_Length(ST_LongestLine(c.the_geom, c.the_geom)) As lenll +SELECT ST_AsText(ST_LongestLine(c.the_geom, c.the_geom)) As llinewkt, + ST_MaxDistance(c.the_geom,c.the_geom) As max_dist, + ST_Length(ST_LongestLine(c.the_geom, c.the_geom)) As lenll FROM (SELECT ST_BuildArea(ST_Collect(the_geom)) As the_geom - FROM (SELECT ST_Translate(ST_SnapToGrid(ST_Buffer(ST_Point(50 ,generate_series(50,190, 50) - ),40, 'quad_segs=2'),1), x, 0) As the_geom + FROM (SELECT ST_Translate(ST_SnapToGrid(ST_Buffer(ST_Point(50 ,generate_series(50,190, 50) + ),40, 'quad_segs=2'),1), x, 0) As the_geom FROM generate_series(1,100,50) As x) AS foo ) As c; - + llinewkt | max_dist | lenll ---------------------------+------------------+------------------ LINESTRING(23 22,129 178) | 188.605408193933 | 188.605408193933 @@ -3734,7 +3734,7 @@ FROM (SELECT ST_BuildArea(ST_Collect(the_geom)) As the_geom , , - + ST_OrderingEquals @@ -3916,7 +3916,7 @@ FROM (SELECT ST_Buffer(ST_GeomFromText('POINT(1 0.5)'), 3) As a, -------------+-------------+----------------+--------------+-------+-------+--------------------- t | f | t | f | 2 | 2 | 2 - + @@ -3997,7 +3997,7 @@ st_perimeter Examples: Geography Return perimeter in meters and feet for Polygon and MultiPolygon. Note this is geography (WGS 84 long lat) -SELECT ST_Perimeter(geog) As per_meters, ST_Perimeter(geog)/0.3048 As per_ft +SELECT ST_Perimeter(geog) As per_meters, ST_Perimeter(geog)/0.3048 As per_ft FROM ST_GeogFromText('POLYGON((-71.1776848522251 42.3902896512902,-71.1776843766326 42.3903829478009, -71.1775844305465 42.3903826677917,-71.1775825927231 42.3902893647987,-71.1776848522251 42.3902896512902))') As geog; @@ -4007,7 +4007,7 @@ FROM ST_GeogFromText('POLYGON((-71.1776848522251 42.3902896512902,-71.1776843766 -- MultiPolygon example -- -SELECT ST_Perimeter(geog) As per_meters, ST_Perimeter(geog,false) As per_sphere_meters, ST_Perimeter(geog)/0.3048 As per_ft +SELECT ST_Perimeter(geog) As per_meters, ST_Perimeter(geog,false) As per_sphere_meters, ST_Perimeter(geog)/0.3048 As per_ft FROM ST_GeogFromText('MULTIPOLYGON(((-71.1044543107478 42.340674480411,-71.1044542869917 42.3406744369506, -71.1044553562977 42.340673886454,-71.1044543107478 42.340674480411)), ((-71.1044543107478 42.340674480411,-71.1044860600303 42.3407237015564,-71.1045215770124 42.3407653385914, @@ -4208,7 +4208,7 @@ SELECT ST_AsEWKT(ST_PointOnSurface(ST_GeomFromEWKT('LINESTRING(0 5 1, 0 0 1, 0 1 Returns a POINT projected along a geodesic from a start point using an azimuth (bearing) measured in radians and distance measured in meters. This is also called a direct geodesic problem. The azimuth is sometimes called the heading or the bearing in navigation. It is measured relative to true north (azimuth zero). East is azimuth 90 (π/2), south is azimuth 180 (π), west is azimuth 270 (3π/2). The distance is given in meters. - + Availability: 2.0.0 @@ -4224,7 +4224,7 @@ SELECT ST_AsEWKT(ST_PointOnSurface(ST_GeomFromEWKT('LINESTRING(0 5 1, 0 0 1, 0 1 (1 row) - + See Also @@ -4235,7 +4235,7 @@ SELECT ST_AsEWKT(ST_PointOnSurface(ST_GeomFromEWKT('LINESTRING(0 5 1, 0 0 1, 0 1 ST_Relate - + Returns true if this Geometry is spatially related to anotherGeometry, by testing for intersections between the Interior, Boundary and Exterior of the two geometries as specified @@ -4251,7 +4251,7 @@ SELECT ST_AsEWKT(ST_PointOnSurface(ST_GeomFromEWKT('LINESTRING(0 5 1, 0 0 1, 0 1 geometry geomB text intersectionMatrixPattern - + text ST_Relate geometry geomA @@ -4269,36 +4269,36 @@ SELECT ST_AsEWKT(ST_PointOnSurface(ST_GeomFromEWKT('LINESTRING(0 5 1, 0 0 1, 0 1 Description - + Version 1: Takes geomA, geomB, intersectionMatrix and Returns 1 (TRUE) if this Geometry is spatially related to anotherGeometry, by testing for intersections between the Interior, Boundary and Exterior of the two geometries as specified by the values in the DE-9IM matrix pattern. - + This is especially useful for testing compound checks of intersection, crosses, etc in one step. Do not call with a GeometryCollection as an argument - + This is the "allowable" version that returns a boolean, not an integer. This is defined in OGC spec - + This DOES NOT automagically include an index call. The reason for that is some relationships are anti e.g. Disjoint. If you are using a relationship pattern that requires intersection, then include the && index call. - + Version 2: Takes geomA and geomB and returns the Version 3: same as version 2, but allows to specify a boundary node rule (1:OGC/MOD2, 2:Endpoint, 3:MultivalentEndpoint, 4:MonovalentEndpoint) - + Do not call with a GeometryCollection as an argument - + not in OGC spec, but implied. see s2.1.13.2 - + Performed by the GEOS module - + &sfs_compliant; s2.1.1.2 // s2.1.13.3 &sqlmm_compliant; SQL-MM 3: 5.1.25 - Enhanced: 2.0.0 - added support for specifying boundary node rule (requires GEOS >= 3.0). + Enhanced: 2.0.0 - added support for specifying boundary node rule (requires GEOS >= 3.0). @@ -4335,11 +4335,11 @@ st_relate t - + See Also - + , , , , @@ -4347,7 +4347,7 @@ t ST_RelateMatch - + Returns true if intersectionMattrixPattern1 implies intersectionMatrixPattern2 @@ -4363,7 +4363,7 @@ t Description - + Takes intersectionMatrix and intersectionMatrixPattern and Returns true if the intersectionMatrix satisfies the intersectionMatrixPattern. For more information refer to . @@ -4380,20 +4380,20 @@ t --example of common intersection matrix patterns and example matrices -- comparing relationships of involving one invalid geometry and ( a line and polygon that intersect at interior and boundary) SELECT mat.name, pat.name, ST_RelateMatch(mat.val, pat.val) As satisfied - FROM + FROM ( VALUES ('Equality', 'T1FF1FFF1'), ('Overlaps', 'T*T***T**'), ('Within', 'T*F**F***'), ('Disjoint', 'FF*FF****') As pat(name,val) - CROSS JOIN + CROSS JOIN ( VALUES ('Self intersections (invalid)', '111111111'), ('IE2_BI1_BB0_BE1_EI1_EE2', 'FF2101102'), ('IB1_IE1_BB0_BE0_EI2_EI1_EE2', 'F11F00212') ) As mat(name,val); - + - + See Also @@ -4454,11 +4454,11 @@ SELECT mat.name, pat.name, ST_RelateMatch(mat.val, pat.val) As satisfied SELECT ST_AsText( - ST_ShortestLine('POINT(100 100)'::geometry, + ST_ShortestLine('POINT(100 100)'::geometry, 'LINESTRING (20 80, 98 190, 110 180, 50 75 )'::geometry) ) As sline; - + sline ----------------- LINESTRING(100 100,73.0769230769231 115.384615384615) @@ -4478,9 +4478,9 @@ SELECT ST_AsText( ST_ShortestLine( ST_GeomFromText('POLYGON((175 150, 20 40, 50 60, 125 100, 175 150))'), ST_Buffer(ST_GeomFromText('POINT(110 170)'), 20) - ) + ) ) As slinewkt; - + LINESTRING(140.752120669087 125.695053378061,121.111404660392 153.370607753949) @@ -4497,7 +4497,7 @@ SELECT ST_AsText( , , , - + ST_Touches @@ -4687,7 +4687,7 @@ SELECT ST_Touches('LINESTRING(0 0, 1 1, 0 2)'::geometry, 'POINT(0 2)'::geometry) the two geometries are considered spatially equal. Performed by the GEOS module - + Enhanced: 2.3.0 Enhancement to PIP short-circuit for geometry extended to support MultiPoints with few points. Prior versions only supported point in polygon. @@ -4706,7 +4706,7 @@ SELECT ST_Touches('LINESTRING(0 0, 1 1, 0 2)'::geometry, 'POINT(0 2)'::geometry) NOTE: this is the "allowable" version that returns a boolean, not an integer. - &sfs_compliant; s2.1.1.2 // s2.1.13.3 + &sfs_compliant; s2.1.1.2 // s2.1.13.3 - a.Relate(b, 'T*F**F***') &sqlmm_compliant; SQL-MM 3: 5.1.30 @@ -4745,6 +4745,6 @@ SELECT ST_Buffer(ST_GeomFromText('POINT(50 50)'), 20) As smallc, , , - - + + diff --git a/doc/reference_misc.xml b/doc/reference_misc.xml index 6894aa435..b2bb670e9 100644 --- a/doc/reference_misc.xml +++ b/doc/reference_misc.xml @@ -82,7 +82,7 @@ all_em|grabone | grab_rest Description Returns a BOX2D representing the maximum extents of the geometry. - + Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced. &curve_support; &P_support; @@ -313,9 +313,9 @@ BOX(-124.659652709961 24.6830825805664,-67.7798080444336 49.0012092590332) either by specifying a single distance with which the box should be expanded in all directions, or by specifying an expansion distance for each direction. - Uses double-precision. Can be very useful for distance queries, or to add a bounding box + Uses double-precision. Can be very useful for distance queries, or to add a bounding box filter to a query to take advantage of a spatial index. - In addition to the geometry version of ST_Expand, which is the most commonly used, variants + In addition to the geometry version of ST_Expand, which is the most commonly used, variants are provided that accept and produce internal BOX2D and BOX3D data types. ST_Expand is similar in concept to ST_Buffer, except while buffer expands the geometry in all directions, @@ -333,7 +333,7 @@ BOX(-124.659652709961 24.6830825805664,-67.7798080444336 49.0012092590332) Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced. Enhanced: 2.3.0 support was added to expand a box by different amounts in different dimensions. - + &P_support; &T_support; @@ -402,11 +402,11 @@ SELECT ST_Expand(CAST('BOX3D(778783 2951741 1,794875 2970042.61545891 10)' As bo ST_Extent will return boxes with only an x and y component even with (x,y,z) coordinate geometries. To maintain x,y,z use ST_3DExtent instead. - + Availability: 1.4.0 - + Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced. &P_support; &T_support; diff --git a/doc/reference_operator.xml b/doc/reference_operator.xml index 812f173b4..daf42d26d 100644 --- a/doc/reference_operator.xml +++ b/doc/reference_operator.xml @@ -90,7 +90,7 @@ FROM ( VALUES - + &&& @@ -136,7 +136,7 @@ FROM ( VALUES Examples: 3D LineStrings - SELECT tbl1.column1, tbl2.column1, tbl1.column2 &&& tbl2.column2 AS overlaps_3d, + SELECT tbl1.column1, tbl2.column1, tbl1.column2 &&& tbl2.column2 AS overlaps_3d, tbl1.column2 && tbl2.column2 AS overlaps_2d FROM ( VALUES (1, 'LINESTRING Z(0 0 1, 3 3 2)'::geometry), @@ -154,7 +154,7 @@ FROM ( VALUES Examples: 3M LineStrings - SELECT tbl1.column1, tbl2.column1, tbl1.column2 &&& tbl2.column2 AS overlaps_3zm, + SELECT tbl1.column1, tbl2.column1, tbl1.column2 &&& tbl2.column2 AS overlaps_3zm, tbl1.column2 && tbl2.column2 AS overlaps_2d FROM ( VALUES (1, 'LINESTRING M(0 0 1, 3 3 2)'::geometry), @@ -285,7 +285,7 @@ FROM The &<| operator returns TRUE if the bounding box of geometry A overlaps or is below of the bounding box of geometry B, or more accurately, overlaps or is NOT above the bounding box of geometry B. - + &curve_support; &P_support; @@ -557,19 +557,19 @@ FROM B - + boolean = - + geography - + A - + geography - + B @@ -593,7 +593,7 @@ FROM This operand will NOT make use of any indexes that may be available on the geometries. - + &curve_support; &P_support; Changed: 2.0.0 , the bounding box of geometries was changed to use double precision instead of float4 precision of @@ -636,7 +636,7 @@ GROUP BY column1; -- In versions prior to 2.0, this used to return true -- SELECT ST_GeomFromText('POINT(1707296.37 4820536.77)') = ST_GeomFromText('POINT(1707296.27 4820536.87)') As pt_intersect; - + --pt_intersect -- f @@ -1036,13 +1036,13 @@ FROM This operator has changed behavior in PostGIS 1.5 - from testing for actual geometric equality to only - checking for bounding box equality. To complicate things - it also depends on if you have done a hard or soft upgrade + from testing for actual geometric equality to only + checking for bounding box equality. To complicate things + it also depends on if you have done a hard or soft upgrade which behavior your database has. To find out which behavior your database has you can run the query below. To check for true equality use or and to check for bounding box equality ; + linkend="ST_Equals" /> and to check for bounding box equality ; operator is a safer option. @@ -1055,7 +1055,7 @@ select 'LINESTRING(0 0, 1 1)'::geometry ~= 'LINESTRING(0 1, 1 0)'::geometry as e equality | -----------------+ t | - + The above can be used to test if you have the new or old behavior of ~= operator. @@ -1063,7 +1063,7 @@ select 'LINESTRING(0 0, 1 1)'::geometry ~= 'LINESTRING(0 1, 1 0)'::geometry as e , , - + <-> @@ -1090,7 +1090,7 @@ Returns the 2D distance between A and B. B - + double precision <-> @@ -1128,14 +1128,14 @@ sphere for geographies. Enhanced: 2.2.0 -- True KNN ("K nearest neighbor") behavior for geometry and geography for PostgreSQL 9.5+. Note for geography KNN is based on sphere rather than spheroid. For PostgreSQL 9.4 and below, geography support is new but only supports centroid box. Changed: 2.2.0 -- For PostgreSQL 9.5 users, old Hybrid syntax may be slower, so you'll want to get rid of that hack if you are running your code only on PostGIS 2.2+ 9.5+. See examples below. Availability: 2.0.0 -- Weak KNN provides nearest neighbors based on geometry centroid distances instead of true distances. Exact results for points, inexact for all other types. Available for PostgreSQL 9.1+ - - + + Examples - d | edabbr | vaabbr @@ -1155,8 +1155,8 @@ ORDER BY d limit 10;]]> Then the KNN raw answer: - 'SRID=3005;POINT(1011102 450541)'::geometry limit 10;]]> d | edabbr | vaabbr @@ -1182,10 +1182,10 @@ For users running with PostgreSQL < 9.5, use a hybrid query to find the true 'SRID=3005;POINT(1011102 450541)'::geometry LIMIT 100) - SELECT * - FROM index_query + FROM va2005 + ORDER BY geom <-> 'SRID=3005;POINT(1011102 450541)'::geometry LIMIT 100) + SELECT * + FROM index_query ORDER BY d limit 10;]]> d | edabbr | vaabbr @@ -1202,7 +1202,7 @@ For users running with PostgreSQL < 9.5, use a hybrid query to find the true 18472.5531479404 | ALQ | 002 (10 rows) - + See Also @@ -1289,7 +1289,7 @@ SELECT track_id, dist FROM ( - + <#> @@ -1330,21 +1330,21 @@ Returns the 2D distance between A and B bounding boxes. Index only kicks in if one of the geometries is a constant e.g. ORDER BY (ST_GeomFromText('POINT(1 2)') <#> geom) instead of g1.geom <#>. Availability: 2.0.0 -- KNN only available for PostgreSQL 9.1+ - + Examples ST_GeomFromText('LINESTRING(746149 2948672,745954 2948576, 745787 2948499,745740 2948468,745712 2948438, - 745690 2948384,745677 2948319)',2249) As b_dist, + 745690 2948384,745677 2948319)',2249) As b_dist, ST_Distance(b.geom, ST_GeomFromText('LINESTRING(746149 2948672,745954 2948576, 745787 2948499,745740 2948468,745712 2948438, 745690 2948384,745677 2948319)',2249)) As act_dist - FROM bos_roads As b + FROM bos_roads As b ORDER BY b_dist, b.tlid LIMIT 100) As foo ORDER BY act_dist, tlid LIMIT 10;]]> @@ -1405,7 +1405,7 @@ boxes. The <<->> operator returns the n-D (euclidean) -distance between the centroids of the bounding boxes of two geometries. +distance between the centroids of the bounding boxes of two geometries. Useful for doing nearest neighbor approximate distance ordering. @@ -1423,8 +1423,8 @@ of a.geom Availability: 2.2.0 -- KNN only available for PostgreSQL 9.1+ - - + + @@ -1480,7 +1480,7 @@ Index only kicks in if one of the geometries is a constant e.g. ORDER BY Availability: 2.2.0 -- KNN only available for PostgreSQL 9.1+ - + diff --git a/doc/reference_output.xml b/doc/reference_output.xml index 9aa58d0ad..c42cd682a 100644 --- a/doc/reference_output.xml +++ b/doc/reference_output.xml @@ -46,10 +46,10 @@ ST_AsBinary is the reverse of for geometry. Use to convert to a postgis geometry from ST_AsBinary representation. - + The default behavior in PostgreSQL 9.0 has been changed to output bytea in hex encoding. ST_AsBinary is the reverse of for geometry. If your GUI tools - require the old behavior, then SET bytea_output='escape' in your database. + require the old behavior, then SET bytea_output='escape' in your database. Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced. @@ -106,13 +106,13 @@ - + ST_AsEncodedPolyline Returns an Encoded Polyline from a LineString geometry. - + @@ -122,16 +122,16 @@ - + Description - + Returns the geometry as an Encoded Polyline. This is a format very useful if you are using google maps - - + + Availability: 2.2.0 - + Examples Basic @@ -140,15 +140,15 @@ --result-- |_p~iF~ps|U_ulLnnqC_mqNvxq`@ - + Use in conjunction with geography linestring and geography segmentize, and put on google maps -- the SQL for Boston to San Francisco, segments every 100 KM SELECT ST_AsEncodedPolyline( ST_Segmentize( - ST_GeogFromText('LINESTRING(-71.0519 42.4935,-122.4483 37.64)'), + ST_GeogFromText('LINESTRING(-71.0519 42.4935,-122.4483 37.64)'), 100000)::geometry) As encodedFlightPath; - - + + javascript will look something like this where $ variable you replace with query result