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 ;