4

I have created an alias for st_expand function like this

CREATE OR REPLACE FUNCTION expand(
geom geometry,
value double precision)
RETURNS geometry AS
$BODY$
    DECLARE my_geom geometry;
BEGIN
    my_geom =  st_expand(geom,value);
    return my_geom;
END;
 $BODY$
LANGUAGE plpgsql VOLATILE;

This function on running my sql query returns in 1.5s, but if I directly use st_expand function - the return is in ~ 30ms.

Do note, I have to do an alias, since RDS Postgres doesn't allow me to create expand same as st_expand since they restrict creation of functions of language C.

The time to change all our sql scripts(legacy) to call st_expand and not expand is way too long. Hence creating the alias, changing to st_expand is not an option for now.

Is there any change that I can do to ensure the time is still ~30ms and not 1.5s since there's no logic done here.

  • What version of PostGIS are you running? – Evan Carroll Aug 18 '17 at 6:09
  • @PolyGeo Why do you tag this with postgresql, if the solution is chosen specifically because my solution does not run on PostgreSQL? – Evan Carroll Sep 12 '17 at 1:15
  • 1
    @EvanCarroll I didn't apply that tag, and in fact I have just removed it, because once a question is tagged postgis any postgresql tag seems superfluous. – PolyGeo Sep 12 '17 at 1:20
  • @PolyGeo I'm not sure I agree with that. For instance, his problem is unique to RDS, and not PostreSQL it stems from not being able to create C functions. It's more specific to RDS than it is to PostGIS, and it has nothing to do with PostgreSQL. My suggestion would be to leave the RDS tag too. – Evan Carroll Sep 12 '17 at 1:25
  • 1
    @EvanCarroll I'm not familiar with RDS, and assumed a PostGIS tag always implied PostgreSQL was the underlying DBMS. If there are too many exceptions to that superficial understanding perhaps its tag wiki and excerpt need a note added to that effect. – PolyGeo Sep 12 '17 at 1:31
10

You should use an SQL wrapper instead. plpgsql wrappers tend to be slower since they are not inlined and you are forcing an intermediary step too which can get slow the bigger the geometry.

So write your function as follows:

CREATE OR REPLACE FUNCTION expand(geom geometry,value double precision)
RETURNS geometry AS
$$
 SELECT st_expand(geom,value);
$$
LANGUAGE sql IMMUTABLE;
| improve this answer | |
4

You can do this another way by there is a file called legacy.sql

postgis-2.2/legacy.sql

Warning don't actually use these unless you're on Postgis 2.2 If you've got 3.3, look for your own postgis-2.3/legacy.sql

-- Deprecation in 1.2.3
CREATE OR REPLACE FUNCTION Expand(box2d,float8)
  RETURNS box2d
  AS '$libdir/postgis-2.2', 'BOX2D_expand'
  LANGUAGE 'c' IMMUTABLE STRICT;

-- Deprecation in 1.2.3
CREATE OR REPLACE FUNCTION Expand(box3d,float8)
  RETURNS box3d
  AS '$libdir/postgis-2.2', 'BOX3D_expand'
  LANGUAGE 'c' IMMUTABLE STRICT;

-- Deprecation in 1.2.3
CREATE OR REPLACE FUNCTION Expand(geometry,float8)
  RETURNS geometry
  AS '$libdir/postgis-2.2', 'LWGEOM_expand'
  LANGUAGE 'c' IMMUTABLE STRICT;

So you run

psql -d <database> -f <path to legacy.sql>
| improve this answer | |
  • We have Amazon RDS, they don't allow us to run this. But this was the first solution I thought, it definitely works. Can't upvote since I don't have points, but this is a legitimate answer – Pavanraotk Aug 18 '17 at 7:43

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.