SQL definitions

===============

Long version:

<https://www.postgresql.org/docs/current/extend-extensions.html>.

If you're writing new features that require SQL support, pick some

descriptive name; let's say my_new_op.

Put your new code into a file called pgs_my_new_op.sql.in. The .in

extension here usually indicates "it's for copying stuff together";

usally, not much processing is done on such files.

Then edit the Makefile. The PGS_SQL variable contains a list of the

SQL files eventually copied together, without the .in. Add your new

file there.

You will also need to create an upgrade file. In order to tell postgres

to execute it, increase PGSPHERE_VERSION as appropriate. As a

consequence, you will have to::

git mv pg_sphere--<old version>.sql.in pg_sphere--<new version>.sql.in

and also to update the version in pg_sphere.control.

Then create a make rule::

pg_sphere--<old version>--<new version>.sql: pgs_my_new_op.sql.in

cat $^ > $@

(of course, this will extend to having multiple sql.in files).

Finally, add the target of that rule to the DATA_built variable.

Regression tests

================

Regressions tests are as per

<https://www.postgresql.org/docs/current/extend-pgxs.html>.

In short, write queries executing your new features into a file

sql/my_new_op.sql, and add "my_new_op" (without the extension or the

directory name) to both REGRESS and TESTS in the Makefile.

Then touch expected/my_new_op.out, run make test. This will of course

fail, because your tests hopefully will output something. But then you

can pick out the diff from

/var/lib/postgresql/pgsphere/regression.diffs, have another critical

look at it and generatoe your .out file from it.

PGSPHERE_VERSION = 1.2.0

PGSPHERE_VERSION = 1.2.1

SRC_DIR =$(shell basename$(shell pwd))

pg_sphere--1.0_gavo--1.1.5beta0gavo.sql\

pg_sphere--1.1.5beta0gavo--1.1.5beta2gavo.sql\

pg_sphere--1.1.5beta2gavo--1.1.5beta4gavo.sql\

pg_sphere--1.1.5beta4gavo--1.2.0.sql

pg_sphere--1.1.5beta4gavo--1.2.0.sql\

pg_sphere--1.2.0--1.2.1.sql

DOCS = README.pg_sphere COPYRIGHT.pg_sphere

REGRESS = init tables points euler circle line ellipse poly path box index\

contains_ops contains_ops_compat bounding_box_gist gnomo healpix\

moc

moc mocautocast

REGRESS_9_5 = index_9.5# experimental for spoint3

TESTS = init_test tables points euler circle line ellipse poly path box index\

contains_ops contains_ops_compat bounding_box_gist gnomo healpix\

moc

moc mocautocast

pgs_line.sql pgs_ellipse.sql pgs_polygon.sql pgs_path.sql\

pgs_box.sql pgs_contains_ops.sql pgs_contains_ops_compat.sql\

pgs_gist.sql gnomo.sql\

healpix.sql pgs_gist_spoint3.sql pgs_moc_type.sql pgs_moc_compat.sql pgs_moc_ops.sql

healpix.sql pgs_gist_spoint3.sql pgs_moc_type.sql pgs_moc_compat.sql pgs_moc_ops.sql\

pgs_moc_geo_casts.sql

PGS_SQL_9_5 = pgs_9.5.sql# experimental for spoint3

USE_PGXS = 1

AUGMENT_GAVO_111 =$(AUGMENT_UNP_111) healpix.sql# for vanilla 1.1.1 users

UPGRADE_GAVO_111 =$(UPGRADE_UNP_COMMON)

sed -i -e '/PARALLEL/d' $@# version $(pg_version) does not have support for PARALLEL

pg_sphere--1.2.0--1.2.1.sql: pgs_moc_geo_casts.sql.in

cat$^>$@

ifeq ($(has_parallel), n)

sed -i -e '/PARALLEL/d' $@# version $(pg_version) does not have support for PARALLEL

sscan.o : sparse.c

</programlisting>

</example>

<funcsynopsis>

<funcprototype>

<funcdef><function>max_order</function></funcdef>

<paramdef>smoc</paramdef>

</funcprototype>

</funcsynopsis>

<para>

Returns the maximal order of an smoc.

</para>

<para>

The maximal order of a MOC can be higher than than the highest

order appearing in the serialisation. For instance, in the example

below the full-sky coverage given at order 6 means that no patch

larger than about 1 degree is not covered. In the ASCII

serialisation, give the maximum order with an with an empty cell

list.

</para>

<example>

<title>Obtaining a MOC order</title>

<programlisting>

<![CDATA[SELECT max_order(smoc('0/0-11 6/')) as order;]]>

<![CDATA[ order]]>

<![CDATA[-----------]]>

<![CDATA[ 6 ]]>

</programlisting>

</example>

</sect2>

</sect1>

OVERLAP_DEFS= [

('subset','<@','@>'),

('not_subset','!<@','!@>'),

('superset','@>','<@'),

('not_superset','!@>','!<@'),

]

INTERSECT_DEFS= [

('intersect','&&','&&'),

('not_intersect','!&&','!&&'),

]

GEO_TYPES= ["scircle","spoly"]

classAccum:

def__init__(self):

self.parts= []

defcontent(self):

return"".join(self.parts)

defwrite(self,s):

self.parts.append(s)

defwriteln(self,*strings):

self.parts.append("\n".join(strings)+"\n")

defreplace_last(self,subs):

forindex,partinenumerate(reversed(self.parts)):

ifpart.strip():

else:

