0

I have a PostGIS database containing entities A with a location (point) and entities B with an area (polygon).

I have a join table C that basically tracks who was where and I want to update it. It's a simple counter that gets incremented, no further dependencies.

It's easy enough to find out who is where:

SELECT a.id, b.id FROM a, b WHERE ST_Contains(b.poly, a.location);

But is it possible to update table C in pure SQL? It's structure is simple:

    Column    |   Type   |                           Modifiers                            
--------------+----------+----------------------------------------------------------------
 id           | integer  | not null
 a_id         | integer  | 
 b_id         | integer  | 
 counter      | smallint | not null

What I want is to run a query at fixed intervalls that increments counter depending on location. However, this could be the first instance, so the matching row a_id, b_id is not guaranteed to already exist.

Possible?

Tom
  • 2,688
  • 3
  • 29
  • 53
  • How is the value of counter determined? Based on unique values of a_id and b_id in combination? – John Powell Jun 12 '14 at 09:29
  • No, it's just a simple counter. I want to run a query at fixed intervals that simply updates it. Think +1 every hour. – Tom Jun 12 '14 at 09:31
  • Shouldn't counter just be a serial type then that auto increments? And how is c.id determined? – John Powell Jun 12 '14 at 09:37
  • c.id is simply the primary key of the table. I'm not sure what you mean by serial type, but I do know that I want to both increment and decrement the counter, and sometimes by more than 1, that's why I've used a smallint type. – Tom Jun 12 '14 at 09:48
  • Serial is just an int data type that auto increments. – John Powell Jun 12 '14 at 09:50
  • Deleted my answer because it is stupid. What you need in an upsert, which can be accomplished via a common table expression. See this very interesting article http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-in-postgresql I ran out of time today, but will try tomorrow, if someone else doesn't. – John Powell Jun 12 '14 at 18:13
  • Or you might want to do separate insert and update queries for when the a_id and b_id combination exists or not in counter table. This will be easier to write, for sure, but less fun. – John Powell Jun 13 '14 at 07:28
  • Why not just use a `VIEW` with `COUNT()`? `MATERIALIZED VIEW` if performance is a problem. – Jakub Kania Jun 19 '14 at 08:48
  • Might work - what would the actual definition of the view look like? – Tom Jun 19 '14 at 18:56

0 Answers0