Efficient & compatible way to call several functions on a single, complex SQL result?

Question

I'm developing a geometry library that delegates complex calculations to a GIS-enabled database such as PostgreSQL with PostGIS, SQLite with SpatiaLite, or newer versions of MySQL.

When communicating with the database, I need so send back and forth:

• A textual or binary representation of the geometry
• Its SRID (Spatial Reference System Identifier), an integer that is commonly associated to a geometry but is not part of its textual representation

Sending a geometry to the database is an easy one-step process:

ST_GeomFromText(text, srid)

But retrieving the geometry is a two-step process:

• ST_AsText(geom)
• ST_SRID(geom)

Now let's take a real-life example.

Here is the SQL I'm sending to get the centroid of a polygon:

SELECT ST_AsText(ST_Centroid(ST_GeomFromText(geom, srid)));

As you can see, I'm only getting the textual representation of the geometry back, and am missing the SRID.

How can I efficiently get the result of both ST_AsText() and ST_SRID() in a single query?

Here are the possibilities I considered so far:

• Sending two separate queries:

  SELECT ST_AsText (ST_Centroid(ST_GeomFromText(geom, srid)));
  SELECT ST_SRID   (ST_Centroid(ST_GeomFromText(geom, srid)));
  

  This is obviously the less efficient of all, and the one I want to avoid at all costs.

• Sending a single query, but repeating the calculation twice:

  SELECT
      ST_AsText (ST_Centroid(ST_GeomFromText(geom, srid))),
      ST_SRID   (ST_Centroid(ST_GeomFromText(geom, srid)));
  

  A bit better, but I'm afraid not all databases will be able to optimize this query so that ST_Centroid() and ST_GeomFromText() are only executed once (maybe I'm wrong on this point, feel free to argue against this). But anyway, I don't think there is a way around passing the parameters twice to the prepared statement, which is a waste of resources.

• Storing the result in a variable, then calling the two functions on it:

  SET @geom = ST_Centroid(ST_GeomFromText(geom, srid));
  SELECT ST_AsText(@geom), ST_SRID(@geom);
  

  This feels like the best way to me, but as you can see the syntax is vendor-specific (MySQL in this example). Is there a standard way to do this?

Note that this must work on MySQL, PostgreSQL, SQLite, and maybe other DBMS in the future.

Feel free to explore other out-of-the-box ideas that I would have missed!

Side note: you may wonder why I'm not always considering that the result geometry has the same SRID as the input geometry; this is probably true, but what happens when a function is called with two geometries with different SRID is not defined by the standard AFAIK, so ideally I'd like the library to stay unopinionated on the subject, and just return whatever SRID the database returns.

Answer 1

I think all of those databases should support subqueries, which are standard

SELECT
    ST_AsText (centroid),
    ST_SRID   (centroid)
FROM (SELECT ST_Centroid(ST_GeomFromText(geom, srid)) as centroid) as t;

(MySQL)(SQLite)(PostgreSQL)