User Tools

Site Tools


gis:postgis

Differences

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

Link to this comparison view

Both sides previous revisionPrevious revision
gis:postgis [2022/05/06 11:49] – [Buffer point using SQL] Jan Formangis:postgis [2022/05/06 11:50] (current) – [Buffer point using SQL] Jan Forman
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 ======
 +1000 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>
gis/postgis.txt · Last modified: 2022/05/06 11:50 by Jan Forman