enter image description here

I have a polyline SDE.ST_GEOMETRY feature class in an Oracle 12c geodatabase (10.7.1).

I want to create a view on the feature class that has X and Y columns that contain the coordinates of the polyline midpoints.

I've noticed that while there is a centroid function for polygons, there isn't a midpoint function for polylines.

Is there a way to get midpoint coordinates of SDE.ST_GEOMETRY polylines with SQL?

  • ST_CENTROID() is documented as finding the center of the bounding box of the minestrone (which might not be on the line, unless it's a 2-point segment) – Vince Dec 2 '19 at 3:17
  • @Vince What's the minestrone part about? – User1973 Dec 2 '19 at 3:29
  • 2
    linestring, as rendered by autocorrect – Vince Dec 2 '19 at 3:33
  • 1
    Assuming your linestrings don't change frequently, pre-computing the location for each geometry midpoint using a geodetic function with a GIS client, and storing this as a point with a common key should be simple enough. – Vince Dec 2 '19 at 4:03
  • 2
    Seems like you need a ST_LineInterpolatePoint( geom, 0.5) equivalent for an oracle database. – Gabriel De Luca Dec 7 '19 at 23:17

The custom ST_MIDPOINT function below works.

However, it's horrendously inefficient because it doesn't seem to be possible to use the SDE functions & operators to directly assign values to variables. I needed to wrap them in queries instead.

More information here: Use SDE.ST_GEOMETRY functions in a custom function

-- execute this as the SDE user
create or replace function ST_MidPoint
   (line_in IN sde.st_geometry)
   RETURN sde.st_geometry
IS midpoint sde.st_geometry;
  srid integer;
  line_length number(38);
  num_parts integer;
  num_points integer;
  partNum integer;
  distanceAlong number(38);
  segmentLength number(38);
  part sde.st_geometry;
  p1 sde.st_geometry;
  p2 sde.st_geometry;
  x1 double precision;
  y1 double precision;
  x2 double precision;
  y2 double precision;
  -- get the SRID of the line for later use in constructing the midpoint geometrey
  select sde.st_srid (line_in)
  into srid
  from dual;

  -- calculate the total length of the line
  select sde.st_length(line_in)
  into line_length
  from dual;

  -- get the number of parts that make up the line
  select sde.st_numgeometries(line_in)
  into num_parts
  from dual;

  distanceAlong := 0;
  for partNum in 1..num_parts loop
    -- get the geometry for this part
    select sde.st_geometryn(line_in, partNum)
    into part
    from dual;

    -- get the number of points that make up this part
    select sde.st_numpoints(part)
    into num_points
    from dual;

    -- get the first point (the "from" point) for the part
    select sde.st_pointn (part, 1)
    into p1
    from dual;

    -- iterate along the line until the section that contains the midpoint is found
    for pointNum in 2..num_points loop
      -- get the "to" point of the segment
      select sde.st_pointn(part, pointNum)
      into p2
      from dual;

      -- calculate the distance between the from point and the to point
      select sde.st_distance(p1, p2)
      into segmentLength
      from dual;

      -- add the distance along this segment to the running total
      distanceAlong := distanceAlong + segmentLength;
      -- check to see if the running total is past the midpoint
      if distanceAlong >= line_length/2.0 then
        -- the two current points encompass the midpoint of the line
        -- determine the midpoint geometry and return it
        select sde.st_x(p1)
        into x1
        from dual;
        select sde.st_y(p1)
        into y1
        from dual;
        select sde.st_x(p2)
        into x2
        from dual;
        select sde.st_y(p2)
        into y2
        from dual;
        select sde.st_point((x1+x2)/2.0, (y1+y2)/2.0, srid)
        into midpoint
        from dual;
        -- the midpoint has been found, not need to interogate the rest of the line
        RETURN midpoint;
      end if;
      -- save the endpoint as the first point and continue down the line looking for the midpoint
    end loop;
  end loop;

  return null;

   raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
grant execute on ST_MidPoint to public;

--select objectid,sde.ST_MidPoint(shape) geom
--from gis.line_test
--order by objectid;

A better option would be to have a nightly python job that precomputes the midpoints in a parallel feature class:

Insert geoprocessing results into an existing feature class (midpoints)


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.