11 PostGIS Common Queries.md

PostGIS Common Queries

There are many spatial queries that can be run within PostGIS. A definitive list is held at:

• PostGIS Functions Index

ST_X

Returns the X coordinate of the geometry.

• ST_X documentation

SELECT ST_X(geom) FROM postcodes_geo WHERE postcode = 'EX1 1EE';

ST_Y

Returns the Y coordinate of the geometry.

• ST_Y documentation

SELECT ST_Y(geom) FROM postcodes_geo WHERE postcode = 'EX1 1EE';

ST_Transform

Transforms a geometry into the specified spatial reference system, by ID. In PostGIS databases spatial reference systems are defined in the spatial_ref_sys table. ST_Transform relies on PostGIS knowing which SRS the geometry is currently in (in section 9 we used the UpdateGeometrySRID function to set this on a column).

• ST_Transform documentation

SELECT ST_Transform(geom, 4326) FROM postcodes_geo WHERE postcode  = 'EX1 1EE';

Using that to extract the Lng/Lat:

SELECT ST_X(ST_Transform(geom, 4326)), ST_Y(ST_Transform(geom, 4326))
FROM postcodes_geo WHERE postcode  = 'EX1 1EE';

ST_DWithin

Returns results that are within a specified distance. In the case below, returns results that are within 100 metres of a certain point.

• ST_DWithin documentation

SELECT postcode FROM postcodes_geo 
WHERE ST_DWithin(geom, ST_SetSRID(ST_MakePoint(292079, 92307), 27700), 100);

ST_Intersects

Returns results that share the same space. In the case below, returns postcodes that are within Devon.

• ST_Intersects documentation

SELECT postcode FROM postcodes_geo WHERE ST_Intersects(
    geom,
    (SELECT geom FROM county_region WHERE name = 'Devon County')
);

ST_Centroid

Returns the centre point of a geometry. For example, return the centre point of this Street (using the Unique Street Reference Number).

• ST_Centroid documentation

SELECT ST_X(ST_Centroid(wkb_geometry)), ST_X(ST_Centroid(wkb_geometry))
FROM street WHERE usrn = 14202557