Skip to content

Instantly share code, notes, and snippets.

View maxcollombin's full-sized avatar

Maxime Collombin maxcollombin

3 followers · 2 following
View GitHub Profile
@maxcollombin
maxcollombin / snap_points_to_lines.sql
Created July 25, 2022 06:29 — forked from wriglz/snap_points_to_lines.sql
SQL to snap points to the closest line within a predefined radius
-- 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
maxcollombin / bbox_qgis
Created July 25, 2022 06:37
Calculation of a bounding box on qgis. Note that the field type must be text (string) with a length of 256.
array_to_string(array(
x_min($geometry),
y_min($geometry),
x_max($geometry),
y_max($geometry)
))
@maxcollombin
maxcollombin / remove_duplicate_geom.sql
Created July 25, 2022 11:58
Remove duplicated geometries
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
maxcollombin / PostGIS 2.0 Cheatsheet.md
Created July 27, 2022 08:22 — forked from kidpixo/PostGIS 2.0 Cheatsheet.md
PostGIS 2.0 Cheatsheet
@maxcollombin
maxcollombin / nearest_neighbour_bigquery.sql
Created August 12, 2022 13:08 — forked from wriglz/nearest_neighbour_bigquery.sql
Spatial SQL to return the id of the closest geometry from table_b to each geometry in table_a
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
maxcollombin / zonalstats.py
Created September 6, 2022 12:03
Zonal Statistics process
#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
maxcollombin / postgres-cheatsheet.md
Created October 11, 2022 09:55 — forked from Kartones/postgres-cheatsheet.md
PostgreSQL command line cheatsheet

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
maxcollombin / data-gov-csw-howto.rst
Created October 25, 2022 14:23 — forked from kalxas/data-gov-csw-howto.rst
Data.gov CSW HowTo
@maxcollombin
maxcollombin / docker_commands.md
Created November 1, 2022 13:37
Useful Docker commands

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
maxcollombin / pinkWorldFlower06.sld
Created February 6, 2023 09:31
Sample SLD file
<?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
-->