5

I'd like to create a new, custom PostGIS function that would return a point geometry from a polygon that I can use for generating label points from a polygon, but I am not sure how to go about composing the function itself.

My goal is really simple...

  1. generate initial label points for all polygons using ST_Centroid,
  2. Test the label point to ensure that it falls on the surface of the polygon,
  3. For centroids not on the surface of the polygon, fall back to ST_PointOnSurface.

This would mean most label points are at the Centroid, but ensure that irregular and "C" shaped polygons would have the label point at least somewhere on the surface. I feel like the psuedo-code would look something like this:

SELECT gid,
  CASE 
    WHEN ST_Intersects(ST_Centroid(x.geom), x.geom) THEN ST_Centroid(x.geom)
    ELSE ST_PointOnSurface(x.geom)
  END AS geom
FROM x
  • Check the DDL of the existing functions in the public schema of your database - ST_Buffer for example - check the language specifics and how parameters are passed and how they are used in the function itself. While I don't completely understand the language, the basics of inputs and outputs can be understood using existing postgis functions as examples. – DPSSpatial Sep 1 at 17:30
  • What would you call such a custom function?...🙂 – Cyril Mikhalchenko Sep 1 at 17:42
  • @CyrilMikhalchenko How about ST_CentroidOnSurface () ??? – RyanKDalton Sep 1 at 18:00
  • Yes, Okey,... 🙂 – Cyril Mikhalchenko Sep 1 at 18:03
7

So, I present another original geometric custom function as a built-in CTE.

Create a function:

CREATE OR REPLACE FUNCTION ST_CentroidOnSurface(
geom GEOMETRY
)
RETURNS GEOMETRY AS
$BODY$
WITH
    tbla AS (SELECT (ST_Dump(geom)).geom geom),
    tblb AS (SELECT ST_Centroid(geom) geom FROM tbla),
    tblc AS (SELECT (a.geom) geom FROM tblb a JOIN tbla b ON ST_Intersects(a.geom, b.geom)),
    tbld AS (SELECT ST_PointOnSurface(geom) geom FROM tbla),
    tble AS (SELECT (a.geom) geom FROM tbld a WHERE NOT EXISTS (SELECT 1 FROM tblb b WHERE ST_Intersects(a.geom, b.geom)))
    SELECT (geom) geom FROM (SELECT * FROM tblc UNION SELECT * FROM tble) foo
$BODY$
LANGUAGE SQL

Run the function to execute:

SELECT ST_CentroidOnSurface(geom) geom FROM <name_table>

And check the result.

Test it hot, because this is the "hot pie that I just pulled out of my oven"...🙂

variant 2

CREATE OR REPLACE FUNCTION ST_CentroidOnSurface(
geom GEOMETRY
)
RETURNS GEOMETRY AS
$BODY$
WITH
    tbla AS (SELECT (ST_Dump(geom)).geom geom),
    tblb AS (SELECT ST_Centroid(geom) geom FROM tbla),
    tblc AS (SELECT (a.geom) geom FROM tblb a JOIN tbla b ON ST_Intersects(a.geom, b.geom)),
    tbld AS (SELECT (a.geom) geom FROM tbla a WHERE NOT EXISTS (SELECT 1 FROM tblb b WHERE ST_Intersects(a.geom, b.geom))),
    tble AS (SELECT ST_PointOnSurface(geom) geom FROM tbld)
    SELECT (geom) geom FROM (SELECT * FROM tblc UNION SELECT * FROM tble) foo
$BODY$
LANGUAGE SQL
| improve this answer | |
  • That is DOPE! It works really well, and fast. I appreciate you sharing not only how to configure the function, but building the function itself! Great work. – RyanKDalton Sep 1 at 19:05
  • 1
    I am glad that I helped you and others... – Cyril Mikhalchenko Sep 1 at 19:07

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.