I was trying to create a PostGIS procedural function which will perform a particular operation depending upon the user's provided coordinates. But since the user can provide any number of coordinates, I had to make the procedural function for all the possibilities (a painful approach). Example SQL query is below

select geom from test_4points('ways', 28.94603, 41.00764, 28.95402, 41.01789, 28.96145, 41.00522, 28.96650, 41.01191) -- Four points SQL
select geom from test_5points('ways', 28.94603, 41.00764, 28.95402, 41.01789, 28.96145, 41.00522, 28.96650, 41.01191, 28.96650, 41.01191) -- Five points SQL
select geom from test_6points('ways', 28.94603, 41.00764, 28.95402, 41.01789, 28.96145, 41.00522, 28.96650, 41.01191, 28.96650, 41.01191, 28.96650, 41.01191) -- Six points SQL

And in order to deal with each of these functions I had to create SQL View in my Geoserver for each one of them, like the following:

SELECT geom FROM test_4points('ways', %x1%, %y1%,%x2%,%y2%,%x3%,%y3%,%x4%,%y4%) ORDER BY seq -- for Four points
SELECT geom FROM test_5points('ways', %x1%, %y1%,%x2%,%y2%,%x3%,%y3%,%x4%,%y4%,%x5%,%y5%) ORDER BY seq -- for Five points
SELECT geom FROM test_6points('ways', %x1%, %y1%,%x2%,%y2%,%x3%,%y3%,%x4%,%y4%,%x5%,%y5%,%x6%,%y6%) ORDER BY seq -- for Six points

But now, I have create a modified SQL procedural function which can accept any number of coordinates, like the following:

select geom from test_allpoints('ways', 28.94603,41.00764,28.95402,41.01789,28.96145,41.00522,28.96650,41.01191) -- Four Points SQL, Notice that same function "test_allpoints" is taking any number of pts
select geom from test_allpoints('ways', 28.94603,41.00764,28.95402,41.01789,28.96145,41.00522,28.96155,41.01715,28.96650,41.01191) -- Five Points SQL
select geom from test_allpoints('ways', 28.94603,41.00764,28.95402,41.01789,28.96145,41.00522,28.96155,41.01715,28.94309,41.02114,28.96650,41.01191) -- Six Points SQL

How to create an SQL view in Geoserver in order to interact with this SQL 'test_allpoints()' functions query? Since there is no limit of the users provided coordinates, how to declare the arguments in the SQL View.

The "test_allpoints()" pl/pgsql function is of the following type:

test_allpoints(character varying, double precision[])

UPDATE1: An elaborate version of my test_allpoints() plpgsql function is here, but my problem is not related with the pl/pgsql fun but the geoserver.


  • You can the array version of ST_MakeLine to create a linestring and then create a Polygon from that. However, this expect an array of points, and it looks like you are passing in an array of x,y,x1,y1, etc. Are you able to use a plpgsql function, which could parse your double array into an array of points? – John Powell Dec 17 '14 at 11:32
  • I am a bit confused. I didn't get your point. Why you are trying to use ST_MakeLine? This is not what I want. Am I missing any point? – Zia Dec 17 '14 at 12:44
  • Never mind. I misunderstood the question :D. You already have a working function. Maybe you should put link to what you have written in the question. – John Powell Dec 17 '14 at 13:07
  • @John: We all really appreciate your activeness on gis.stakexhange :) – Zia Dec 17 '14 at 13:18
  • Thanks, not always very helpful though :D – John Powell Dec 17 '14 at 13:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.