0

So basically there's a buffer view defined by:

CREATE OR REPLACE VIEW niceview2 AS 
select 'test'::text AS route_type,
st_setsrid(st_union(st_buffer(a.geom, 25::double precision)), 27700) AS geom
from testdata a

I need to have several values counted in it from a few tables. I've had success using:

(select sum(count) from testpoints b where st_contains(st_setsrid(st_union(st_buffer(a.geom, 25::double precision)), 27700), b.geom) ) as units

problem is that this will apparently recalculate the buffers and then do the st_contains. And I need to do it 5 times for 5 different columns so this layer would be really slow

Is there a more efficient way of achieving this outcome?

  • 1
    any progress on that? if sth. doesn't work, give a shout... – ThingumaBob Apr 12 '18 at 11:52
1

If I get your question right, you'd want to move the buffer creation into a sub-query.

CREATE OR REPLACE VIEW niceview2 AS
  SELECT a.route_type,
         sum(b.<value>) AS b_value,
         sum(c.<value>) AS c_value,
         a.geom
  FROM (
    SELECT 'test'::text AS route_type,
           ST_SetSRID(ST_Union(ST_Buffer(geom, 25::DOUBLE PRECISION)), 27700) AS geom
    FROM testdata
  ) AS a
  JOIN testpoints AS b
    ON ST_Contains(a.geom, b.geom)
  JOIN othertestpoints AS c
    ON ST_Contains(a.geom, c.geom)

Now, if I understand correctly what you have in mind with your query, I strongly suggest using ST_DWithin instead; your query looks like you want to sum up values from other tables based on some distance of their respective geometries to the base table's geometries.
If that is the case, ST_DWithin is the better choice, since you can make use of the spatial indexes. This will greatly reduce execution time.
(Note: I dare to assume that you want to group your results by road_type, despite in your example you union the whole table. Also, this query still returns the union of buffers around the grouped geometries in case you really want those):

CREATE OR REPLACE VIEW niceview2 AS
  SELECT a.road_type,
         sum(b.<value>) AS b_value,
         sum(c.<value>) AS c_value,
         ST_Union(ST_Buffer(a.geom, 25)) AS geom
  FROM (
    SELECT road_type,
           ST_Collect(geom) AS geom
    FROM testdata
    GROUP BY road_type
  ) AS a
  JOIN testpoints AS b
    ON ST_DWithin(a.geom, b.geom, 25)
  JOIN othertestpoints AS c
    ON ST_DWithin(a.geom, c.geom, 25)
  GROUP BY a.road_type

In both cases, you can add more joins if needed.

Another thought:
A view is basically a named query, no data is stored or cached; each time you open that view, the query executes again. If you want better performance and automatic updates when your tables are updated, consider a materialized view.

(I wrote this out of my head, couldn't test for syntax or with data; the structure should be fine, though)

  • 1
    sorry I had a bunch of stuff in between so had to come up with a temporary non ideal solution. will test this tomorrow and thanks! You're right about the dwithin though. I just wasn't sure how it handles the points that overlap – Luffydude Apr 12 '18 at 16:26
  • @Luffydude ah, you´re right, I haven´t taken that into account! As it stands, it will count points multiple times...I will update that query! – ThingumaBob Apr 13 '18 at 9:45
  • Man I couldn't test your query properly since the dataset that I'm trying to join is too large but it seems that it works so I gave you the tick as a thank you. another optimization that I need to do is on your "testpoints" table, i need to use a where condition where value is over 50. Do I need to do a subselect for that? First join would look like JOIN (select * from testpoints where cound >50) as b on st_dwithin – Luffydude Apr 17 '18 at 11:25

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.