4

I'm attempting to utilize the PostGIS function ST_MaximumInscribedCircle() but having some trouble interpreting the example. How can I feed the geometry into a from clause to retrieve the center and the radius values? I've tried sub-queries and CTE's but it doesn't seem to want to recognize it. Here is an example of what I've attempted:

SELECT radius, st_geomfromtext(ST_AsText(center)) AS center, st_geomfromtext(ST_AsText(nearest)) AS nearest
FROM (select ST_MaximumInscribedCircle(st_astext(st_union(s.geom))) from table s) as foo

This is the error I'm getting: SQL Error [42703]: ERROR: column "radius" does not exist

1

2 Answers 2

7

An elegant way to expand and access SETOF records (i.e. from Table Functions) is a LATERAL join:

SELECT
  mic.radius,
  mic.center,   --already a GEOMETRY(POINT) type
  mic.nearest   --already a GEOMETRY(POINT) type
FROM
  <table> AS t,
  LATERAL ST_MaximumInscribedCircle(t.geom) AS mic
;

If you require the base table geometries to get ST_Union'ed first, use a sub-query:

SELECT
  mic.radius,
  mic.center,   --already a GEOMETRY(POINT) type
  mic.nearest   --already a GEOMETRY(POINT) type
FROM
  (
    SELECT
      ST_Union(geom) AS geom
    FROM
      <table>
  ) AS ut,
  LATERAL ST_MaximumInscribedCircle(ut.geom) AS mic
;

Alternatively, you can use composite type access notation:

SELECT
  (ST_MaximumInscribedCircle(ST_Union(geom))).*
FROM
  <table>
;

to expand the record into its named fields, or:

SELECT
  (ut.mic).radius,
  (ut.mic).center,   --already a GEOMETRY(POINT) type
  (ut.mic).nearest   --already a GEOMETRY(POINT) type
FROM
  (
    SELECT
      ST_MaximumInscribedCircle(ST_Union(geom)) AS geom
    FROM
      <table>
  ) AS ut
;

or even:

SELECT
  (mic).radius,
  (mic).center,   --already a GEOMETRY(POINT) type
  (mic).nearest   --already a GEOMETRY(POINT) type
FROM
  ST_MaximumInscribedCircle((
    SELECT
      ST_Union(geom) AS geom
    FROM
      <table>
  )) AS mic
;

to get access to those fields by identifier.

1

If the example in https://postgis.net/docs/manual-3.2/ST_MaximumInscribedCircle.html works for you know that your PostGIS has the function and everything is fine so far.

SELECT radius, ST_AsText(center) AS center, ST_AsText(nearest) AS nearest
    FROM ST_MaximumInscribedCircle('POLYGON ((50 50, 150 50, 150 150, 50 150, 50 50))');

The synopsis of the function is

(geometry, geometry, double precision) ST_MaximumInscribedCircle(geometry geom);

You seem to use this as input

FROM (select ST_MaximumInscribedCircle(st_astext(st_union(s.geom)))

but st_astext returns a string and ST_MaximumInscribedCircle will fail. Leave st_astext out.

Then there is something that I cannot explain with the radius, center, and nearest. They cannot be used just by names as in the example when the geometry is selected from a table. Instead a syntax including the name of the geometry like in (radius(geom) must be used. I edited the query as follows and managed to get a result. My input table was "states" and the geometry column "wkb_geometry".

SELECT radius(geom), ST_AsText(center(geom)) AS center, ST_AsText(nearest(geom)) AS nearest from
(select (ST_MaximumInscribedCircle(st_union(s.wkb_geometry))) as geom  from states s) as foo;

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.