Update column for st contains

ALTER TABLE  public.tcm_centre
 add column kod_negeri char(2),
 add column par_id smallint,
 add column dun_id integer,
 add column no_dun smallint;
 
update tcm_centre g set (kod_negeri, par_id, dun_id, no_dun) = (SELECT f.kod_negeri, f.par_id, f.dun_id, f.no_dun FROM 
tcm_centre, (SELECT d.kod_negeri, d.par_id, d.dun_id, d.no_dun, d.geom FROM dun d) as f WHERE ST_CONTAINS(f.geom, g.geom) aND 
tcm_centre.gid = g.gid)

OS X, Apache with PHP and PGSQL

Just decided to take alternate route, i.e. to install whole PHP5 module in the system

curl -s http://php-osx.liip.ch/install.sh | bash -s 5.6

Also, don't forget:

I killed the whole day trying to make it work on El Capitan after I made an upgrade yesterday and it turned out that I forgot to modify httpd.conf and change the path from the default php module (version 5.5.27) to the one I installed (version 5.6.14). This should be done in httpd.conf by modifying your default LoadModule php5_module path to LoadModule php5_module /usr/local/opt/php56/libexec/apache2/libphp5.so. Just decided to leave it here as the potential solution for those who upgrade their OS or just the PHP version and face the same problem.

source: http://stackoverflow.com/questions/6588174/enabling-postgresql-support-in-php-on-mac-os-x

Install GCC 5.1 on CentOS 7.2 Core

This step is working for GCC 5.1. For newer version, change FedoraCore repo. Add repo fedora and name it as warning:fedora (don't forget to disable it, so that it won't conflict with others)

# cat << EOF > /etc/yum.repos.d/Fedora-Core23.repo
[warning:fedora]
name=fedora
mirrorlist=http://mirrors.fedoraproject.org/mirrorlist?repo=fedora-23&arch=$basearch
enabled=0
gpgcheck=1
gpgkey=https://getfedora.org/static/34EC9CBA.txt

Ensure that enabled=0 is placed in there. We don't want to messed up with other dependency in the system! Now we ready to install GCC 5.1

# yum install gcc --enablerepo=warning:fedora

Check!

gcc (GCC) 5.1.1 20150618 (Red Hat 5.1.1-4)
Copyright (C) 2015 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.  There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

He he (helium helium)!!

Installing PECL on CentOS 7.2 with Php 7.0w (and pecl-stats)

Installing PECL on CentOS 7.2 with Php 7.0w Install php devel

# yum install php-devel

And then, we try

$ pecl version

Nope. Not working. Now lets ask yum, who provides pecl.

# yum provides pecl

This is what I got.

Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: ftp.jaist.ac.jp
 * epel: mirror.smartmedia.net.id
 * extras: ftp.jaist.ac.jp
 * updates: ftp.jaist.ac.jp
 * webtatic: sp.repo.webtatic.com
base/7/x86_64/filelists_db                                                                                                                                       | 6.2 MB  00:00:06
epel/x86_64/filelists_db                                                                                                                                         | 7.3 MB  00:00:12
extras/7/x86_64/filelists_db                                                                                                                                     | 426 kB  00:00:01
updates/7/x86_64/filelists_db                                                                                                                                    | 4.3 MB  00:00:04
webtatic/x86_64/filelists_db                                                                                                                                     |  48 kB  00:00:00
1:php-pear-1.9.4-21.el7.noarch : PHP Extension and Application Repository framework
Repo        : base
Matched from:
Filename    : /usr/bin/pecl



1:php54w-pear-1.10.1-1.w7.noarch : PHP Extension and Application Repository framework
Repo        : webtatic
Matched from:
Filename    : /usr/bin/pecl



1:php55w-pear-1.10.1-1.w7.noarch : PHP Extension and Application Repository framework
Repo        : webtatic
Matched from:
Filename    : /usr/bin/pecl



1:php56w-pear-1.10.1-1.w7.noarch : PHP Extension and Application Repository framework
Repo        : webtatic
Matched from:
Filename    : /usr/bin/pecl



1:php70w-pear-1.10.1-1.w7.noarch : PHP Extension and Application Repository framework
Repo        : webtatic
Matched from:
Filename    : /usr/bin/pecl

Now there it is. Now proceed!!

# yum install php70w-pear

and viola pecl version returns

PEAR Version: 1.10.1
PHP Version: 7.0.9
Zend Engine Version: 3.0.0
Running on: Linux localhost.localdomain 3.10.0-327.22.2.el7.x86_64 #1 SMP Thu Jun 23 17:05:11 UTC 2016 x86_64

Most of newer version PECL requires GCC 5++. See my other post on how to install GCC 5+ on CentOS (default GCC in CentOS is 4.8). Now we are ready. Lemme try install stats package.

# pecl install stats

Which gives me.

pecl/stats requires PHP (version >= 5.3.0, version <= 5.6.99), installed version is 7.0.9
No valid packages found
install failed

Now that's bummer. Let me try version specific.

# pecl install stats-2.0.3

Which gives us..

downloading stats-2.0.3.tgz ...
Starting to download stats-2.0.3.tgz (112,280 bytes)
.........................done: 112,280 bytes
17 source files, building
.
.
(and some other sexy stuff
.
.
Build process completed successfully
Installing '/usr/lib64/php/modules/stats.so'
install ok: channel://pecl.php.net/stats-2.0.3
configuration option "php_ini" is not set to php.ini location
You should add "extension=stats.so" to php.ini

Next step, put that line on php.ini.

# vi /etc/php.ini

Now, you see that in php.ini in dynamic extensions says:

;;;;;;;;;;;;;;;;;;;;;;
; Dynamic Extensions ;
;;;;;;;;;;;;;;;;;;;;;;

; If you wish to have an extension loaded automatically, use the following
; syntax:
;
;   extension=modulename.extension
;
; For example, on Windows:
;
;   extension=msql.dll
;
; ... or under UNIX:
;
;   extension=msql.so
;
; ... or with a path:
;
;   extension=/path/to/extension/msql.so
;
; If you only provide the name of the extension, PHP will look for it in its
; default extension directory.

;;;;
; Note: packaged extension modules are now loaded via the .ini files
; found in the directory /etc/php.d; these are loaded by default.
;;;;

Let's do it the correct way!

# vi /etc/php.d/pecl-stats.ini

Write this, and :wq

; Enable extension PECL/Stats module
extension=/usr/lib64/php/modules/stats.so

And there you go!

plainto_or_tsquery

CREATE OR REPLACE FUNCTION plainto_or_tsquery (TEXT) RETURNS tsquery AS $$
SELECT to_tsquery (regexp_replace ($1, E ' [\\s \' | :& ()!] + ', ' | ', ' g '));
$$ LANGUAGE SQL STRICT IMMUTABLE;

source: http://www.progtown.com/topic700061-selection-of-keywords-to-the-text.html

tsvector update table dan create index

Update table menggunakan tsvector

UPDATE table SET (col1_keywords, col2_keywords, col3_keywords) = (to_tsvector(col1), to_tsvector(col1 || ' ' || col2), to_tsvector(col1 || ' ' || col2 || ' ' || col3))

Kemudian, create index untuk column keyword tersebut.

CREATE INDEX namadma_dun_par_idx ON daerahmengundifull USING gin(namadma_dun_par_keywords)