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

[P Kishor]

On 7/25/06, Gregory S. Williamson <gsw at globexplorer.com> wrote:
> It does indeed look like you are in the right ballpark at least.

Thanks Greg. Close, but no...

>
> You want to make sure you have a spatial (GIST) index on the relevant
> columns, both polygon and point.

If by "relevant columns" you mean the_geom for both, then yes, I
created indexes on those via

CREATE INDEX idx_states_the_geom ON states USING gist(the_geom);
CREATE INDEX idx_cities_the_geom ON cities USING gist(the_geom);

but...

> In earlier versions of postgres/GIS you had to run an explicit command
> [SELECT * FROM update_geometry_stats('table name', 'column name');
> in newer (postgres 8.x I think, and postGIS > 1. something) the stats are
> updated with the regular "ANALYZE tablename;" command.
>

... it seems you mean something else when you say "spatial index." I
will read up on this.


> The bounding box operator (&&) in your queries hopes to get the planner
> to use the indexes. It may not use them anyway if you are trying to access
> a significant number of rows of the relevant tables (since 100,000 indexed
> reads are more disk i/o than a sequential scan of the table, if 100,000 is
> more than a tiny percent of your data).

100k is a very small % of my point data. I am talking about 5.25
million points against 200,000 polys. All points are guaranteed to lie
within a poly, but all polys may not have a point (for example, all
cities belong to a state, but all states may not have a city). Of
course, as I said earlier, points and polys are in different tables. I
tried a few variations on the the UPDATE command, but failed. Here is
one I tried, and the error I got --

UPDATE cities
SET cities.state_id = states.state_id
FROM cities, states
WHERE cities.the_geom && states.the_geom AND
  WITHIN(cities.the_geom, states.the_geom)


ERROR:  current transaction is aborted, commands ignored until end of
transaction block


>
> I grabbed this example from a some code in a plpgsql function:
> UPDATE raw_log_data
> SET fips = p_fips
> WHERE (req_pnts && sp_county_geom)
> AND (distance(req_pnts, sp_county_geom) <= 0);
>
> The req_pnts are a column with point data and sp_county_geom is the area
> of interest. In this case we iterate through our areas of interest -- that turned
> out to be faster than trying to do all of them at once (think about 8000
> polygons and some log files with a few million points)
>
> The && does make use of indexes and the distance () test seems to be a bit
> faster than other methods of making sure a point is on or in a polygon.


I will try out the above variation. Any speed bump would be nice. Just
doing a plain vanilla GIS point-in-poly overlay takes about 4 days to
finish the process, so cutting that down with the help of PostGIS
would be really nice.


>
>
> -----Original Message-----
> From:   postgis-users-bounces at postgis.refractions.net on behalf of P Kishor
> Sent:   Mon 7/24/2006 10:08 PM
> To:     Postgis
> Cc:
> Subject:        [postgis-users] updating points based on the poly they lie within
>
> 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/
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
> !DSPAM:44c5a74a216985856016513!
>
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>


-- 
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/