postgresql , 天文台 , pg_sphere , q3c , postgis
在天文或gis领域，找出某个位置附近的对象，或者找到两张表的位置互相关的数据，是非常普遍的需求。
在postgresql中有很多方法可以实现以上需求。
比如
<a href="http://skyview.gsfc.nasa.gov/xaminblog/index.php/tag/pgsphere/">http://skyview.gsfc.nasa.gov/xaminblog/index.php/tag/pgsphere/</a>
haversine: using the haversine formula with tables clustered on an index in declination. a simple constraint on declination is included to make the index useful.
unit vector: using the dot product of unit vectors with tables clustered on an index on dec. a simple constraint on declination is included to make the index useful.
postgis: using st_dwithin with tables where a postgis geography object has been added representing the ra/dec of each row. tables are clustered on an index using the geography object.
pgsphere: using the includes operator (~) in pgsphere with tables where an spoint object has been added representing the position of each row. tables are clustered on an index on the spoint object.
q3c: using the q3c_join function in q3c with tables clustered on an index on the q3c_ang2ipix(ra,dec). in this case no column was added to the table.
much greater reductions are possible using specialized indices. of the three possibilities studied, q3c has generally good performance, running about twice as fast the the postgis geography type.
the pgsphere library is very fast in doing point queries and but a bit slower than both q3c and postgis for cross-correlations. the slight disagreement in cross-corrleation counts is a bit disconcerting but it’s possible that it is due to the kind of rounding issues we discovered in the positional queries.
for correlations all of the methods using spherical indexing seem to have some startup/caching cost. the second and subsequent iterations run about three times as fast as the first. the implication on an operational system are unclear and presumably depend critically upon the query mix.
for the positional queries postgis still shows strong evidence of caching, but pgsphere and q3c do not. note that the results for the positional queries are for an aggregate 162 queries. the averaged times for individual queries ranged from about 10-300 milliseconds.
although one should be cautious extrapolating from any small number of tests, it does appear that spatial indices substantially improve performance. we see an improvement of somewhere between a factor of 2-15 in the time taken for queries.
either q3c or postgis seem like reasonable choices. q3c gives the better performance and has a more transparent syntax for astronomers. however postgis has a much broader community and far greater certainty of continued support. pgsphere’s performance in positional queries is remarkably good but the lack of clear support and variance in results in the cross-correlation are worrying.
如上所述，pgsphere是一个天文相关的postgresql数据库插件，包括针对天文数据新增的数据类型，操作符，函数等。
1. what is pgsphere?
pgsphere is an extra module for postgresql which adds spherical data types.
it provides:
input and output of data
containing, overlapping, and other operators
various input and converting functions and operators
circumference and area of an object
spherical transformation
indexing of spherical data types
several input and output formats
hence, you can do a fast search and analysis for objects with spherical attributes as used in geographical, astronomical, or other applications using postgresql.
for instance, you can manage data of geographical objects around the world and astronomical data like star and other catalogs conveniently using an sql interface.
the aim of pgsphere is to provide uniform access to spherical data.
because postgresql itself supports a lot of software interfaces, you can now use the same database with different utilities and applications.
详见手册
<a href="http://pgsphere.projects.pgfoundry.org/index.html">http://pgsphere.projects.pgfoundry.org/index.html</a>
<a href="https://github.com/mnullmei/pgsphere/releases">https://github.com/mnullmei/pgsphere/releases</a>
可能因为浮点精度的问题，会有几个check不一定能通过
pgsphere开发比较早，所以没有打包成extension。
稍作调整就可以了。
将pg_sphere--1.1.1.sql和pg_sphere.control拷贝到$pghome/share/extension目录
将pg_sphere.so拷贝到$pghome/lib目录
然后就可以使用 create extension pg_sphere; 创建这个模块了
修改makefile，略。
<a href="https://github.com/mnullmei/pgsphere/tree/fixes-1-1-1">https://github.com/mnullmei/pgsphere/tree/fixes-1-1-1</a>