PostGIS

https://postgis.net/docs/
Admin
PostGIS is a spatial database extension for PostgreSQL that adds support for geographic objects,...
Tokens:1,028,686
Snippets:5,764
Trust Score:9.7
Update:2 days ago
Show doc for...
Tokens:
Raw
### Example Output of \dx postgis*

Source: https://postgis.net/docs/postgis_installation.html

This is the expected output from the \dx postgis* command, showing the installed extensions, their versions, schemas, and descriptions.

```text
List of installed extensions
-[ RECORD 1 ]-------------------------------------------------
Name        | postgis
Version     | 3.6.5dev
Schema      | public
Description | PostGIS geometry, geography, and raster spat..
-[ RECORD 2 ]-------------------------------------------------
Name        | postgis_raster
Version     | 3.0.0dev
Schema      | public
Description | PostGIS raster types and functions
-[ RECORD 3 ]-------------------------------------------------
Name        | postgis_tiger_geocoder
Version     | 3.6.5dev
Schema      | tiger
Description | PostGIS tiger geocoder and reverse geocoder
-[ RECORD 4 ]-------------------------------------------------
Name        | postgis_topology
Version     | 3.6.5dev
Schema      | topology
Description | PostGIS topology spatial types and functions
```

--------------------------------

### Create address_standardizer_data_us extension

Source: https://postgis.net/docs/debug_standardize_address.html

One-time setup to enable the address_standardizer_data_us extension, required for the examples.

```sql
CREATE EXTENSION address_standardizer_data_us; -- only needs to be done once
```

--------------------------------

### Switch to extension-based install

Source: https://postgis.net/docs/postgis_administration.html

Run this command to switch from SQL script install to an extension-based install.

```bash
psql -c "SELECT postgis_extensions_upgrade();"
```

--------------------------------

### Short Version Installation Commands

Source: https://postgis.net/docs/postgis_installation.html

Commands to compile and install PostGIS assuming all dependencies are in the search path. After installation, enable or upgrade PostGIS in each database.

```bash
tar -xvzf postgis-3.6.5dev.tar.gz
cd postgis-3.6.5dev
./configure
make
make install
```

--------------------------------

### Example: SELECT PostGIS_Scripts_Released();

Source: https://postgis.net/docs/PostGIS_Scripts_Released.html

Shows the function call and its output, which returns the version number of the postgis.sql script released with the installed PostGIS lib. Starting with version 1.1.0, this returns the same value as PostGIS_Lib_Version.

```sql
SELECT PostGIS_Scripts_Released();
  postgis_scripts_released
-------------------------
 3.4.0dev 3.3.0rc2-993-g61bdf43a7
(1 row)
```

--------------------------------

### shp2pgsql example session: create SQL and load

Source: https://postgis.net/docs/using_postgis_dbmanagement.html

Example session using the loader to create an input file and load it. The first command creates a SQL file with options -c -D -s 4269 -i -I, and the second loads it with psql.

```bash
# shp2pgsql -c -D -s 4269 -i -I shaperoads.shp myschema.roadstable > roads.sql
# psql -d roadsdb -f roads.sql
```

--------------------------------

### ST_Scroll example: change start point to 3rd vertex

Source: https://postgis.net/docs/ST_Scroll.html

Example SQL query using ST_Scroll to change the start point of a closed LineString to its 3rd vertex. The function supports 3D and M coordinates. The output shows the resulting LineString.

```sql
SELECT ST_AsEWKT(ST_Scroll('SRID=4326;LINESTRING(0 0 0 1, 10 0 2 0, 5 5 4 2,0 0 0 1)', 'POINT(5 5 4 2)'));
```

```text
st_asewkt
----------
SRID=4326;LINESTRING(5 5 4 2,0 0 0 1,10 0 2 0,5 5 4 2)
```

--------------------------------

### Get validity details with ST_IsValidDetail

Source: https://postgis.net/docs/using_postgis_dbmanagement.html

Use ST_IsValidDetail to get the validity flag, reason, and location of invalidity. The example shows a self-intersection at a specific point.

```sql
SELECT valid, reason, ST_AsText(location) AS location
    FROM ST_IsValidDetail('POLYGON ((20 20, 120 190, 50 190, 170 50, 20 20))') AS t;

 valid |      reason       |                  location
-------+-------------------+---------------------------------------------
 f     | Self-intersection | POINT(91.51162790697674 141.56976744186045)
```

