postgresql postgis使用心得

1.pg_dump 导出备份文件时，需指定库的用户名，否则在环境变更后，用户名与原库不相同的情况下，还原时会报用户名异常。

2.psql还原sql文件时，如果目标表有自增主键，需将主键id的类型手动设为serial，否则会在还原时报主键自增序列不存在。

3.geometry是平面geohash，而geography是地理geohash，在进行地理距离计算时需使用geography，或者将geometry转换为geography后再使用，否则会产生极大的误差值。

4.在地图上查询不规则区域内部所有点坐标时，我们需要获得多边形的多个点坐标，使用函数st_linefromtext()将点坐标连为多个线，再使用函数ST_MakePolygon()，将多个线条封闭为一个平面多边形后，使用st_contains获取内部存在的点。

ps：本方法只支持geometry类型

例：select lon,lat from map where st_contains( ST_MakePolygon(st_linefromtext(concat(‘LINESTRING(‘,area,’)’))),st_point(lon,lat));//LINESTRING指明了该坐标的类型为线，必需

ps:即时生成平面是非常消耗效率的事情，所以建议在生成坐标表是就将不规则多边形保存在一个新的字段中。

如下：update zone set geo = ST_MakePolygon(st_linefromtext(concat(‘LINESTRING(‘,area,’)’)))

5.postgresql与mysql不同，支持双向模糊查询索引，需要使用gin或者gist索引，在本次使用实践中使用gin索引，并未生效，原因待查。

ps：gin索引生成较慢，但查询效率较高，gist索引生成较快，但查询效率相对较低。

例：

    CREATE INDEX company_trgm_idx ON company using GIST（company_name gist_trgm_ops）;

    CREATE INDEX company_trgm_idx ON company using GIN（company_name gin_trgm_ops）;

索引有失效可能性，详见：https://blog.csdn.net/azhegps/article/details/76177319

6. psql执行命令中如果报错就带上 -v ON_ERROR_STOP=1    这样就可以看到第一个报错的信息了。

7. postgresql并没有mysql5.7所支持的match against分词查询，但是相对的有模糊查询，分词查询，相似性查询三种查询方式。

模糊查询效率和索引方式远优于mysql。

分词查询需要安装中文分词器。支持gin索引和安装插件的索引方式。

但是分词查询并不如mysql灵活，并不支持双向的分词匹配，在查询词长于目标词时无法使用

例：查询词：长三角经济区，查询内容：长三角，这样是查询不出结果的

在这种情况下需要使用相似性查询。

相似性查询：similarity(text,text) 能够根据文本的相似度进行评分，通过评分排序的方式获得查询结果

例：

select name from tbl_content order by similarity(name,'王力宏') desc

ps:相似性查询只能支持从头开始相似，如过使用苏宁去查询南京苏宁就无法获得结果。

8.postgresql的联合更新与mysql不同：

mysql: update a,b set a.name = b.name where a.id = b.aid

postgresql: update a set a.name = b.name from b where a.id  = b.id

ps：postgresql的联合更新不支持别名

转载自：https://blog.csdn.net/yangzan0816/article/details/88559945