postgres_study - wnh5/myStudy GitHub Wiki

PostgreSQL 的官方下载地址为： http://58.58.25.191:8079/doc/html/9.3.1_zh/

　　ftp://ftp.postgresql.org/pub/v7.1.3/postgresql-7.1.3.tar.gz 　　http://www.postgresql.org/

　　如果下载最新的开发版本，你需要下载并安装 flex（版本号大于 2.5.4）以及 bison （版本号大于 1.28）

　　设计人员为了安全考虑，PostgreSQL 不能以 root 用户运行，所以必须建立对应的用户和组。

　　# useradd postgre （自动建立 postgre 组）

　　安装的过程并不复杂和其他源码版本的安装方法类似：　　解压到 /usr/local/src：　　# tar xvfz postgresql-7.1.3.tar.gz 　　# cd postgresql-7.1.3 　　# ./configure --prefix=/usr/local/pgsql 　　# make 　　# make install 　　# chown -R postgre.postgre /usr/local/pgsql

　　这样安装完毕后，并不是万事大吉了，还有一些收尾工作要做：　　# vi ~postgre/.bash_profile 　　添加：

　　PGLIB=/usr/local/pgsql/lib 　　PGDATA=$HOME/data 　　PATH=$PATH:/usr/local/pgsql/bin 　　MANPATH=$MANPATH:/usr/local/pgsql/man 　　export PGLIB PGDATA PATH MANPATH

　　以 postgres 用户登录，　　# su - postgre 　　建立数据库目录：　　$ mkdir data

　　启动数据库引擎：

　　$ initdb 　　[postgre@www postgre]$ initdb 　　This database system will be initialized with username "postgre". 　　This user will own all the data files and must also own the server process.

　　Fixing permissions on pre-existing data directory /home/postgre/data 　　Creating database system directory /home/postgre/data/base 　　Creating database XLOG directory /home/postgre/data/pg_xlog 　　Creating template database in /home/postgre/data/base/template1 　　Creating global relations in /home/postgre/data/base 　　Adding template1 database to pg_database

　　Creating view pg_user. 　　Creating view pg_rules. 　　Creating view pg_views. 　　Creating view pg_tables. 　　Creating view pg_indexes. 　　Loading pg_description. 　　Vacuuming database.

　　Success. You can now start the database server using:

　　/usr/local/pgsql/bin/postmaster -D /home/postgre/data 　　or 　　/usr/local/pgsql/bin/pg_ctl -D /home/postgre/data start 　　$ postmaster -i -D ~/data & 　　[1] 22603 　　[postgre@www postgre]$ DEBUG: Data Base System is starting up at Thu Jan 31 02:00:44 2002 　　DEBUG: Data Base System was shut down at Thu Jan 31 01:57:58 2002 　　DEBUG: Data Base System is in production state at Thu Jan 31 02:00:44 2002

　　这样 PostgreSQL 使用位于 /usr/local/pgsql/data 的数据库，允许 Internet 用户的连接（ -i ），并在后台运行。

　　建立数据库　　$createdb mydb 　　PostgreSQL 会返回 “ CREATED DATABASE”的信息，表明数据库建立完成。　　$psql mydb 　　进入交互 psql 工具，建立表：

　　CREATE TABLE mytable ( 　　id varchar(20), 　　name varchar(30));

　　建立完成后，会得到一条 “CREATED” 的信息，表示建立成功。现在插入一条数据：

　　INSERT INTO mytable values('Author', 'Xu Yongjiu');

　　psql 返回 INSERT 18732 1，查询插入是否成功：

　　SELECT * FROM MYTABLE;

　　退出 psql ，用 \q 命令。

geos http://trac.osgeo.org/geos ./configure --prefix=/usr/local/geos make make install chown -R postgre.postgre /usr/local/geos

proj https://trac.osgeo.org/proj/ ./configure --prefix=/usr/local/proj4
make make install chown -R postgre.postgre /usr/local/proj4

GDAL http://gdal.org/ ./configure --prefix=/usr/local/gdal --with-pg=/usr/local/pgsql/bin/pg_config

cgal git clone https://github.com/CGAL/cgal

--postgis

psql -U postgres -d postgres -c "CREATE EXTENSION postgis;" psql -U postgres -d postgres -c "CREATE EXTENSION postgis_topology;" -- if you built with sfcgal support -- psql -U postgres -d postgres -c "CREATE EXTENSION postgis_sfcgal;"

-- if you want to install tiger geocoder -- psql -U postgres -d postgres -c "CREATE EXTENSION fuzzystrmatch" psql -U postgres -d postgres -c "CREATE EXTENSION postgis_tiger_geocoder;"

-- if you installed with pcre -- you should have address standardizer extension as well psql -U postgres -d postgres -c "CREATE EXTENSION address_standardizer;" --杭州扬州距离 select ST_distance_sphere(ST_GeomFromText('POINT(120.19 30.26)',4326),ST_GeomFromText('POINT(119.42 32.39)',4326)) ,ST_distance(ST_GeomFromText('POINT(120.19 30.26)',4326),ST_GeomFromText('POINT(119.42 32.39)',4326)); SELECT ST_AsEWKT(ST_GeomFromText('POINT(120.19 30.26)',4326)); SELECT ST_MakeLine(ST_GeomFromText('POINT(120.19 30.26)',4326),ST_GeomFromText('POINT(119.42 32.39)',4326)) ,ST_AsEWKT(ST_MakeLine(ST_GeomFromText('POINT(120.19 30.26)',4326),ST_GeomFromText('POINT(119.42 32.39)',4326))); SELECT ST_GeomFromText('LINESTRING(0 0,1 1,1 2)',4326) ,ST_GeomFromText('POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))',4326)

