current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
up vote 2 down vote favorite
1

I have a fair bit of experience with Postgres and PostGIS running queries and scripts, but no experience creating functions. I feel like what I want to achieve is rather more complex than the examples I've seen so I'm hoping someone can help me out.

I'm creating a web application that allows users to update lot boundary records stored in a spatial table based on the intersection with a polygon drawn on a map and some values entered into a form. I don't know if there is a way of storing a subset of database records in an array and iterating over it, updating each record in turn, or if I have to run separate update scripts within the function. I'm also not sure if its possible to pass in a table name as an argument to a function, as I'd like to just run the function and have it work on different tables.

If I would create a function that would do all the things I wanted it to do by simply running a bunch of separate UPDATE scripts, it might look like the following (function not actually tested):

CREATE OR REPLACE FUNCTION updateLots(wkt_geom text, tablename varchar(25), landuse varchar(25), density NUMERIC(4,1))
  RETURNs VOID AS
$$
  BEGIN
    UPDATE [tablename] SET landuse = [landuse] WHERE ST_Intersection(geom, GeomFromWKT([wkt_geom], 3857));
    UPDATE [tablename] SET density = [density] WHERE ST_Intersection(geom, GeomFromWKT([wkt_geom], 3857)) WHERE landuse = 'Residential';
    UPDATE [tablename] SET density = NULL WHERE ST_Intersection(geom, GeomFromWKT([wkt_geom], 3857)) WHERE landuse != 'Residential';
    UPDATE [tablename] SET yield = area / 10000 * [density] WHERE ST_Intersection(geom, GeomFromWKT([wkt_geom], 3857));
END;
$$
LANGUAGE plpgsql;

While this approach would save me from running several nested database scripts from the server, it seems inefficient and Postgres will not accept tablename as an argument as is. Hence I'm wondering the following 2 things:

  1. Is there a way to create a subset of a table based on the spatial intersection with a provided geometry and iterate over each record, performing the necessary updates? If yes, how might this function be specified?
  2. Can I provide a table name as an argument to a function?

I'm not sure of the best way to proceed, so if someone can tell me if what I want to do is possible and if so get me started with specifying a function I'd be very appreciative.

Cheers.

share|improve this question
up vote 1 down vote accepted

In PostgreSQL you can pass a table name to a function and then operate on that table, but you would have to EXECUTE a dynamic query, which is inefficient because the query has to be parsed and planned on every function call. If you have only a few tables then it is probably better to just put the command for each of the few tables in one function: the function is bigger but you have to call it only once and the queries can be planned and stored for future usage by the query planner.

Making a subset of the table based on the intersection between geometries is probably not a good thing. Instead, work on your UPDATE command, which can be greatly optimized:

CREATE FUNCTION updateLots(wkt_geom text, lu varchar(25), dens NUMERIC(4,1))
RETURNS void AS $$
BEGIN
  UPDATE t1 SET landuse = lu,
                density = (CASE WHEN lu = 'Residential' THEN dens END), -- ELSE NULL
                yield = area * 0.0001 * dens
  WHERE ST_Intersection(geom, GeomFromWKT(wkt_geom, 3857));

  ...; -- Same for other tables

END; $$ LANGUAGE plpgsql STRICT;

A few notes:

Another potential big cost saver is to pass the wkt_geom as a geometry, not as text. If this can be done in your case then you do not have to do the expensive ST_GeomFromWKT().

Since you want to run the function with a passed table name, you should use the following version:

CREATE FUNCTION updateLots(wkt_geom geometry, tablename varchar(25), lu varchar(25), dens NUMERIC(4,1))
RETURNS void AS $$
BEGIN
  EXECUTE format('
    UPDATE %I SET landuse = %L,
                  density = (CASE WHEN %2$L = ''Residential'' THEN $1 END),
                  yield = area * 0.0001 * $1
    WHERE ST_Intersection(geom, $2)', tablename, lu)
  USING dens, wkt_geom;
END; $$ LANGUAGE plpgsql STRICT;

In this case you should definitely convert the wkt_geom data to a geometry before calling this function once for each table name.

share|improve this answer
    
This looks just about exactly what I need. The one thing is that I do need table names to be dynamic, as each user in the app get's their own table in the DB and new users can register at any time. As such it wouldn't be possible to repeat the UPDATE for multiple tables. I amended your function a bit to EXECUTE a dynamic query, but I'm getting errors using the function as the parameters (e.g. lu and dens) are being interpreted as column names. Any suggestions how to fix that? – user2642365 Sep 16 '15 at 13:40
    
I think I got it sorted now. I just did a bunch of concatenations to build up the UPDATE string with the param names in between. I wonder if there is a cleaner way but this will work. Cheers! – user2642365 Sep 16 '15 at 14:03
    
See updated answer. Using the format() function is generally regarded the best practice. Among other things, such as cleaner code, it guards against SQL injection. – Patrick Sep 16 '15 at 14:10
    
Awesome, that's way cleaner than what I had. I attempted the format() approach as I saw some examples but it wasn't clear to me when to use I%, s% or some of the other options and it whatever I tried didn't work, but I also didn't have the "USING" part or see that in other examples of using format(). Thanks so much. – user2642365 Sep 16 '15 at 18:51
    
A subtle change based upon your first comment: Since you are working with "users in the app" you should be very careful about SQL injection. In the format() function %I and %L (for identifier (table name, column name) and literal, respectively) this is taken care of. So I moved the lu parameter from the USING clause to %L in format(): SQL injection is only possible with text data values. See updated code. – Patrick Sep 17 '15 at 0:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.