### Setup Topology Table and Column
Source: https://postgis.net/docs/ST_CreateTopoGeo.html
This example shows the necessary SQL commands to create a table for storing road data and then add a topology geometry column to it within the 'ri_topo' topology. This setup is required before populating the topology with geometries.
```sql
-- 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');
```
--------------------------------
### Get Start Point of a LineString
Source: https://postgis.net/docs/ST_StartPoint.html
Demonstrates retrieving the starting point of a standard LINESTRING geometry. The output is formatted as Well-Known Text (WKT).
```sql
SELECT ST_AsText(ST_StartPoint('LINESTRING(0 1, 0 2)'::geometry));
st_astext
------------
POINT(0 1)
```
--------------------------------
### Populate Demo Table for ST_CoverageClean
Source: https://postgis.net/docs/ST_CoverageClean.html
Populates a table named 'example' with sample polygonal geometries to demonstrate the ST_CoverageClean function. This setup is necessary before running cleaning operations.
```sql
CREATE TABLE example AS SELECT * FROM (VALUES
(1, 'POLYGON ((10 190, 30 160, 40 110, 100 70, 120 10, 10 10, 10 190))'::geometry),
(2, 'POLYGON ((100 190, 10 190, 30 160, 40 110, 50 80, 74 110.5, 100 130, 140 120, 140 160, 100 190))'::geometry),
(3, 'POLYGON ((140 190, 190 190, 190 80, 140 80, 140 190))'::geometry),
(4, 'POLYGON ((180 40, 120 10, 100 70, 140 80, 190 80, 180 40))'::geometry)
) AS v(id, geom);
```
--------------------------------
### Snap LineString to Grid and Get WKT
Source: https://postgis.net/docs/ST_SnapToGrid.html
This example demonstrates snapping a LineString geometry to a grid with a cell size of 0.001 and returning the result as Well-Known Text (WKT). It shows how precision is reduced.
```sql
SELECT ST_AsText(ST_SnapToGrid(
ST_GeomFromText('LINESTRING(1.1115678 2.123, 4.111111 3.2374897, 4.11112 3.23748667)'),
0.001)
);
st_astext
-------------------------------------
LINESTRING(1.112 2.123,4.111 3.237)
```
--------------------------------
### Install PostGIS Extensions
Source: https://postgis.net/docs/postgis_installation.html
Use CREATE EXTENSION SQL commands to install PostGIS and its associated extensions into the current database. Ensure all necessary dependencies, like fuzzystrmatch, are installed first.
```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;
```
--------------------------------
### Install and Test Address Standardizer Extension
Source: https://postgis.net/docs/postgis_installation.html
Install and run regression tests for the address_standardizer extension. This is typically done after a root PostGIS installation.
```bash
cd extensions/address_standardizer
make install
make installcheck
```
--------------------------------
### Get PostGIS Script Release Version
Source: https://postgis.net/docs/PostGIS_Scripts_Released.html
Use this 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();
```
--------------------------------
### Get Start Point of a CircularString
Source: https://postgis.net/docs/ST_StartPoint.html
Demonstrates retrieving the starting point of a CIRCULARSTRING geometry. The output is formatted as Well-Known Text (WKT).
```sql
SELECT ST_AsText(ST_StartPoint('CIRCULARSTRING(5 2,-3 1.999999, -2 1, -4 2, 6 3)'::geometry));
st_astext
------------
POINT(5 2)
```
--------------------------------
### Switch to Extension-Based Install
Source: https://postgis.net/docs/postgis_administration.html
Advised command to switch to an extension-based PostGIS installation.
```bash
psql -c "SELECT postgis_extensions_upgrade();"
```
--------------------------------
### Get Raster Rotation
Source: https://postgis.net/docs/RT_ST_Rotation.html
This example demonstrates how to use ST_Rotation to get the rotation of a raster after setting its scale and skew. It selects the raster ID and the calculated rotation from the dummy_rast table.
```sql
SELECT rid, ST_Rotation(ST_SetScale(ST_SetSkew(rast, sqrt(2)), sqrt(2))) as rot FROM dummy_rast;
```
--------------------------------
### Quick PostGIS Installation from Source
Source: https://postgis.net/docs/postgis_installation.html
A streamlined process for compiling and installing PostGIS assuming all dependencies are met and in the search path.
```bash
tar -xvzf postgis-3.6.4dev.tar.gz
cd postgis-3.6.4dev
./configure
make
make install
```
--------------------------------
### Get X Coordinates for Skewed Raster
Source: https://postgis.net/docs/RT_ST_RasterToWorldCoordX.html
This example shows how to get X coordinates for a skewed raster, specifying both the column and row. It also retrieves the X scale of the pixel.
```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;
```
--------------------------------
### Install Address Standardizer Extension
Source: https://postgis.net/docs/standardize_address.html
This command needs to be run once to enable the address standardizer functionality.
```sql
CREATE EXTENSION address_standardizer_data_us;
```
--------------------------------
### Get Start Point of a 3D LineString
Source: https://postgis.net/docs/ST_StartPoint.html
Shows how ST_StartPoint extracts the starting point from a 3D LINESTRING, preserving the z-coordinate. The output is formatted as Extended Well-Known Text (EWKT).
```sql
SELECT ST_AsEWKT(ST_StartPoint('LINESTRING(0 1 1, 0 2 2)'::geometry));
st_asewkt
------------
POINT(0 1 1)
```
--------------------------------
### PostGIS Extensions Upgrade Output Example
Source: https://postgis.net/docs/PostGIS_Extensions_Upgrade.html
This is an example output from running the PostGIS_Extensions_Upgrade() function. It shows the progress of packaging extensions and a confirmation message upon completion. Note that some extensions might not be available or packagable.
```sql
NOTICE: Packaging extension postgis
NOTICE: Packaging extension postgis_raster
NOTICE: Packaging extension postgis_sfcgal
NOTICE: Extension postgis_topology is not available or not packagable for some reason
NOTICE: Extension postgis_tiger_geocoder is not available or not packagable for some reason
postgis_extensions_upgrade
-------------------------------------------------------------------
Upgrade completed, run SELECT postgis_full_version(); for details
(1 row)
```
--------------------------------
### Create a fully functional X3D document with a cube
Source: https://postgis.net/docs/ST_AsX3D.html
This example demonstrates how to generate a complete X3D document representing a cube. The output can be viewed in X3D viewers like FreeWrl. It utilizes ST_AsX3D with a POLYHEDRALSURFACE geometry.
```sql
SELECT '
' ||
ST_AsX3D( 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)) )')) ||
'
' As x3ddoc;
```
```text
x3ddoc
--------
```
--------------------------------
### Get Installed PostGIS Scripts Version
Source: https://postgis.net/docs/PostGIS_Scripts_Installed.html
This snippet demonstrates how to call the PostGIS_Scripts_Installed function to retrieve the version of the PostGIS scripts currently installed in the database. Ensure the function is available in your PostGIS version.
```sql
SELECT PostGIS_Scripts_Installed();
```
--------------------------------
### Populate Topology with ST_CreateTopoGeo
Source: https://postgis.net/docs/ST_CreateTopoGeo.html
This example demonstrates how to use ST_CreateTopoGeo to add a MULTILINESTRING geometry to an existing topology named 'ri_topo'. Ensure the topology exists and is empty before running this command.
```sql
-- Populate topology --
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,
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))',3438)
);
st_createtopogeo
----------------------------
Topology ri_topo populated
```
--------------------------------
### Normalize Address Example
Source: https://postgis.net/docs/postgis_installation.html
Verify your PostGIS Tiger Geocoder installation by running this SQL query to normalize an address.
```sql
SELECT na.address, na.streetname,na.streettypeabbrev, na.zip
FROM normalize_address('1 Devonshire Place, Boston, MA 02109') AS na;
```
--------------------------------
### Get Geometry Type for Polyhedral Surface
Source: https://postgis.net/docs/GeometryType.html
This example demonstrates retrieving the type for a POLYHEDRALSURFACE geometry using ST_GeometryType.
```sql
SELECT ST_GeometryType(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)) ));
```
--------------------------------
### Build and Install PostGIS Extensions
Source: https://postgis.net/docs/postgis_installation.html
Commands to navigate to the extensions directory, clean, build, test, and install PostGIS extensions. This is typically done for PostgreSQL 9.1+ when building from the source repository.
```bash
cd extensions
cd postgis
make clean
make
export PGUSER=postgres #overwrite psql variables
make check #to test before install
make install
```
--------------------------------
### Get Original Geometries for Simplification Example
Source: https://postgis.net/docs/CG_Simplify.html
Retrieves the original geometries from a GeometryCollection, likely for comparison or further processing before simplification.
```sql
WITH depts_pds as (SELECT ST_GeomFromText('GEOMETRYCOLLECTION(
POLYGON((88.46 158.85,90.77 171.54,147.31 173.85,146.15 145,173.85 119.62,146.15 103.46,112.69 118.46,91.92 93.08,65.38 101.15,34.23 121.92,41.15 142.69,49.23 143.85,88.46 158.85)),
POLYGON((112.69 118.46,146.15 103.46,190 60.77,185.38 43.46,126.54 26.15,83.85 28.46,67.69 64.23,43.46 58.46,10 83.85,34.23 121.92,65.38 101.15,91.92 93.08,112.69 118.46)))
') as geom)
SELECT (ST_Dump(CG_Simplify(geom, 0.5, true))).geom FROM depts_pds;
```
--------------------------------
### Create Topology and Insert TopoGeometries
Source: https://postgis.net/docs/toTopoGeom.html
This snippet demonstrates a full workflow for setting up a topology, creating a table with a TopoGeometry column, and inserting data using toTopoGeom. It assumes no prior topology setup and uses a layer ID of 1 with 0 tolerance.
```sql
-- do this if you don't have a topology setup already
-- creates topology not allowing any tolerance
SELECT topology.CreateTopology('topo_boston_test', 2249);
-- create a new table
CREATE TABLE nei_topo(gid serial primary key, nei varchar(30));
--add a topogeometry column to it
SELECT topology.AddTopoGeometryColumn('topo_boston_test', 'public', 'nei_topo', 'topo', 'MULTIPOLYGON') As new_layer_id;
new_layer_id
-----------
1
--use new layer id in populating the new topogeometry column
-- we add the topogeoms to the new layer with 0 tolerance
INSERT INTO nei_topo(nei, topo)
SELECT nei, topology.toTopoGeom(geom, 'topo_boston_test', 1)
FROM neighborhoods
WHERE gid BETWEEN 1 and 15;
--use to verify what has happened --
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
Layer 1, type Polygonal (3), 15 topogeoms
Deploy: public.nei_topo.topo
```
--------------------------------
### Get Raster Summary
Source: https://postgis.net/docs/RT_ST_Summary.html
This example demonstrates how to use ST_Summary to get a text summary of a raster created with multiple bands using ST_AddBand and ST_MakeEmptyRaster. It shows the output format including pixel types and NODATA values.
```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
)
);
```
--------------------------------
### List Upgrade SQL Files
Source: https://postgis.net/docs/postgis_administration.html
List all available `*_upgrade.sql` files in the PostGIS installation directory using `pg_config`.
```bash
ls `pg_config --sharedir`/contrib/postgis-3.6.4dev/*_upgrade.sql
```
--------------------------------
### One-Step Shapefile Loading with Pipes
Source: https://postgis.net/docs/using_postgis_dbmanagement.html
This example demonstrates how to perform a conversion and load in a single step using UNIX pipes, combining shp2pgsql with psql.
```bash
# shp2pgsql -c -D -s 4269 -i -I shaperoads.shp myschema.roadstable | psql -d roadsdb
```
--------------------------------
### PostGIS Build Check Output Example
Source: https://postgis.net/docs/postgis_installation.html
Sample output from the 'make check' command, illustrating a successful run of CUnit tests for PostGIS.
```text
CUnit - A unit testing framework for C - Version 2.1-3
http://cunit.sourceforge.net/
.
.
.
Run Summary: Type Total Ran Passed Failed Inactive
suites 44 44 n/a 0 0
tests 300 300 300 0 0
asserts 4215 4215 4215 0 n/a
Elapsed time = 0.229 seconds
.
.
.
Running tests
.
.
.
Run tests: 134
Failed: 0
-- if you build with SFCGAL
.
.
.
Running tests
.
.
.
Run tests: 13
Failed: 0
-- if you built with raster support
.
.
.
Run Summary: Type Total Ran Passed Failed Inactive
suites 12 12 n/a 0 0
tests 65 65 65 0 0
asserts 45896 45896 45896 0 n/a
.
.
.
Running tests
.
.
.
Run tests: 101
Failed: 0
-- topology regress
.
.
.
Running tests
.
.
.
Run tests: 51
Failed: 0
-- if you built --with-gui, you should see this too
CUnit - A unit testing framework for C - Version 2.1-2
http://cunit.sourceforge.net/
.
.
.
Run Summary: Type Total Ran Passed Failed Inactive
suites 2 2 n/a 0 0
tests 4 4 4 0 0
asserts 4 4 4 0 n/a
```
--------------------------------
### Get PostGIS Version and Compile Options
Source: https://postgis.net/docs/PostGIS_Version.html
Use this SQL query to retrieve the version of PostGIS installed and the compile-time options it was built with.
```sql
SELECT PostGIS_Version();
```
--------------------------------
### Get Geometry Type of a TIN
Source: https://postgis.net/docs/ST_GeometryType.html
This example demonstrates retrieving the geometry type of a TIN (Triangulated Irregular Network) using ST_GeomFromEWKT.
```sql
SELECT ST_GeometryType(geom) as result
FROM
(SELECT
ST_GeomFromEWKT('TIN (((
0 0 0,
0 0 1,
0 1 0,
0 0 0
)), ((
0 0 0,
0 1 0,
1 1 0,
0 0 0
))
)') AS geom
) AS g;
result
--------
ST_Tin
```
--------------------------------
### Optimal Convex Partition Example
Source: https://postgis.net/docs/CG_OptimalConvexPartition.html
Demonstrates how to compute an optimal convex partition of a given polygon geometry. This example is identical to those used for CG_YMonotonePartition, CG_ApproxConvexPartition, and CG_GreeneApproxConvexPartition.
```sql
SELECT ST_AsText(CG_OptimalConvexPartition('POLYGON((156 150,83 181,89 131,148 120,107 61,32 159,0 45,41 86,45 1,177 2,67 24,109 31,170 60,180 110,156 150))'::geometry));
```
```text
GEOMETRYCOLLECTION(POLYGON((156 150,83 181,89 131,148 120,156 150)),POLYGON((32 159,0 45,41 86,32 159)),POLYGON((45 1,177 2,67 24,45 1)),POLYGON((41 86,45 1,67 24,41 86)),POLYGON((107 61,32 159,41 86,67 24,109 31,107 61)),POLYGON((148 120,107 61,109 31,170 60,180 110,148 120)),POLYGON((156 150,148 120,180 110,156 150)))
```
--------------------------------
### PostGIS Tiger Geocoder Test Output
Source: https://postgis.net/docs/postgis_installation.html
Example output showing successful installation and regression tests for the postgis_tiger_geocoder extension, including dependencies.
```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.
=====================
```
--------------------------------
### PostGIS Point Geometry Examples
Source: https://postgis.net/docs/using_postgis_dbmanagement.html
Demonstrates the creation of Point geometries with different coordinate dimensions (XY, XYZ, XYZM).
```sql
POINT (1 2)
POINT Z (1 2 3)
POINT ZM (1 2 3 4)
```
--------------------------------
### Install PostGIS
Source: https://postgis.net/docs/postgis_installation.html
Copies PostGIS installation files to their specified directories after configuration.
```bash
make install
```
--------------------------------
### Standardize Address using Tiger Geocoder Tables
Source: https://postgis.net/docs/standardize_address.html
This example demonstrates using 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');
```
--------------------------------
### Get Raster X Pixel Width
Source: https://postgis.net/docs/RT_ST_ScaleX.html
This example demonstrates how to retrieve the X component of the pixel width for each raster in a table using ST_ScaleX.
```sql
SELECT rid, ST_ScaleX(rast) As rastpixwidth
FROM dummy_rast;
```
--------------------------------
### Standardize Address and Output as HSTORE
Source: https://postgis.net/docs/standardize_address.html
This snippet shows how to install the hstore extension and then use standardize_address, outputting the results using the hstore function for easier readability.
```sql
CREATE EXTENSION hstore;
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;
```
--------------------------------
### Create Tables for Overview Constraints
Source: https://postgis.net/docs/RT_AddOverviewConstraints.html
Sets up two tables, res1 and res2, each containing a raster column. These tables are prerequisites for demonstrating the AddOverviewConstraints function.
```sql
CREATE TABLE res1 AS SELECT
ST_AddBand(
ST_MakeEmptyRaster(1000, 1000, 0, 0, 2),
1, '8BSI'::text, -129, NULL
) r1;
```
```sql
CREATE TABLE res2 AS SELECT
ST_AddBand(
ST_MakeEmptyRaster(500, 500, 0, 0, 4),
1, '8BSI'::text, -129, NULL
) r2;
```
--------------------------------
### Get Raster Height
Source: https://postgis.net/docs/RT_ST_Height.html
Returns the height of the raster in pixels. This example queries a dummy raster table and selects the raster ID and its height.
```sql
SELECT rid, ST_Height(rast) As rastheight
FROM dummy_rast;
rid | rastheight
-----+------------
1 | 20
2 | 5
```
--------------------------------
### ST_LinestringFromWKB Example
Source: https://postgis.net/docs/ST_LinestringFromWKB.html
Demonstrates creating a LINESTRING from WKB and checking for NULL return when input is not a LINESTRING.
```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;
aline | null_return
------------------------------------------------
010200000002000000000000000000F ... | t
```
--------------------------------
### Get Minimum X of a Buffered Point's Bounding Diagonal
Source: https://postgis.net/docs/ST_BoundingDiagonal.html
This example demonstrates how to retrieve the minimum X-coordinate of the bounding diagonal for a buffered point. It utilizes ST_Buffer to create a geometry and then ST_BoundingDiagonal to get its diagonal, followed by ST_StartPoint and ST_X to extract the desired coordinate.
```sql
-- Get the minimum X in a buffer around a point
SELECT ST_X(ST_StartPoint(ST_BoundingDiagonal(
ST_Buffer(ST_Point(0,0),10)
)));
```
--------------------------------
### Get liblwgeom Library Version
Source: https://postgis.net/docs/PostGIS_Liblwgeom_Version.html
Call the PostGIS_Liblwgeom_Version function to retrieve the version string of the liblwgeom library. This version should correspond to the installed PostGIS version.
```sql
SELECT PostGIS_Liblwgeom_Version();
```
--------------------------------
### Compare Colormap Visualizations using ST_AsPNG
Source: https://postgis.net/docs/RT_ST_ColorMap.html
This example generates PNG images for the original raster and rasters transformed with various colormaps (grayscale, pseudocolor, fire, bluered, and a custom red colormap). This allows for a direct visual comparison of the colormapping effects.
```sql
SELECT
ST_AsPNG(rast) As orig_png,
ST_AsPNG(ST_ColorMap(rast,1,'greyscale')) As grey_png,
ST_AsPNG(ST_ColorMap(rast,1, 'pseudocolor')) As pseudo_png,
ST_AsPNG(ST_ColorMap(rast,1, 'nfire')) As fire_png,
ST_AsPNG(ST_ColorMap(rast,1, 'bluered')) As bluered_png,
ST_AsPNG(ST_ColorMap(rast,1, '
100% 255 0 0
80% 160 0 0
50% 130 0 0
30% 30 0 0
20% 60 0 0
0% 0 0 0
nv 255 255 255
')) As red_png
FROM funky_shapes;
```
--------------------------------
### Get X Maxima from Geometry (LINESTRING)
Source: https://postgis.net/docs/ST_XMax.html
Returns the X maxima of a geometry by first converting it from Well-Known Text. This example uses a 3D LINESTRING.
```sql
SELECT ST_XMax(ST_GeomFromText('LINESTRING(1 3 4, 5 6 7)'));
```
--------------------------------
### Get Y Coordinate from EWKT Point
Source: https://postgis.net/docs/ST_Y.html
Retrieves the Y coordinate from a point defined using EWKT format. This example demonstrates basic usage with a 3D point.
```sql
SELECT ST_Y(ST_GeomFromEWKT('POINT(1 2 3 4)'));
st_y
------
2
(1 row)
```
--------------------------------
### Get Y Coordinate of a Centroid
Source: https://postgis.net/docs/ST_X.html
This example demonstrates using ST_Y with ST_Centroid to find the Y coordinate of a line's centroid. It shows a related geometry accessor function.
```sql
SELECT ST_Y(ST_Centroid(ST_GeomFromEWKT('LINESTRING(1 2 3 4, 1 1 1 1)')));
st_y
------
1.5
(1 row)
```
--------------------------------
### Get Point on Surface of 3D LINESTRING
Source: https://postgis.net/docs/ST_PointOnSurface.html
Demonstrates retrieving a point on the surface of a 3D LINESTRING geometry using ST_GeomFromEWKT. This example shows that the z-index is preserved.
```sql
SELECT ST_AsEWKT(ST_PointOnSurface(ST_GeomFromEWKT('LINESTRING(0 5 1, 0 0 1, 0 10 2)')));
----------------
POINT(0 0 1)
```
--------------------------------
### Preparing Test Rasters and Canvas
Source: https://postgis.net/docs/RT_ST_MapAlgebraFct2.html
Sets up a table with sample raster geometries and creates a canvas raster. It then inserts rasters representing geometries and the canvas into the 'map_shapes' table.
```sql
-- prep our test table of rasters
DROP TABLE IF EXISTS map_shapes;
CREATE TABLE map_shapes(rid serial PRIMARY KEY, rast raster, bnum integer, descrip text);
INSERT INTO map_shapes(rast,bnum, descrip)
WITH mygeoms
AS ( SELECT 2 As bnum, ST_Buffer(ST_Point(90,90),30) As geom, 'circle' As descrip
UNION ALL
SELECT 3 AS bnum,
ST_Buffer(ST_GeomFromText('LINESTRING(50 50,150 150,150 50)'), 15) As geom, 'big road' As descrip
UNION ALL
SELECT 1 As bnum,
ST_Translate(ST_Buffer(ST_GeomFromText('LINESTRING(60 50,150 150,150 50)'), 8,'join=bevel'), 10,-6) As geom, 'small road' As descrip
),
-- define our canvas to be 1 to 1 pixel to geometry
canvas
AS ( SELECT ST_AddBand(ST_MakeEmptyRaster(250,
250,
ST_XMin(e)::integer, ST_YMax(e)::integer, 1, -1, 0, 0 ) , '8BUI'::text,0) As rast
FROM (SELECT ST_Extent(geom) As e,
Max(ST_SRID(geom)) As srid
from mygeoms
) As foo
)
-- return our rasters aligned with our canvas
SELECT ST_AsRaster(m.geom, canvas.rast, '8BUI', 240) As rast, bnum, descrip
FROM mygeoms AS m CROSS JOIN canvas
UNION ALL
SELECT canvas.rast, 4, 'canvas'
FROM canvas;
```
--------------------------------
### Example: Split a LineString by a Point using ST_Snap
Source: https://postgis.net/docs/ST_Split.html
Shows how to use ST_Snap to ensure a LineString can be split by a point that does not lie exactly on it, contrasting with a direct split attempt.
```SQL
WITH data AS (
SELECT
'LINESTRING(0 0, 100 100)'::geometry AS line,
'POINT(51 50)':: geometry AS point
)
SELECT ST_AsText( ST_Split( ST_Snap(line, point, 1), point)) AS snapped_split,
ST_AsText( ST_Split(line, point)) AS not_snapped_not_split
FROM data;
snapped_split | not_snapped_not_split
---------------------------------------------------------------------+---------------------------------------------
GEOMETRYCOLLECTION(LINESTRING(0 0,51 50),LINESTRING(51 50,100 100)) | GEOMETRYCOLLECTION(LINESTRING(0 0,100 100))
```
--------------------------------
### Get Upper Left Y Coordinate of Raster
Source: https://postgis.net/docs/RT_ST_UpperLeftY.html
Retrieves the upper left Y coordinate for each raster in the dummy_rast table. This example demonstrates basic usage of the ST_UpperLeftY function.
```sql
SELECT rid, ST_UpperLeftY(rast) As uly
FROM dummy_rast;
rid | uly
-----+---------
1 | 0.5
2 | 5793244
```
--------------------------------
### Create Dummy Raster Table and Insert Data
Source: https://postgis.net/docs/RT_ST_BandIsNoData.html
This snippet demonstrates how to create a table with a raster column and insert a raster with two bands. The first band is configured so its nodata value matches its pixel value, while the second band has a different nodata value and pixel value.
```sql
create table dummy_rast (rid integer, rast raster);
insert into dummy_rast values(1,
(
'01' -- little endian (uint8 ndr)
||
'0000' -- version (uint16 0)
||
'0200' -- nBands (uint16 0)
||
'17263529ED684A3F' -- scaleX (float64 0.000805965234044584)
||
'F9253529ED684ABF' -- scaleY (float64 -0.00080596523404458)
||
'1C9F33CE69E352C0' -- ipX (float64 -75.5533328537098)
||
'718F0E9A27A44840' -- ipY (float64 49.2824585505576)
||
'ED50EB853EC32B3F' -- skewX (float64 0.000211812383858707)
||
'7550EB853EC32B3F' -- skewY (float64 0.000211812383858704)
||
'E6100000' -- SRID (int32 4326)
||
'0100' -- width (uint16 1)
||
'0100' -- height (uint16 1)
||
'6' -- hasnodatavalue and isnodata value set to true.
||
'2' -- first band type (4BUI)
||
'03' -- novalue==3
||
'03' -- pixel(0,0)==3 (same that nodata)
||
'0' -- hasnodatavalue set to false
||
'5' -- second band type (16BSI)
||
'0D00' -- novalue==13
||
'0400' -- pixel(0,0)==4
)::raster
);
```
--------------------------------
### Get Raster Envelope as WKT
Source: https://postgis.net/docs/RT_ST_Envelope.html
This example demonstrates how to retrieve the bounding box of rasters in a table and represent it as Well-Known Text (WKT) using ST_Envelope and ST_AsText.
```sql
SELECT rid, ST_AsText(ST_Envelope(rast)) As envgeomwkt
FROM dummy_rast;
```
--------------------------------
### Create Topology Schema with SRID
Source: https://postgis.net/docs/CreateTopology.html
This example demonstrates creating a topology schema with a specified SRID. If precision, hasZ, topoid, or useslargeids are not provided, they will use their default values.
```sql
SELECT topology.CreateTopology('ri_topo', 3438) AS topoid;
```
--------------------------------
### Get PostGIS Library Version
Source: https://postgis.net/docs/PostGIS_Lib_Version.html
Use this SQL query to retrieve the version number of the PostGIS library installed on your system. The output will be a text string representing the version.
```sql
SELECT PostGIS_Lib_Version();
```
--------------------------------
### Get Raster Pixel Height
Source: https://postgis.net/docs/RT_ST_ScaleY.html
This example demonstrates how to retrieve the Y component of the pixel height for rasters in a table. It selects the raster ID and the result of ST_ScaleY applied to the raster.
```sql
SELECT rid, ST_ScaleY(rast) As rastpixheight
FROM dummy_rast;
```
--------------------------------
### View Installed Extensions with psql
Source: https://postgis.net/docs/postgis_installation.html
Connect to a specific database and use psql commands to list installed extensions, their versions, schemas, and descriptions. This is useful for detailed inspection of the extension status.
```psql
\connect mygisdb
\x
\dx postgis*
```
--------------------------------
### Install Address Standardizer US Data
Source: https://postgis.net/docs/debug_standardize_address.html
Before using the address standardizer functions, you need to install the necessary data extension. This command only needs to be run once.
```sql
CREATE EXTENSION address_standardizer_data_us;
-- only needs to be done once
```
--------------------------------
### Get Y Coordinates for Skewed Raster
Source: https://postgis.net/docs/RT_ST_RasterToWorldCoordY.html
Illustrates how to obtain Y coordinates for a skewed raster by providing both the column and row. This example uses ST_SetSkew to introduce skew before calling ST_RasterToWorldCoordY.
```sql
SELECT rid, ST_RasterToWorldCoordY(rast,1,1) As y1coord,
ST_RasterToWorldCoordY(rast,2,3) As y2coord,
ST_ScaleY(rast) As pixely
FROM (SELECT rid, ST_SetSkew(rast,0,100.5) As rast FROM dummy_rast) As foo;
```
--------------------------------
### Get Raster Memory Size
Source: https://postgis.net/docs/RT_ST_MemSize.html
Calculates the memory size in bytes for a raster created from a buffered geometry. This example demonstrates how to use ST_MemSize with a raster generated by ST_AsRaster.
```sql
SELECT ST_MemSize(ST_AsRaster(ST_Buffer(ST_Point(1,5),10,1000),150, 150, '8BUI')) As rast_mem;
rast_mem
--------
22568
```