Re: indexes on float8 vs integer - Mailing list pgsql-general

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
haveor will load this module. 
     At first, I was just going to interpolate the distance as a bounding box based on the distance between latitude
linesand longitude lines at that latitude. Then serve the data based on the integers for lat/long between two values.
Allthe 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
setof 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.wood@niwa.co.nz> wrote:

> From: Brent Wood <b.wood@niwa.co.nz>
> Subject: Re: [GENERAL] indexes on float8 vs integer
> To: gearond@sbcglobal.net
> Cc: pgsql-general@postgresql.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.marlowe@gmail.com>
> 07/12/09 10:31 PM >>>
> On Sat, Jul 11, 2009 at 10:15 PM, Dennis Gearon<gearond@sbcglobal.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@postgresql.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.
>