PosgreSQL

PGadmin 88
SSL 4431
user postgres
pass admin

all open remote akses
host all all 0.0.0.0/0 md5

Alter Column
===================
alter table the_table alter column the_column type bigint using the_column::bigint

============================================================

Backup: $ pg_dump -U {user-name} {source_db} -f {dumpfilename.sql}

Restore: $ psql -U {user-name} -d {desintation_db}-f {dumpfilename.sql}

=====================

psql -h hostname -d databasename -U username -f file.sql -p port

createdb -T temlate nyc

psql -f nyc_buildings.sql nyc

pg_dump -U user database > archivename.sql
pg_dump -D -a -t zones_seq -t interway -t “<regex>” -f /tmp/zones_seq.sql <DBNAME>

restore
psql -U user database < location/archivr.sql

shp to table============
shp2pgsql is a commandline tool to import ESRI shapefiles to the database. Under Unix, you can use the following command for importing a new PostGIS table:
========================
shp2pgsql -s <SRID> -c -D -I <path to shapefile> <schema>.<table> | \
psql -d <databasename> -h <hostname> -U <username>

Under Windows, you have to perform the import process in two steps:
=====================================
shp2pgsql -s <SRID> -c -D -I <path to shapefile> <schema>.<table> > import.sql
psql psql -d <databasename> -h <hostname> -U <username> -f import.sql

sql postgresql command line
-====================-
psql -U username mydatabase

table to shp
===========
pgsql2shp is a commandline tool to export PostGIS Tables, Views or SQL select queries. To do this under Unix:

pgsql2shp -f <path to new shapefile> -g <geometry column name> \
-h <hostname> -U <username> <databasename> <table | view>

To export the data using a query:
pgsql2shp -f <path to new shapefile> -g <geometry column name> \
-h <hostname> -U <username> “<query>”

shp2pgsql -I -s <SRID> <PATH/TO/SHAPEFILE> <SCHEMA>.<DBTABLE> | psql -d <DATABASE>

alter srid / Update

ALTER TABLE my_table
ALTER COLUMN geom TYPE geometry(Point,4326) USING ST_Transform(geom,4326);

ALTER TABLE roads
ALTER COLUMN geom TYPE geometry(MULTILINESTRING, 4326)
USING ST_SetSRID(geom,4326);

select ST_Transform(ST_SetSRID(geom_line, 4326), 32650) from my_Table;

ALTER TABLE jaringan_jalan_kota_baubau_2018_2
ALTER COLUMN geom TYPE geometry(MultiLineString,4326)
USING ST_Transform(ST_SetSRID(geom,32751),4326);

======================
Convert to csv
=======================

COPY (SELECT * FROM view1) TO ‘/var/lib/postgres/myfile1.csv’;

— Add a spatial column to the table
SELECT AddGeometryColumn (‘my_schema’,’my_spatial_table’,’geom’,4326,’POINT’,2);

— Add a point using the old constraint based behavior
SELECT AddGeometryColumn (‘my_schema’,’my_spatial_table’,’geom_c’,4326,’POINT’,2, false);

— Add a curvepolygon using old constraint behavior
SELECT AddGeometryColumn (‘my_schema’,’my_spatial_table’,’geomcp_c’,4326,’CURVEPOLYGON’,2, false);

— Describe the table again reveals the addition of a new geometry columns.
\d my_schema.my_spatial_table

==DropGeometryColumn — Removes a geometry column from a spatial table=====.

SELECT DropGeometryColumn (‘my_schema’,’my_spatial_table’,’geom’);

POINT(0 0)

LINESTRING(0 0,1 1,1 2)

POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))

MULTIPOINT((0 0),(1 2))

MULTILINESTRING((0 0,1 1,1 2),(2 3,3 2,5 4))

MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0),(1 1,2 1,2 2,1 2,1 1)), ((-1 -1,-1 -2,-2 -2,-2 -1,-1 -1)))

GEOMETRYCOLLECTION(POINT(2 3),

LINESTRING(2 3,3 4))

==================Get Size Database Postgesql===========================
select t1.datname AS db_name,

pg_size_pretty(pg_database_size(t1.datname)) as db_size

from pg_database t1

order by pg_database_size(t1.datname) desc;

— Database Size

SELECT pg_size_pretty(pg_database_size(‘Database Name’));

— Table Size

SELECT pg_size_pretty(pg_relation_size(‘table_name’));

//Update from another table
UPDATE B SET
COLUMN1 = A.COLUMN1,
COLUMN2 = A.COLUMN2,
COLUMN3 = A.COLUMN3
FROM A
WHERE A.ID = B.ID