Re: indexes on float8 vs integer

I wish that I didn't have to say this, but that is over my head at this point. I see this HUGE, steep mountain ahead of me and a little sign in front of it saying, "Learning Curve, start here."

:-)

Dennis Gearon

Signature Warning
----------------
EARTH has a Right To Life

I agree with Bolivian President Evo Morales

# The right to life: "The right for no ecosystem to be eliminated by the irresponsible acts of human beings."

# The right of biosystems to regenerate themselves: "Development cannot be infinite. There's a limit on everything."

# The right to a clean life: "The right for Mother Earth to live without contamination, pollution. Fish and animals and trees have rights."

# The right to harmony and balance between everyone and everything: "We are all interdependent."

See the movie - 'Inconvenient Truth'
See the movie - 'Syriana'

--- On Sun, 7/12/09, Brent Wood <b(dot)wood(at)niwa(dot)co(dot)nz> wrote:

> From: Brent Wood <b(dot)wood(at)niwa(dot)co(dot)nz>
> Subject: Re: [GENERAL] indexes on float8 vs integer
> To: gearond(at)sbcglobal(dot)net
> Date: Sunday, July 12, 2009, 9:10 PM
> You might look at UMN mapserver or
> Geoserver to provide PostGIS data via WMS/WFS and OpenLayers
> to plot these layers on top of Google Maps. These tools
> facilitate this sort of online map production pretty easily,
> although hosting can be an issue as teh requirements become
> more specific.
>
> Cheers,
>
>   Brent
>
>
> Brent Wood
> DBA/GIS consultant
> NIWA, Wellington
> New Zealand
> >>> Dennis Gearon <gearond(at)sbcglobal(dot)net>
> 07/13/09 1:05 PM >>>
>
> Well, Brent,
>      I'm just getting started on this
> design. I'm doing it at a hosting site, initially, so I have
> to find out if they have or will load this module.
>      At first, I was just going to
> interpolate the distance as a bounding box based on the
> distance between latitude lines and longitude lines at that
> latitude. Then serve the data based on the integers for
> lat/long between two values. All the geographic calculations
> would have taken place in the server app, then postgres
> would only be working with integers.
>      So, what is the base type for the
> point column?
>      I had planned on using google maps
> as the geographic server, I was going to query them using
> their API and a data set of center location and labeled
> points within a certain range.
>      Lot's to learn here, that's for
> sure. I will file your reply and look at it in a week or so
> when I store the first data.
> Dennis Gearon
>
>
>
> --- On Sun, 7/12/09, Brent Wood <b(dot)wood(at)niwa(dot)co(dot)nz>
> wrote:
>
> > From: Brent Wood <b(dot)wood(at)niwa(dot)co(dot)nz>
> > Subject: Re: [GENERAL] indexes on float8 vs integer
> > To: gearond(at)sbcglobal(dot)net
> > Cc: pgsql-general(at)postgresql(dot)org
> > Date: Sunday, July 12, 2009, 1:52 PM
> > Hi Dennis,
> >
> > Is there any reason you are not using PostGIS to store
> the
> > values as point geometries & use a spatial (GIST)
> index
> > on them? I have tables with hundreds of millions of
> point
> > features which work well. On disk data volume is not
> really
> > worth optimising for with such systems, i suggest
> > flexibility, ease of implementation & overall
> > performance should be more valuable.
> >
> > If you need to store & query coordinates, then a
> map
> > based tool seems relevant, and there are plenty of
> tools to
> > do this soirt of thing with PostGIS data, such as
> Mapserver,
> > GeoServer at the back end & OpenLayers in the
> front
> > end.
> >
> >
> > Cheers,
> >
> >   Brent Wood
> >
> >
> > Brent Wood
> > DBA/GIS consultant
> > NIWA, Wellington
> > New Zealand
> > >>> Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
> > 07/12/09 10:31 PM >>>
> > On Sat, Jul 11, 2009 at 10:15 PM, Dennis Gearon<gearond(at)sbcglobal(dot)net>
> > wrote:
> > >
> > > Anyone got any insight or experience in the speed
> and
> > size of indexes on Integer(4 byte) vs float (8byte).
> For a
> > project that I'm on, I'm contemplating using an
> integer
> > for:
> > >
> > >     Latitude
> > >     Longitude
> > >
> > > In a huge, publically searchable table.
> > >
> > > In the INSERTS, the representation would be equal
> to:
> > >
> > >     IntegerLatOrLong =
> to_integer(
> > float8LatOrLong * to_float(1000000) );
> > >
> > > This would keep it in a smaller (4 bytes vs 8
> byte)
> > representation with simple numeric comparison for
> indexing
> > values while still provide 6 decimals of precision,
> i.e.
> > 4.25 inches of resolution, what google mapes
> provides.
> > >
> > > I am expecting this table to be very huge. Hey, I
> want
> > to be the next 'portal' :-)
> > > Dennis Gearon
> >
> > Well, floats can be bad if you need exact math or
> matching
> > anyway, and
> > math on them is generally slower than int math. 
> OTOH,
> > you could look
> > into numeric to see if it does what you want. 
> Used to
> > be way slower
> > than int, but in recent versions of pgsql it's gotten
> much
> > faster.
> > Numeric is exact, where float is approximate, so if
> having
> > exact
> > values be stored is important, then either using int
> and
> > treating it
> > like fixed point, or using numeric is usually better.
> >
> > --
> > Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
> >
> > NIWA is the trading name of the National Institute of
> Water
> > & Atmospheric Research Ltd.
> >
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
> NIWA is the trading name of the National Institute of Water
> & Atmospheric Research Ltd.
>