SQL scripts

! SQL scripts

SQL scripts

create_db.sql

# postgresql
create database dbname;
create user dbuser with password 'password';
grant all privileges on database dbname to dbuser;

#mysql
create database dbname;
create user 'dbuser'@'%' identified by 'password';
grant all privileges on dbname.* to 'dbuser'@'%';
revoke all privileges on dbname.* to 'dbuser'@'%';
grant all privileges on *.* from 'dbuser'@'%'; # allow database creation
revoke all privileges on *.* from 'dbuser'@'%';

mysql-8.sql

-- allow login with root
-- first login with: sudo mysql
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'insert_password';

-- check database size
-- https://stackoverflow.com/questions/1733507/how-to-get-size-of-mysql-database
SELECT table_schema "DB Name", ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"  FROM information_schema.tables GROUP BY table_schema; 

postgres.sql

-- change password
\password postgres;

-- create table with geometry column (PostGIS)
CREATE TABLE locations (
    id varchar(255) NOT NULL,
    coords geometry,
    CONSTRAINT locations_pkey PRIMARY KEY (id)
)
CREATE INDEX locations_coords_index ON locations USING GIST(coords)

-- find 1 nearest coords from lat=-6.125215 lon=106.8362474
SELECT id, st_x(coords) as lon , st_y(coords) as lat
FROM locations ORDER BY coords <-> 'POINT(106.8362474 -6.125215)'::geometry ASC LIMIT 1;

-- find locations within 5000 m radius from lat=-6.1753871 lon=106.8249641
SELECT id FROM locations
WHERE ST_DistanceSphere(coords, 'POINT(106.8249641 -6.1753871)') < 5000

-- find locations withing box boundary
-- ST_MakeEnvelope(minLon, minLat, maxLon, maxLat)
select id from locations
where coords && ST_MakeEnvelope(106.8177, -6.1819, 106.8379, -6.1694);