POTGIS雑記

クエリレコード数

SELECT Count(*) FROM siteaddresses;

マルチフィールドクエリ||ハイフネーションを表す

SELECT siteaddres || ' ' || city || ' ' || zipcode AS str
  FROM siteaddresses
  LIMIT 100;
--     
SELECT
  initcap(siteaddres || ', ' || city) AS address,
  ts_rank_cd(ts, query) AS rank
FROM siteaddresses

文字操作オペレータ:"~"Matches regular expression,case sensitive"~*"Matches regular expression,case insensitive

SELECT array_to_string(regexp_split_to_array('the quick brown fox jumps over the lazy dog', E'\\s+'),'&');
--  
--the&quick&brown&fox&jumps&over&the&lazy&dog

regexp_split_to_array関数文字列array_をスペース(正規表現s)で分割to_string関数は分割して&接続します
全文検索PostgreSQL’s full-text search includes a number of useful features:

Matching partial words(ローカルフレーズマッチング).

Ranking results based on match quality(検索結果のソート).

Synonym dictionaries.(同義辞典)

-- Add a column for the text search data
ALTER TABLE siteaddresses ADD COLUMN ts tsvector;

-- Populate text search column by joining together relevant fields
-- into a single string
UPDATE siteaddresses
  SET ts  = to_tsvector('simple', siteaddres || ' ' || city || ' ' || zipcode)
  WHERE siteaddres IS NOT NULL;

PostgreSQL allows text search queries to be logically structured so that they search out documents that include all words, any words, or a combination of those conditions using and (&) and or (|) clauses.
Find all the records with “120 CINDY CT” in them:

SELECT siteaddres, city
FROM siteaddresses
WHERE ts @@ to_tsquery('simple','120 & CINDY & CT');

'@@'オペレータはここでbooleanタイプを返し、tsvectorがtsqueryと「simple」パラメータに一致するかどうかを判断し、テキストを処理する際に採用される辞書は、異なる言語に対応する辞書があり、虚語の除去、同義検索などを実現することができ、ここで「simple」パラメータ表は単純なスペースと句読点の除去のみを示す.
":*"接尾辞によるローカルマッチング、例えば120とCI(i)で始まるコンテンツのマッチング

SELECT siteaddres, city
FROM siteaddresses
WHERE ts @@ to_tsquery('simple','120 & CI:*');

ts_rank_cd()関数は、マッチングの度合いを計算し、ウェイトを与え、マッチングの度合いでソートします.

SELECT
  siteaddres,
  city,
  ts_rank_cd(ts, query) AS rank
FROM siteaddresses,
     to_tsquery('simple','120 & CI:*') AS query
WHERE ts @@ query
ORDER BY rank DESC
LIMIT 10;

siteaddresとcityをパッケージ(表示、頭文字大文字)して、よりきれいに表示します.

SELECT
  initcap(siteaddres || ', ' || city) AS address,
  ts_rank_cd(ts, query) AS rank
FROM siteaddresses,
     to_tsquery('simple','120 & CI:*') AS query
WHERE ts @@ query
ORDER BY rank DESC;

インタラクションの便利さを考慮して、「120 CI」を入力して上記の式「120&CI:*」を自動的に生成するなど、関数でマッチング式の自動生成を実現します.

-- An SQL function to wrap up the pre-processing step that takes
-- an unformated query string and converts it to a tsquery for
-- use in the full-text search
--xx ~ ' $'              
CREATE OR REPLACE FUNCTION to_tsquery_partial(text)
  RETURNS tsquery AS $$
    SELECT to_tsquery('simple',
           array_to_string(
           regexp_split_to_array(
           trim($1),E'\\s+'),' & ') ||
           CASE WHEN $1 ~ ' $' THEN '' ELSE ':*' END)
  $$ LANGUAGE 'sql';

-- Input:  100 old high
-- Output: 100 & old & high:*
SELECT to_tsquery_partial('100 old high');

--              
-- geoserver   SQL ，        ，      
SELECT
  initcap(a.siteaddres || ', ' || city) AS address,
  a.gid AS gid,
  ts_rank_cd(a.ts, query) AS rank
FROM siteaddresses AS a,
     to_tsquery_partial('100 old high') AS query
WHERE ts @@ query
ORDER BY rank DESC
LIMIT 10;