--------------------------------

### List upgrade SQL files

Source: https://postgis.net/docs/postgis_administration.html

After compiling and installing, list the *_upgrade.sql files in the installation folder.

```bash
ls `pg_config --sharedir`/contrib/postgis-3.6.5dev/*_upgrade.sql
```

--------------------------------

### ST_Width Example Query

Source: https://postgis.net/docs/RT_ST_Width.html

Example SQL query that uses ST_Width to get the width of a raster from the dummy_rast table. The output shows the result as 10 pixels.

```sql
SELECT ST_Width(rast) As rastwidth
FROM dummy_rast WHERE rid=1;

rastwidth
----------------
10
                
```

--------------------------------

### FindLayer example: SELECT layer_id(findLayer('features.land_parcels', 'feature'))

Source: https://postgis.net/docs/FindLayer.html

Example query that uses FindLayer to get the layer_id for a feature table and column. The output shows the result as 1.

```sql
SELECT layer_id(findLayer('features.land_parcels', 'feature'));
 layer_id
----------
        1
(1 row)
```

--------------------------------

### Install PostGIS with make install

Source: https://postgis.net/docs/postgis_installation.html

Run this command in the PostGIS source directory to install the built files into the prefix directory. The loader/dumper binaries go to [prefix]/bin, SQL files to [prefix]/share/contrib, and libraries to [prefix]/lib.

```shell
make install
```

--------------------------------

### Example: Return point 20% along 3D line

Source: https://postgis.net/docs/ST_3DLineInterpolatePoint.html

This example shows how to use ST_3DLineInterpolatePoint to get a point at 20% along a 3D line. The function returns a POINT Z with interpolated coordinates.

```sql
SELECT ST_AsText(
    ST_3DLineInterpolatePoint('LINESTRING(25 50 70, 100 125 90, 150 190 200)',
        0.20));

   st_asetext
----------------
 POINT Z (59.0675892910822 84.0675892910822 79.0846904776219)
```

--------------------------------

### Run topology_upgrade.sql

Source: https://postgis.net/docs/postgis_administration.html

Upgrade the topology extension using the topology_upgrade.sql script.

```bash
psql -f topology_upgrade.sql -d your_spatial_database
```

--------------------------------

### ST_InitTopoGeo Example

Source: https://postgis.net/docs/ST_InitTopoGeo.html

Example SQL query that calls ST_InitTopoGeo to create a topology schema named 'topo_schema_to_create'. The output shows the returned text description indicating the topology creation with an id.

```sql
SELECT topology.ST_InitTopoGeo('topo_schema_to_create') AS topocreation;
                      astopocreation
------------------------------------------------------------
 Topology-Geometry 'topo_schema_to_create' (id:7) created.

```

--------------------------------

### ST_LineSubstring with equal start and end fractions

Source: https://postgis.net/docs/ST_LineSubstring.html

When startfraction and endfraction are equal, the result is a POINT. This example uses 0.333 for both.

```sql
SELECT ST_AsText(ST_LineSubstring( 'LINESTRING(25 50, 100 125, 150 190)', 0.333, 0.333));
------------------------------------------
 POINT(69.2846934853974 94.2846934853974)
```

--------------------------------

### ST_Summary Example with ST_MakeEmptyRaster and ST_AddBand

Source: https://postgis.net/docs/RT_ST_Summary.html

This example creates a 10x10 raster with three bands using ST_MakeEmptyRaster and ST_AddBand, then calls ST_Summary to display the raster's properties. The output shows the raster dimensions, band count, extent, and per-band pixel type, NODATA value, and storage status.

```sql
SELECT ST_Summary(
    ST_AddBand(
        ST_AddBand(
            ST_AddBand(
                ST_MakeEmptyRaster(10, 10, 0, 0, 1, -1, 0, 0, 0)
                , 1, '8BUI', 1, 0
            )
            , 2, '32BF', 0, -9999
        )
        , 3, '16BSI', 0, NULL
    )
);
```

```text
                            st_summary
------------------------------------------------------------------
 Raster of 10x10 pixels has 3 bands and extent of BOX(0 -10,10 0)+
     band 1 of pixtype 8BUI is in-db with NODATA value of 0      +
     band 2 of pixtype 32BF is in-db with NODATA value of -9999  +
     band 3 of pixtype 16BSI is in-db with no NODATA value
(1 row)
```

