Re: indexes on float8 vs integer

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.