1

I am using PostgreSQL 9.2/PostGIS 2.0. I have a table of house addresses and a table of contour lines which are as follows:

address_list

house_id | geom (point, 4283)
---------+---------
    1    | Point
    2    | Point

sa_elevation

   gid   | altitude  |geom (MultiLineString, 4283)
---------+-----------+-----------------
    1    |  100m     | 
    2    |  550m     | 

I am trying to compute the altitude for each house in the address_list table (approx 6,000 houses) using a 'nearest neighbour' approach ie. find the nearest contour line to the home and tell me what the altitude is for that contour line. My syntax is included below. I use a left join because I don't know if all houses will have a contour line within 3km (following PostGIS in Action p. 137). Note that 3112 is in metres. I've tested the syntax on 5 houses and it works.

But the problem is that if I run it on the whole table and leave it running all night it never seems to finish. I have run vacuum analyse and haves spatial indices on each table. Could someone suggest a faster procedure? Or is there anything else I should do? Many thanks.

SELECT DISTINCT ON(a.house_id) 
    a.house_id, 
    e.altitude AS altitude
FROM address_list AS a
LEFT JOIN sa_elevation AS e 
ON ST_Dwithin (ST_Transform(a.wkb_geometry, 3112),ST_Transform(e.wkb_geometry, 3112), 3000)
ORDER BY a.house_id, ST_Distance(ST_Transform(a.wkb_geometry,3112), ST_Transform(e.wkb_geometry,3112));

The explain plan is:

   Unique  (cost=232208649.81..232208684.17 rows=6871 width=4109)
  ->  Sort  (cost=232208649.81..232208666.99 rows=6871 width=4109)
        Sort Key: a.house_id, (st_distance(st_transform(a.wkb_geometry, 3112), st_transform(e.wkb_geometry, 3112)))
        ->  Nested Loop Left Join  (cost=0.00..232183921.91 rows=6871 width=4109)
              Join Filter: ((st_transform(a.wkb_geometry, 3112) && st_expand(st_transform(e.wkb_geometry, 3112), 3000::double precision)) AND (st_transform(e.wkb_geometry, 3112) && st_expand(st_transform(a.wkb_geometry, 3112), 3000::double precision)) AND _st_dwithin(st_transform(a.wkb_geometry, 3112), st_transform(e.wkb_geometry, 3112), 3000::double precision))
              ->  Seq Scan on address_list a  (cost=0.00..212.71 rows=6871 width=36)
              ->  Materialize  (cost=0.00..46527.78 rows=42852 width=4073)
                    ->  Seq Scan on sa_elevation e  (cost=0.00..24845.52 rows=42852 width=4073)
  • We need the EXPLAIN plan of the query. – Jakub Kania Sep 27 '13 at 6:05
  • Thanks Jakub. I have edited the question, is that what you needed? I didn't know the explain plan to be honest. – Mike Sep 27 '13 at 6:29
  • Oh, it might be a little slow taken it's comparing every of 6871 houses with each one of 42852 contours. That's some 294 milion comparisons. – Jakub Kania Sep 27 '13 at 12:03
  • Many thanks Jakub. Of course I should've known you could index on transform. Will check documentation more thoroughly next time.Thank you. – Mike Sep 27 '13 at 12:21
4

What you need are indexes. You may have indexes on your tables right now but since you're using ST_TRANSFORM they're not being used. Indexing on transform is even mentioned on the function's page: http://postgis.net/docs/ST_Transform.html

CREATE INDEX idx_address_3112
  ON address_list
  USING gist
  (ST_Transform(wkb_geometry, 3112))

CREATE INDEX idx_altitude_3112
  ON sa_elevation
  USING gist
  (ST_Transform(wkb_geometry, 3112))

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.