PostGIS Documentation

https://github.com/wybert/postgis-doc-md
Admin
This repository contains the PostGIS documentation converted from HTML to Markdown format, making it...
Tokens:618,911
Snippets:4,540
Trust Score:7.4
Update:3 months ago
Show doc for...
Tokens:
Raw
### Linestring Boundary Example

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/ST_Boundary.md

This example demonstrates how to get the boundary points of a linestring.

```SQL
SELECT ST_Boundary(geom) FROM (SELECT 'LINESTRING(100 150,50 60, 70 80, 160 170)'::geometry As geom) As f;
```

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

### Install PostGIS Binaries and SQL Files

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/postgis_installation.md

Run 'make install' to copy PostGIS binaries to '[prefix]/bin' and SQL files to '[prefix]/share/contrib'. Libraries are installed in '[prefix]/lib'.

```bash
make install
```

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

### Install Documentation Conversion Dependencies

Source: https://github.com/wybert/postgis-doc-md/blob/main/README.md

Install the required Python packages for the documentation conversion script.

```bash
pip install requests beautifulsoup4 markdownify rich
```

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

### Build and Test PostGIS Extensions

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/postgis_installation.md

Navigate to the extensions directory, clean, build, and test the PostGIS extensions before installation. This example shows how to set PGUSER to overwrite psql variables.

```bash
cd extensions
cd postgis
make clean
make
export PGUSER=postgres #overwrite psql variables
make check #to test before install
make install
# to test extensions
make check RUNTESTFLAGS=--extension
```

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

### Example Usage

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/RT_ST_Max4ma.md

Example demonstrating the use of ST_Max4ma with ST_MapAlgebraFctNgb.

```APIDOC
## Examples

```sql
SELECT
    rid,
    st_value(
        st_mapalgebrafctngb(rast, 1, NULL, 1, 1, 'st_max4ma(float[][],text,text[])'::regprocedure, 'ignore', NULL), 2, 2
    )
FROM dummy_rast
WHERE rid = 2;
```

### Result

```
 rid | st_value
-----+----------
   2 |      254
(1 row)
```
```

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

### Point Geometry Examples

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/using_postgis_dbmanagement.md

Examples of Point geometries in 2D, 3D (Z), and 4D (ZM) coordinate systems.

```sql
POINT (1 2)
POINT Z (1 2 3)
POINT ZM (1 2 3 4)
```

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

### GeometryCollection Example

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/using_postgis_dbmanagement.md

A GeometryCollection example, demonstrating a heterogeneous collection of different geometry types.

```sql
GEOMETRYCOLLECTION ( POINT(2 3), LINESTRING(2 3, 3 4))
```

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

### Example Output of Straight Skeleton

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/CG_StraightSkeleton.md

This is an example of the output format for a straight skeleton computation, represented as a MULTILINESTRING with M values.

```text
MULTILINESTRING M ((0 0 0,0.5 0.5 0.5),(1 0 0,0.5 0.5 0.5),(1 1 0,0.5 0.5 0.5),(0 1 0,0.5 0.5 0.5))
```

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

### Get PostGIS Script Release Version

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/PostGIS_Scripts_Released.md

Call the PostGIS_Scripts_Released function to retrieve the version number of the postgis.sql script that was released with the installed PostGIS library. This function is kept for backward compatibility and returns the same value as PostGIS_Lib_Version starting from version 1.1.0.

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

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

### Copy Topology Example

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/CopyTopology.md

Use this snippet to create a backup of an existing topology. Replace 'ma_topo' with the name of the topology you want to back up and 'ma_topo_backup' with the desired name for the new backup topology.

```sql
SELECT topology.CopyTopology('ma_topo', 'ma_topo_backup');
```

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

### Get Start Point of a 3D LineString

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/ST_StartPoint.md

This function supports 3D geometries and preserves the z-coordinate when returning the start point.

```sql
SELECT ST_AsEWKT(ST_StartPoint('LINESTRING(0 1 1, 0 2 2)'::geometry));
```

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

### ST_Resize Examples with Different Resizing Methods

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/RT_ST_Resize.md

