[postgis-users] [postgis-devel] Sphere or Spheroid
pcreso at pcreso.com
pcreso at pcreso.com
Fri Oct 30 17:32:33 PDT 2009
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
>
More information about the postgis-users
mailing list