q3c_poly_query using postgresql polygon type

[rhandberg]

When using the q3c_poly_query function it has to be called using an string-array like shown in the examples:

SELECT * FROM mytable WHERE q3c_poly_query(ra, dec, '{0, 0, 2, 0, 2, 1, 0, 1}');

However, if I have a table of polygons (in column named "footprint", stores as PostgreSQL polygon objects (https://www.postgresql.org/docs/10/datatype-geometric.html) I would like to check which polygons the point is in:

SELECT * FROM mytable WHERE q3c_poly_query(69.57945923, -57.20383833, footprint);

This fails because q3c_poly_query does not accept polygons as input.

Wouldn't it make sense to have q3c_poly_query accept actual polygons as input?

[segasai]

I agree that it would make perfect sense. I am happy to accept the patch on that, or maybe I'll code it during Christmas...

[demitri]

I would be very interested in seeing this as well. I'd be happy to contribute to the code to make this happen if it would help.

[segasai]

I've started coding it today by cleaning up the existing code that should make the change easier https://github.com/segasai/q3c/tree/polygon_type The only bit that is needed is to read the PG polygon in C and some SQL plumbing. I'll see if I can code it soonish.

[segasai]

Done in Q3C-1.8.0 See and test https://github.com/segasai/q3c/releases/tag/v1.8.0

[demitri]

Thanks! I will test it ASAP.

[dannygoldstein]

does this now work as a join model? I.e., can one do

SELECT * from IMAGE I join SOURCE s on q3c_poly_query(s.ra, s.dec, i.polygon) and s.name = whatever?;

[segasai]

It is certainly not optimized for that. However (to my surprise). It works, however I've had to set enable_seqscan to off

wsdb=> \d xx
     Table "koposov.xx"
 Column |  Type   | Modifiers 
--------+---------+-----------
 a      | polygon | 

wsdb=> select * from xx;
             a             
---------------------------
 ((1,1),(2,1),(2,2),(1,2))
(1 row)

wsdb=> \timing
wsdb=> select count(objid) from xx,sdssdr9.phototag as s where q3c_poly_query(s.ra,s.dec,xx.a);
 count  
--------
 176231
(1 row)
Time: 276.023 ms

[dannygoldstein]

Awesome! Would be really nice to have an optimized version...