WKT precision
388 views
Skip to first unread message
Karrel
Jan 6, 2011, 4:37:23 AM1/6/11
to SpatiaLite Users
I have been working with SpatiaLite for a while. There was never any
question how to load geometries in a DB. Of course as WKB.
Because of my curiosity I tried it now. In addition I have written a
small program that writes a linestring in 4 different ways in a DB and
then read them out again. With the following results:
written as | Read as | Result
---------------------|---------------|--------------
(1) WKT | as Text | Input unequal Output
(2) WKB | as Text | Input unequal Output
(3) WKT | as Binary| Input equal Output
(4) WKB | as Binary | Input equal Output
The problem lies in the loss of accuracy when I read it as text. My
question now is: Why?
When I write it in the DB as Text SpatiaLite save the coordinates as
internal Blob. (says point 3 and 4) So no loss of accuracy. But if I
read it out as Text I only get 6 positions after the comma/point.
question how to load geometries in a DB. Of course as WKB.
Because of my curiosity I tried it now. In addition I have written a
small program that writes a linestring in 4 different ways in a DB and
then read them out again. With the following results:
written as | Read as | Result
---------------------|---------------|--------------
(1) WKT | as Text | Input unequal Output
(2) WKB | as Text | Input unequal Output
(3) WKT | as Binary| Input equal Output
(4) WKB | as Binary | Input equal Output
The problem lies in the loss of accuracy when I read it as text. My
question now is: Why?
When I write it in the DB as Text SpatiaLite save the coordinates as
internal Blob. (says point 3 and 4) So no loss of accuracy. But if I
read it out as Text I only get 6 positions after the comma/point.
a.furieri
Jan 6, 2011, 11:42:10 AM1/6/11
to SpatiaLite Users
Hi Karrel,
coordinate values are internally
stored as DOUBLE PRECISION FLOATs:
and converting back and forth such
values to text surely introduces some
loss of precision.
Please note well: usually coordinates
are measured in Meters, so after the
sixth decimal digits we are speaking
of quite infinitesimal lengths.
Anyway, when preserving an uncompromised
accuracy is a primary goal using WKB
is mandatory, because converting to WKT
surely introduces some (slight) approximation.
bye
Sandro
coordinate values are internally
stored as DOUBLE PRECISION FLOATs:
and converting back and forth such
values to text surely introduces some
loss of precision.
Please note well: usually coordinates
are measured in Meters, so after the
sixth decimal digits we are speaking
of quite infinitesimal lengths.
Anyway, when preserving an uncompromised
accuracy is a primary goal using WKB
is mandatory, because converting to WKT
surely introduces some (slight) approximation.
bye
Sandro
mem
Jan 7, 2011, 6:09:38 AM1/7/11
to SpatiaLite Users
Hi Karrel, Sandro and all,
I am trying to insert geomtries (lineString) in a BD and in order to
optimize performences I am trying to use PreparedStatement;
and the coordinates String as a parametrized value. I am having pb
with it.
String sql = "INSERT INTO Acquisitions ( " + columnNames + ") VALUES
(? , ? , ? , ? , " + "GeometryFromText('LINESTRING (?) ', 4326));";
PreparedStatement prepStatement = con.prepareStatement(sql);
prepStatement.addBatch();
I have this exception java.lang.ArrayIndexOutOfBoundsException :8. I
have several coordinates for each line string and not necessarly the
same number.
Perhaps you have used a better method?
Since you have already done it, could you please post the piece of
code?
Many thanks in advance.
mem
I am trying to insert geomtries (lineString) in a BD and in order to
optimize performences I am trying to use PreparedStatement;
and the coordinates String as a parametrized value. I am having pb
with it.
String sql = "INSERT INTO Acquisitions ( " + columnNames + ") VALUES
(? , ? , ? , ? , " + "GeometryFromText('LINESTRING (?) ', 4326));";
PreparedStatement prepStatement = con.prepareStatement(sql);
prepStatement.addBatch();
I have this exception java.lang.ArrayIndexOutOfBoundsException :8. I
have several coordinates for each line string and not necessarly the
same number.
Perhaps you have used a better method?
Since you have already done it, could you please post the piece of
code?
Many thanks in advance.
mem
mem
Jan 7, 2011, 6:17:21 AM1/7/11
to SpatiaLite Users
Since I will also need no loss of accuracy, I need the insertion as
WKB
> > read it out as Text I only get 6 positions after the comma/point.- Masquer le texte des messages précédents -
>
> - Afficher le texte des messages précédents -
WKB
>
> - Afficher le texte des messages précédents -
a.furieri
Jan 7, 2011, 6:36:12 AM1/7/11
to SpatiaLite Users
Hi Mem,
your approach seems to be fully legitimate.
At least, this will surely work using C:
not really sure if there is some obscure
Java specific bug.
Just for precision: obviously your
prepared statement expects its 5th
argument [LINESTRING(?)] to be bounded
from a single TEXT STRING.
In other words: you are absolutely
free to store your coordinate sequences
into a (may be ?) DOUBLE array.
But it's your responsibility to serialize
such array into a single text string
before attempting to bound this value
to your prepared statement.
Second thought: it's not at all difficult
such text string may require several MB RAM
(linestrings easily have several thousand
points): and I'm not really sure if the JDBC
module can support such an huge memory request
for TEXT STRING.
Binding a BLOB binary-encoded value usually is
a much more robust (and efficient) solution.
bye
Sandro
your approach seems to be fully legitimate.
At least, this will surely work using C:
not really sure if there is some obscure
Java specific bug.
Just for precision: obviously your
prepared statement expects its 5th
argument [LINESTRING(?)] to be bounded
from a single TEXT STRING.
In other words: you are absolutely
free to store your coordinate sequences
into a (may be ?) DOUBLE array.
But it's your responsibility to serialize
such array into a single text string
before attempting to bound this value
to your prepared statement.
Second thought: it's not at all difficult
such text string may require several MB RAM
(linestrings easily have several thousand
points): and I'm not really sure if the JDBC
module can support such an huge memory request
for TEXT STRING.
Binding a BLOB binary-encoded value usually is
a much more robust (and efficient) solution.
bye
Sandro
mem
Jan 7, 2011, 8:14:30 AM1/7/11
to SpatiaLite Users
Thanks Sandro for your quit answer, But I was wondering how to create
this BLOB object since I use AddGeometryColumn function
to create the Geometry column. I need to do this since I am going to
use the R-tree index afterwards.
Shall I :
1-insert the other values (different from geometry) using a prepared
stament
2-query on the geometry column using the id and get the Blob object
from the resultset
3-get the bytes from the following string
"GeometryFromText('LINESTRING(acq.getDataValue(coordinates_string))',
4326)".getBytes()
4- set the bytes to the blob
5-update the geometry column with this blob value!
Does this seem correct? This seems not very perfoments? moreover
would this be consistent with the Geometry column definition?
According to the Spatialite manual the Blob format is particular...
Moreover, when I read from the DB, I would like to get the coordinates
as is...
Could please clear this to me !
Thx in advance
mem
this BLOB object since I use AddGeometryColumn function
to create the Geometry column. I need to do this since I am going to
use the R-tree index afterwards.
Shall I :
1-insert the other values (different from geometry) using a prepared
stament
2-query on the geometry column using the id and get the Blob object
from the resultset
3-get the bytes from the following string
"GeometryFromText('LINESTRING(acq.getDataValue(coordinates_string))',
4326)".getBytes()
4- set the bytes to the blob
5-update the geometry column with this blob value!
Does this seem correct? This seems not very perfoments? moreover
would this be consistent with the Geometry column definition?
According to the Spatialite manual the Blob format is particular...
Moreover, when I read from the DB, I would like to get the coordinates
as is...
Could please clear this to me !
Thx in advance
mem
mem
Jan 7, 2011, 8:33:50 AM1/7/11
to SpatiaLite Users
I found the method createBlob from Connection object, so rather than
quering and using ResultSet.getBob, I can use it the set the Blob to
the geometry column but is this ok (please refer to my previous post).
Thanks
mem
> > Sandro- Masquer le texte des messages précédents -
quering and using ResultSet.getBob, I can use it the set the Blob to
the geometry column but is this ok (please refer to my previous post).
Thanks
mem
a.furieri
Jan 7, 2011, 9:01:53 AM1/7/11
to SpatiaLite Users
Hi Mem,
I suppose there is some basic point
to be preliminary cleared.
a) SpatiaLite always internally stores
Geometries as binary objects
(aka BLOBs)
b) WKT / WKB notations simply are
intended to support neutral (cross
platform) data import/export
c) you can use AsText(), AsBinary(),
GeomFromText() and GeomFromWkb()
so to support any possible
notation conversion to/from the
internal binary BLOB format
Direct access to "internal" binary
encoded geometries is fully supported
using the C API; but I fear this is
absolutely unsupported using Java / JDBC.
So standing things, I foresee two
possible approaches for JDBC:
a) write your own serialization/deserialization
classes to support WKT
In Java's own perspective a WKT value
corresponds to a text string
b) write your own serialization/deserialization
classes to support WKB
In Java's own perspective a WKB value
corresponds to a BLOB / byte array
alternatively, you can check for some Java
package supporting WKT and WKB:
I'm quite sure someone else has already
written something like this.
GeoTools seems to be a valid candidate;
but I'm not a Java user, so I cannot
say you nothing more than this.
bye
Sandro
I suppose there is some basic point
to be preliminary cleared.
a) SpatiaLite always internally stores
Geometries as binary objects
(aka BLOBs)
b) WKT / WKB notations simply are
intended to support neutral (cross
platform) data import/export
c) you can use AsText(), AsBinary(),
GeomFromText() and GeomFromWkb()
so to support any possible
notation conversion to/from the
internal binary BLOB format
Direct access to "internal" binary
encoded geometries is fully supported
using the C API; but I fear this is
absolutely unsupported using Java / JDBC.
So standing things, I foresee two
possible approaches for JDBC:
a) write your own serialization/deserialization
classes to support WKT
In Java's own perspective a WKT value
corresponds to a text string
b) write your own serialization/deserialization
classes to support WKB
In Java's own perspective a WKB value
corresponds to a BLOB / byte array
alternatively, you can check for some Java
package supporting WKT and WKB:
I'm quite sure someone else has already
written something like this.
GeoTools seems to be a valid candidate;
but I'm not a Java user, so I cannot
say you nothing more than this.
bye
Sandro
mem
Jan 7, 2011, 10:17:57 AM1/7/11
to SpatiaLite Users
Thx for the response and sorry for this further question !
The concern is that createBob from connection does not work on xerial
JDBC since it is compiled with 1.5 and not 1.6 so cannot be used.
When adding a geometry column this way, I have no garanty that the
geometry is not null:
SELECT AddGeometryColumn('Acquisitions', 'FOOTPRINT', 4326,
'LINESTRING', 2);
In fact, when I have a look to the values before inserting the
geometry there are NULL (I use spatialite exec with .nullvalue NULL).
So I cannot get the Blob object and update it with my Bytes array.
Is there any way (using java) to initialise the column with an empty
blob and keep using AddGeometryColumn (for the reasons I have already
mentionned) ?
Thx again
The concern is that createBob from connection does not work on xerial
JDBC since it is compiled with 1.5 and not 1.6 so cannot be used.
When adding a geometry column this way, I have no garanty that the
geometry is not null:
SELECT AddGeometryColumn('Acquisitions', 'FOOTPRINT', 4326,
'LINESTRING', 2);
In fact, when I have a look to the values before inserting the
geometry there are NULL (I use spatialite exec with .nullvalue NULL).
So I cannot get the Blob object and update it with my Bytes array.
Is there any way (using java) to initialise the column with an empty
blob and keep using AddGeometryColumn (for the reasons I have already
mentionned) ?
Thx again
a.furieri
Jan 7, 2011, 11:07:13 AM1/7/11
to SpatiaLite Users
Mem,
I'm not really sure to understand your strategy:
a) SELECT AddGeometryColumn(...)
is the Spatial-aware equivalent of
ALTER TABLE ... CREATE COLUMN ...
so you can successfully execute
AddGeometryColumn() only once in
the table life-cycle (at creation time).
b) determining if any given Geometry column
can / cannot accept NULL values is completely
under your responsibility.
Please, see the latest (optional) arg
you can pass to AddGeometryColumn():
default is = accept NULLs, but you can
explicitly select the opposite behaviours:
c) there is any need at all to get a BLOB
object from the DBMS (actually, this will
simply cause a system crash, because
querying an empty geometry will return
a NULL pointer, i.e. an invalid reference).
Creating the BLOB (i.e. nothing more than a
trivial byte array, from the language own
perspective) falls under your direct
responsibility. And initializing the
array with appropriate bytes is under
your responsibility as well.
Conclusion: I see no need at all to implement
such a complex two-steps mechanism, because
you can directly bind any required parameter
(this including geometry) and then duly
INSERT a row into the DBMS table.
Test the following simple example
(pseudo-code, fantasy language):
// creating a test table
STRING sql = "CREATE TABLE test (";
sql += "id INTEGER NOT NULL,";
sql += "name TEXT NOT NULL)";
Execute(sql);
// creating a geometry column
sql = "SELECT AddGeometryColumn(";
sql += "'test', 'geom', 4326, ";
sql += "'LINESTRING', 'XY')";
Execute(sql);
// creating the SQL INSERT stmt
sql = "INSERT INTO test (id, name, geom) ";
sql += "VALUES (?, ?, ";
sql += "GeomFromText(?, 4326))";
STATEMENT stmt = CreateStatement(sql);
// inserting a row
INT id = 1;
STRING name = "alpha";
STRING line = "LINESTRING(1.1 2.2, ";
line += "3.3 4.4, 5.5 6.6, 7.7 8.8)";
stmt->BindIntValue(1, id);
stmt->BindStringValue(2, name);
stmt->BindStringValue(3, pt);
stmt->Execute();
bye
Sandro
I'm not really sure to understand your strategy:
a) SELECT AddGeometryColumn(...)
is the Spatial-aware equivalent of
ALTER TABLE ... CREATE COLUMN ...
so you can successfully execute
AddGeometryColumn() only once in
the table life-cycle (at creation time).
b) determining if any given Geometry column
can / cannot accept NULL values is completely
under your responsibility.
Please, see the latest (optional) arg
you can pass to AddGeometryColumn():
default is = accept NULLs, but you can
explicitly select the opposite behaviours:
SELECT AddGeometryColumn('Acquisitions', 'FOOTPRINT', 4326,
'LINESTRING', 'XY', 1);
c) there is any need at all to get a BLOB
object from the DBMS (actually, this will
simply cause a system crash, because
querying an empty geometry will return
a NULL pointer, i.e. an invalid reference).
Creating the BLOB (i.e. nothing more than a
trivial byte array, from the language own
perspective) falls under your direct
responsibility. And initializing the
array with appropriate bytes is under
your responsibility as well.
Conclusion: I see no need at all to implement
such a complex two-steps mechanism, because
you can directly bind any required parameter
(this including geometry) and then duly
INSERT a row into the DBMS table.
Test the following simple example
(pseudo-code, fantasy language):
// creating a test table
STRING sql = "CREATE TABLE test (";
sql += "id INTEGER NOT NULL,";
sql += "name TEXT NOT NULL)";
Execute(sql);
// creating a geometry column
sql = "SELECT AddGeometryColumn(";
sql += "'test', 'geom', 4326, ";
sql += "'LINESTRING', 'XY')";
Execute(sql);
// creating the SQL INSERT stmt
sql = "INSERT INTO test (id, name, geom) ";
sql += "VALUES (?, ?, ";
sql += "GeomFromText(?, 4326))";
STATEMENT stmt = CreateStatement(sql);
// inserting a row
INT id = 1;
STRING name = "alpha";
STRING line = "LINESTRING(1.1 2.2, ";
line += "3.3 4.4, 5.5 6.6, 7.7 8.8)";
stmt->BindIntValue(1, id);
stmt->BindStringValue(2, name);
stmt->BindStringValue(3, pt);
stmt->Execute();
bye
Sandro
mem
Jan 7, 2011, 2:51:32 PM1/7/11
to SpatiaLite Users
Hi Sandro,
Acctually that 's how I proceeded in the begining (please refer to my
first post) however I encountred ArrayOutOfBound exception and I
thought
the coordinates string is too long. So that is the reason for which I
tried setting the Blob object directly into the preparedString.
On Oracle JDBC there is method a empty_blob() which allows
initialising an empty blob and then update it with the desired value.
However, I am
using xerial JDBC and I haven't found a way to initialise the Blob.
Sorry but I don't understand, this sentence about the
AddGeometryColumn: but you can
to 2 !
So I tried again with the String use for the Geometry and I encountred
pb like:
column values shall not be NULL so I removed all the not NULL
constrains (even if I have checked that the value are not null so I
don't know what is going on !)
and used the AddGeometryColumn without the last parameter.
I have no pb od null values but the execution of the prepared
statement batch takes ages....and the program does not terminate !
I am still cheking...
Thx for your answers,
mem
Acctually that 's how I proceeded in the begining (please refer to my
first post) however I encountred ArrayOutOfBound exception and I
thought
the coordinates string is too long. So that is the reason for which I
tried setting the Blob object directly into the preparedString.
On Oracle JDBC there is method a empty_blob() which allows
initialising an empty blob and then update it with the desired value.
However, I am
using xerial JDBC and I haven't found a way to initialise the Blob.
Sorry but I don't understand, this sentence about the
AddGeometryColumn: but you can
explicitly select the opposite behaviours:
SELECT AddGeometryColumn('Acquisitions', 'FOOTPRINT', 4326,
'LINESTRING', 'XY', 1);
I though it is the dimension of the geometry since it can be also set
SELECT AddGeometryColumn('Acquisitions', 'FOOTPRINT', 4326,
'LINESTRING', 'XY', 1);
to 2 !
So I tried again with the String use for the Geometry and I encountred
pb like:
column values shall not be NULL so I removed all the not NULL
constrains (even if I have checked that the value are not null so I
don't know what is going on !)
and used the AddGeometryColumn without the last parameter.
I have no pb od null values but the execution of the prepared
statement batch takes ages....and the program does not terminate !
I am still cheking...
Thx for your answers,
mem
a.furieri
Jan 8, 2011, 3:31:00 PM1/8/11
to SpatiaLite Users
Hi Mem,
you probably already noticed my previous post:
I've solved your JDBC problem; this including
the odd "array-out-of-bounds" mistery :-)
http://www.gaia-gis.it/spatialite-2.4.0-4/splite-jdbc.html
bye
Sandro
you probably already noticed my previous post:
I've solved your JDBC problem; this including
the odd "array-out-of-bounds" mistery :-)
http://www.gaia-gis.it/spatialite-2.4.0-4/splite-jdbc.html
bye
Sandro
mem
Jan 10, 2011, 5:47:48 AM1/10/11
to SpatiaLite Users
Hi Sandro,
It is ok for me now, I perhaps had also these stupid quotes ' ' too in
my first trial !
Thanks for your tests,
you could then taste some Java :-) !
Thx again
Mem
It is ok for me now, I perhaps had also these stupid quotes ' ' too in
my first trial !
Thanks for your tests,
you could then taste some Java :-) !
Thx again
Mem
mem
Jan 10, 2011, 6:02:15 AM1/10/11
to SpatiaLite Users
I have noticed some strange NULL row (the first one on the table)
whereas the first value I insert is not null and it is inserted
afterwards which makes my ID (unique and autoincremented)
shifted by one. have you the same pb when using the PreparedStement?
Moreover I have a strange :
cannot start a transaction within a transaction
cannot rollback transaction - SQL statements in progress
after the execution.
I just use inserts and I add commands to the batch (addBatch commands)
and no Selects.
whereas the first value I insert is not null and it is inserted
afterwards which makes my ID (unique and autoincremented)
shifted by one. have you the same pb when using the PreparedStement?
Moreover I have a strange :
cannot start a transaction within a transaction
cannot rollback transaction - SQL statements in progress
after the execution.
I just use inserts and I add commands to the batch (addBatch commands)
and no Selects.
Reply all
Reply to author
Forward
0 new messages