self.parts[index]=re.sub("[^\s](\s*)$",

lambdamat:subs+mat.group(1),

self.parts[index])

defintroduce_section(self,sec_name):

self.writeln()

self.writeln("-- #################################")

self.writeln(f"--{sec_name}")

defemit_drop_code(accum):

accum.introduce_section("Cleanup")

accum.writeln("DROP OPERATOR IF EXISTS")

for_,op,_inOP_DEFS:

forgeo_typeinGEO_TYPES:

accum.writeln(f"{op} (smoc,{geo_type}),")

accum.writeln(f"{op} ({geo_type}, smoc),")

accum.replace_last(";")

defmake_negator(op):

ifop.startswith("!"):

returnop[1:]

else:

defemit_op_def(accum,operator,leftarg,rightarg,procedure,commutator):

accum.writeln(

f"CREATE OPERATOR{operator} (",

f" LEFTARG ={leftarg},",

f" RIGHTARG ={rightarg},",

f" PROCEDURE ={procedure},",

f" COMMUTATOR = '{commutator}',",

f" NEGATOR = '{make_negator(operator)}',",

f" RESTRICT = contsel,",

f" JOIN = contjoinsel",

f");")

defemit_op_and_func(accum,op_def):

func_stem,operator,commutator=op_def

forgeo_typeinGEO_TYPES:

accum.writeln(

f"CREATE OR REPLACE FUNCTION{func_name}(",

f" geo_arg{geo_type}, a_moc smoc) RETURNS BOOL AS $body$",

f" SELECT smoc(max_order(a_moc), geo_arg){operator} a_moc",

f" $body$ LANGUAGE SQL STABLE;")

emit_op_def(accum,operator,

geo_type,"smoc",

func_name,

commutator)

accum.writeln()

accum.writeln(

f"CREATE OR REPLACE FUNCTION{func_name}(",

f" a_moc smoc, geo_arg{geo_type}) RETURNS BOOL AS $body$",

f" SELECT a_moc{operator} smoc(max_order(a_moc), geo_arg)",

f" $body$ LANGUAGE SQL STABLE;")

emit_op_def(accum,operator,

"smoc",geo_type,

func_name,

commutator)

accum.writeln()

defmain():

accum=Accum()

accum.writeln("-- MOC/geometry automatic casts.")

accum.writeln(f"-- Generated{datetime.date.today()} by{sys.argv[0]}.")

accum.writeln(f"-- Re-generation needs to be triggered manually.")

accum.writeln()

emit_drop_code(accum)

accum.introduce_section(" smoc/geo OVERLAPS")

forop_definOVERLAP_DEFS:

emit_op_and_func(accum,op_def)

accum.writeln()

accum.introduce_section(" smoc/geo INTERSECTS")

forop_definINTERSECT_DEFS:

emit_op_and_func(accum,op_def)

accum.writeln()

print(accum.content)

if__name__=="__main__":

main()

<para>

An overlap or contain operator does not exist for all

combinations of data types.

For instance, scircle<@ <type>spoint</type> is

For instance, scircle&lt;@ <type>spoint</type> is

useless because a spherical point can never contain a

spherical circle.

</para>

<para>

When one of the arguments of such an operator is a MOC

and the other is an scircle or an spoly, the non-MOC argument

is converted to a MOC of the order of the maximum order of

the MOC. When comparing against a MOC-valued column, it

is usually much faster to explicitly convert the geometry

using the smoc constructor, as the conversion will then

only happen once.

</para>

<example>

<title>Is the left circle contained by the right circle?</title>

<programlisting>

<![CDATA[(1 row)]]>

</programlisting>

</example>

<example>

<title>Overlaps between a circle and a moc</title>

<programlisting>

<![CDATA[sql> SELECT scircle '<(37d, 5d), 0.25d>' <@ smoc('4/1117') AS test ;]]>

<![CDATA[ test]]>

<![CDATA[------]]>

<![CDATA[ f]]>

<![CDATA[(1 row)]]>

</programlisting>

</example>

<example>

<title>Overlaps between a circle and a moc with explicit order</title>

<programlisting>

<![CDATA[sql> SELECT scircle '<(37d, 5d), 0.25d>' <@ smoc('4/1117 5/') AS test ;]]>

<![CDATA[ test]]>

<![CDATA[------]]>

<![CDATA[ t]]>

<![CDATA[(1 row)]]>

</programlisting>

</example>

<example>

<title>Overlaps between a circle and a moc with explicit cast (normally faster)</title>

<programlisting>

<![CDATA[sql> SELECT smoc(5, scircle '<(37d, 5d), 0.25d>') <@ smoc('4/1117 5/') AS test ;]]>

<![CDATA[ test]]>

<![CDATA[------]]>

<![CDATA[ t]]>

<![CDATA[(1 row)]]>

</programlisting>

</example>

</sect2>

<sect2 id="op.cross">

psql:pg_sphere.test.sql:8575: NOTICE: argument type pointkey is only a shell

psql:pg_sphere.test.sql:8581: NOTICE: argument type pointkey is only a shell

psql:pg_sphere.test.sql:8587: NOTICE: argument type pointkey is only a shell

psql:pg_sphere.test.sql:9152: NOTICE: return type smoc is only a shell

psql:pg_sphere.test.sql:9158: NOTICE: argument type smoc is only a shell

psql:pg_sphere.test.sql:9154: NOTICE: return type smoc is only a shell

psql:pg_sphere.test.sql:9160: NOTICE: argument type smoc is only a shell