[postgis-users] updating points based on the poly they lie within

[P Kishor]

I have been tinkering with PostGIS for the past few hours, and am
simply amazed by its capabilities (yes, I've been living under a
rock).

I have two tables, one of points (say, cities), and the other of polys
(say, states). I want to update the column cities.state_id =
states.state_id for the state it falls within. A little bit of
snooping around, and I got the following query working

SELECT c.gid, s.state_id
FROM cities c, states s
WHERE c.the_geom && s.the_geom
 AND WITHIN(c.the_geom, s.the_geom)

which finds for me the state_ids for each city. A few newbie questions --

1. am I doing the right thing above?
2. what would the syntax be for updating c.state_id with the state_id
of the state it falls within? I am thinking something like

UPDATE cities
SET state_id = (
  SELECT s.state_id
  FROM cities c, states s
  WHERE c.the_geom && s.the_geom AND WITHIN(c.the_geom, s.the_geom)
)
WHERE EXISTS (
  SELECT s.state_id
  FROM cities c, states s
  WHERE c.the_geom && s.the_geom AND WITHIN(c.the_geom, s.the_geom)
);

Am I out of whack here?

3. Are indexes on the_geom enough to speed this up?

I have been reading the following point-in-poly thread
<http://postgis.refractions.net/pipermail/postgis-users/2006-March/011678.html>,
and it was not very encouraging speed-wise. However, I haven't
experienced major slow down yet. Besides, this calculation is to be
performed very infrequently, so speed is important but not super
important.

Many thanks in advance for all suggestions.

-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.ies.wisc.edu/
Open Source Geospatial Foundation https://edu.osgeo.org/