Skip to content

Instantly share code, notes, and snippets.

View sfkeller's full-sized avatar

Stefan sfkeller

25 followers · 6 following
View GitHub Profile
@sfkeller
sfkeller / SVZD-LSZG-Test
Created March 15, 2015 13:32
SVZD-LSZG-Test
<?xml version="1.0" encoding="UTF-8"?><kml xmlns="http://www.opengis.net/kml/2.2">
<Document>
<ExtendedData >
<Data name="id"><value>1</value></Data>
<Data name="name"><value>LSZG</value></Data>
<Data name="Type"><value>AD</value></Data>
<Data name="Date"><value></value></Data>
</ExtendedData>
<Placemark>
<Polygon><outerBoundaryIs><LinearRing><coordinates>7.410616476649881,47.18059371801634 7.422500012347867,47.1841179075316 7.423050767794946,47.18343729918494 7.423865507403813,47.18232618470535 7.412025739260667,47.17865417249334 7.41121397447568,47.179756384761625 7.410616476649881,47.18059371801634</coordinates></LinearRing></outerBoundaryIs>
@sfkeller
sfkeller / shp2gpkg.bat
Last active December 5, 2019 16:26
@echo off
echo %0.bat - Reading all Shapefiles from a directory into a GeoPackage file
rem Constants
set version=Version 0.1
set outfile=geopackage.gpkg
rem Checking if OGR is installed and in program path
ogr2ogr --version >nul
if not %errorlevel%==0 goto ogrnotfound
@sfkeller
sfkeller / Snap_polygons_to_polygons.sql
Created September 14, 2016 14:15
Snapping a polygon (= (to-)snappoly) to a reference polygon (refpoly)
/*
Snapping a polygon (= (to-)snappoly) to a reference polygon (refpoly).
Note that snapping only works on nearby points of two polygons.
So there are maybe orphaned points on snappoly which still are nearby
a refpoly segment but did not snap in the first round. So, in addition
the closest points on refpoly to the orphaned snappoly points are
collected and the first snapped poly output is snapped again given
this closest points collection.
*/
with
@sfkeller
sfkeller / opening_hours of City of Zurich
Created September 29, 2016 20:06
node_id;"name";"type";"opening_hours"
3653699172;"Höschgasse";"toilets";"06:00-22:00"
4083710471;"Migrolino";"supermarket";"06:00-23:00"
3726400508;"Marthas Salad";"fast_food";"07:00 -14:00 Sa So geschlossen"
4197310869;"Marthas Salad";"fast_food";"07:00 - 19:00"
3771154410;"Chinderhuus BABAR";"kindergarten";"07:00-19:00; Sa,Su off"
2822385924;"OneOverZero";"";"08:00-18:00"
1315473965;"Camping-Lädeli";"convenience";"08:00-22:00; Fr,Sa 08:00-23:00"
307747470;"Die Waid";"restaurant";"08:00-23:30"
3434503075;"Bibliothek, Geographisches Institut";"library";"08:15-17:00"
@sfkeller
sfkeller / Mittelalterfeste-Agenda_Schweiz_2017.geojson
Last active June 6, 2017 21:35
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
@sfkeller
sfkeller / line_table_to_point_files.bat
Created July 21, 2017 08:01
Converts lines (PostgreSQL/PostGIS table) to points which are stored in files (Shapefile)
@echo off
echo.
echo Converts lines (PostreSQL/PostGIS table) to points which are stored in files (Shapefile)
echo.
rem If Shapefile (or better) is the original input, load it in PostGIS e.g. using QGIS DB Manager.
rem Typical DB Manager options are: Create single-part geometries, and attributes id and geom.
if %1.==. goto error
@sfkeller
sfkeller / embedded_umap_example.html
Created November 22, 2017 12:50
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Embedded uMap Example</title>
<link rel="stylesheet" href="style.css">
<script src="script.js"></script>
</head>
<body>
<!-- page content -->
@sfkeller
sfkeller / Building within Mellingen (AG)
Created December 10, 2017 14:14
SELECT ST_AsText(way) geom,
COALESCE(name, '')||' '||osm_id AS label
FROM osm_polygon
WHERE tags->'building' > ''
AND ST_Intersects(way,
(SELECT way
FROM osm_polygon
WHERE osm_id=-1684362))
@sfkeller
sfkeller / cube_example_with_grouping
Last active February 28, 2018 18:15
SELECT
CASE WHEN GROUPING(movie.genre) = 1 then 'All Genres' else movie.genre end as Genre,
CASE WHEN GROUPING(customer.gender) = 1 then 'All Genders' else customer.gender end as Gender,
sum(sales.quantity) AS Quantity
FROM factsales sales, dimmovie movie, dimcustomer customer
WHERE sales.movieId = movie.movieId
AND sales.custID = customer.custID
GROUP BY CUBE(movie.genre, customer.gender);
/*
@sfkeller
sfkeller / map.geojson
Last active July 16, 2018 11:23
GR_NBK_2_modif_Stefan.geojson
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.