PostGIS Terminal Examples - Versionsgeschichte

Stefan: /* Nearest Neighbor Syntax */

2025-04-07T09:32:36Z

Nearest Neighbor Syntax

← Nächstältere Version		Version vom 7. April 2025, 09:32 Uhr
Zeile 473:		Zeile 473:
	Es ist in PostGIS möglich, eine indizierte KNN-Suche zu erhalten, die nur eine einzige verwendet für die Abstands-Berechnung und für das ORDER-BY. Siehe: http://blog.cleverelephant.ca/2021/12/knn-syntax.html		Es ist in PostGIS möglich, eine indizierte KNN-Suche zu erhalten, die nur eine einzige verwendet für die Abstands-Berechnung und für das ORDER-BY. Siehe: http://blog.cleverelephant.ca/2021/12/knn-syntax.html

	<nowiki>		<nowiki>
	-- Calculates for all buildings the distance to nearest public transport stop with KNN index		-- Calculates for all buildings the distance to nearest public transport stop with KNN index
	WITH buildings AS (		WITH buildings AS (
Zeile 508:		Zeile 508:
	FROM nearest_distances		FROM nearest_distances
	LIMIT 10000;		LIMIT 10000;
	</nowiki>		</nowiki>

	OSM SQL Terminal-Aufruf [https://terminal.osmdatapipeline.geoh.infs.ch/?query=--+Calculates+for+all+buildings+the+distance+to+nearest+public+transport+stop+with+KNN+index%0D%0AWITH+buildings+AS+%28%0D%0A++SELECT+osm_id%2C+label%2C+tags%2C+geom%0D%0A++FROM+osm_poi%0D%0A++WHERE+tags-%3E%27building%27+IS+NOT+NULL%0D%0A%29%2C%0D%0Astops+AS+%28%0D%0A++SELECT+osm_id%2C+label%2C+tags%2C+geom%0D%0A++FROM+osm_point%0D%0A++WHERE+tags+%40%3E+%27public_transport%3D%3Estop_position%27%0D%0A%29%2C%0D%0Anearest_distances+AS+%28%0D%0A++SELECT%0D%0A++++b.osm_id+AS+building_id%2C%0D%0A++++s.osm_id+AS+stop_id%2C%0D%0A++++s.label+AS+stop_label%2C%0D%0A++++ST_Distance%28b.geom%2C+s.geom%29+AS+distance%2C%0D%0A%09b.geom%0D%0A++FROM+buildings+b%0D%0A++--JOIN+stops+s+ON+ST_DWithin%28b.geom%2C+s.geom%2C+5000%29%0D%0A++JOIN+LATERAL+%28%0D%0A++++SELECT+s.osm_id%2C+s.label%2C+s.geom%0D%0A++++FROM+stops+s%0D%0A++++ORDER+BY+s.geom+%3C-%3E+b.geom+LIMIT+1%0D%0A++%29+s+ON+TRUE%0D%0A%29%0D%0ASELECT+DISTINCT+ON+%28building_id%29%0D%0A++building_id%2C%0D%0A++distance%2C%0D%0A++stop_id%2C%0D%0A++stop_label+as+label%2C%0D%0A++geom++%0D%0AFROM+nearest_distances%0D%0ALIMIT+10000%3B direkt]		OSM SQL Terminal-Aufruf [https://terminal.osmdatapipeline.geoh.infs.ch/?query=--+Calculates+for+all+buildings+the+distance+to+nearest+public+transport+stop+with+KNN+index%0D%0AWITH+buildings+AS+%28%0D%0A++SELECT+osm_id%2C+label%2C+tags%2C+geom%0D%0A++FROM+osm_poi%0D%0A++WHERE+tags-%3E%27building%27+IS+NOT+NULL%0D%0A%29%2C%0D%0Astops+AS+%28%0D%0A++SELECT+osm_id%2C+label%2C+tags%2C+geom%0D%0A++FROM+osm_point%0D%0A++WHERE+tags+%40%3E+%27public_transport%3D%3Estop_position%27%0D%0A%29%2C%0D%0Anearest_distances+AS+%28%0D%0A++SELECT%0D%0A++++b.osm_id+AS+building_id%2C%0D%0A++++s.osm_id+AS+stop_id%2C%0D%0A++++s.label+AS+stop_label%2C%0D%0A++++ST_Distance%28b.geom%2C+s.geom%29+AS+distance%2C%0D%0A%09b.geom%0D%0A++FROM+buildings+b%0D%0A++--JOIN+stops+s+ON+ST_DWithin%28b.geom%2C+s.geom%2C+5000%29%0D%0A++JOIN+LATERAL+%28%0D%0A++++SELECT+s.osm_id%2C+s.label%2C+s.geom%0D%0A++++FROM+stops+s%0D%0A++++ORDER+BY+s.geom+%3C-%3E+b.geom+LIMIT+1%0D%0A++%29+s+ON+TRUE%0D%0A%29%0D%0ASELECT+DISTINCT+ON+%28building_id%29%0D%0A++building_id%2C%0D%0A++distance%2C%0D%0A++stop_id%2C%0D%0A++stop_label+as+label%2C%0D%0A++geom++%0D%0AFROM+nearest_distances%0D%0ALIMIT+10000%3B direkt]

Stefan: /* Nearest Neighbor Syntax */

2025-04-07T09:32:08Z

Nearest Neighbor Syntax

@@ Zeile 471: / Zeile 471: @@
 === Nearest Neighbor Syntax ===
-Es ist in PostGIS möglich, eine indizierte KNN-Suche zu erhalten, die nur eine einzige verwendet für die Abstands-Berechnung und für das ORDER-BY.
+Es ist in PostGIS möglich, eine indizierte KNN-Suche zu erhalten, die nur eine einzige verwendet für die Abstands-Berechnung und für das ORDER-BY. Siehe: http://blog.cleverelephant.ca/2021/12/knn-syntax.html
-Aus: http://blog.cleverelephant.ca/2021/12/knn-syntax.html
+<nowiki>
-  <nowiki>SELECT
+OSM SQL Terminal-Aufruf [https://terminal.osmdatapipeline.geoh.infs.ch/?query=--+Calculates+for+all+buildings+the+distance+to+nearest+public+transport+stop+with+KNN+index%0D%0AWITH+buildings+AS+%28%0D%0A++SELECT+osm_id%2C+label%2C+tags%2C+geom%0D%0A++FROM+osm_poi%0D%0A++WHERE+tags-%3E%27building%27+IS+NOT+NULL%0D%0A%29%2C%0D%0Astops+AS+%28%0D%0A++SELECT+osm_id%2C+label%2C+tags%2C+geom%0D%0A++FROM+osm_point%0D%0A++WHERE+tags+%40%3E+%27public_transport%3D%3Estop_position%27%0D%0A%29%2C%0D%0Anearest_distances+AS+%28%0D%0A++SELECT%0D%0A++++b.osm_id+AS+building_id%2C%0D%0A++++s.osm_id+AS+stop_id%2C%0D%0A++++s.label+AS+stop_label%2C%0D%0A++++ST_Distance%28b.geom%2C+s.geom%29+AS+distance%2C%0D%0A%09b.geom%0D%0A++FROM+buildings+b%0D%0A++--JOIN+stops+s+ON+ST_DWithin%28b.geom%2C+s.geom%2C+5000%29%0D%0A++JOIN+LATERAL+%28%0D%0A++++SELECT+s.osm_id%2C+s.label%2C+s.geom%0D%0A++++FROM+stops+s%0D%0A++++ORDER+BY+s.geom+%3C-%3E+b.geom+LIMIT+1%0D%0A++%29+s+ON+TRUE%0D%0A%29%0D%0ASELECT+DISTINCT+ON+%28building_id%29%0D%0A++building_id%2C%0D%0A++distance%2C%0D%0A++stop_id%2C%0D%0A++stop_label+as+label%2C%0D%0A++geom++%0D%0AFROM+nearest_distances%0D%0ALIMIT+10000%3B direkt]
 == Special SQL Queries ==

Stefan am 5. März 2025 um 15:47 Uhr

2025-03-05T15:47:15Z

← Nächstältere Version		Version vom 5. März 2025, 15:47 Uhr
Zeile 1:		Zeile 1:
	Here are some examples queries for the [[PostGIS Terminal\|OSM SQL Terminal]]. All SQL queries work in the terminal (like [http://labs.geometa.info/postgisterminal/?xapi=node%5Btourism%3Dzoo%5D this]) but mostly also on any PostGIS database created with osm2pgsql.		Here are some examples queries for the [[PostGIS Terminal\|OSM SQL Terminal]] (formerly PostGIS Terminal). All SQL queries work in the terminal (like [http://labs.geometa.info/postgisterminal/?xapi=node%5Btourism%3Dzoo%5D this]) but mostly also on any PostGIS database created with osm2pgsql.

	See also [[PostGIS - Tipps und Tricks]].		See also [[PostGIS - Tipps und Tricks]].

Stefan am 5. März 2025 um 15:46 Uhr

2025-03-05T15:46:53Z

← Nächstältere Version		Version vom 5. März 2025, 15:46 Uhr
Zeile 1:		Zeile 1:
	Here are some examples queries for the [[PostGIS Terminal]]. All SQL queries work in the terminal (like [http://labs.geometa.info/postgisterminal/?xapi=node%5Btourism%3Dzoo%5D this]) but mostly also on any PostGIS database created with osm2pgsql.		Here are some examples queries for the [[PostGIS Terminal\|OSM SQL Terminal]]. All SQL queries work in the terminal (like [http://labs.geometa.info/postgisterminal/?xapi=node%5Btourism%3Dzoo%5D this]) but mostly also on any PostGIS database created with osm2pgsql.

	See also [[PostGIS - Tipps und Tricks]].		See also [[PostGIS - Tipps und Tricks]].

Stefan: /* Longest Station Streets in Switzerland */

2023-05-02T12:17:04Z

Longest Station Streets in Switzerland

← Nächstältere Version		Version vom 2. Mai 2023, 12:17 Uhr
Zeile 31:		Zeile 31:
	count(*)::int as segments,		count(*)::int as segments,
	round(st_length(st_union(geom)))::int as len,		round(st_length(st_union(geom)))::int as len,
	~~--round(st_length(min(geom)))::int as len, -- Halber Workaround wegen aktuellem ST_Union Error~~
	st_transform(st_union(geom),4326) as geom		st_transform(st_union(geom),4326) as geom
	~~--st_transform(min(geom),4326) as geom -- Halber Workaround wegen aktuellem ST_Union Error~~
	from touched_linestrings		from touched_linestrings
	group by osm_id_a		group by osm_id_a

Stefan: /* Fun with SQL Queries */

2023-04-30T21:29:26Z

Fun with SQL Queries

← Nächstältere Version		Version vom 30. April 2023, 21:29 Uhr
Zeile 8:		Zeile 8:

	Die längsten Bahnhofstrassen der Schweiz (Variante OSM SQL Terminal mit geom im CRS EPSG:4326).		Die längsten Bahnhofstrassen der Schweiz (Variante OSM SQL Terminal mit geom im CRS EPSG:4326).

	First in temporary CTE table 'touched_linestrings' all adjacent ways/lines are joined with ST_Touches (including the 'first' line itself with "or a.osm_id = b.osm_id"). Then the touched_linestrings from are grouped in the actual query and joined with ST_Union. Note that osm_id_a is a randomly chosen way.		First in temporary CTE table 'touched_linestrings' all adjacent ways/lines are joined with ST_Touches (including the 'first' line itself with "or a.osm_id = b.osm_id"). Then the touched_linestrings from are grouped in the actual query and joined with ST_Union. Note that osm_id_a is a randomly chosen way.

Zeile 36:		Zeile 37:
	group by osm_id_a		group by osm_id_a
	order by len desc;		order by len desc;


	name \| osm_id_a \| osm_id_arr \| segments \| len		name \| osm_id_a \| osm_id_arr \| segments \| len

Stefan: /* Longest Station Streets in Switzerland */

2023-04-30T21:28:53Z

Longest Station Streets in Switzerland

← Nächstältere Version		Version vom 30. April 2023, 21:28 Uhr
Zeile 8:		Zeile 8:

	Die längsten Bahnhofstrassen der Schweiz (Variante OSM SQL Terminal mit geom im CRS EPSG:4326).		Die längsten Bahnhofstrassen der Schweiz (Variante OSM SQL Terminal mit geom im CRS EPSG:4326).
	~~Zuerst werden~~ in ~~der~~ CTE ~~mit~~ ST_Touches ~~alle benachbarten Lines ge-joined~~ (~~inkl. die~~ '~~erste~~' ~~Linie selber mit~~ "or a.osm_id = b.osm_id")~~, dann werden sie~~ in ~~der eigentlichen Query gruppiert und mit~~ ST_Union ~~zusammengesetzt~~.		First in temporary CTE table 'touched_linestrings' all adjacent ways/lines are joined with ST_Touches (including the 'first' line itself with "or a.osm_id = b.osm_id"). Then the touched_linestrings from are grouped in the actual query and joined with ST_Union. Note that osm_id_a is a randomly chosen way.

	<pre>		<pre>
Zeile 36:		Zeile 36:
	group by osm_id_a		group by osm_id_a
	order by len desc;		order by len desc;


			name \| osm_id_a \| osm_id_arr \| segments \| len
			----------------+-----------+---------------------------------+----------+------
			Rue de la Gare \| 515809858 \| {121934832,169063995,515809858} \| 3 \| 3749
			Rue de la Gare \| 169063995 \| {169063995,285235569,515809858} \| 3 \| 3302
			Bahnhofstrasse \| 241375882 \| {241375882,648836640,648836641} \| 3 \| 3269
			Bahnhofstrasse \| 170266721 \| {103940942,170266721,170266722} \| 3 \| 3061
			Rue de la Gare \| 255394066 \| {255394066} \| 1 \| 3042
			Rue de la Gare \| 121934832 \| {121934832,515809858} \| 2 \| 2946
			...
	</pre>		</pre>

Stefan: /* Longest Station Streets in Switzerland */

2023-04-30T21:21:17Z

Longest Station Streets in Switzerland

← Nächstältere Version		Version vom 30. April 2023, 21:21 Uhr
Zeile 8:		Zeile 8:

	Die längsten Bahnhofstrassen der Schweiz (Variante OSM SQL Terminal mit geom im CRS EPSG:4326).		Die längsten Bahnhofstrassen der Schweiz (Variante OSM SQL Terminal mit geom im CRS EPSG:4326).
	Zuerst werden in der CTE mit ST_Touches alle benachbarten Lines ge-joined (inkl. die ~~Line~~ selber mit "or a.osm_id = b.osm_id"), dann werden sie in der eigentlichen Query gruppiert und mit ST_Union zusammengesetzt.		Zuerst werden in der CTE mit ST_Touches alle benachbarten Lines ge-joined (inkl. die 'erste' Linie selber mit "or a.osm_id = b.osm_id"), dann werden sie in der eigentlichen Query gruppiert und mit ST_Union zusammengesetzt.

	<pre>		<pre>
Zeile 37:		Zeile 37:
	order by len desc;		order by len desc;
	</pre>		</pre>


	=== Checking if a road is mapped twice ===		=== Checking if a road is mapped twice ===

Stefan: /* Fun with SQL Queries */ Longest Station Streets in Switzerland

2023-04-30T21:20:30Z

Fun with SQL Queries: Longest Station Streets in Switzerland

← Nächstältere Version		Version vom 30. April 2023, 21:20 Uhr
Zeile 4:		Zeile 4:

	== Fun with SQL Queries ==		== Fun with SQL Queries ==

			=== Longest Station Streets in Switzerland ===

			Die längsten Bahnhofstrassen der Schweiz (Variante OSM SQL Terminal mit geom im CRS EPSG:4326).
			Zuerst werden in der CTE mit ST_Touches alle benachbarten Lines ge-joined (inkl. die Line selber mit "or a.osm_id = b.osm_id"), dann werden sie in der eigentlichen Query gruppiert und mit ST_Union zusammengesetzt.

			<pre>
			with touched_linestrings as (
			select
			a.name,
			a.osm_id as osm_id_a,
			b.osm_id as osm_id_b,
			st_transform(b.geom, 3857) as geom
			from osm_line as a
			join osm_line as b
			on (st_touches(st_transform(a.geom, 3857), st_transform(b.geom, 3857)) or a.osm_id = b.osm_id)
			where a.name in ('Bahnhofstrasse','Rue de la Gare','Via Stazione','Via Staziun')
			and b.name in ('Bahnhofstrasse','Rue de la Gare','Via Stazione','Via Staziun')
			order by osm_id_a, osm_id_b
			)
			select
			min(name) as name,
			osm_id_a,
			array_agg(osm_id_b) as osm_id_arr,
			count(*)::int as segments,
			round(st_length(st_union(geom)))::int as len,
			--round(st_length(min(geom)))::int as len, -- Halber Workaround wegen aktuellem ST_Union Error
			st_transform(st_union(geom),4326) as geom
			--st_transform(min(geom),4326) as geom -- Halber Workaround wegen aktuellem ST_Union Error
			from touched_linestrings
			group by osm_id_a
			order by len desc;
			</pre>


	=== Checking if a road is mapped twice ===		=== Checking if a road is mapped twice ===

Stefan: /* Nearest Neighbor Syntax */

2022-02-15T22:36:40Z

Nearest Neighbor Syntax

← Nächstältere Version		Version vom 15. Februar 2022, 22:36 Uhr
Zeile 428:		Zeile 428:

	=== Nearest Neighbor Syntax ===		=== Nearest Neighbor Syntax ===

			Es ist in PostGIS möglich, eine indizierte KNN-Suche zu erhalten, die nur eine einzige verwendet für die Abstands-Berechnung und für das ORDER-BY.

	Aus: http://blog.cleverelephant.ca/2021/12/knn-syntax.html		Aus: http://blog.cleverelephant.ca/2021/12/knn-syntax.html