<table cellspacing="0" cellpadding="0" border="0" ><tr><td valign="top" style="font: inherit;">Try this:<br><br>Create a table with chopped up polygons derived from your continents table.<br><br>Say you have continents (continent_id, name, the_geom)<br><br>then you have continents_chopped(chop_id, continent_id, continent_name, the_geom)<br><br>Spatial index on continents_chopped.polygon<br><br>SELECT Count(distinct continent_id)<br> FROM c, continents_chopped n<br> WHERE ST_Intersects(c.the_geom, n.the_geom) AND<br> n.continent = 'North America';<br><br>the BB index will very quickly identify a few chopped polygons which contain the point, then the much slower test against all the vertices in the continent polygons is carried out against a few chopped polygons with vastly fewer vertices, making for a much faster operation. <br><br><br><br>This,
from an email I sent a colleague a few weeks ago may be helpful:<br><br>As far as an easy way to generate a grid of specified size cells over a specified area, see:<br>
<br>
http://trac.osgeo.org/postgis/wiki/UsersWikiCreateFishnet<br>
<br>
Thus allowing arbitrary slicing/binning of polygons via a simple overlay operation - ST_Intersection()<br>
<div class="x_BodyFragment"><font size="2"><span style="font-size:10pt">
<div class="x_PlainText"><br>
This can be wrapped up in a shell script which generates the grid, as below: <br>
<br>
#! /bin/bash<br>
<br>
# script to generate an arbitrary grid<br>
# test only<br>
# B Wood 12/11/2011<br>
<br>
<br>
DB=test_grid<br>
<br>
# create db<br>
dropdb $DB<br>
createdb $DB<br>
psql -d $DB -qf /usr/share/postgresql/9.1/contrib/postgis-1.5/postgis.sql<br>
psql -d $DB -qf /usr/share/postgresql/9.1/contrib/postgis-1.5/spatial_ref_sys.sql<br>
<br>
# create function<br>
psql -d $DB -c "CREATE OR REPLACE FUNCTION ST_CreateGrid(<br>
nrow integer, ncol integer,<br>
xsize float8, ysize float8,<br>
x0 float8 DEFAULT 0, y0 float8 DEFAULT 0,<br>
srid integer DEFAULT 4326)<br>
<br>
RETURNS SETOF geometry AS<br>
\$\$<br>
SELECT ST_Translate(cell, j * \$3 + \$5, i * \$4 + \$6)<br>
FROM generate_series(0, \$1 - 1) AS i,<br>
generate_series(0, \$2 - 1) AS j,<br>
(<br>
SELECT setsrid(('POLYGON((0 0, 0 '||\$4||', '||\$3||' '||\$4||', '||\$3||' 0,0 0))')::geometry, \$7) AS cell<br>
) AS foo;<br>
\$\$ LANGUAGE sql IMMUTABLE STRICT;"<br>
<br>
# create table<br>
psql -d $DB -c "create table test_grid<br>
( id serial primary key);"<br>
psql -d $DB -c "select <br>
addgeometrycolumn('','test_grid','geom',4326,'POLYGON',2);"<br>
<br>
# create lat/long grid<br>
# set extent<br>
W=150<br>
E=190<br>
S=-55<br><font size="2"><span style="font-size:10pt">N=-30<br>
SRID=4326<br>
<br>
# set cell size in degrees<br>
SIZE=0.05<br>
<br>
# get cell counts for extent<br>
NX=`echo "($E - $W) / $SIZE" | bc`<br>
NY=`echo "($N - $S) / $SIZE" | bc | tr -d "-"`<br>
<br>
# create grid<br>
psql -d $DB -c "delete from test_grid;"<br>
psql -d $DB -c "insert into test_grid (geom) <br>
select ST_CreateGrid($NY,$NX,$SIZE,$SIZE,$W.0,$S.0,$SRID);"<br>
</span></font><br></div></span></font></div><br><br><br>--- On <b>Wed, 12/21/11, Puneet Kishor <i><punk.kish@gmail.com></i></b> wrote:<br><blockquote style="border-left: 2px solid rgb(16, 16, 255); margin-left: 5px; padding-left: 5px;"><br>From: Puneet Kishor <punk.kish@gmail.com><br>Subject: Re: [postgis-users] speeding up simple pt-in-poly lookups<br>To: "PostGIS Users Discussion" <postgis-users@postgis.refractions.net><br>Date: Wednesday, December 21, 2011, 2:28 PM<br><br><div class="plainMail"><br>On Dec 20, 2011, at 7:21 PM, Paul Ramsey wrote:<br><br>> Chop up the continents into smaller pieces.<br>> <br><br><br>hmmm... I am not sure I understand the above. And then what? UNION each smaller piece query? <br><br><br>> On Tue, Dec 20, 2011 at 4:35 PM, Puneet Kishor <<a ymailto="mailto:punk.kish@gmail.com" href="/mc/compose?to=punk.kish@gmail.com">punk.kish@gmail.com</a>> wrote:<br>>> This is probably a really
basic question... my ST_Within or ST_Intersects selecting points in a continent are way too slow (both take upward of 200 secs).<br>>> <br>>> SELECT Count(c_id)<br>>> FROM c, continents n<br>>> WHERE ST_Intersects(c.the_geom, n.the_geom) AND<br>>> n.continent = 'North America';<br>>> <br>>> <br>>> Both tables have gist indexes on the geometries. The above query has the following plan<br>>> <br>>> "Aggregate (cost=9.66..9.67 rows=1 width=4)"<br>>> " -> Nested Loop (cost=0.00..9.66 rows=1 width=4)"<br>>> " Join Filter: _st_intersects(c.the_geom, n.the_geom)"<br>>> " -> Seq Scan on continents n (cost=0.00..1.10 rows=1 width=32)"<br>>> "
Filter: ((continent)::text = 'North America'::text)"<br>>> " -> Index Scan using pbdb__collections_the_geom on collections c (cost=0.00..8.30 rows=1 width=104)"<br>>> " Index Cond: (c.the_geom && n.the_geom)"<br>>> <br>>> The table c has approx 120K rows, and the continents table has 8 rows.Suggestions on how I can improve this? Yes, the computer is otherwise very swift and modern.<br>>> <br>>> <br>>> <br>>> --<br>>> Puneet Kishor<br><br>_______________________________________________<br>postgis-users mailing list<br><a ymailto="mailto:postgis-users@postgis.refractions.net" href="/mc/compose?to=postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br></div></blockquote></td></tr></table>