[postgis-devel] Sphere or Spheroid
Paul Ramsey
pramsey at cleverelephant.ca
Fri Oct 30 17:00:58 PDT 2009
It looks like, for basic usage, the performance difference is
relatively small (which is probably because most of the calculations
are on the sphere, only the last few are on the spheroid). For cases
line point/point tests the differences will be most stark. But here's
a medium sized spatial join on line/polygon.
geog=# select count(*) from buildings b, streets s where
st_dwithin(b.geog, s.geog, 50000) and s.gid = 100;
count
--------
144783
(1 row)
Time: 6185.445 ms
geog=# select count(*) from buildings b, streets s where
st_dwithin(b.geog, s.geog, 50000, false) and s.gid = 100;
count
--------
144795
(1 row)
Time: 5879.593 ms
geog=#
On Fri, Oct 30, 2009 at 4:48 PM, Paragon Corporation <lr at pcorp.us> wrote:
>
> The choice of default sphere vs. spheroid is probably something we should
> ask the general PostGIS users group so cc'ing them since more people
> frequent that list that would have strong opinions on this subject.
>
> So question folks -- is which do you prefer as default behavior for new
> geography data type
>
> faster sphere calculation (with 0.05% - 1% margin error) (I vaguely recall
> that's what my studies suggested around the globe compared to UTM)
>
> or
> slower more accurate spheroid calculation
>
> Keep in mind -- this is just default behavior without an extra arg to the
> functions, you can use both in your work.
>
> details of current functions below
>
> http://www.postgis.org/documentation/manual-svn/ch08.html#PostGIS_GeographyF
> unctions
>
> Thanks,
> Regina
> -----Original Message-----
> From: postgis-devel-bounces at postgis.refractions.net
> [mailto:postgis-devel-bounces at postgis.refractions.net] On Behalf Of Paul
> Ramsey
> Sent: Friday, October 30, 2009 7:41 PM
> To: PostGIS Development Discussion
> Subject: Re: [postgis-devel] Sphere or Spheroid
>
> Oh, BTW, we could just as easily have a utility function
> geography_set_spheroid(bool) that you can call from SQL that will manipulate
> the global variable. That's all that the SET command ends up doing in the
> end anyways.
>
> P.
>
> On Fri, Oct 30, 2009 at 4:29 PM, Paragon Corporation <lr at pcorp.us> wrote:
>> Ah okay, maybe that was why we decided against it last time. You
>> should put that in the notes. Anyrate even if we had GUC, I would
>> still go with the multiple signatures as well since you may want
>> different behavior for a specific query or when you are comparing numbers
> (you want both).
>>
>>
>>
>> -----Original Message-----
>> From: postgis-devel-bounces at postgis.refractions.net
>> [mailto:postgis-devel-bounces at postgis.refractions.net] On Behalf Of
>> Paul Ramsey
>> Sent: Friday, October 30, 2009 7:25 PM
>> To: PostGIS Development Discussion
>> Subject: Re: [postgis-devel] Sphere or Spheroid
>>
>> I did GUC research this morning and even did a GUC implementation! I
>> don't think it makes this decision any less relevant, because frankly
>> the default will rule 95% of the users. So choosing the "right"
>> default is a good idea.
>>
>> Anyhow, on the GUC, a module-controlled GUC has an issue, in that the
>> GUC can't exist until it's initialized by the module, and the
>> initialization routine for the module is not executed until the first
>> time a module function is called. So, running "SET
>> geography.use_spheroid = true" will return an error if it's the first
>> thing you do, but will work if you first do something like "SELECT
>> ST_AsText('POINT(0 0)'::geography)" to force the module to load
>>
>> In all, a non-happy situation, so I have opted for function signatures
>> for now, unless someone can tell me I should do otherwise.
>>
>> P.
>>
>> On Fri, Oct 30, 2009 at 4:10 PM, Paragon Corporation <lr at pcorp.us> wrote:
>>> I'm still dreaming of the GUC by the way.
>>> http://trac.osgeo.org/postgis/ticket/267 to make this decision less
>>> relevant.
>>
>>
>>>
>>> What is bool (will that mean True if you want measured in sphere?).
>>> Regardless of what default you decide, that boolean has to mean the
>>> same thing I think. (but I guess that's not too important)
>>>
>>> Thanks,
>>> Regina
>>>
>>> -----Original Message-----
>>> From: postgis-devel-bounces at postgis.refractions.net
>>> [mailto:postgis-devel-bounces at postgis.refractions.net] On Behalf Of
>>> Paul Ramsey
>>> Sent: Friday, October 30, 2009 4:41 PM
>>> To: PostGIS Development Discussion
>>> Subject: Re: [postgis-devel] Sphere or Spheroid
>>>
>>> I should add that in general, the differences seem to be about 0.1%
>>> to 0.2% for my Oregon test area.
>>>
>>> P.
>>>
>>> On Fri, Oct 30, 2009 at 1:39 PM, Paul Ramsey <pramsey at opengeo.org> wrote:
>>>> I am just now hooking up some spheroid calculations into SQL and the
>>>> choice is here: what is the default return for ST_Distance(geog,
>>>> geog) ? I have an extra signature ST_Distance(geog, geog, bool)
>>>> which allows you to choose, *but* something has to be the default.
>>>> So, slow and accurate or fast and not-quite-right? For reference,
>>>> here's some test
>>>> numbers:
>>>>
>>>> spheroid | sphere | diff |
>>>> diff_pct
>>>> ------------------+------------------+----------------------+-------
>>>> ------------------+------------------+----------------------+-
>>>> ------------------+------------------+----------------------+-
>>>> ------------------+------------------+----------------------+-------
>>>> ------------------+------------------+----------------------+-
>>>> ------------------+------------------+----------------------+-
>>>> ------------------+------------------+----------------------+-----
>>>> 14.1244075108035 | 14.1394160043876 | -0.0150084935841761 |
>>>> -0.00106146488507862
>>>> 14.6266188748041 | 14.5910056309084 | 0.0356132438956287 |
>>>> 0.00244076692151968
>>>> 20.7346086743544 | 20.7259709082645 | 0.00863776608995082 |
>>>> 0.000416760504402065
>>>> 21.9682444424626 | 21.9811452394388 | -0.0129007969762469 |
>>>> -0.000586902858596291
>>>> 23.7383733187578 | 23.7519404190066 | -0.0135671002487641 |
>>>> -0.000571199658193295
>>>> 24.71417232105 | 24.6751858115921 | 0.0389865094578177 |
>>>> 0.00157998848541607
>>>> 26.060846145618 | 25.9957171978245 | 0.0651289477934469 |
>>>> 0.00250537222334829
>>>> 29.1593418185795 | 29.0828389449609 | 0.0765028736186224 |
>>>> 0.00263051601542076
>>>> 30.4726043383638 | 30.407477679772 | 0.0651266585918009 |
>>>> 0.00214179746434954
>>>> 33.3089494734479 | 33.2231927324098 | 0.085756741038054 |
>>>> 0.00258123118174602
>>>> 54.5278744382593 | 54.5740236709768 | -0.0461492327175321 |
>>>> -0.000845626355054244
>>>> 56.6270596077484 | 56.4794616931023 | 0.147597914646141 |
>>>> 0.00261330243280571
>>>> 58.0564235524421 | 57.9072919817022 | 0.149131570739875 |
>>>> 0.00257535045477517
>>>> 61.5962238336604 | 61.6609971505153 | -0.0647733168549323 |
>>>> -0.00105047468980788
>>>> 61.7015675105702 | 61.5433341315128 | 0.158233379057449 |
>>>> 0.00257108883180295
>>>> 67.6094786823676 | 67.4707740811243 | 0.138704601243305 |
>>>> 0.00205577308297266
>>>> 68.2569872101138 | 68.0830690889332 | 0.173918121180577 |
>>>> 0.00255449884248604
>>>> 68.4943435865133 | 68.5554249222873 | -0.0610813357739914 |
>>>> -0.000890977422184046
>>>> 68.5746705524887 | 68.4885568487924 | 0.0861137036962845 |
>>>> 0.00125734440406453
>>>> 68.6758026445452 | 68.7123761613873 | -0.0365735168421395 |
>>>> -0.000532269714501474
>>>> 73.4229241308488 | 73.4212994751178 | 0.00162465573099269 |
>>>> 2.21278531244639e-05
>>>> 79.4484754792361 | 79.4536134385929 | -0.00513795935687256 |
>>>> -6.46661509088383e-05
>>>> 85.4392924899187 | 85.3402382287158 | 0.0990542612029799 |
>>>> 0.00116069820355446
>>>> 85.4539811995932 | 85.3919394786419 | 0.0620417209512425 |
>>>> 0.00072655242789936
>>>> 93.5788665631846 | 93.4985937971677 | 0.0802727660169325 |
>>>> 0.000858545169043645
>>>> 96.3199573238008 | 96.1797387047942 | 0.14021861900666 |
>>>> 0.00145788105577033
>>>> 101.328501281213 | 101.062654489096 | 0.265846792116648 |
>>>> 0.00263051463926598
>>>> 101.649559586565 | 101.502533562595 | 0.14702602397027 |
>>>> 0.00144849609965254
>>>> 108.809850830453 | 108.894277925073 | -0.0844270946195422 |
>>>> -0.000775312497848917
>>>> 109.397307465226 | 109.316521134613 | 0.0807863306121561 |
>>>> 0.000739013003466101
>>>> 110.654864554623 | 110.763015546595 | -0.108150991971058 |
>>>> -0.000976417908426867
>>>> 113.60376179349 | 113.561772904965 | 0.0419888885251396 |
>>>> 0.000369744919007898
>>>> 114.121155128581 | 113.828085749691 | 0.293069378889427 |
>>>> 0.00257466667351227
>>>> 114.310532244975 | 114.431988777869 | -0.121456532893603 |
>>>> -0.00106138619271374
>>>> 115.797340494535 | 115.877511025684 | -0.0801705311490082 |
>>>> -0.000691855826375479
>>>> 116.509315790761 | 116.204507950436 | 0.304807840325097 |
>>>> 0.00262302939620126
>>>> 116.944936442573 | 116.776885086999 | 0.168051355574235 |
>>>> 0.00143908064895751
>>>> 118.802848975488 | 118.599760910854 | 0.203088064634358 |
>>>> 0.00171238173732078
>>>> 124.752683002401 | 124.793829377275 | -0.0411463748743159 |
>>>> -0.000329714819071083
>>>>
>>> _______________________________________________
>>> postgis-devel mailing list
>>> postgis-devel at postgis.refractions.net
>>> http://postgis.refractions.net/mailman/listinfo/postgis-devel
>>>
>>>
>>> _______________________________________________
>>> postgis-devel mailing list
>>> postgis-devel at postgis.refractions.net
>>> http://postgis.refractions.net/mailman/listinfo/postgis-devel
>>>
>> _______________________________________________
>> postgis-devel mailing list
>> postgis-devel at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-devel
>>
>>
>> _______________________________________________
>> postgis-devel mailing list
>> postgis-devel at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-devel
>>
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel
>
>
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel
>
More information about the postgis-devel
mailing list