PostGIS (PostgesSQL)

Gua introductoria
Miguel ngel Manso ETSI en Topografa, Geodesia y Cartografa - UPM

ndice
Cmo crear una base de datos con la extensin espacial? Cmo crear una tabla con Geometras? Cmo dar de alta los metadatos de la tabla? Cmo crear un ndice espacial? Qu operaciones se pueden realizar con las geometras?

Crear una base de datos con extensin espacial

Si la base de datos es nueva:
Lo ideal es realizarlo con el asistente de instalacin de la base de datos Crea o aade, esta funcionalidad a una base de datos

Si en la base de datos ya existe el template

Crear la nueva base de datos indicando el template

Cmo saber si la base de datos es PostGIS?

En el esquema public se habrn generado dos tablas:
En el mismo esquema se dispondr de un buen nmero de funciones

Crear una tabla con geometras

Usa la misma Clausura que para el resto de tablas: CREATE TABLE (); La columna que contendr la geometra ser de tipo: GEOMETRY Ejemplo:
CREATE TABLE cola_markets ( mkt_id double precision NOT NULL, name character varying( 32), shape geometry);

Crear metadatos geometras

Existe la tabla geometry_columns en la que se registran las tablas que contienen alguna columna de tipo geometry Esta tabla contiene las siguientes columnas:
F_TABLE_CATALOG, F_TABLE_SCHEMA, F_TABLE_NAME, F_GOMETRY_COLUM, COORD_DIMENSION, SRID, TYPE

Crear metadatos
INSERT INTO geometry_columns VALUES ( any, public .., 'cola_markets', 'shape', 2, 4326, MULTIPOLYGON);

AddGeometryColumn
Existe la funcin AddGeometryColumn que realiza simultneamente el alta del atributo en la tabla y crea los metadatos
AddGeometryColumn( <schema_name>, <table_name>, <column_name>, <srid>, <type>, <dimension> )

SELECT AddGeometryColumn(public, roads_geom, geom, 423, LINESTRING, 2)

Crear ndices espaciales

Para crear ndices SQL proporciona la siguiente clausura:
CREATE INDEX nombre ON la_tabla USING gist(la_columna);

Ejemplo:
CREATE INDEX constru_the_geom_gist ON public.constru USING gist (the_geom);

Operaciones con las geometras

PostGIS clasifica las operaciones en los siguientes paquetes:
Management Functions Geometry Constructors Geometry Accessors Geometry Editors Geometry Outputs Operators Spatial Relationships and Measurements Geometry Processing Functions Linear Referencing Long Transactions Support Miscellaneous Functions Exceptional Functions

Management Functions
AddGeometryColumn - Adds a geometry column to an existing table of attributes. DropGeometryColumn - Removes a geometry column from a spatial table. DropGeometryTable - Drops a table and all its references in geometry_columns. PostGIS_Full_Version - Reports full postgis version and build configuration infos. PostGIS_GEOS_Version - Returns the version number of the GEOS library. PostGIS_LibXML_Version - Returns the version number of the libxml2 library. PostGIS_Lib_Build_Date - Returns build date of the PostGIS library. PostGIS_Lib_Version - Returns the version number of the PostGIS library. PostGIS_PROJ_Version - Returns the version number of the PROJ4 library. PostGIS_Scripts_Build_Date - Returns build date of the PostGIS scripts. PostGIS_Scripts_Installed - Returns version of the postgis scripts installed in this database. PostGIS_Scripts_Released Version number of the postgis.sql script released with the installed postgis lib. PostGIS_Uses_Stats - Returns TRUE if STATS usage has been enabled. PostGIS_Version - Returns PostGIS version number and compile-time options. Populate_Geometry_Columns - Ensures geometry columns have appropriate spatial constraints and exist in the geometry_columns table. Probe_Geometry_Columns - Scans all tables with PostGIS geometry constraints and adds them to the geometry_columns table if they are not there. UpdateGeometrySRID - Updates the SRID of all features in a geometry column, and in geometry_columns