Demonstrates resizing a raster using percentage width and pixel height, exact pixel dimensions, and decimal percentage values for width and height. The examples show how to create an empty raster, add a band, and then apply ST_Resize with different parameters.

```sql
WITH foo AS(
SELECT
    1 AS rid,
    ST_Resize(
        ST_AddBand(
            ST_MakeEmptyRaster(1000, 1000, 0, 0, 1, -1, 0, 0, 0)
            , 1, '8BUI', 255, 0
        )
    , '50%', '500') AS rast
UNION ALL
SELECT
    2 AS rid,
    ST_Resize(
        ST_AddBand(
            ST_MakeEmptyRaster(1000, 1000, 0, 0, 1, -1, 0, 0, 0)
            , 1, '8BUI', 255, 0
        )
    , 500, 100) AS rast
UNION ALL
SELECT
    3 AS rid,
    ST_Resize(
        ST_AddBand(
            ST_MakeEmptyRaster(1000, 1000, 0, 0, 1, -1, 0, 0, 0)
            , 1, '8BUI', 255, 0
        )
    , 0.25, 0.9) AS rast
), bar AS (
    SELECT rid, ST_Metadata(rast) AS meta, rast FROM foo
)
SELECT rid, (meta).* FROM bar
```

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

### Get Installed PostGIS Scripts Version

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/PostGIS_Scripts_Installed.md

Use this SQL query to retrieve the version of the PostGIS scripts currently installed in your database. This helps in verifying the installation and upgrade status.

```sql
SELECT PostGIS_Scripts_Installed();

```

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

### Verify Tiger Geocoder Installation

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/postgis_installation.md

Execute this SQL query to confirm that the Tiger Geocoder is installed and functioning correctly by normalizing an address. The output should match the provided example.

```sql
SELECT na.address, na.streetname,na.streettypeabbrev, na.zip
   	FROM normalize_address('1 Devonshire Place, Boston, MA 02109') AS na;
```

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

### Example: Guarantee all lines in a table are closed

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/ST_AddPoint.md

This example shows how to use ST_AddPoint to ensure all lines in a table are closed by appending their starting point to the end if they are not already closed.

```APIDOC
## Example: Guarantee all lines in a table are closed

```sql
UPDATE sometable
SET geom = ST_AddPoint(geom, ST_StartPoint(geom))
FROM sometable
WHERE ST_IsClosed(geom) = false;
```
```

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

### Standardize Two-Part Address and Output as HSTORE

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/standardize_address.md

This example shows how to use the two-part address input variant of standardize_address and output the results as hstore. It requires the hstore extension.

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

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

### Change Start Point of Closed LineString

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/ST_Scroll.md

This example demonstrates how to use ST_Scroll to set the starting vertex of a closed LineString to a specific point. The function takes the LineString and the desired starting Point as arguments and returns the modified LineString in EWKT format.

```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)'));
```

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

### Create and Populate TopoGeometry

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/toTopoGeom.md

This snippet demonstrates a full workflow for setting up a topology, creating a table with a TopoGeometry column, and populating it by converting existing geometries using toTopoGeom. It includes verification steps using TopologySummary.

```sql
SELECT topology.CreateTopology('topo_boston_test', 2249);
CREATE TABLE nei_topo(gid serial primary key, nei varchar(30));
SELECT topology.AddTopoGeometryColumn('topo_boston_test', 'public', 'nei_topo', 'topo', 'MULTIPOLYGON') As new_layer_id;
INSERT INTO nei_topo(nei, topo)
SELECT nei,  topology.toTopoGeom(geom, 'topo_boston_test', 1)
FROM neighborhoods
WHERE gid BETWEEN 1 and 15;
SELECT * FROM
    topology.TopologySummary('topo_boston_test');
```

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

### Get Y Coordinate of a LineString Centroid

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/ST_X.md

This example demonstrates using ST_Centroid to find the center of a linestring and then extracting its Y coordinate using ST_Y. This is an example of using multiple geometry functions together.

```sql
SELECT ST_Y(ST_Centroid(ST_GeomFromEWKT('LINESTRING(1 2 3 4, 1 1 1 1)')));
 st_y
------
  1.5
(1 row)
```

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

### Compile PostGIS from Source (Short Version)

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/postgis_installation.md

