Maxime Collombin maxcollombin

Title: PostGIS 2.0 Cheatsheet CSS : css/cheaters.css

PostGIS 2.0 Cheatsheet

PostgreSQL PostGIS Geometry/Geography/Box Types

Magic words:

psql -U postgres

Some interesting flags (to see all, use -h or --help depending on your psql version):

-E: will describe the underlaying queries of the \ commands (cool for learning!)
-l: psql will list all databases and then exit (useful if the user you connect with doesn't has a default database, like at AWS RDS)

CSW (Catalogue Service for the Web) is an `OGC (Open Geospatial Consortium)`_ specification that defines common interfaces to discover, browse, and query metadata about data, services, and other potential resources.

	-- Snap the points to their closest lines, found in the subquery below
	SELECT
	point_id,
	line_id,
	ST_LINE_INTERPOLATE_POINT(line_geom,
	ST_Line_Locate_Point(line_geom, point_geom)) AS snapped_points --Create the snapped points
	FROM

	--Subquery to find the closest line to each point (within a pre-defined raidus)
	(

	SELECT
	a.id,
	-- Note that on line 13 the results are grouped by a.id so if you want to return the geometry from table_a,
	-- here you can use the function ANY_VALUE(a.geom) (as you cannot group by geometry in BigQuery)
	ARRAY_AGG(b.id ORDER BY ST_Distance(a.geog, b.geog) LIMIT 1) [ORDINAL(1)] AS neighbor_id
	-- Here we return the id of the closest geometry from table_b to each geometry in table_a.
	-- If you want to include more fields from table_b here you can use STRUCT(b.id, b.second_field, b.third_field) etc.
	FROM
	table_a a
	JOIN