--------------------------------

### Query PostGIS_Scripts_Installed

Source: https://postgis.net/docs/PostGIS_Scripts_Installed.html

Run this SQL query to get the version of the PostGIS scripts installed in the current database. The output shows the installed version, e.g., '3.4.0dev 3.3.0rc2-993-g61bdf43a7'. If this does not match PostGIS_Scripts_Released, the database may need an upgrade.

```sql
SELECT PostGIS_Scripts_Installed();
  postgis_scripts_installed
-------------------------
 3.4.0dev 3.3.0rc2-993-g61bdf43a7
(1 row)
```

--------------------------------

### ST_YMin with text representation (error example)

Source: https://postgis.net/docs/ST_YMin.html

This example shows that passing a text representation of a geometry (LINESTRING) directly to ST_YMin fails because it attempts to auto-cast the string to BOX3D, resulting in an error: 'BOX3D parser - doesn't start with BOX3D('.

```sql
SELECT ST_YMin('LINESTRING(1 3, 5 6)');

--ERROR:  BOX3D parser - doesn't start with BOX3D(
```

--------------------------------

### DropGeometryTable example with output and equivalent DROP TABLE

Source: https://postgis.net/docs/DropGeometryTable.html

Shows how to call DropGeometryTable with schema and table name, the resulting output message, and the equivalent DROP TABLE statement.

```sql
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;
```

--------------------------------

### shp2pgsql conversion and load using UNIX pipes

Source: https://postgis.net/docs/using_postgis_dbmanagement.html

Example showing a conversion and load done in one step using UNIX pipes, piping shp2pgsql output directly into psql.

```bash
# shp2pgsql shaperoads.shp myschema.roadstable | psql -d roadsdb
```

--------------------------------

### Start point of a CircularString

Source: https://postgis.net/docs/ST_StartPoint.html

Returns the first point of a CIRCULARLINESTRING geometry. The example shows the output POINT(5 2).

```sql
SELECT ST_AsText(ST_StartPoint('CIRCULARSTRING(5 2,-3 1.999999, -2 1, -4 2, 6 3)'::geometry));
 st_astext
------------
 POINT(5 2)
```

--------------------------------

### Notice: Version already installed

Source: https://postgis.net/docs/postgis_administration.html

This notice indicates the extension is already up to date. If upgrading from a development version, append 'next' to the version string.

```text
Version "3.6.5dev" of extension "postgis" is already installed
```

--------------------------------

### Query PostGIS_Wagyu_Version

Source: https://postgis.net/docs/PostGIS_Wagyu_Version.html

Run this SQL query to get the Wagyu library version. The output shows '0.5.0 (Internal)' as an example result.

```sql
SELECT PostGIS_Wagyu_Version();
 postgis_wagyu_version
-----------------------
 0.5.0 (Internal)
(1 row)
```

--------------------------------

### Start point of a non-LineString is NULL

Source: https://postgis.net/docs/ST_StartPoint.html

Returns NULL when the input is not a LINESTRING or CIRCULARLINESTRING. The example checks if the result is NULL and outputs t (true).

```sql
SELECT ST_StartPoint('POINT(0 1)'::geometry) IS NULL AS is_null;
  is_null
----------
 t
```

--------------------------------

### Populate_Topology_Layer output

Source: https://postgis.net/docs/Populate_Topology_Layer.html

This is the output of the previous example, showing the results of the Populate_Topology_Layer() function and the subsequent query on the topology.layer table.

```text
 schema_name | table_name | feature_column
-------------+------------+----------------
 strk        | parcels    | topo
(1 row)

 topology_id | layer_id |  sn  |   tn    |  fc
-------------+----------+------+---------+------
           2 |        2 | strk | parcels | topo
(1 row)
```

--------------------------------

### Start point of a LineString

Source: https://postgis.net/docs/ST_StartPoint.html

Returns the first point of a LINESTRING geometry as a POINT. The example shows the output POINT(0 1).

```sql
SELECT ST_AsText(ST_StartPoint('LINESTRING(0 1, 0 2)'::geometry));
 st_astext
------------
 POINT(0 1)
```

--------------------------------

### Tessellate a polygon with CG_Tesselate

Source: https://postgis.net/docs/CG_Tesselate.html

Example showing how to create a polygon and then tessellate it using CG_Tesselate. The first query creates the original polygon, the second applies CG_Tesselate, and the third shows the ST_AsText output of the tessellated polygon.

```sql
SELECT 'POLYGON (( 10 190, 10 70, 80 70, 80 130, 50 160, 120 160, 120 190, 10 190 ))'::geometry;
```

```sql
SELECT CG_Tesselate('POLYGON (( 10 190, 10 70, 80 70, 80 130, 50 160, 120 160, 120 190, 10 190 ))'::geometry);
```

```text
TIN(((80 130,50 160,80 70,80 130)),((50 160,10 190,10 70,50 160)), ((80 70,50 160,10 70,80 70)),((120 160,120 190,50 160,120 160)), ((120 190,10 190,50 160,120 190)))
```

--------------------------------

### SQL example: Compute minimum distance between two moving objects

Source: https://postgis.net/docs/ST_DistanceCPA.html

This example creates two trajectories with time measures using ST_AddMeasure and then calls ST_DistanceCPA to get the distance at their closest point of approach. The output shows the resulting distance value.

```sql
-- Return the minimum distance of two objects moving between 10:00 and 11:00
WITH inp AS ( SELECT
  ST_AddMeasure('LINESTRING Z (0 0 0, 10 0 5)'::geometry,
    extract(epoch from '2015-05-26 10:00'::timestamptz),
    extract(epoch from '2015-05-26 11:00'::timestamptz)
  ) a,
  ST_AddMeasure('LINESTRING Z (0 2 10, 12 1 2)'::geometry,
    extract(epoch from '2015-05-26 10:00'::timestamptz),
    extract(epoch from '2015-05-26 11:00'::timestamptz)
  ) b
)
SELECT ST_DistanceCPA(a,b) distance FROM inp;
```

```text
     distance
-------------------
 1.965214737762069
```

--------------------------------

### PostGIS_Raster_Lib_Version() example

Source: https://postgis.net/docs/RT_PostGIS_Raster_Lib_Version.html

Shows the function call and its output. The function returns the raster version and build configuration info.

```sql
SELECT PostGIS_Raster_Lib_Version();
```

```text
postgis_raster_lib_version
-----------------------------
 2.0.0
```

--------------------------------

### Tessellate a polyhedral surface with CG_Tesselate

Source: https://postgis.net/docs/CG_Tesselate.html

Example showing how to create a polyhedral surface and then tessellate it using CG_Tesselate. The first query creates the original cube, the second applies CG_Tesselate, and the third shows the ST_AsText output of the tessellated cube.

```sql
SELECT ST_GeomFromText('POLYHEDRALSURFACE Z( ((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)) )');
```

```sql
SELECT CG_Tesselate(ST_GeomFromText('POLYHEDRALSURFACE Z( ((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)) )'));
```

```text
TIN Z (((0 0 0,0 0 1,0 1 1,0 0 0)),((0 1 0,0 0 0,0 1 1,0 1 0)), ((0 0 0,0 1 0,1 1 0,0 0 0)), ((1 0 0,0 0 0,1 1 0,1 0 0)),((0 0 1,1 0 0,1 0 1,0 0 1)), ((0 0 1,0 0 0,1 0 0,0 0 1)), ((1 1 0,1 1 1,1 0 1,1 1 0)),((1 0 0,1 1 0,1 0 1,1 0 0)), ((0 1 0,0 1 1,1 1 1,0 1 0)),((1 1 0,0 1 0,1 1 1,1 1 0)), ((0 1 1,1 0 1,1 1 1,0 1 1)),((0 1 1,0 0 1,1 0 1,0 1 1)))
```

--------------------------------

### SELECT ST_AsText(ST_CurveN(...)) example

Source: https://postgis.net/docs/ST_CurveN.html

Example SQL query that uses ST_CurveN to extract the first component curve from a CompoundCurve and display it as text with ST_AsText. The index starts at 1, and the function returns NULL if the geometry is not a CompoundCurve or the index is out of range.

```sql
SELECT ST_AsText(ST_CurveN('COMPOUNDCURVE(
    (2 2, 2.5 2.5),
    CIRCULARSTRING(2.5 2.5, 4.5 2.5, 3.5 3.5),
    (3.5 3.5, 2.5 4.5, 3 5, 2 2)
  )', 1));
```

--------------------------------

### SELECT PostGIS_Lib_Build_Date() with output

Source: https://postgis.net/docs/PostGIS_Lib_Build_Date.html

Example query that calls PostGIS_Lib_Build_Date() and shows the resulting build date. The output includes the column header and a sample date.

```sql
SELECT PostGIS_Lib_Build_Date();
 postgis_lib_build_date
------------------------
 2023-06-22 03:56:11
(1 row)
```

--------------------------------

### Get SRID from a geometry with ST_SRID

Source: https://postgis.net/docs/ST_SRID.html

Example query that returns the SRID (4326) for a point geometry created with ST_GeomFromText. The result is shown in a comment.

```sql
SELECT ST_SRID(ST_GeomFromText('POINT(-71.1043 42.315)',4326));
    --result
    4326
    
```

--------------------------------

### Create view and query geometry_columns

Source: https://postgis.net/docs/using_postgis_dbmanagement.html

SQL to create a view vw_pois_ny_parks and then query geometry_columns to see how the view's geometry columns are registered.

```sql
CREATE VIEW vw_pois_ny_parks AS
SELECT *
  FROM pois_ny
  WHERE cat='park';

SELECT f_table_name, f_geometry_column, srid, type
	FROM geometry_columns
	WHERE f_table_name = 'vw_pois_ny_parks';
```

--------------------------------

### SQL example: Get raster pixel polygon with ST_PixelAsPolygon

Source: https://postgis.net/docs/RT_ST_PixelAsPolygon.html

This example selects pixel polygons from a raster using ST_PixelAsPolygon and displays them as text with ST_AsText. It uses a CROSS JOIN with generate_series to iterate over rows and columns. The output shows the resulting polygon geometries.

```sql
-- get raster pixel polygon
SELECT i,j, ST_AsText(ST_PixelAsPolygon(foo.rast, i,j)) As b1pgeom
FROM dummy_rast As foo
    CROSS JOIN generate_series(1,2) As i
    CROSS JOIN generate_series(1,1) As j
WHERE rid=2;

 i | j |                                                    b1pgeom
---+---+-----------------------------------------------------------------------------
 1 | 1 | POLYGON((3427927.75 5793244,3427927.8 5793244,3427927.8 5793243.95,...
 2 | 1 | POLYGON((3427927.8 5793244,3427927.85 5793244,3427927.85 5793243.95, ..
```

--------------------------------

### Get raster values at geometry points with ST_Value

Source: https://postgis.net/docs/RT_ST_Value.html

Shows how to use ST_Value with a geometry point to get band values. The geometry's SRID must match the raster's SRID. Includes a query on dummy_rast and a general example using a real table with ST_Intersects.

```sql
-- get raster values at particular postgis geometry points
-- the srid of your geometry should be same as for your raster
SELECT rid, ST_Value(rast, foo.pt_geom) As b1pval, ST_Value(rast, 2, foo.pt_geom) As b2pval
FROM dummy_rast CROSS JOIN (SELECT ST_SetSRID(ST_Point(3427927.77, 5793243.76), 0) As pt_geom) As foo
WHERE rid=2;

 rid | b1pval | b2pval
-----+--------+--------
   2 |    252 |     79


-- general fictitious example using a real table
SELECT rid, ST_Value(rast, 3, sometable.geom) As b3pval
FROM sometable
WHERE ST_Intersects(rast,sometable.geom);
                
```

--------------------------------

### Expected output for address_standardizer tests

Source: https://postgis.net/docs/postgis_installation.html

Example output from a successful 'make installcheck' for the address_standardizer extension.

```text
============== dropping database "contrib_regression" ==============
DROP DATABASE
============== creating database "contrib_regression" ==============
CREATE DATABASE
ALTER DATABASE
============== running regression test queries        ==============
test test-init-extensions     ... ok
test test-parseaddress        ... ok
test test-standardize_address_1 ... ok
test test-standardize_address_2 ... ok

=====================
 All 4 tests passed.
=====================
```

--------------------------------

### Example return debugging setting

Source: https://postgis.net/docs/Get_Geocode_Setting.html

Example query that retrieves the value of the 'debug_geocode_address' setting and shows the expected output.

```sql
SELECT get_geocode_setting('debug_geocode_address) As result;
result
---------
false
```
Tokens:
Raw