3

I'm facing this query which is used to extract a centroid point (3D) of a 3D polygon resulting from the cartographic intersection of a 2D polygon given by the user of an application (which is inserted dynamically and is represented by the raw MULTIPOLYGON string hereunder) and some large 3D polygons (actually MULTIPOLYGON ZM) stored in an existing PostGIS table named polygon_table:

SELECT 
st_setsrid(
  st_makepoint(
    st_x(
      st_centroid(
        st_intersection(
          polygon_table.geom,
          ST_Transform(
            ST_GeomFromText(
              'MULTIPOLYGON(((
                 8.0118 46.5232,
                 7.9930 46.5476,
                 7.9935 46.5476,
                 8.0118 46.5232)))',
            4326),
          2056)))),
    st_y(
      st_centroid(
        st_intersection(
          polygon_table.geom,
          ST_Transform(
            ST_GeomFromText(
              'MULTIPOLYGON(((
                 8.0118 46.5232,
                 7.9930 46.5476,
                 7.9935 46.5476,
                 8.0118 46.5232)))',
            4326),
          2056)))),
    (
     st_zmax(
       st_intersection(
         polygon_table.geom,
         ST_Transform(
            ST_GeomFromText(
              'MULTIPOLYGON(((
                 8.0118 46.5232,
                 7.9930 46.5476,
                 7.9935 46.5476,
                 8.0118 46.5232)))',
            4326),
          2056))) +
     st_zmin(
       st_intersection(
         polygon_table.geom,
         ST_Transform(
            ST_GeomFromText(
              'MULTIPOLYGON(((
                 8.0118 46.5232,
                 7.9930 46.5476,
                 7.9935 46.5476,
                 8.0118 46.5232)))',
            4326),
          2056)))
    )/2),
  2056)
FROM polygon_table
WHERE id = 1
;

Because the polygon given by the user can have >>> hundreds of thousands vertices (I over-simplified this polygon here), I would like to simplify this query to limit the number of ST_Intersection() calls, e.g. by not extracting both the X and Y coordinates to build the point using ST_MakePoint(), but if possible by only appending the computed mean Z-value of the resulting 3D polygon intersection to the point given by ST_Centroid().

Is this possible? Or does it exist a better solution?

2

Simply add a sub-query for the ST_Intersection used in deriving the Z value, and a LATERAL for the extra call to ST_Ceontroid:

SELECT ST_MakePoint(
         ST_X(ctr),
         ST_Y(ctr),
         (ST_ZMin(its.geom)+ST_ZMax(its.geom)) / 2.0
      ) AS geom
FROM   (
  SELECT ST_Intersection(pt.geom, <dynamic_polygon>) AS geom
  FROM   polygon_table AS pt
  WHERE  id = 1
  --AND  ST_Intersects(pt.geom, <dynamic_polygon>)
) AS its, LATERAL ST_Centroid(its.geom) AS ctr
;

However, given that you pass in 2D geometries, the Z value will always be 0.0, so no need to calculate it, really:

SELECT ST_MakePoint(
         ST_X(ctr),
         ST_Y(ctr),
         0.0
      ) AS geom
FROM   (
  SELECT ST_Intersection(pt.geom, <dynamic_polygon>) AS geom
  FROM   polygon_table AS pt
  WHERE  id = 1
  --AND  ST_Intersects(pt.geom, <dynamic_polygon>)
) AS its
;

Not even SFCGAL backed ST_3DIntersection can do sth. about that.

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.