--建立gist索引 create index idx_cities_geom on cities using gist(the_geom);

--导出数据 copy (select id,name,ST_AsEWKT(the_geom) from cities where 1=1) to 'd:/download/cities.txt' with delimiter '^'; --导入数据 copy cities from 'd:/download/cities.txt' with delimiter '^';

SELECT * FROM cities WHERE ST_Contains(the_geom,'POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))'); select ST_GeomFromText('POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))',4326) ,ST_AsEWKT(ST_GeomFromText('POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))',4326)) ,ST_AsEWKB(ST_GeomFromText('POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))',4326)) ;

SELECT 'SRID=4326;POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))'::geometry;

SELECT ST_Distance('LINESTRING(-122.33 47.606, 0.0 51.5)'::geography, 'POINT(-21.96 64.15)':: geography);

SELECT ST_GeomFromText('POLYGON((0 0,1 1,1 0,0 0))') && ST_GeomFromText('POLYGON((0 0,1 1,1 0,0 0))') ,ST_GeomFromText('POLYGON((0 0,1 1,1 0,0 0))') && ST_GeomFromText('POLYGON((2 2,4 4,4 2,2 2))') ; --geometry ST_MakeEnvelope(double precision xmin, double precision ymin, double precision xmax, double precision ymax, integer srid=unknown); SELECT ST_AsEWKT(ST_MakeEnvelope(10, 10, 11, 11, 4326));

--CREATE INDEX [indexname] ON [tablename] USING GIST ( [geometryfield] ); --CREATE INDEX [indexname] ON [tablename] USING GIST ([geometryfield] gist_geometry_ops_nd); CREATE INDEX idx_cities_geom ON cities USING GIST (the_geom); --。创建完索引后，必须强制让PostgreSQL更新表的统计信息，这些统计信息可以用于查询计划的优化，更新方法如下： --VACUUM ANALYZE [table_name] [(column_name)]; VACUUM ANALYZE cities(the_geom); --交集面 select ST_AsEWKT(ST_Intersection(ST_MakeEnvelope(0, 0, 100, 100, 4326),ST_MakeEnvelope(50, 50, 200, 200, 4326))) AS intersection_geom ; --边界 select ST_AsEWKT(ST_Boundary(ST_Intersection(ST_MakeEnvelope(0, 0, 100, 100, 4326),ST_MakeEnvelope(50, 50, 200, 200, 4326)))) AS intersection_geom ; --查看执行计划 EXPLAIN ANALYZE select * from chn_adm0; --表大小 SELECT nspname || '.' || relname AS "relation", pg_size_pretty(pg_relation_size(C.oid)) AS "size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') and relname = 'chn_adm0';

--围栏 create table dwd_loc_geofence_fence_postgis_dd(id bigint,name varchar(1024)); SELECT AddGeometryColumn ('dwd_loc_geofence_fence_postgis_dd', 'coords', 4326, 'POLYGON', 2); CREATE INDEX idx_postgis_coords ON dwd_loc_geofence_fence_postgis_dd USING GIST (coords); select pg_relation_size('dwd_loc_geofence_fence_postgis_dd'); select pg_relation_size('idx_postgis_coords');

select id,name,concat('POLYGON((',UDFLBSDecodeCoordFormat(coords, ' ', ','),'))') from alipaydw.dwd_loc_geofence_fence_tool_dd where dt='20170526' and pre_type_code =2000001 and name like '%杭州%';

insert into dwd_loc_geofence_fence_es_dd(id,name,coords) values(293308,'竞对_网易_网易杭州总部',ST_GeomFromText('POLYGON((120.188672 30.188522,120.188854 30.186704,120.192716 30.186806,120.192427 30.18868))',4326)) ;

select * from dwd_loc_geofence_fence_postgis_dd where ST_Within(ST_GeomFromText('POINT(121.476259 31.229133)',4326),coords) ;

copy dwd_loc_geofence_fence_postgis_dd from 'D:/project/LBS/doc/dwd_loc_geofence_fence_postgis_dd.txt.gbk' with delimiter '^';

--mysql create table dwd_loc_geofence_fence_postgis_dd(id bigint,name varchar(1024),coords polygon) ENGINE=MyISAM;

CREATE SPATIAL INDEX idx_postgis_coords ON dwd_loc_geofence_fence_postgis_dd(coords); INSERT INTO dwd_loc_geofence_fence_postgis_dd VALUES(1,'',GEOMFROMTEXT(''));

select * from dwd_loc_geofence_fence_postgis_dd where MBRWithin(GEOMFROMTEXT('POINT(121.476259 31.229133)'),coords) ; select * from dwd_loc_geofence_fence_postgis_dd where MBRWithin(GEOMFROMTEXT('POINT(120.121164 30.272967)'),coords) ;

SET @hz = ST_GeomFromText('Point(120.203014 30.256622)'); SET @yz = ST_GeomFromText('Point(119.414745 32.405402)'); SELECT st_distance(@hz, @yz)*111195 ;