[postgis-users] SQLite and postGIS

[P Kishor]

On 4/11/08, shoaib <saburq at gmail.com> wrote:
> >  I do have one question for you all -- why on earth does Spatialite
>  >  (and also PostGIS as well as ArcGIS) store the geometry as a BLOB?
>  >  What are the advantages? Is it space? Is it speed? Both? I can't
>  >  really understand the reason for all that AsWKT and WKB and AsPoints
>  >  and whatever back-and-forth. Why not just store coords as numbers, or
>  >  a string of coords as, well, a string of coords? Especially in the
>  >  case of PostGIS, PostgreSQL has a col type of arrays, so why not just
>  >  use that? SQLite stores everything as strings anyway, so why bother
>  >  with the back and forth unless there is speed advantage. I don't care
>  >  about the space -- hard disk is cheap.
>
>
> Punkish, coordinates are stored in a hexadecimal representation to
>  prevent rounding errors and coordinate drift when performing spatial
>  operations and insertions. If a set of coordinates stored as string
>  are copied from say one column to another the database or a function
>  may decide to round it off. This can lead to serious loss of
>  integrity.
>


that doesn't make any sense at all. Databases can do rounding tricks
with numbers, but never with strings. If databases started doing their
own thing with strings, they would be good candidate to be chucked
into the trash.

With regards to SQLite being "text" only, I think the way to think
about SQLite is that it is typeless. Everything is stored as string
(and, lately that is not entirely true... one would have to follow the
development of SQLite from version 2.x to 3.x to understand what is
going on), but SQLite really doesn't care what you store where. All
responsibility for data type checking lies with the application
designer.

Anyway, I am having a lot of fun with Spatialite and a few other
lightweight solutions that I am exploring. More when I have something
to share.

Puneet.