Access PostGIS Functions from plv8

Question

I am trying to write a plv8 function that returns an integer representing a UTM zone value. The function utilizes PostGIS functions. Here is the (not-functioning) idea:

CREATE OR REPLACE FUNCTION utm_z(geometry)
  RETURNS integer AS
  $$
    var geom_geog = st_transform($1, 4326);
    var utm_zone = Math.floor((st_x(geom_geog)+180)/6)+1;
    return utm_zone;
  $$ LANGUAGE plv8;

When invoking that function I get

ERROR: ReferenceError: st_transform is not defined

How am I able to access those PostGIS functions from inside plv8?

EDIT: PostGIS is installed and functioning in this DB.

score 1 · Answer 1 · answered Mar 02 '16 at 01:03

Here is a working version of the function. Surely there must be a better way to do this in plv8...?

CREATE OR REPLACE FUNCTION utm_z(geometry) RETURNS integer AS $$ var geom_geog_wkt = plv8.execute("SELECT st_transform($1::geometry, 4326);", $1)[0].st_transform; var utm_zone = Math.floor( plv8.execute( "SELECT (st_x($1::geometry)+180)/6+1 AS \"utm_z\"", geom_geog_wkt )[0].utm_z ); return utm_zone; $$ LANGUAGE plv8;

Running plv8.execute() returns an array of objects. In the function above, I grab the 0th array object, and then access that object's value using the key, which is the first function name invoked inside the statement.

score 0 · Answer 2 · answered Mar 01 '16 at 18:09

0

First you need install Postgis to postgres

http://postgis.net/install/

Then add the postgis extension to your db

answered Mar 01 '16 at 18:09

Juan Carlos Oropeza

47,252
12
78
118

Thanks for that, I've definitely got PostGIS installed in this db. Updated question with screenshot. – Joebocop Mar 01 '16 at 18:20
Looks like is a problem with plv8. Did you try create the same function using `LANGUAGE plpgsql` to test everything is ok? – Juan Carlos Oropeza Mar 01 '16 at 18:28
Yes, I am actually trying to convert the function from an existing plpgsql one, into this new plv8 one. plpgsql function works fine, but I'm not clear on how to access PostGIS functions from inside a plv8 block. – Joebocop Mar 01 '16 at 18:32
I tought was the extension problem. But I will leave my answer here in case other think the same. I also found this one, maybe help http://stackoverflow.com/questions/30627205/its-possible-to-call-functions-in-another-schema-from-within-plv8-functions?rq=1 – Juan Carlos Oropeza Mar 01 '16 at 18:36

Access PostGIS Functions from plv8

2 Answers2