This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
gis:postgis [2022/05/06 11:47] – [Buffer point using SQL] Jan Forman | gis:postgis [2022/05/06 11:50] (current) – [Buffer point using SQL] Jan Forman | ||
---|---|---|---|
Line 1: | Line 1: | ||
+ | ====== Add geometry with X,Y columns ====== | ||
+ | < | ||
+ | ALTER TABLE your_table ADD COLUMN geom geometry(Point, | ||
+ | UPDATE your_table SET geom = ST_SetSRID(ST_MakePoint(longitude, | ||
+ | </ | ||
+ | |||
+ | ====== Auto regenerate X,Y while inserting ====== | ||
+ | PostgreSQL 12+ | ||
+ | |||
+ | < | ||
+ | ALTER TABLE test ADD COLUMN geom geometry(Point, | ||
+ | </ | ||
+ | |||
+ | < | ||
+ | CREATE TABLE table( | ||
+ | ... | ||
+ | geom geometry(Point, | ||
+ | ); | ||
+ | </ | ||
+ | |||
+ | ====== 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, | ||
+ | </ |