Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a PostGIS/Postgresql DB that has two tables in it. One with point geometries and the other that represents country boundaries as polygons. I would like to add the country name that each point intersects with to each row in my point table. Maybe as one big update query. I think this is possible to do using straight SQL but I don't know where to begin. Any advice on this would be greatly appreciated...

share|improve this question

Another option, without needing the function

update points set country = from 
    select points.oid, as country from
    countries INNER JOIN points on st_contains(countries.wkb_geometry,points.wkb_geometry)
) t1 
where t1.oid = points.oid

I suspect (although I haven't tested) that this will be faster than using a nested function like in your example.

My output from running explain (hopefully your looks similar). If you have got more Seq Scan results, then that is something to look at, perhaps the indexes aren't quite set up properly.

Update on points  (cost=1.18..29.40 rows=121 width=129)"
  ->  Nested Loop  (cost=1.18..29.40 rows=121 width=129)"
        Join Filter: _st_contains(countries.geometry, public.points.geometry)"
        ->  Hash Join  (cost=1.18..2.37 rows=28 width=220)"
              Hash Cond: (public.points.oid = public.points.oid)"
              ->  Seq Scan on points  (cost=0.00..1.08 rows=28 width=114)"
              ->  Hash  (cost=1.08..1.08 rows=28 width=110)"
                    ->  Seq Scan on points  (cost=0.00..1.08 rows=28 width=110)"
        ->  Index Scan using "countries_Idx" on countries  (cost=0.00..0.91 rows=1 width=414)"
              Index Cond: (geometry && public.points.geometry)"
share|improve this answer
Awesome! This appears to be a lot faster too. Thanks! – AdamEstrada Nov 15 '12 at 3:44

OK...I did a little bit of hacking and found that a SQL FUNCTION get me most of the way there. Anyone have any thoughts on taking this to the bridge?

       country_geom geometry
    ) RETURNS TABLE(country text) AS $$
        SELECT as country FROM  
                    geonames d, world_borders b WHERE
                    $1 && b.wkb_geometry 
                    AND intersects($1, b.wkb_geometry) ;

UPDATE geonames 
    SET country = val
    FROM (SELECT getcountry(point_geom) FROM geonames) AS val
share|improve this answer

Your Answer


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.