liveBook · Manning

ST_X

As explained in Just Use Postgres!

ST_X is a PostGIS function that returns the X coordinate of a point geometry.

ST_X is a PostGIS function that returns the X coordinate from a point geometry. Use it after selecting or transforming a point when a query needs the horizontal coordinate separately. section 10.5.1

Generate a map URL from a transformed point

Transform Tampa’s point to SRID 4326, then combine ST_X and ST_Y in an OpenStreetMap URL: WITH tampa_city_point AS ( SELECT ST_Transform(way, 4326) AS coordinate FROM florida.planet_osm_point WHERE name = 'Tampa' AND place = 'city' ) SELECT 'https://www.openstreetmap.org/?mlat=' || ST_Y(coordinate) || '&mlon=' || ST_X(coordinate) AS osm_url FROM tampa_city_point; The result places ST_Y at mlat and ST_X at mlon, producing a URL for Tampa’s center.

How Just Use Postgres! covers it

How deep it goes
Defines coordinate retrieval and demonstrates execution after ST_Transform, without descending into implementation internals.
Examples
Uses Tampa’s city-center point and produces an OpenStreetMap URL containing its extracted coordinates.
What it assumes
Assumes point geometries and coordinate reference systems, while demonstrating their use through a concrete SQL query.
What you can do after
Apply it via PostGIS to extract X coordinates and combine them with other values in a location-oriented SQL result.
How it connects
Connects point geometries with ST_Transform, ST_Y, spatial reference systems, and OpenStreetMap URLs.
Cover of Just Use Postgres!
liveBookmanning.com
Read Just Use Postgres! on liveBook Get the book at manning.com

Related concepts

This explainer was generated by Manning's knowledge graph from the text of Just Use Postgres!. The section links point to the parts of the book it draws on.