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

maxcollombin

array_to_string(array(
    x_min($geometry),
    y_min($geometry),
    x_max($geometry),
    y_max($geometry)
))

maxcollombin

DELETE FROM schema_name.table_name a
USING schema_name.table_name b 
WHERE a.id > b.id 
AND ST_Equals(a.geom, b.geom)
AND ST_DWithin(a.geom, b.geom, 0);

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

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

#https://pcjericks.github.io/py-gdalogr-cookbook/raster_layers.html#calculate-zonal-statistics
#https://github.com/ECCC-MSC/msc-pygeoapi/blob/master/msc_pygeoapi/process/weather/extract_raster.py

import logging

import gdal, ogr, osr, numpy
from pygeoapi.process.base import BaseProcessor, ProcessorExecuteError

LOGGER = logging.getLogger(__name__)

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

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

Write dc log in a dedicated file:

docker-compose logs -f -t >> pygeoapi.log

Access the container shell:

docker ps -a docker exec -it /bin/sh

maxcollombin

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<StyledLayerDescriptor version="1.0.0"
                       xmlns="http://www.opengis.net/sld"
                       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                       xsi:schemaLocation="http://www.opengis.net/sld http://schemas.opengis.net/sld/1.0.0/StyledLayerDescriptor.xsd">

    <!--
    SLD spec documentation: http://portal.opengeospatial.org/files/?artifact_id=22364
    Howto SLD user guide: http://wiki.deegree.org/deegreeWiki/HowToUseWMSGetMapRequestsWithSLD
    -->