PostgreSQL 天文插件 - pg_sphere

4 minute read





  • 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.
select count(*) from swiftdec a, xmmdec b  
a.dec between b.dec - .01 and b.dec + .01 and  
( sin(radians(b.dec-a.dec)/2)^2 + cos(radians(a.dec))*cos(radians(b.dec))*sin(radians(b.ra-a.ra)/2)^2  
  • 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.
select count(*) from swiftunit a, xmmunit b  
   a.dec between b.dec -.01 and b.dec+.01 and  
   a.__x*b.__x + a.__y*b.__y + a.__z*b.__z > 0.999999984769129  
  • 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.
select count(*) from swiftgeog a, xmmgeog b   
    st_dwithin(a.geog,b.geog,1111.950792, false)  
  • 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.
select count(*) from swiftpgs a, xmmpgs b   
    scircle(a.pnt, radians(.01))~b.pnt  
  • 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.
select count(*) from swiftq3c a, xmmq3c b   
where q3c_join(a.ra,a.dec,b.ra,b.dec,.01)  


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.




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.

3. Data types    
3.1. Overview    
3.2. Point    
3.3. Euler transformation    
3.4. Circle    
3.5. Line    
3.6. Ellipses    
3.7. Path    
3.8. Polygon    
3.9. Coordinates range    
4. Constructors    
4.1. Point    
4.2. Euler transformation    
4.3. Circle    
4.4. Line    
4.5. Ellipse    
4.6. Polygon    
4.7. Path    
4.8. Coordinates range    
5. Operators    
5.1. Casting    
5.2. Equality    
5.3. Contain and overlap    
5.4. Crossing of lines    
5.5. Distance    
5.6. Length and circumference    
5.7. Center    
5.8. Change the direction    
5.9. Turn the path of a line    
5.10. Transformation    
6. Functions    
6.1. Area function    
6.2. spoint functions    
6.3. strans functions    
6.4. scircle functions    
6.5. sellipse functions    
6.6. sline functions    
6.7. spath functions    
6.8. spoly functions    
6.9. sbox functions    
7. Create an index    
7.1. Spherical index    
8. Usage examples    
8.1. General    
8.2. Geographical    
8.3. Astronomical    





Beginning of 2017 
The pgSphere development team is working towards a new release of pgSphere to replace the outdated latest release 1.1.1. 

In the meantime, we recommend using the development version that is linked on the download page. 

Watch this space or the mailing list for announcements.!forum/pgsphere

tar -zxvf version-1-1-1-p3.tar.gz    
cd pgsphere-version-1-1-1-p3    
export PATH=/home/digoal/pgsql9.4/bin:$PATH    
USE_PGXS=1 make clean    
USE_PGXS=1 make     
USE_PGXS=1 make install    
start database and then installcheck    
USE_PGXS=1 make crushtest    





cd pgsphere-version-1-1-1-p3    
cp pg_sphere.sql pg_sphere--1.1.1.sql    
vi pg_sphere--1.1.1.sql    
vi pg_sphere.control    
comment = 'R-Tree implementation using GiST for spherical objects like spherical points and spherical circles with useful functions and operators.'    
default_version = '1.1.1'    
relocatable = true    



然后就可以使用 create extension pg_sphere; 创建这个模块了





pg_sphere 1.1.5手册

Flag Counter

digoal’s 大量PostgreSQL文章入口