Use this command sequence to compile and install PostGIS if all dependencies are already in your system's search path.

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

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

### Get Start Point of a CircularString

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/ST_StartPoint.md

ST_StartPoint can also process CIRCULARSTRING geometries, returning the initial point.

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

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

### Get Pixel Upper-Left Corner as Point

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/RT_ST_PixelAsPoint.md

Use ST_PixelAsPoint to get the upper-left corner of a raster pixel as a point geometry. This example queries a dummy raster table and displays the result in Well-Known Text format.

```sql
SELECT ST_AsText(ST_PixelAsPoint(rast, 1, 1)) FROM dummy_rast WHERE rid = 1;

   st_astext
----------------
 POINT(0.5 0.5)
                

```

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

### Install PostGIS Extensions

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/postgis_installation.md

Use SQL commands to create PostGIS and related extensions in the current database.

```sql
CREATE EXTENSION postgis;
CREATE EXTENSION postgis_raster;
CREATE EXTENSION postgis_sfcgal;
CREATE EXTENSION fuzzystrmatch; --needed for postgis_tiger_geocoder
--optional used by postgis_tiger_geocoder, or can be used standalone
CREATE EXTENSION address_standardizer;
CREATE EXTENSION address_standardizer_data_us;
CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION postgis_topology;
```

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

### Initialize Topology Schema

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/ST_InitTopoGeo.md

This snippet demonstrates how to use ST_InitTopoGeo to create a new topology schema named 'topo_schema_to_create'. It returns a text description of the creation process.

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

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

### Get SRID of a Geometry

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/TG_ST_SRID.md

This example demonstrates how to retrieve the SRID of a geometry created from Well-Known Text. The SRID is returned as an integer.

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

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

### Get Start Point of a LineString

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/ST_StartPoint.md

Use ST_StartPoint to retrieve the first point of a LINESTRING geometry. The result is returned as text.

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

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

### Create LINESTRING from WKB and check for non-LINESTRING input

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/ST_LinestringFromWKB.md

This example demonstrates creating a LINESTRING geometry from its WKB representation using ST_AsBinary and ST_GeomFromText. It also shows that providing WKB for a different geometry type (POINT) results in NULL.

```sql
SELECT
  ST_LineStringFromWKB(
	ST_AsBinary(ST_GeomFromText('LINESTRING(1 2, 3 4)'))
  ) AS aline,
  ST_LinestringFromWKB(
	ST_AsBinary(ST_GeomFromText('POINT(1 2)'))
  ) IS NULL AS null_return;
```

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

### Get a specific pixel value from a band

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/RT_ST_DumpValues.md

This example demonstrates how to retrieve a single pixel value from a specific band of a raster. It uses ST_DumpValues to get the 2D array for band 1 and then accesses a specific element using array indexing.

```sql
WITH foo AS (
    SELECT ST_SetValue(ST_AddBand(ST_MakeEmptyRaster(3, 3, 0, 0, 1, -1, 0, 0, 0), 1, '8BUI', 1, 0), 1, 2, 5) AS rast
)
SELECT
    (ST_DumpValues(rast, 1))[2][1]
FROM foo;
```

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

### Switch to Extension-Based Install

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/postgis_administration.md

After performing upgrades on a non-extension-based installation, run this command to switch your database to use the extension model for PostGIS. This is the recommended approach for future upgrades.

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

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

### PostGIS Tiger Geocoder Test Output

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/postgis_installation.md

Example output for successful testing of the postgis_tiger_geocoder extension, including the installation of other required extensions.

```text
============== dropping database "contrib_regression" ============== 
DROP DATABASE
============== creating database "contrib_regression" ============== 
CREATE DATABASE
ALTER DATABASE
============== installing fuzzystrmatch               ==============
CREATE EXTENSION
============== installing postgis                     ==============
CREATE EXTENSION
============== installing postgis_tiger_geocoder      ==============
CREATE EXTENSION
============== installing address_standardizer        ==============
CREATE EXTENSION
============== running regression test queries        ==============
test test-normalize_address   ... ok
test test-pagc_normalize_address ... ok

=====================
All 2 tests passed.
=====================
```

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

### Create Sample Raster Tables

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/RT_AddOverviewConstraints.md

