[OSM-talk] Re: Data types; indexes etc.

dblasby at openplans.org dblasby at openplans.org
Thu Apr 27 16:15:01 BST 2006


Nice wrote:
>I have just performed tests on a 1M point field for the three data
>types. The averaged results for 25,600 point tiles as follows:
>
>Double float 64 0.51s
>Single float 32 0.33s
>Integer32 0.237s

What does 25,600 point tiles mean?  Does that mean you're storing 40
points in each row of your table?

I'm still surprised that int32s are >30% faster than float32s - you'd
think the math processor would evaluate a<b in 1 cycle no matter what
the data type was.  It makes sense that the 64bit datatypes are slower
just because you're going to have to fetch twice as much data from
memory (not to mention your fast CPU cache memory will only hold 1/2
the data, and it will take twice as long to load 64bits into a
register).

Still, the difference is only 0.1 seconds.

>I wouldn't recommend using single floats for geographic
>representations; the rounding errors would be intolerable. Literally
>miles out.
>http://docs.sun.com/source/806-3568/ncg_goldberg.html#689

I think you misunderstood me -- I was just talking about using float32
bounding boxes in your index (although you could also make a
BOX2D_INT32SCALED to use in the index).  I'd suggest you use doubles
(or your *10,000,000 integer representation) for your actual data.

PostGIS stores the actual geographic data in double precision, and the
spatial index is built from float32 bounding boxes.

For Lat/long data, your "furthest-away-from-zero" number will be +/-180.
 I think the eta (ie. whats the next larger number that can be
represented) for a float32 around 180 is 0.001.  Thats not very much -
remember this is a "lossy" index so its okay for it to give you a few
*extra* rows.


As I asked before - why are you storing each point in the database?  Why
dont you store edges (homogeneous lines)?  It seems this would make
your database much more efficient if you did this.  But, I dont really
know what your datamodel is.

dave


----------------------------------------------------------
This mail sent through IMP: https://webmail.limegroup.com/




More information about the talk mailing list