[postgis-users] [postgis-devel] Sphere or Spheroid

Paul Ramsey pramsey at cleverelephant.ca
Fri Oct 30 17:34:37 PDT 2009 

Yes, though right now you are hard-coded into WGS84. Funding
gratefully accepted to allow arbitrary spheroids :)

P.

On Fri, Oct 30, 2009 at 5:32 PM,  <pcreso at pcreso.com> wrote:
> And I assume that while the count(*) difference will be less, it may still vary depending on which spheroid you use?
>
> Brent
>
> --- On Sat, 10/31/09, Paul Ramsey <pramsey at cleverelephant.ca> wrote:
>
>> From: Paul Ramsey <pramsey at cleverelephant.ca>
>> Subject: Re: [postgis-users] [postgis-devel] Sphere or Spheroid
>> To: "PostGIS Development Discussion" <postgis-devel at postgis.refractions.net>
>> Cc: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
>> Date: Saturday, October 31, 2009, 1:00 PM
>> 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
>> >
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>

More information about the postgis-users mailing list