[postgis-users] Spatial Join Performance

[Paul Ramsey]

Have you tried

SELECT taxlot,zone_
FROM  taxlot,zoning
WHERE
   taxlot.the_geom && zoning.the_geom
AND
   distance(zoning.the_geom,taxlot.the_point_geom) < 0.01

?


On 17-Nov-06, at 4:28 PM, Eric Shuman wrote:

> Paul, Brent, etc...
>
> So I have been trying the various work arounds for the TOAST issue,  
> but
> can't seem to force the query to use the spatial index on the table in
> question.  None of the work arounds seem to change anything.  Below  
> are some
> of my stats and the query with query plan.
>
>
> SELECT version();
> "PostgreSQL 8.0.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC)  
> 3.3.5
> (Debian 1:3.3.5-13)"
>
> SELECT postgis_version();
> "1.0 USE_GEOS=1 USE_PROJ=0 USE_STATS=1"
>
>
> SET enable_seqscan=OFF;
> SET random_page_cost=1;
> SET effective_cache_size=1000; -- Have tried up to 10000000 as well
>
> EXPLAIN
>
> SELECT taxlot,zone_
> FROM  taxlot,zoning
> WHERE
>   taxlot.the_geom && zoning.the_geom
> AND
>   contains(zoning.the_geom,taxlot.the_point_geom)
>
> ---------  Query Plan --------------------------
> "Nested Loop  (cost=100000000.00..100003612.20 rows=62135 width=23)"
> "  Join Filter: contains("outer".the_geom, "inner".the_point_geom)"
> "  ->  Seq Scan on zoning  (cost=100000000.00..100000132.49 rows=1149
> width=1967)"
> "  ->  Index Scan using taxlot_geom_index on taxlot   
> (cost=0.00..3.01 rows=1
> width=492)"
> "        Index Cond: (taxlot.the_geom && "outer".the_geom)"
> ------------------------------------------------
>
> Some of the things I have also tried:
> -- SELECT AddGeometryColumn 
> ('','taxlot','the_point_geom','-1','POINT',2);
> -- update taxlot set the_point_geom = centroid(taxlot.the_geom);
> -- create index point_index on taxlot USING GIST (the_point_geom);
> -- vacuum analyze taxlot;
> -- vacuum analyze zoning;
>
> -- SELECT addGeometryColumn('','zoning','bbox','-1','GEOMETRY','2');
> -- UPDATE zoning set bbox = Envelope(Force_2d(the_geom));
>
>
> Would the fact that I don't have a srid set on the tables matter?
>
>
>
> Thanks again for all the help!
>
> ---------------
> Eric Shuman
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users