PostGIS - Tipps und Tricks - Versionsgeschichte

Stefan: /* Find and add nearest hydrant to a parcel */

2025-04-07T09:33:29Z

Find and add nearest hydrant to a parcel

← Nächstältere Version		Version vom 7. April 2025, 09:33 Uhr
Zeile 623:		Zeile 623:
	Given parcels (or other kind of (point?) feature class) and hydrants (or other kind of (point?) feature class) as input (e.g. imported from external files), find and add closest hydrant to parcel. Output: All parcel attributes incl. geometry, hydrant_id and distance from hydrant to parcel.		Given parcels (or other kind of (point?) feature class) and hydrants (or other kind of (point?) feature class) as input (e.g. imported from external files), find and add closest hydrant to parcel. Output: All parcel attributes incl. geometry, hydrant_id and distance from hydrant to parcel.

	This is an example of a lateral join driving a nearest-neighbor distance calculation. Needs PostgreSQL 9.5 / PostGIS 2.x. (Credits: https://twitter.com/pwramsey/status/686980049967443968 )		This is an example of a lateral join driving a nearest-neighbor distance calculation. Needs PostgreSQL 9.5 / PostGIS 2.x. (Credits: https://twitter.com/pwramsey/status/686980049967443968 and http://blog.cleverelephant.ca/2021/12/knn-syntax.html )

	SELECT		SELECT

2022-04-26T20:30:24Z

PostGIS optimieren

← Nächstältere Version		Version vom 26. April 2022, 20:30 Uhr
Zeile 452:		Zeile 452:
	Version:		Version:
	* 1.4 and GEOS 3.1 will bring prepared geometries which will make things faster.		* 1.4 and GEOS 3.1 will bring prepared geometries which will make things faster.

			Fast map intersection by spatial partioning https://github.com/gbb/fast_map_intersection

	Reorder on disk:		Reorder on disk:

2022-02-15T22:31:01Z

Clip all features from within a BBOX

2020-10-18T10:34:20Z

Clip all features from within a BBOX

← Nächstältere Version		Version vom 18. Oktober 2020, 10:34 Uhr
Zeile 898:		Zeile 898:
	select * from points		select * from points


			=== Safe ST_UNION ===

			=> https://trac.osgeo.org/postgis/attachment/ticket/4182/fun_geo_safe_union4.sql

	[[Kategorie:PostGIS]] [[Kategorie:HowTo]]		[[Kategorie:PostGIS]] [[Kategorie:HowTo]]

2020-09-22T20:00:38Z

Find and add nearest hydrant to a parcel

← Nächstältere Version		Version vom 22. September 2020, 20:00 Uhr
Zeile 642:		Zeile 642:
	-- per parcel:		-- per parcel:
	CROSS JOIN LATERAL		CROSS JOIN LATERAL
	-- The guts of the query, actually do a KNN query for each		-- The guts of the query, actually do a KNN query for each
	-- candidate parcel on the other side of the join:		-- candidate parcel on the other side of the join:
	(SELECT id, geom		(SELECT id, geom
	FROM hydrants_imported		FROM hydrants_imported
	ORDER BY parcels.geom <-> geom -- take here cartesian CRS like webmercator		ORDER BY parcels.geom <-> geom -- take here cartesian CRS like webmercator
	LIMIT 1) AS hydrants;		LIMIT 1) AS hydrants
			ON TRUE;

	=== PostGIS views ===		=== PostGIS views ===

2020-05-13T18:18:35Z

Create Polygon given Bounding Box (BBox) Coordinates

← Nächstältere Version		Version vom 13. Mai 2020, 18:18 Uhr
Zeile 75:		Zeile 75:
	SELECT ST_AsText(ST_Transform(ST_SetSRID('BOX(8.795611 46.872886, 9.674135 47.675419)'::box2d, 4326), 3857)) AS geom, '#' AS label		SELECT ST_AsText(ST_Transform(ST_SetSRID('BOX(8.795611 46.872886, 9.674135 47.675419)'::box2d, 4326), 3857)) AS geom, '#' AS label
	SELECT ST_AsText(ST_Transform(ST_SetSRID('BOX3D(8.795611 46.872886, 9.674135 47.675419)'::box3d, 4326), 3857)) AS geom, '#' AS label		SELECT ST_AsText(ST_Transform(ST_SetSRID('BOX3D(8.795611 46.872886, 9.674135 47.675419)'::box3d, 4326), 3857)) AS geom, '#' AS label
			SELECT ST_AsText(ST_SetSRID(ST_Extent(way), 4326)) AS geom FROM osm_point

	== Curved Geometries ==		== Curved Geometries ==

2019-06-16T18:22:17Z

@@ Zeile 76: / Zeile 76: @@
    SELECT ST_AsText(ST_Transform(ST_SetSRID('BOX3D(8.795611 46.872886, 9.674135 47.675419)'::box3d, 4326), 3857)) AS geom, '#' AS label
-== Curved geometries ==
+== Curved Geometries ==
 Curved geometries in PostGIS with notes for QGIS (and OGR).
@@ Zeile 124: / Zeile 124: @@
 * ST_CurveToLine(geometry curveGeom, ﬂoat tolerance, integer tolerance_type, integer ﬂags) — Converts a CIRCULARSTRING/CURVEPOLYGON/MULTISURFACE to a LINESTRING/POLYGON/MULTIPOLYGON
- % psql -A -t -d gisdb -c "SELECT askmldoc('MyTable', 'A Comment...', the_geom) FROM mytable WHERE gid=1;" -o mykmlfile.kml
+== PostGIS-Daten exportieren ==
-=== Export a table with a geometry attribute to GeoJSON  ===
+=== Export a table with a geometry attribute to GeoJSON ===
 Sources:
@@ Zeile 245: / Zeile 197: @@
    % psql -U <username> -d <dbname> -f togeojson.sql -t -o out.geojson
 Ev. beautify output using geojson.io or http://geojsonlint.com/
 === PostGIS und Google Earth ===

2019-06-16T18:19:49Z

Export a PostGIS table to GeoJSON

← Nächstältere Version		Version vom 16. Juni 2019, 18:19 Uhr
Zeile 175:		Zeile 175:
	% psql -A -t -d gisdb -c "SELECT askmldoc('MyTable', 'A Comment...', the_geom) FROM mytable WHERE gid=1;" -o mykmlfile.kml		% psql -A -t -d gisdb -c "SELECT askmldoc('MyTable', 'A Comment...', the_geom) FROM mytable WHERE gid=1;" -o mykmlfile.kml

	=== Export a ~~PostGIS~~ table to GeoJSON ===		=== Export a table with a geometry attribute to GeoJSON ===

	Sources:		Sources:

2019-06-16T18:19:00Z

Export a PostGIS table to KML

← Nächstältere Version		Version vom 16. Juni 2019, 18:19 Uhr
Zeile 138:		Zeile 138:
	** [[ArcGIS]] Server		** [[ArcGIS]] Server

	=== Export a ~~PostGIS~~ table to KML ===		=== Export a table with a geometry attribute to KML ===

	PostGIS kennt die Funktion ST_AsKML(), die eine Geometrie KML-konform ausgibt. Allerdings werden nur diejenigen KML-Elemente der Geometrie selbst ausgegeben. Kopf- und Fusszeile des KML-Dokuments muss man selber hinzufügen. Dazu erzeugen wir nun eine Funktion askmldoc, die ein gültiges KML-Dokument ausgibt:		PostGIS kennt die Funktion ST_AsKML(), die eine Geometrie KML-konform ausgibt. Allerdings werden nur diejenigen KML-Elemente der Geometrie selbst ausgegeben. Kopf- und Fusszeile des KML-Dokuments muss man selber hinzufügen. Dazu erzeugen wir nun eine Funktion askmldoc, die ein gültiges KML-Dokument ausgibt:

2019-05-05T23:59:09Z

PostGIS views

← Nächstältere Version		Version vom 5. Mai 2019, 23:59 Uhr
Zeile 656:		Zeile 656:

	A3. Grab the key from the underlying geometry table, & use it for a key column in your aggregate view, eg. "create view v1 as select min(key) as key" ...		A3. Grab the key from the underlying geometry table, & use it for a key column in your aggregate view, eg. "create view v1 as select min(key) as key" ...

			A4. Use window function "SELECT row_number() OVER() As generated_id, ... FROM ..."

	=== Standard direction for polylines ===		=== Standard direction for polylines ===