PostGIS Quick Help

Install PostGIS

sudo apt update
sudo apt install postgis postgresql-13-postgis-3

Login:

sudo -i -u postgres

psql [-h localhost] -d <database> -U <userName>

1. Create User and Schema

Login (see above)

Create user:

createuser postgis_user

Create password:

"ALTER ROLE postgis_user PASSWORD 'ВАШ_ПАРОЛЬ'"

Create db:

createdb postgis_db -O postgis_user

Connect:

psql -d postgis_db

Create extension PostGIS:

CREATE EXTENSION postgis;

2. Create Geometry from CSV

CREATE TABLE test_csv
(
  x float8,
  y float8,
  name varchar(100),
  the_geom geometry(POINT, 4326)
);

CSV content:

x,y,name
22.2093,44.59183,test1
26.8141224397,41.8399243276,test2
26.8141224397,41.8399243276,test3

Add data:

COPY test_csv (x, y, name) 
FROM '/tmp/test.csv'
WITH CSV HEADER;

Add geometry:

UPDATE test_csv SET the_geom = ST_SetSRID(ST_MakePoint(x,y), 4326);

OR

UPDATE test_csv SET the_geom = ST_PointFromText('POINT(' || x || ' ' || y || ')', 4326);

Create index:

CREATE INDEX idx_test_geom ON test_csv USING GIST (the_geom);

Show some rows:

SELECT place, ST_AsText(the_geom) AS wkt_geom 
FROM test_csv 
ORDER BY name
LIMIT 2;

See also

https://computingforgeeks.com/how-to-install-postgis-on-debian/

https://stackoverflow.com/questions/18580066/how-to-allow-remote-access-to-postgresql-database