2

In my application, I am rendering Geoserver layer on map. Base database for Geoserver is PostGresql.

Below give is the request that call WMS layer. You can see, I am passing multiple viewParams as key value(layerid,centerlat and centerlong)

http://localhost:8080/geoserver/wms?viewparams=layerid:9;centerlat:39.44043541908485;centerlong:-83.66638183593751&service=WMS&request=GetMap&layers=myWorkSpace%3Avw_GetLayerData&styles=&format=image%2Fpng&transparent=true&version=1.1.1&tiled=true&height=256&width=256&srs=EPSG%3A3857&bbox=-9549125.069610499,4618019.500877211,-9470853.552646479,4696291.017841231

In Geoserver, I have created a view that run the below given script

select geom from fn_getlayerdata('%layerid%','%centerlat%','%centerlong%')

fn_getlayerdata (integer,float,float): This is a function created in PostgreSQL.

Problem is that fn_getlayerdata function is not called. But If change the Geoserver view as given below it works.

select geom from fn_getlayerdata(%layerid%,88.78,-90.89)

function fn_getlayerdata returns table. Strange thing is that in fn_getlayerdata function value of centerlat and centerlong is not stored in tempcenterlocation table. Also there is no exception.

   CREATE OR REPLACE FUNCTION fn_getlayerdata(
                  layerid integer,centerlat float,centerlong float)
              RETURNS TABLE(geom geometry) 
              LANGUAGE 'plpgsql'
           VOLATILE 
        AS $BODY$
       DECLARE exec_error VARCHAR;
      BEGIN

INSERT INTO tempcenterlocation(centerlat,centerlong)
values(centerlat,centerlong);
RETURN QUERY
select geom from myTable where dataid>=layerid;


 EXCEPTION WHEN Others THEN exec_error:=SQLERRM;
 Insert Into public."logtable" ("id","errormessage","idtype") VALUES 
      (10,exec_error,'fn_getlayerdata');
 END;

    $BODY$;

       ALTER FUNCTION fn_getlayerdata(integer,float,float)
           OWNER TO postgres;

Any Idea What wrong I am doing?

1

In the view, which you defined in GeoServer, you are passing all parameters as strings. Perhaps you should change the function's parameters' data types. Try converting them to strings and then cast them to integer/float inside the function, but first try with hardcoded values inside your function, without parameters:

CREATE OR REPLACE FUNCTION fn_getlayerdata()
  RETURNS TABLE(geom geometry) 
  LANGUAGE 'plpgsql'
  VOLATILE 
  AS $BODY$

BEGIN

  RETURN QUERY
  select geom from myTable where dataid>=9;

END;
$BODY$;

Also, you should check the GeoServer log for errors.

  • I have already tried this and no success. I got following error in log file DEBUG [org.geoserver.ows] - Could not find kvp parser for: 'layerid'. Storing as raw string. DEBUG [org.geoserver.ows] - Could not find kvp parser for: 'centerlat'. Storing as raw string. DEBUG [org.geoserver.ows] - Could not find kvp parser for: 'centerlong'. Storing as raw string. – M.Sharma Sep 14 '17 at 3:58
  • In the geoserver sql view my statement was: SELECT geom from fn_getlayerdata('%layerid%','%centerlat%', '%centerlong%') – M.Sharma Sep 14 '17 at 3:58
  • It looks to me that we can not call Postgresql function in geoserver sql view. – M.Sharma Sep 14 '17 at 12:41
  • @M.Sharma: it is possible, I have just tested it on my local GeoServer (v2.10.2), connecting po Postgres 9.4. Maybe try first creating a function without parameters, then add layerid and then centerlat, centerlong. Also, try your function without DML statements - get rid of INSERT statements. (edit: I have edited the function body in my answer accordingly) – Jure M. Sep 14 '17 at 12:50

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.