Your rows in the second query do look a lot wider. (a) if you can, upgrade to the latest pgsql 8.4 and postgis 1.4, there's big speed benefits to both (b) if you have even a moderately recent postgis, try using
st_intersects(a.the_geom, b.the_geom) instead of && and distance() = 0 again, there's more power to be had there. Paul On Tue, Sep 29, 2009 at 1:35 PM, Ted Spradley <[email protected]> wrote: > > Hi All! > > An elementary question from a new user. Please be patient with my > understanding! > > I am running the well known query to find adjacent polygons that first > compares > polygon bounding boxes, then uses the distance function to (I think) compare > the > actual polygon vertices of the result of the bounding box portion of the > query. > > Query_1 and Query_2 below are, I believe, identical queries. Query_1 has a > runtime > of 71.423 ms, and Query_2 a runtime of 36236.674 ms. > > What is the difference between the two queries? Is the resolution of > Query_1.data, > 0.000278, a resolution producing roughly twice as many points as the > resolution of > Query_2.data, 0.000458 or do I have that backward? > > I have found references in the archives to the width of a row. Is this > likely referring > to the width of the geometry column, meaning number of coordinate pairs? > How does > one do a count query on a column to find the number of pairs that a row > contains? > > Thank you! > Ted S. > > Thank you > > > ################## Query 1 #################################### > > Data: > Spatial_Reference_Information: > Horizontal_Coordinate_System_Definition: > Geographic: > Latitude_Resolution: 0.000278 > Longitude_Resolution: 0.000278 > Geographic_Coordinate_Units: Decimal degrees > Geodetic_Model: > Horizontal_Datum_Name: North American Datum of 1983 > Ellipsoid_Name: GRS1980 > Semi-major_Axis: 6378137 > Denominator_of_Flattening_Ratio: 298.257222 > > Query: > project1=# SELECT DISTINCT a.county, a.state > FROM countyp020 AS a, countyp020 AS b > WHERE b.county='Bexar County' > AND b.state='TX' > AND a.wkb_geometry && b.wkb_geometry > AND distance(a.wkb_geometry, b.wkb_geometry) = 0; > county | state > ----------------------------------------------------+------- > Comal County | TX > Bandera County | TX > Guadalupe County | TX > Atascosa County | TX > Wilson County | TX > Kendall County | TX > Medina County | TX > Bexar County | TX > (8 rows) > > Query Analyse Explain: > project1=# EXPLAIN ANALYSE SELECT DISTINCT a.county, a.state > FROM countyp020 AS a, countyp020 AS b > WHERE b.county='Bexar County' > AND b.state='TX' > AND a.wkb_geometry && b.wkb_geometry > AND distance(a.wkb_geometry, b.wkb_geometry) = 0; > QUERY > PLAN > --------------------------------------------------------------------------------------------------------------------------------------------------- > HashAggregate (cost=20.23..20.24 rows=1 width=45) (actual > time=70.894..70.902 rows=8 loops=1) > -> Nested Loop (cost=4.27..20.22 rows=1 width=45) (actual > time=17.118..70.697 rows=8 loops=1) > Join Filter: (distance(a.wkb_geometry, b.wkb_geometry) = 0::double > precision) > -> Bitmap Heap Scan on countyp020 b (cost=4.27..11.94 rows=1 > width=8285) (actual time=0.080..0.083 rows=1 loops=1) > Recheck Cond: (county = 'Bexar County'::bpchar) > Filter: (state = 'TX'::bpchar) > -> Bitmap Index Scan on idx_countyp020_county > (cost=0.00..4.27 rows=2 width=0) (actual time=0.062..0.062 rows=1 loops=1) > Index Cond: (county = 'Bexar County'::bpchar) > -> Index Scan using countyp020_geom_idx on countyp020 a > (cost=0.00..8.27 rows=1 width=8330) (actual time=9.529..10.248 rows=10 > loops=1) > Index Cond: (a.wkb_geometry && b.wkb_geometry) > Total runtime: 71.423 ms > (11 rows) > ################## End Query 1 ################################# > > ################## Query 2 #################################### > > Data: > <spref> > <horizsys> > <geograph> > <latres>0.000458</latres> > <longres>0.000458</longres> > <geogunit>Decimal degrees</geogunit> > </geograph> > <geodetic> > <horizdn>North American Datum of 1983 in the > 48 contiguous states, the > District of Columbia, Alaska, Hawaii, Puerto Rico, the Virgin Islands of the > United States, and the Pacific Island Areas.</horizdn> > <ellips>Geodetic Reference System 80</ellips> > <semiaxis>6378137</semiaxis> > <denflat>298257</denflat> > </geodetic> > </horizsys> > </spref> > > Query: > tiger=# SELECT DISTINCT a.name, a.statefp > FROM tl_2008_us_county AS a, tl_2008_us_county AS b > WHERE b.name='Bexar' > AND b.statefp='48' > AND a.the_geom && b.the_geom > AND distance(a.the_geom, b.the_geom) = 0; > name | statefp > -----------+--------- > Bandera | 48 > Guadalupe | 48 > Wilson | 48 > Atascosa | 48 > Kendall | 48 > Bexar | 48 > Comal | 48 > Medina | 48 > (8 rows) > > Query Explain Analyse: > tiger=# EXPLAIN ANALYSE SELECT DISTINCT a.name, a.statefp > FROM tl_2008_us_county AS a, tl_2008_us_county AS b > WHERE b.name='Bexar' > AND b.statefp='48' > AND a.the_geom && b.the_geom > AND distance(a.the_geom, b.the_geom) = 0; > > QUERY PLAN > ---------------------------------------------------------------------------------------------------------------------------------------------------------- > HashAggregate (cost=16.56..16.57 rows=1 width=11) (actual > time=36236.168..36236.176 rows=8 loops=1) > -> Nested Loop (cost=0.00..16.56 rows=1 width=11) (actual > time=3.990..36236.075 rows=8 loops=1) > Join Filter: (distance(a.the_geom, b.the_geom) = 0::double > precision) > -> Index Scan using indx_tl_county_name on tl_2008_us_county b > (cost=0.00..8.27 rows=1 width=154180) (actual time=0.060..0.064 rows=1 > loops=1) > Index Cond: ((name)::text = 'Bexar'::text) > Filter: ((statefp)::text = '48'::text) > -> Index Scan using indx_tl_county_geo on tl_2008_us_county a > (cost=0.00..8.27 rows=1 width=154191) (actual time=3.259..5.932 rows=10 > loops=1) > Index Cond: (a.the_geom && b.the_geom) > Total runtime: 36236.674 ms > (9 rows) > > tiger=# SET enable_seqscan TO off; ... didn't help > > ################## End Query 2 ################################# > > ----- > PGSQL 8.2.4 / (PostGIS Version 1.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1 ) > Proj4 -4.6.1 / gdal 1.6.1 / GD 2.0.34 > Linux/Apache/2.2.8 (Fedora) > -- > View this message in context: > http://www.nabble.com/Query-runtime-comparison-for-two-datasets-using-%27--%27-geometric-operator-tp25670401p25670401.html > Sent from the PostGIS - User mailing list archive at Nabble.com. > > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
