PostGIS (PostgesSQL) Guía introductoria
Miguel Ángel Manso ETSI en Topografía, Topografía, Geodesia y Cart Ca rtog ogrraf afía ía - UP UPM M
Índice •
•
•
•
•
¿Cómo crear una base de datos con la extensión extensión espacial? ¿Cómo crear una tabla con Geometrías? ¿Cómo dar de alta los metadatos metadatos de la tabla? ¿Cómo crear un índice espacial? ¿Qué operaciones se pueden realizar con las geometrías?
Crear una base de datos con extensión espacial •
Si la base de datos es nueva: –
–
•
Lo ideal es realizarlo con el asistente de instalación de la base de datos Crea o añade, 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
¿Cómo saber si la base de datos es PostGIS? •
•
En el esquema public se habrán generado dos tablas: En el mismo esquema se dispondrá de un buen número de funciones
Crear una tabla con geometrías •
•
•
Usa la misma Clausura que para el resto de tablas: CREATE TABLE …(); La columna que contendrá la geometría será de tipo: GEOMETRY Ejemplo: CREATE TABLE cola_markets ( mkt_id double precision NOT NULL, name character varying( 32), shape geometry);
Crear metadatos geometrías •
•
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 función AddGeometryColumn que realiza simultáneamente el alta del atributo en la tabla y crea los metadatos AddGeometryColumn(
, , , , , )
•
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 geometrías •
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.
