[postgis-users] Storage efficiency of point and line data

[David Blasby]

Michael Graff wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> It seems there is a large overhead to storing point and line data in
> a geometry type.  mem_size() returns 172 bytes for a two-point line,
> and goes up by 24 bytes per additional point.  Returning the data in
> binary form seems to show only 6 bytes per point, so perhaps this
> is twice the actual storage.

Yes - as you can see from the actual structure, there is quite a bit of
overhead per-geometry.  The 24 bytes per points is because each point is
being stored as 3 double coordinates {x,y,z}.  A DOUBLE is 8 bytes, so
each point takes 24 bytes.

I dont see how you can get 6 bytes per points - thats only 2 bytes per
ordinate.  You will not have much precision.

If storage overhead is a problem, you'll need to go to MULTILINESTRINGS()
- but this could force you to alter how your database is setup.



>    cnt    | points | size
> - ----------+--------+-------
>  23333966 |      2 |   172
>   6789516 |      3 |   196
>   3712433 |      4 |   220
>   2438493 |      5 |   244
>   1749440 |      6 |   268
>   1346119 |      7 |   292
>    976198 |      8 |   316
>    806865 |      9 |   340
>    658199 |     10 |   364
>
> Is the storage format fairly efficient, and I'm simply storing a whole
> lot of data?

40,000,000 rows is a LOT of data!

dave