maxcollombin

Data.gov Catalogue Service for the Web (CSW) API

CSW (Catalogue Service for the Web)

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.

maxcollombin

PSQL

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)

maxcollombin

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

maxcollombin

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

PostGIS 2.0 Cheatsheet

Permalink to PostGIS 2.0 Cheatsheet

PostgreSQL PostGIS Geometry/Geography/Box Types

maxcollombin

-- 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)
(