[postgis-users] RE: Question: How can I improve the performance of function DISTANCE_SPHERE?

[Reichle, Florian]

Does it cover the whole world or just a
limited region like a country?
--> As an eample take Germany as country

If you can transform to a meter based
projection, that would be the most efficient.
--> How can I do this?

My EXPLAIN SELECT:

QUERY PLAN

Unique  (cost=18858475.73..18858492.79 rows=4 width=39)

  ->  Group  (cost=18858475.73..18858492.77 rows=4 width=39)

        ->  Merge Left Join  (cost=18858475.73..18858492.75 rows=4 width=39)

              Merge Cond: (("outer"."?column3?" = "inner"."?column3?") AND (point.poi_id = point.poi_id))

              Filter: (point.poi_name IS NULL)

              ->  Sort  (cost=86.94..89.03 rows=836 width=39)

                    Sort Key: (point.poi_name)::text, point.poi_id

                    ->  Seq Scan on navteq_poi point  (cost=0.00..46.36 rows=836 width=39)

              ->  Sort  (cost=18858388.80..18858392.28 rows=1394 width=39)

                    Sort Key: (point.poi_name)::text, point.poi_id

                    ->  Nested Loop  (cost=47.20..18858316.00 rows=1394 width=39)

                          Join Filter: ((expand(line.the_geom, 0.01::double precision) && point.the_geom) AND ((distance_spheroid(point.the_geom, startpoint(line.the_geom), 'SPHEROID("WGS 84",6378137,298.257223563)'::spheroid) < 50::double precision) OR (distance_spheroid(point.the_geom, centroid(line.the_geom), 'SPHEROID("WGS 84",6378137,298.257223563)'::spheroid) < 50::double precision) OR (distance_spheroid(point.the_geom, endpoint(line.the_geom), 'SPHEROID("WGS 84",6378137,298.257223563)'::spheroid) < 50::double precision)))

                          ->  Seq Scan on navteq_streets line  (cost=0.00..43075.15 rows=473815 width=85)

                          ->  Materialize  (cost=47.20..55.56 rows=836 width=71)

                                ->  Seq Scan on navteq_poi point  (cost=0.00..46.36 rows=836 width=71)

The EXPLAIN ANALYZE took to long. So I hope the information will help.

Thanks


 

-------------- next part --------------
A non-text attachment was scrubbed...
Name: winmail.dat
Type: application/ms-tnef
Size: 8929 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070925/13032af6/attachment.bin>