Differences

This shows you the differences between two versions of the page.

--- gis:postgis [2022/05/06 11:49]
Jan Forman [Buffer point using SQL]
+++ gis:postgis [2022/05/06 11:50] (current)
Jan Forman [Buffer point using SQL]
@@ Line 1: / Line 1: @@
+====== Add geometry with X,Y columns ======
+<code>
+ALTER TABLE your_table ADD COLUMN geom geometry(Point, 4326);
+UPDATE your_table SET geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);
+</code>
+====== Auto regenerate X,Y while inserting ======
+PostgreSQL 12+
+<code>
+ALTER TABLE test ADD COLUMN geom geometry(Point,5514) GENERATED ALWAYS AS (ST_SetSRID(ST_MakePoint(jtsk_x, jtsk_y), 5514)) STORED
+</code>
+<code>
+CREATE TABLE table(
+    ...
+    geom geometry(Point,4326) GENERATED ALWAYS AS (ST_SetSRID(ST_MakePoint(longitude, latitude), 4326)) STORED
+);
+</code>
+====== Read Only schema ======
+<code>
+GRANT SELECT ON ALL TABLES IN SCHEMA public TO username;
+ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO username;
+</code>
+====== Buffer point using SQL ======
+meters - keep SJTSK in meters then convert it to WGS84
+<code>
+SELECT id,ST_Transform(ST_Buffer(ST_Transform(shape,5514),1000),4326) FROM table
+</code>

wiki.janforman.com