Creates two sample tables, res1 and res2, each containing a raster column with specific dimensions and band settings. These tables are used in the subsequent example to demonstrate the AddOverviewConstraints function.

```sql
CREATE TABLE res1 AS SELECT
ST_AddBand(
  ST_MakeEmptyRaster(1000, 1000, 0, 0, 2),
  1, '8BSI'::text, -129, NULL
) r1;

CREATE TABLE res2 AS SELECT
ST_AddBand(
  ST_MakeEmptyRaster(500, 500, 0, 0, 4),
  1, '8BSI'::text, -129, NULL
) r2;
```

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

### Install Missing Indexes

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/postgis_installation.md

Run this SQL command after loading state data to ensure all necessary indexes are in place for optimal performance.

```sql
SELECT install_missing_indexes();
```

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

### Get PostGIS Raster Library Version

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/RT_PostGIS_Raster_Lib_Version.md

Call the PostGIS_Raster_Lib_Version function to retrieve the installed raster library version and build information.

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

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

### View Query Plan with EXPLAIN

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/using_postgis_dbmanagement.md

Execute a query with EXPLAIN prepended to view the query plan. This helps in understanding how the database intends to execute the query and whether indexes are being considered.

```sql
EXPLAIN SELECT ...;
```

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

### Standardize Single-Line Address with Tiger Geocoder

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/standardize_address.md

This example uses the standardize_address function with tables packaged with the tiger geocoder. It requires the postgis_tiger_geocoder to be installed.

```sql
SELECT *  FROM standardize_address('tiger.pagc_lex',
         'tiger.pagc_gaz', 'tiger.pagc_rules', 'One Devonshire Place, PH 301, Boston, MA 02109-1234');
```

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

### Download and Extract PostGIS Source

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/postgis_installation.md

Download the PostGIS source archive using wget and extract it. Then, navigate into the created directory to proceed with the installation.

```bash
wget https://postgis.net/stuff/postgis-3.5.3dev.tar.gz
tar -xvzf postgis-3.5.3dev.tar.gz
cd postgis-3.5.3dev
```

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

### Get PostGIS GDAL Version

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/RT_PostGIS_GDAL_Version.md

Use this function to check the GDAL library version and data file accessibility within your PostGIS installation.

```sql
SELECT PostGIS_GDAL_Version();
```

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

### Setup for Topology Geometries

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/ST_CreateTopoGeo.md

This snippet demonstrates the SQL commands to create a table for storing road data and then add a topology column to it using AddTopoGeometryColumn. This prepares the schema for using topology features.

```sql
CREATE TABLE ri.roads(gid serial PRIMARY KEY, road_name text);

SELECT topology.AddTopoGeometryColumn('ri_topo', 'ri', 'roads', 'topo', 'LINE');
```

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

### Get Geocode Setting Example

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/Get_Geocode_Setting.md

Retrieves the current value of the 'debug_geocode_address' setting. This is useful for checking if debugging information is enabled for the geocode_address function.

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

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

### Getting a Point from Identical Fractional Locations

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/ST_LineSubstring.md

When the start and end fractional locations are the same, ST_LineSubstring returns a POINT representing that specific location on the line.

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

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

### Extracting a LineString Substring

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/ST_LineSubstring.md

This example demonstrates how to extract a portion of a LINESTRING using fractional start and end points. The result is a new LINESTRING.

```sql
SELECT ST_AsText(ST_LineSubstring( 'LINESTRING (20 180, 50 20, 90 80, 120 40, 180 150)', 0.333, 0.666));
------------------------------------------------------------------------------------------------
LINESTRING (45.17311810399485 45.74337011202746, 50 20, 90 80, 112.97593050157862 49.36542599789519)
```

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

### Install address_standardizer_data_us Extension

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/standardize_address.md

This command is used to install the address_standardizer_data_us extension, which is necessary for using the standardize_address function with US data. This only needs to be done once.

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

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

### Get Coordinate Dimension of a Point

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/ST_CoordDim.md

Use ST_CoordDim to determine the coordinate dimension of a point geometry created with ST_Point. This example shows a 2D point.

```sql
SELECT ST_CoordDim(ST_Point(1,2));
			--result--
				2
```

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

