PostgreSQL+PostGIS安装

8-24 2,141 views

PostgreSQL(http://www.postgresql.org/)与MySQL类似,也是一款开源、免费数据库。PostGIS(http://www.postgis.org/)是一款PostgreSQL插件,为PostgreSQL提供GIS支持。在开发页面点击热力图时,需要以(x,y)坐标方式存储点击位置,所以使用PostgreSQL+PostGIS存储点击数据。

安装PostgreSQL

groupadd postgres
useradd -g postgres postgres
passwd postgres
tar -zxvf postgresql-9.5alpha2.tar.gz
cd postgresql-9.5alpha2
./configure –prefix=/opt/postgresql
make
make install
cd /opt/
chown -R postgres:postgres postgresql
su – postgres
/opt/postgresql/bin/initdb -D /opt/postgresql/data
/opt/postgresql/bin/pg_ctl -D /opt/postgresql/data -l /opt/postgresql/logfile start

1
使用“/opt/postgresql/bin/psql -U postgres”可以登录数据库,执行相关操作。

安装PostGIS

从源码安装,需要先安装GEOS,Proj.4,GDAL,LibXML2和JSON-C(http://postgis.net/source/)。

tar -jxvf geos-3.5.0.tar.bz2
cd geos-3.5.0
./configure
make
make install
tar -zxvf proj.4-4.9.1.tar.gz
cd proj.4-4.9.1
./configure
make
make install
tar -zxvf gdal-1.10.0.tar.gz
cd gdal-1.10.0
./configure
make
make install
tar -zxvf libxml2-2.9.2.tar.gz
cd libxml2-2.9.2
./configure
make
make install
tar -zxvf json-c-json-c-0.11-20130402.tar.gz
cd json-c-json-c-0.11-20130402
./configure
make
make install
tar -zxvf postgis-2.1.8.tar.gz
cd postgis-2.1.8
./configure –with-pgconfig=/opt/postgresql/bin/pg_config
make
make install
su – postgres
/opt/postgresql/bin/createdb postgis
/opt/postgresql/bin/psql -d postgis -U postgres -f /opt/postgresql/share/contrib/postgis-2.1/postgis.sql

执行上述语句有可能报错,打开日志文件/opt/postgresql/logfile,会有如下错误:

ERROR: could not load library “/opt/postgresql/lib/postgis-2.1.so”: libgeos_c.so.1: cannot open shared object file: No such file or directory

执行“ldd /opt/postgresql/lib/postgis-2.1.so”,缺少两个so文件:

linux-vdso.so.1 => (0x00007fff3d1fe000)
libgeos_c.so.1 => not found
libproj.so.9 => not found
libjson-c.so.2 => /lib64/libjson-c.so.2 (0x00007fb8f08f1000)
libxml2.so.2 => /lib64/libxml2.so.2 (0x00007fb8f0588000)
libm.so.6 => /lib64/libm.so.6 (0x00007fb8f0285000)
libc.so.6 => /lib64/libc.so.6 (0x00007fb8efec4000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007fb8efcc0000)
libz.so.1 => /lib64/libz.so.1 (0x00007fb8efaa9000)
liblzma.so.5 => /lib64/liblzma.so.5 (0x00007fb8ef884000)
/lib64/ld-linux-x86-64.so.2 (0x00007fb8f0dac000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fb8ef668000)

执行以下语句可解决:

ln -s /usr/local/lib/libgeos_c.so.1 /lib64/libgeos_c.so.1
ln -s /usr/local/lib/libproj.so.9 /lib64/libproj.so.9

使用

postgis=# CREATE TABLE IF NOT EXISTS click (id int primary key,pt geometry);
postgis=# insert into click values (1,’POINT(30 40)’);
postgis=# select * from click;
id | pt
—-+——————————————–
1 | 01010000000000000000003E400000000000004440

Infobright简介与实践

infobright(https://www.infobright.org/)是一款基于MySQL的数据仓库,它采用列式存储,压缩比高,查询速度快。 infobright提供社区(ICE)和商业(IEE)两个...

阅读全文

聚簇索引(摘自《高性能MySQL》)

InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。 当表有聚簇索引时,它的数据行实际上存放在索引的叶子页(leaf page)中。术语“聚簇”表示...

阅读全文