PostgreSQL | Common statement Records

First, the basic operation
Postgresql backup: pg_dump -U postgres -d gisportal -F c -f i:\db2.dump
Postgresql restore: pg_restore -U postgres -d gisportal i:\db2.dump
                          Pg_restore -U postgres -d gisportal e:\postgres-20150420.backup
 
Back up a single table: pg_dump -U postgres -F c -b -v -f i:\test.bak -t sqs.test gisportal
Back up the entire database (non-mode): pg_dump -U postgres -F c -b -v -f i:\test.bak -t sqs.test gisportal
Back up the schema under the database: pg_dump -U postgres -F c -b -v -f i:\test.bak -n sqs gisportal
Restore a single table: pg_restore -U postgres -W -d gisportal -v "d:\poi.bak"
 
To execute the statement, first execute: cd postgresql installation directory bin
Pg_dump -U postgres -F c -f i:\gisportal.dump gisportal (back up the entire database, backing up a single schema table for another way)
 
Pg_dump -h localhost -U postgres (username) database name (default with the same user name) -t table (table name) >/data/dum.sql
Pg_dump -h localhost -U postgres gisportal -t provincial _region >i:\pros.sql
 
Insert the entire column of another table: insert into sqs.sqs_role(username) select username from sqs.sqs_user
 
Insert the date of the day date: update sqs.sqs_shopmall set version=current_date;
 
Intercept character: update sqs.sqs_poi2 set prcode=substring(prcode,1,2) (index starts at 1, 1 is reserved)
 
Empty string processing:
Update property set memorial_no = btrim(memorial_no, ‘ ‘) where memorial_no like ‘ %‘
Update property set memorial_no = 2 where memorial_no= btrim(memorial_no, ‘ ‘)
 
Multi-condition fuzzy query:
SELECT * FROM table WHERE column LIKE ‘Text%‘ OR column LIKE ‘Link%’ OR column LIKE ‘Hello%’ OR column LIKE ‘%World%’
 
Second, space operations
Calculate the area (calculate the area according to the geometry) update sqs.sqs_town set totarea=st_area(geography(geom))/1000000
Calculate the distance:
Select name, st_astext(geom) as geomtext, st_distance(Geography(ST_PointFromText('POINT(121.43543 31.2399)', 4326)), Geography(geom)) as distance from cz_basedata_other_l where ST_Intersects(st_buffer(geography(ST_PointFromText('POINT(121.43543 31.2399) ', 4326)), 3000), geom) and dlvydate> date '2010-01-01'
 
Cross calculation:
Select st_astext(geom) as GeomText ,"County" ,jdlon as Centerx , jdlat as Centery ,( people::DECIMAL/area::DECIMAL*1000000.0 )::INT as Density from vtown
Where ST_Intersects(st_buffer( geography( ST_PointFromText(‘POINT(121.492498 31.229649)‘, 4326)), 4000), geom)
 
The array is converted to geometry (also applicable to Baidu arrays):
Select name from sqs.sqs_pipeline where ST_Intersects(ST_GeomFromText('POLYGON((121.550391 31.048365,121.845897 31.063214,121.734364 30.859081,121.580286 30.940391,121.550391 31.048365)) ‘, 4326), geom)
Select name from sqs.sqs_pipeline where ST_Within(geom,ST_PolygonFromText(‘POLYGON((121.550391 31.048365,121.845897 31.063214,121.734364 30.859081,121.580286 30.940391,121.550391 31.048365)) ‘, 4326)) and status=1
 
Calculate x, y:update sqs.sqs_poi set lng=st_x(geom), lat=st_y(geom) according to geom
 
Get the point x, y coordinates of the point geometry:
Select *, st_x(geom), st_y(geom) from sqs.sqs_store where status=1 and username='admin' and ST_Within(geom,ST_GeomFromText('POLYGON((121.289092 31.38394,121.333648 31.275857,121.462429 31.333862,121.459267 31.38172,121.379928 31.405393,121.289092 31.38394))',4326))
 
Point to geometry:
Update sqs.sqs_pipeline set pipelineid=‘pp00000008', name=‘admin‘, geom=ST_PointFromText(‘POINT(121.845897 31.063214)‘, 4326),stat=1=1;
Update sqs.yichang set geom=ST_PointFromText(‘POINT(‘||ng||‘ ‘||lat||‘)‘, 4326);
Update sqs.sqs_pipeline_wfztest set geomta500=ST_PolygonFromText(st_astext(st_buffer(geography(ST_PointFromText('POINT('|_st_x(geom)||' '||st_y(geom)||')',4326)), 500 )) , 4326);
 
 
String sql = "select st_Area(Geography(st_intersection(a.geom,b.geom)))/st_Area(geography(a.geom)) as per100 "
                + "from sqs.sqs_city a, "+tablename +" b "
                + "where st_Intersects(a.geom,b.geom) and b."+id+"=‘"+ storeid+ "‘ and b.username=‘"+username +"‘ and b.status=1” ;
 
String sql = "select ceil(totarea) as totarea, st_asgeojson(b.geom) as geometry, ceil(st_distance(Geography(ST_PointFromText('POINT(" + x + " " + y +")',4326)),Geography (b.geom))) as distance from sqs.sqs_newhouse b "
                + "where ST_Intersects(ST_PolygonFromText('POLYGON(("+p+"))',4326), b.geom) and "
                +"dlvydate > ‘2010-1-1‘
                + "order by tothh desc " + "limit 10";
 
Create a new table and copy the table structure and data from another existing table:
Create table town_20150525 as select * from vtown_20150526
 
Add a field (self-growth sequence):
Alter table cz_users add column gender character varying(50) unique
Alter table cz_udatarange add column id serial
Unique unique value timestamp without time zone DEFAULT now(), time yyyy-MM-dd hh:mm:ss
 
Add column space space separately: select addgeometrycolumn(‘sqs‘, ‘sqs_province‘, ‘geom‘, 4326, ‘MULTIPOLYGON’, 2, TRUE)
 
The first few data: LIMIT num OFFSET startNum can achieve a specified number of starts to take a few
 
Replace replace:update cz_basedata_test2 set cnty=replace(cnty,city,‘‘)//cannot be null, otherwise the entire column will become null
 
Pipeid and userid assignment methods in tables such as sqs.sqs_pineline:
Update sqs.sqs_pipeline set userid='24c19d21-f519-4a49-869d-6455787537a3',status=1,pipelineid='pp'||to_char(id, 'FM00000000'),name='pp'||to_char(id, 'FM00000000') where userid is null
Update sqs.sqs_competitor set storeid=‘cc‘||to_char(id,‘FM00000000‘),userid=‘e27accfb-e5ff-409c-830f-4a67387237ff‘ where userid is null
Update sqs.sqs_store set userid=‘55bf68a3-adf2-49d6-89f8-aa05f59cbe01', username=‘xxx‘,stat=1, storeid=to_char(id, ‘FM00000000‘) where userid is null
 
Sql statement + sign: update 

Sqs.sqs_streets set geom=ST_PointFromText(‘POINT(‘||lng||‘ ‘||lat||‘)‘, 4326)