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

[Obe, Regina]

Florian,
 
I'm not too familiar with Germany spatial ref systems so maybe some
postgis user in Germany would be best able to help you on that.  My
guess is an SRID between 31466 and 312469 may work for you  - below is
the query to see the descriptions of these)
 
SELECT * FROM spatial_ref_sys WHERE srid between 31466 and 31469
 
Now for transforming your data.  I your SRID of your existing data is -1
instead something like say 4326 (longlat WGS 84)  then you need to force
it into the right SRID using ST_SetSRID and then transform.
 
Let us say for example you made the mistake of importing your data with
SRID -1 -> Then to remedy the situation , you would do the following.
The below assumes 31466 is the projection you have decided for your
data.
 
SELECT AddGeometryColumn('public', 'navteq_poi', 'the_geom_germany',
31466, 'POINT', 2);
UPDATE navteq_poi  SET the_geom_germany =
ST_Transform(ST_SetSRID(the_geom, 4326), 31466);
CREATE INDEX idx_navteq_poi_the_geom_germany ON parceltime_2006 USING
gist (the_geom_germany);
 
If your SRID is already something other than -1, then you can simply
replace the second line with 
UPDATE navteq_poi  SET the_geom_germany = ST_Transform(the_geom,31466);
 
Optionally if you don't need the old geom field for anything else, you
can drop your original geom field with a 
SELECT DropGeometryColumn('public', 'navteq_poi', 'the_geom');
 
Repeat the above process for your line table except of course replacing
POINT with LINESTRING or whatever your current type is for the_geom.
 
Now looking at the below plan, it looks like you may be missing a couple
of useful indexes.  Make sure you have btree indexes on the following
fields
navteq_poi.poi_name
navteq_poi .poi_id
 
 
Then do a vacuum analyze
 
Then from there you can simply use 
 
for older postgis
(Expand(point.the_geom_germany, 50) && line.the_geom_germany) AND
Distance(point.the_geom_germany, line.the_geom_germany) < 50
 
 
If you are using the newer version of Postgis (1.3.1) then you can
simplify the above further with
 
ST_DWithin(point.the_geom_germany, line.the_geom_germany, 50)
 
 
(Note if you are using an older version of postgis, you may have to
remove the ST_ from above and ST_DWithin may not exist)
 
 
 
Hope that helps,
Regina
 

________________________________

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Reichle, Florian
Sent: Tuesday, September 25, 2007 4:48 AM
To: postgis-users at postgis.refractions.net
Subject: RE: Question: How can I improve the performance of function
DISTANCE_SPHERE?



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


 




-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070925/8f1a69c9/attachment.html>