<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2900.2668" name=GENERATOR></HEAD>
<BODY style="MARGIN: 4px 4px 1px; FONT: 10pt Tahoma">
<DIV>Postgres 8.0.1 on an up-to-date Gentoo system. </DIV>
<DIV>POSTGIS="1.1.0CVS" GEOS="2.1.2" PROJ="Rel. 4.4.8, 3 May 2004" USE_STATS DBPROC="0.3.0" RELPROC="0.3.0"</DIV>
<DIV> </DIV>
<DIV>I'm struggling to get anything but very simple PostGIS queries to work fast, if at all. I'm working both from the psql terminal and with Python via psycopg. What I really want to do is select fires that fall within a buffer of powerlines and then match those fire to 'towers'. So far I've been attempting this in one query and relying on PostGRES query planner to optimise it. I've tried forcing bounding box queries before doing distance predicates and so forth. My features are in latlong and buffer and distance operations need to be in metres so I also need to do some on-the-fly GeomUnions, transforms, buffers and expands.</DIV>
<DIV> </DIV>
<DIV>Here is a simple query that does not even work predictably and as you can see doesn't even use the Gist indexes as it should. What seems to be concerning are the LWGEOM_gist_joinsel notice and the OM_gist_joinsel 'Examine'.</DIV>
<DIV> </DIV>
<DIV>Any suggestions as to query approach or what these errors mean and what to do about them?</DIV>
<DIV> </DIV>
<DIV>many thanks</DIV>
<DIV> </DIV>
<DIV>Gavin</DIV>
<DIV>
<HR>
</DIV>
<DIV> </DIV>
<DIV>SET enable_seqscan TO off;</DIV>
<DIV>explain SELECT t.gid,t.objectid,t.id,t.sub_cat,t.lat,t.longx,f.fid,astext(f.the_geom) FROM towers t,fire f<BR>WHERE (f.tick > '2005-07-18') AND (expand(f.the_geom,.01) && expand(t.the_geom,.01));</DIV>
<DIV>SET enable_seqscan TO on;</DIV>
<DIV><BR><STRONG>NOTICE: LWGEOM_gist_joinsel called with arguments that are not column references</STRONG><BR></DIV>
<DIV> gid | objectid | id | sub_cat | lat | longx | fid | astext<BR>-------+----------+-------+-------------+--------------+-------------+--------+-----------------------<BR> 47323 | 45547 | 47322 | 400kv_tower | -29.31961943 | 30.46373380 | 235516 | POINT(30.453 -29.339)<BR> ...skipping...<BR> 76589 | 85420 | 76588 | 275kv_tower | -25.39751241 | 30.91836298 | 233293 | POINT(30.931 -25.415)<BR>(4178 rows)</DIV>
<DIV> </DIV>
<DIV><STRONG>Examine: OM_gist_joinsel called with arguments that are not column references</STRONG><BR>
<HR>
</DIV>
<DIV> QUERY PLAN <BR>-----------------------------------------------------------------------------------------------------------------------<BR> Nested Loop (cost=334.95..7621023.93 rows=1386 width=90)<BR> Join Filter: (expand("inner".the_geom, 0.01::double precision) && expand("outer".the_geom, 0.01::double precision))<BR> -> Seq Scan on towers t (cost=0.00..2129.57 rows=76657 width=86)<BR> -> Materialize (cost=334.95..371.09 rows=3614 width=29)<BR> -> Seq Scan on fire f (cost=0.00..331.34 rows=3614 width=29)<BR> Filter: ((tick)::text > '2005-07-18'::text)<BR>(6 rows)<BR>
<HR>
</DIV>
<DIV>espacio-# \d fire<BR> Table "public.fire"<BR> Column | Type | Modifiers <BR>-------------+-----------------------+-------------------------------------------------------<BR> fid | integer | not null default nextval('public.fire_fid_seq'::text)<BR> origin | integer | not null<BR> tick | character varying(64) | not null<BR> temperature | real | not null<BR> confidence | real |<BR> spread | real |<BR> the_geom | geometry | not null<BR>Indexes:<BR> "fire_pkey" PRIMARY KEY, btree (fid)<BR> "fire_ind_the_geom" gist (the_geom) CLUSTER<BR> "fire_ind_tick" btree (tick)<BR>Check constraints:<BR> "enforce_dims_the_geom" CHECK (ndims(the_geom) = 2)<BR> "enforce_geotype_the_geom" CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL)<BR> "enforce_srid_the_geom" CHECK (srid(the_geom) = 4326)</DIV>
<DIV>
<HR>
</DIV>
<DIV>espacio-# \d towers<BR> Table "public.towers"<BR> Column | Type | Modifiers<BR>----------+-------------------+---------------------------------------------------------<BR> gid | integer | not null default nextval('public.towers_gid_seq'::text)<BR> objectid | bigint |<BR> id | character varying |<BR> sub_cat | character varying |<BR> lat | numeric |<BR> longx | numeric |<BR> tenth | bigint |<BR> the_geom | geometry | not null<BR>Indexes:<BR> "towers_pkey" PRIMARY KEY, btree (gid)<BR> "tower_geom_index" gist (the_geom) CLUSTER<BR>Check constraints:<BR> "enforce_dims_the_geom" CHECK (ndims(the_geom) = 2)<BR> "enforce_geotype_the_geom" CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL)<BR> "enforce_srid_the_geom" CHECK (srid(the_geom) = 4326)<BR></DIV></BODY></HTML>