1

Whilst reading the documentation about using the PostGIS function ST_PixelAsCentroids() I was confused somewhat by this (ST_PixelAsPoints(rast, 1)).* syntax and as a newbie to SQL I was wondering if someone could explain this convention and why it's used?

It's obviously used as a method to unbundle complex results but I don't even know what it is called to google it properly to get a formal understanding of it.

I realise this post may be better suited to a SQL forum on Stack but I'll see what GIS Stack conjures up.

3

The function returns a table with typed columns (a schema) and rows (values). The .* at the end means "all columns from the table." SELECT * FROM f(x) would give a similar output to SELECT (f(x)).*.

Due to a PostgreSQL quirk functions with multiple columns may be called once for each column by the query planner in the cases above. So it is advised to use a Common Table Expression (CTE) or subquery to prevent this behaviour, writing for example:

SELECT (mf).* FROM (
    SELECT my_func(x) AS mf FROM some_table OFFSET 0
) sub;

or in PostgreSQL 9.3

SELECT mf.*
FROM some_table
LEFT JOIN LATERAL my_func(some_table.x) AS mf ON true;
  • 2
    The two forms aren't quite equivalent; SELECT * FROM function() will execute the function once, SELECT (function()).* will execute the function once per column in the output. – dbaston Oct 22 '14 at 23:45
  • I edited with a link to a good stackoverflow Q & A explaining the issue @dbaston raised – raphael Oct 23 '14 at 21:50
2

This is more of a PostgreSQL question rather than a PostGIS question, but sill a good one.

Functions like ST_PixelAsPoints, ST_PixelAsPolygons, and ST_PixelAsCentroids return a set of composite record data type, which have several fields. This is very similar to ST_Dump, described here. To gain access to all the returned fields from these composite data types, they need to be in the form (function(x)).*, as described in the PostgreSQL manual.

Note that function(x).* will not work, and is a common gotcha.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.