User Tools

Site Tools


gis:postgis

Add geometry with X,Y columns

ALTER TABLE your_table ADD COLUMN geom geometry(Point, 4326);
UPDATE your_table SET geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);

Auto regenerate X,Y while inserting

PostgreSQL 12+

ALTER TABLE test ADD COLUMN geom geometry(Point,5514) GENERATED ALWAYS AS (ST_SetSRID(ST_MakePoint(jtsk_x, jtsk_y), 5514)) STORED
CREATE TABLE table(
    ...
    geom geometry(Point,4326) GENERATED ALWAYS AS (ST_SetSRID(ST_MakePoint(longitude, latitude), 4326)) STORED
);

Read Only schema

GRANT SELECT ON ALL TABLES IN SCHEMA public TO username;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO username;

Buffer point using SQL

1000 meters - keep SJTSK in meters then convert it to WGS84

SELECT id,ST_Transform(ST_Buffer(ST_Transform(shape,5514),1000),4326) FROM table
gis/postgis.txt · Last modified: 2022/05/06 11:50 by Jan Forman