### Example Usage

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/ST_AsEWKT.md

Demonstrates how to use ST_AsEWKT with a geometry object and a geography object.

```APIDOC
## Examples

```sql
SELECT ST_AsEWKT('0103000020E61000000100000005000000000000
			000000000000000000000000000000000000000000000000000000
			F03F000000000000F03F000000000000F03F000000000000F03
			F000000000000000000000000000000000000000000000000'::geometry);

		   st_asewkt
--------------------------------
SRID=4326;POLYGON((0 0,0 1,1 1,1 0,0 0))
(1 row)

SELECT ST_AsEWKT('0108000080030000000000000060E30A4100000000785C0241000000000000F03F0000000018
		E20A4100000000485F024100000000000000400000000018
		E20A4100000000305C02410000000000000840')

--st_asewkt---
CIRCULARSTRING(220268 150415 1,220227 150505 2,220227 150406 3)
```
```

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

### Get LibJSON-C Version

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/PostGIS_LibJSON_Version.md

Use this SQL function to retrieve the version of the LibJSON-C library linked with PostGIS. The output indicates the specific version installed.

```sql
SELECT PostGIS_LibJSON_Version();
```

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

### Populate Topology Layer

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/Populate_Topology_Layer.md

Demonstrates how to use Populate_Topology_Layer. It first creates a topology, a schema, a table, and adds a TopoGeometry column. It then shows the function returning no records initially, truncates the topology.layer table, and then calls Populate_Topology_Layer again to populate it. Finally, it selects from the topology.layer table to show the populated data.

```sql
SELECT CreateTopology('strk_topo');
CREATE SCHEMA strk;
CREATE TABLE strk.parcels(gid serial, parcel_id varchar(20) PRIMARY KEY, address text);
SELECT topology.AddTopoGeometryColumn('strk_topo', 'strk', 'parcels', 'topo', 'POLYGON');
-- this will return no records because this feature is already registered
SELECT *
  FROM topology.Populate_Topology_Layer();

-- let's rebuild
TRUNCATE TABLE topology.layer;

SELECT *
  FROM topology.Populate_Topology_Layer();

SELECT topology_id,layer_id, schema_name As sn, table_name As tn, feature_column As fc
FROM topology.layer;
```

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

### Get Topology SRID Example

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/GetTopologySRID.md

This snippet demonstrates how to use the GetTopologySRID function to retrieve the SRID for a topology named 'ma_topo'. The result is aliased as 'SRID'.

```sql
SELECT topology.GetTopologySRID('ma_topo') As SRID;
```

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

### Create LINESTRING from WKB and check for non-LINESTRING input

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/ST_LineFromWKB.md

This example demonstrates creating a LINESTRING from its WKB representation using ST_LineFromWKB. It also shows how the function returns NULL when the input WKB represents a different geometry type (a POINT in this case).

```sql
SELECT ST_LineFromWKB(ST_AsBinary(ST_GeomFromText('LINESTRING(1 2, 3 4)'))) AS aline,
		ST_LineFromWKB(ST_AsBinary(ST_GeomFromText('POINT(1 2)'))) IS NULL AS null_return;

```

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

### Get Coordinate Dimension of a Circular String

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/ST_CoordDim.md

Use ST_CoordDim to find the coordinate dimension of a circular string geometry. This example demonstrates a 3D circular string.

```sql
SELECT ST_CoordDim('CIRCULARSTRING(1 2 3, 1 3 4, 5 6 7, 8 9 10, 11 12 13)');
			---result--
				3
```

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

### Install PostGIS Comment SQL Files

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/postgis_installation.md

If 'make comments' was run to generate comment SQL files, use 'make comments-install' to install them.

```bash
make comments-install
```

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

### Get PostGIS Library Version

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/PostGIS_Lib_Version.md

Use this SQL query to retrieve the version number of the installed PostGIS library. The output will be a text string representing the version.

```sql
SELECT PostGIS_Lib_Version();
 postgis_lib_version
---------------------
 3.4.0dev
(1 row)
```

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

### Configure PostGIS Build

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/postgis_installation.md

Run the configure script to prepare the PostGIS source code for compilation. This step should be performed after checking out the source code.

```bash
./configure
```

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

### Get Pixel Points and Values

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/RT_ST_PixelAsPoints.md

This example demonstrates how to retrieve point geometries, values, and coordinates for each pixel in a specified raster band. It filters out NODATA values by default.

```sql
SELECT x, y, val, ST_AsText(geom) FROM (SELECT (ST_PixelAsPoints(rast, 1)).* FROM dummy_rast WHERE rid = 2) foo;
```

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

### Test Address Standardizer Extension

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/postgis_installation.md

Install and test the address_standardizer extension. This requires navigating to the extension's directory and running installation and check commands.

```bash
cd extensions/address_standardizer
make install
make installcheck
```

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

### Example 2: Using ST_Tile and ST_Union

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/RT_ST_Aspect.md

This example showcases a more complex scenario using ST_Tile to create multiple raster tiles and ST_Union to combine them before calculating the aspect. This query requires PostgreSQL 9.1 or higher.

```SQL
WITH foo AS (
    SELECT ST_Tile(
        ST_SetValues(
            ST_AddBand(
                ST_MakeEmptyRaster(6, 6, 0, 0, 1, -1, 0, 0, 0),
                1, '32BF', 0, -9999
            ),
            1, 1, 1, ARRAY[
                [1, 1, 1, 1, 1, 1],
                [1, 1, 1, 1, 2, 1],
                [1, 2, 2, 3, 3, 1],
                [1, 1, 3, 2, 1, 1],
                [1, 2, 2, 1, 2, 1],
                [1, 1, 1, 1, 1, 1]
            ]::double precision[] 
        ),
        2, 2
    ) AS rast
)
SELECT 
    t1.rast,
    ST_Aspect(ST_Union(t2.rast), 1, t1.rast)
FROM foo t1
CROSS JOIN foo t2
WHERE ST_Intersects(t1.rast, t2.rast)
GROUP BY t1.rast;
```

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

### Get Summary Statistics for Raster Coverage Bands

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/RT_ST_SummaryStats.md

Calculates summary statistics for each band of a raster coverage. This example shows how to query statistics for all bands of a named raster coverage.

```sql
-- stats for each band --
SELECT band, (stats).* 
FROM (SELECT band, ST_SummaryStats('o_4_boston','rast', band) As stats 
    FROM generate_series(1,3) As band) As foo;
```

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

### Find SRID for a Geometry Column

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/Find_SRID.md

This example demonstrates how to use the Find_SRID function to get the SRID for a geometry column named 'geom_4269' in the 'tiger_us_state_2007' table within the 'public' schema.

```sql
SELECT Find_SRID('public', 'tiger_us_state_2007', 'geom_4269');
find_srid
----------
4269

```

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

### ST_AsMVTGeom Example with Text Output

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/ST_AsMVTGeom.md

This example demonstrates the basic usage of ST_AsMVTGeom to transform a polygon and display the result as Well-Known Text (WKT). It uses a simple polygon, a computed tile box, and specific extent, buffer, and clip settings.

```sql
SELECT ST_AsText(ST_AsMVTGeom(
	ST_GeomFromText('POLYGON ((0 0, 10 0, 10 5, 0 -5, 0 0))'),
	ST_MakeBox2D(ST_Point(0, 0), ST_Point(4096, 4096)),
	4096, 0, false));
                              st_astext
--------------------------------------------------------------------
 MULTIPOLYGON(((5 4096,10 4091,10 4096,5 4096)),((5 4096,0 4101,0 4096,5 4096)))
```

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

### Get X Coordinates for Skewed Raster

Source: https://github.com/wybert/postgis-doc-md/blob/main/docs/RT_ST_RasterToWorldCoordX.md

This example demonstrates retrieving X coordinates from a skewed raster using both column and row. It also displays the pixel's X scale.

```sql
SELECT rid, ST_RasterToWorldCoordX(rast, 1, 1) As x1coord,
    ST_RasterToWorldCoordX(rast, 2, 3) As x2coord,
    ST_ScaleX(rast) As pixelx
FROM (SELECT rid, ST_SetSkew(rast, 100.5, 0) As rast FROM dummy_rast) As foo;
```
Tokens:
Raw