Geometry Constructors
ST_BdPolyFromText ST_BdMPolyFromText ST_GeogFromText ST_GeographyFromText ST_GeogFromWKB ST_GeomCollFromText ST_GeomFromEWKB ST_GeomFromEWKT ST_GeometryFromText ST_GeomFromGML ST_GeomFromKML ST_GMLToSQL ST_GeomFromText ST_GeomFromWKB. ST_LineFromMultiPoint ST_LineFromText ST_LineFromWKB ST_LinestringFromWKB ST_MakeBox2D ST_MakeBox3D ST_MakeLine ST_MakeEnvelope ST_MakePolygon ST_MakePoint ST_MakePointM ST_MLineFromText ST_MPointFromText ST_MPolyFromText ST_Point ST_PointFromText ST_PointFromWKB ST_Polygon ST_PolygonFromText ST_WKBToSQL ST_WKTToSQL

Geometry Accessors
GeometryType ST_Boundary ST_CoordDim ST_Dimension ST_EndPoint ST_Envelope ST_ExteriorRing ST_GeometryN ST_GeometryType ST_InteriorRingN ST_IsClosed ST_IsEmpty ST_IsRing ST_IsSimple ST_IsValid ST_IsValidReason ST_M ST_NDims ST_NPoints ST_NRings ST_NumGeometries ST_NumInteriorRings ST_NumInteriorRing ST_NumPoints ST_PointN ST_SRID ST_StartPoint ST_Summary ST_X ST_Y ST_Z ST_Zmflag

Geometry Editors
ST_AddPoint ST_Affine ST_Force_2D ST_Force_3D ST_Force_3DZ ST_Force_3DM ST_Force_4D ST_Force_Collection ST_ForceRHR ST_LineMerge ST_CollectionExtract ST_Multi ST_RemovePoint ST_Reverse ST_Rotate ST_RotateX ST_RotateY ST_RotateZ ST_Scale ST_Segmentize ST_SetPoint ST_SetSRID ST_SnapToGrid ST_Transform ST_Translate ST_TransScale

Geometry Outputs
ST_AsBinary Return WKB representation of the geometry without SRID metadata. ST_AsEWKB Return WKB representation of the geometry with SRID metadata. ST_AsEWKT Return WKT representation of the geometry with SRID metadata. ST_AsGeoJSON - Return the geometry as a GeoJSON element. ST_AsGML - Return the geometry as a GML version 2 or 3 element. ST_AsHEXEWKB - Returns a Geometry in HEXEWKB format (as text) using either little-endian (NDR) or big-endian (XDR) encoding.

ST_AsKML - Return the geometry as a KML element. Several variants.

ST_AsSVG - Returns a Geometry in SVG path data given a geometry object. ST_GeoHash - Return a GeoHash representation (geohash.org) of the geometry.

ST_AsText Return WKT representation of the geometry without SRID metadata.

Operators
&& - Returns TRUE if A's bounding box overlaps B's. &< - Returns TRUE if A's bounding box overlaps or is to the left of B's. &<| - Returns TRUE if A's bounding box overlaps or is below B's. &> - Returns TRUE if A' bounding box overlaps or is to the right of B's. << - Returns TRUE if A's bounding box is strictly to the left of B's. <<| - Returns TRUE if A's bounding box is strictly below B's. = - Returns TRUE if A's bounding box is the same as B's. >> - Returns TRUE if A's bounding box is strictly to the right of B's. @ - Returns TRUE if A's bounding box is contained by B's. |&> - Returns TRUE if A's bounding box overlaps or is above B's. |>> - Returns TRUE if A's bounding box is strictly above B's. ~ - Returns TRUE if A's bounding box contains B's. ~= - Returns TRUE if A's bounding box is the same as B's.

Relationships and Measurements

ST_Area ST_Azimuth ST_Centroid ST_ClosestPoint ST_Contains ST_ContainsProperly ST_Covers ST_CoveredBy ST_Crosses ST_LineCrossingDirection ST_Disjoint ST_Distance ST_HausdorffDistance ST_MaxDistance ST_Distance_Sphere ST_Distance_Spheroid ST_DFullyWithin ST_Dwithin ST_Equals ST_HasArc ST_Intersects ST_Length ST_Length2D ST_Length3D ST_Length_Spheroid ST_Length2D_Spheroid ST_Length3D_Spheroid ST_LongestLine ST_OrderingEquals ST_Overlaps ST_Perimeter ST_Perimeter2D ST_Perimeter3D ST_PointOnSurface ST_Relate ST_ShortestLine ST_Touches ST_Within

Geometry Processing Functions

ST_Buffer ST_BuildArea ST_Collect ST_ConvexHull ST_CurveToLine ST_Difference ST_Dump ST_DumpPoints ST_DumpRings ST_Intersection ST_LineToCurve ST_MemUnion ST_MinimumBoundingCircle ST_Polygonize ST_Shift_Longitude ST_Simplify ST_SimplifyPreserveTopology ST_SymDifference ST_Union

Linear Referencing
ST_Line_Interpolate_Point - Returns a point interpolated along a line. Second argument is a float8 between 0 and 1 representing of total length of linestring the point has to be located. ST_Line_Locate_Point - Returns a float between 0 and 1 representing the location of the closest point on LineString to the given Point, as a fraction of total 2d line length. ST_Line_Substring - Return a linestring being a substring of the input one starting and ending at the given fractions of total 2d length. Second and third arguments are float8 values between 0 and 1. ST_Locate_Along_Measure - Return a derived geometry collection value with elements that match the specified measure. Polygonal elements are not supported. ST_Locate_Between_Measures - Return a derived geometry collection value with elements that match the specified range of measures inclusively. Polygonal elements are not supported. ST_LocateBetweenElevations - Return a derived geometry (collection) value with elements that intersect the specified range of elevations inclusively. Only 3D, 4D LINESTRINGS and MULTILINESTRINGS are supported. ST_AddMeasure - Return a derived geometry with measure elements linearly interpolated between the start and end points. If the geometry has no measure dimension, one is added. If the geometry has a measure dimension, it is over-written with new values. Only LINESTRINGS and MULTILINESTRINGS are supported.

Long Transactions
AddAuth - Add an authorization token to be used in current transaction. CheckAuth - Creates trigger on a table to prevent/allow updates and deletes of rows based on authorization token. DisableLongTransactions - Disable long transaction support. This function removes the long transaction support metadata tables, and drops all triggers attached to lock-checked tables. EnableLongTransactions - Enable long transaction support. This function creates the required metadata tables, needs to be called once before using the other functions in this section. Calling it twice is harmless. LockRow - Set lock/authorization for specific row in table UnlockRows - Remove all locks held by specified authorization id. Returns the number of locks released.

Miscellaneous Functions
ST_Accum - Aggregate. Constructs an array of geometries. Box2D - Returns a BOX2D representing the maximum extents of the geometry. Box3D - Returns a BOX3D representing the maximum extents of the geometry. ST_Estimated_Extent - Return the 'estimated' extent of the given spatial table. The estimated is taken from the geometry column's statistics. The current schema will be used if not specified. ST_Expand - Returns bounding box expanded in all directions from the bounding box of the input geometry. Uses double-precision ST_Extent - an aggregate function that returns the bounding box that bounds rows of geometries. ST_Extent3D - an aggregate function that returns the box3D bounding box that bounds rows of geometries. Find_SRID - Function returns the integer SRID of the specified column by searching through the GEOMETRY_COLUMNS table. ST_Mem_Size - Returns the amount of space (in bytes) the geometry takes. ST_Point_Inside_Circle - Is the point geometry insert circle defined by center_x, center_y , radius ST_XMax - Returns X maxima of a bounding box 2d or 3d or a geometry. ST_XMin - Returns X minima of a bounding box 2d or 3d or a geometry. ST_YMax - Returns Y maxima of a bounding box 2d or 3d or a geometry. ST_YMin - Returns Y minima of a bounding box 2d or 3d or a geometry. ST_ZMax - Returns Z minima of a bounding box 2d or 3d or a geometry. ST_ZMin - Returns Z minima of a bounding box 2d or 3d or a geometry.

Exceptional Functions
PostGIS_AddBBox - Add bounding box to the geometry. PostGIS_DropBBox - Drop the bounding box cache from the geometry. PostGIS_HasBBox - Returns TRUE if the bbox of this geometry is cached, FALSE otherwise.