Importing csv with GUI changes double to text
Micha
(ubuntu). The Longitude values are negative, i.e. -3.307. On import
the Longitude column becomes type text and then I cannot use it
MakePoint(x,y,srid).
If I do the import from the command line:
CREATE TABLE locations (id integer, Latitude double, Longitude
double, ...);
.mode csv
.import <text_file.csv> locations
it works fine, of course. And then MakePoint is happy.
How would I import West Longitude values, preceded by a '-', using the
GUI??
Thanks,
Micha
Brad Hards
> I'm tying to import a csv file of point locations using spatialite-gui
> (ubuntu). The Longitude values are negative, i.e. -3.307. On import
> the Longitude column becomes type text and then I cannot use it
> MakePoint(x,y,srid).
VIRTUAL TABLE construct.
> If I do the import from the command line:
>
> CREATE TABLE locations (id integer, Latitude double, Longitude
> double, ...);
> .mode csv
> .import <text_file.csv> locations
>
> it works fine, of course. And then MakePoint is happy.
Useful trick. Do you mind if I add this to some documentation I'm writing up?
Because I didn't know that, I constructed a VIRTUAL TABLE, then a temporary
table of the right type, and then used MakePoint on that table.
> How would I import West Longitude values, preceded by a '-', using the
> GUI??
Unfortunately I don't know the answer to this. Sounds like it might need a
code change...
Brad
B Freed
behavior until I changed all my definitions to the 5 types used by
sqlite.
My neg. Longitudes are working fine. Converting from Access,
attaching converted db then inserting into REAL type columns.
a.furieri
just some simple test about this:
test1.csv
---------------------
"pos_num","neg_num","string"
1,-1,"alpha"
2,-2,"beta"
3,-3,"gamma"
---------------------
test2.csv
---------------------
"pos_num","neg_num","string"
1.1,-1.1,"alpha"
2.2,-2.2,"beta"
3.3,-3.3,"gamma"
---------------------
test3.csv
---------------------
"pos_num","neg_num","string"
"1.1","-1.1","alpha"
"2.2","-2.2","beta"
"3.3","-3.3","gamma"
---------------------
test procedure:
a) create a VirtualText table
[first line contains column names,
text separator: double-quotes,
column separator: commma,
decimal separator: point]
b) SELECT * from test;
c) SELECT typeof(pos_num), typeof(neg_num),
typeof (string) FROM test;
test1.csv
--------------------------
integer, integer, text
test2.csv
--------------------------
real, real, text
test3.csv
--------------------------
text, text, text
post mortem:
--------------------------
the VirtualText driver does actually correctly
recognizes integers and doubles (both signed
and unsigned).
but a value is assumed to be 'numeric' only when
it's *not* enclosed within quotes; any quoted value
is assumed to represent 'text'.
please note: this one is an impossible-to-guess case;
some previous version of VirtualText attempted to
automatically recognize numbers.
And several users (correctly) loudly complained about
such a design choice, because their alphanumeric
relational keys become completely useless this way
[due to leading zeroes suppression].
Anyway, nursing for this is really simple: you
simply have to create a "real" table, setting
the appropriate datatypes, and SQLite will then
correctly cast to numeric types as required.
e.g. using test3.csv:
-----------------------
CREATE TABLE test2 (
pos_num DOUBLE NOT NULL,
neg_num DOUBLE NOT NULL,
string TEXT NOT NULL);
INSERT INTO test_ok (pos_num, neg_num, string)
SELECT pos_num, neg_num, string
FROM test3;
test_ok
--------------------------
real, real, text
bye Sandro
Jukka Rahkonen
First, it looks like the following ogr2ogr commands are producing rather
well behaving Spatialite database. I used them for copying data from one
Spatialite db which had a foulty layer with both POLYGON and MULTIPOLYGON
objects.
> ogr2ogr -f SQLite -dsco SPATIALITE=yes -dsco INIT_WITH_EPSG=yes -t_srs
epsg:3857 -nln osm_polygon -nlt MULTIPOLYGON berlin2.sqlite
berlin.sqlite osm_polygon
> ogr2ogr -update -t_srs epsg:3857 -nln osm_point berlin2.sqlite
berlin.sqlite osm_point
> ogr2ogr -update -t_srs epsg:3857 -nln osm_line berlin2.sqlite
berlin.sqlite osm_line
It looks like a good habit to use the INIT_WITH_EPSG=yes data store
creation option and to give the output projection explicitly with the
-t_srs parameter. That way the contents of the geometry_columns table look
right and for example Transform() works later. New layer name (-nln) is
not necessary but I use to include it so I remember how I can rename the
layer when I want. And giving the output geometry type with -nlt
MULTIPOLYGON solved the polygon/multipolygon mixture problem by making
them all to multipolygons. I can see and open all the layers in the
resulting database with Spatialite-GIS and QGIS 1.8.0. Both the native
QGIS Spatialite drover and the QSpatiaLite 5.0.3 plugin are working for
me. I was using gdal version 1.8.1 that came with installing Mapserver
from MS4W 3.0.3 package.
I was then trying to create a spatial view with Spatialite-gui but I did
not succeed totally. The result is always an error message with text
"SQLite SQL error: foreign key mismatch" The view is created but adding a
new line into views_geometry_columns fails.
What is odd is that it is possible to create the view and add the new line
into views_geometry_columns with ogr. I never knew that it is possible to
send SQL with ogrinfo in this way:
ogrinfo berlin2.sqlite -sql "create view osm_line_highway_not_null as select
OGC_FID AS ROWID, GEOMETRY, osm_id ,highway,ref,name, tunnel from osm_line
where highway is not null"
ogrinfo berlin2.sqlite -sql "INSERT INTO views_geometry_columns (view_name,
view_geometry, view_rowid, f_table_name, f_geometry_column) VALUES
('osm_line_highway_not_null', 'GEOMETRY', 'ROWID', 'osm_line', 'GEOMETRY')"
Even Rouault wrote a note too:
Note: you must call the primary key ROWID if you want QGIS to be able to
display the view on the map. I'm not sure why this is necessary (the OGR
driver works with other names), may be a limitation of the qgis spatialite
driver.
So my first question is that what might go wrong when creating spatial
view with Spatialite-gui? The error is referring mismatch in the foreing
key but I cannot see a collision in "f_table_name, f_geometry_column)
REFERENCES geometry_columns(f_table_name,f_geometry_column)".
I also imported the same data through shapefiles into empty database
created with Spatialite-gui. Not surprisingly there were no errors with
creating spatial views.
My second question is that is there any more reliable tool than ogr2ogr
for converting data from PostGIS into Spatialite? I would not really like
to use shapefiles in between because of attribute name restrictions but of
course that is one option. Would it be better to use ogr without the
SPATIALITE=yes option with through FDO system and with WKT of WKB
formatted geometries?
-Jukka Rahkonen-
a.fu...@lqt.it
many long questions, few short answers ;-)
> It looks like a good habit to use the INIT_WITH_EPSG=yes
> data store creation option
>
please note well: this is completely useless using any
recent SpatiaLite version (> 2.3.1), because now the
spatial_ref_sys table will be correctly populated
immediately when you'll create a new DB-file.
This is internally managed by libspatialite itself,
once CreateSpatialMetadatata() is invoked.
(I'll answer later to your question about Spatial Views ...)
> My second question is that is there any more reliable tool
> than ogr2ogr for converting data from PostGIS into Spatialite?
>
http://www.gaia-gis.it/OpenLite/index.html
I hope to be able to release the "stable" version
in the next few days (really ...)
> Would it be better to use ogr without the SPATIALITE=yes
> option with through FDO system and with WKT of WKB
> formatted geometries?
>
may well be ... if you are merely interested to store
somewhere your geometries in a vaguely SQL-related fashion.
SpatiaLite is a complete self-standing Spatial DBMS,
supporting advanced Spatial Analysis capabilities
(full-fledged Spatial SQL) and implementing an effective
Spatial Index.
Roughly speaking SpatiaLite can offer you about the same
identical features supported by PostGIS: the main
difference between them is weight, complexity and
underlying architecture.
PostGIS is client-server, SpatiaLite isn't.
PostGIS is heavy and complex, SpatiaLite is elementary
simple and lightweight.
None is "better": they simply are "different".
You can freely choose the one or the other accordingly to
your very specific requirements.
As a rule-of-the-thumb, PostGIS is the obviously winner in
the enterprise DBMS role; SpatiaLite takes its revenge on
standalone desktops, smart-phones, embedded- and mobile- devices.
FDO ranks in a completely different product class:
- no Spatial SQL, no Spatial Analysis capabilities;
simply supporting basic Geometry storage
- no Spatial Index support at all
bye Sandro
Jukka Rahkonen
> Hi Jukka,
>
> many long questions, few short answers ;-)
>
>> It looks like a good habit to use the INIT_WITH_EPSG=yes
>> data store creation option
>>
>
> please note well: this is completely useless using any
> recent SpatiaLite version (> 2.3.1), because now the
> spatial_ref_sys table will be correctly populated
> immediately when you'll create a new DB-file.
> This is internally managed by libspatialite itself,
> once CreateSpatialMetadatata() is invoked.
If one works with ogr2ogr, for example because of the large vector format
support http://gdal.org/ogr/ogr_formats.html it is not completely useless.
I will need to make some more trials and try if it would be better to
create first an empty database with Spatialite itself and let ogr2ogr just
feed new data into it.
> (I'll answer later to your question about Spatial Views ...)
>
>> My second question is that is there any more reliable tool
>> than ogr2ogr for converting data from PostGIS into Spatialite?
>>
> http://www.gaia-gis.it/OpenLite/index.html
> I hope to be able to release the "stable" version
> in the next few days (really ...)
Great, I will try it immediately.
>> Would it be better to use ogr without the SPATIALITE=yes
>> option with through FDO system and with WKT of WKB
>> formatted geometries?
>>
>
> may well be ... if you are merely interested to store
> somewhere your geometries in a vaguely SQL-related fashion.
...
> FDO ranks in a completely different product class:
> - no Spatial SQL, no Spatial Analysis capabilities;
> simply supporting basic Geometry storage
> - no Spatial Index support at all
...
Blaah, not attractive at all. I will go on with Spatialite. And the less I
write here the more time you'll have to finalise Spatialite 3.0 and the
faster you can get grip on the gdal Spatialite driver...
-Jukka Rahkonen-
Jukka Rahkonen
Proj4 has been traditionally missing the +towgs84 parameters for the
Finnish coordinate systems EPSG:2391, 2392, 2393, 2394, 3386, 3387 and
3067. They are now correct at least in the Proj trunk
http://gdal.org/ogr/ogr_formats.html
Hopefully Spatialite 3.0 will include the correct parameters. Otherwise
there will always occur 100-200 meter error when re-projecting to other
systems by using the EPSG codes.
-Jukka Rahkonen-
a.fu...@lqt.it
as a general policy SpatiaLite always ships
the EPSG dataset included within the "stable
version" of Proj.4 (currently: 4.7.0)
I'll be absolutely glad to support as soon as
possible a fresher Proj.4 once officially released,
but depending on "trunk" (i.e. unstable) features
always is an unwise and unsafe option, except for
experimental testing purposes.
Anyway, patching by your own any Finnish SRS
applying some specific +towgs84 string is really
simple: look at the column 'proj4text' in the table
'spatial_ref_sys'.
You can eventually automate the whole process
using a small SQL script like the following one:
-------------------------------------------
BEGIN:
UPDATE spatial_ref_sys SET proj4text = '...'
WHERE SRID = 2391;
UPDATE spatial_ref_sys SET proj4text = '...'
WHERE SRID = 2392;
UPDATE spatial_ref_sys SET proj4text = '...'
WHERE SRID = 2393;
UPDATE spatial_ref_sys SET proj4text = '...'
WHERE SRID = 2394;
UPDATE spatial_ref_sys SET proj4text = '...'
WHERE SRID = 3386;
UPDATE spatial_ref_sys SET proj4text = '...'
WHERE SRID = 3387;
UPDATE spatial_ref_sys SET proj4text = '...'
WHERE SRID = 3067;
COMMIT;
-------------------------------------------
bye Sandro
Jukka Rahkonen
Yesterday I could convert data from PostGIS into Spatialite database with
OpenLite with more or less satisfying result. Today with another computer
I cannot do it anymore. The data is the same OSM data. OpenLite is listing
the PostGIS tables, I can select them for data transfer and the From...To
lines appear into the task pane but the Start Data Transfer button remains
inactive. Could it be because this time I have PostgreSQL 9.0 while
yesterday I was using PostgreSQL version 8.3? When I run OpenLite for the
first time on this computer I pointed it to use the version 9.0 pglib.dll.
-Jukka Rahkonen-
Jukka Rahkonen
I am preparing a reasonably sized test database. I have collected some
polygons into it and filesize is now 5.8 gigabytes. It starts to be a
little bit slow to handle and I wonder if it makes sense to go on with
adding a few lines into the database too. Are there any recommendations
about how big database is still well manageable if the aim is to use it
for everyday SQL queries and not just for a long term storage?
-Jukka Rahkonen-
a.fu...@lqt.it
there is no simple answer to this question:
it mainly depends on hardware and operating system,
because SQLite fully relies on the underlying filesystem
and doesn't applies any special caching strategy.
As a general rule, Linux is by far better
than Windows: in many cases Windows is rather
slow when accessing really huge files.
A second rule-of-the-thumb: as long as the DB-file
size is less than (or quite equal to) the total
amount of the installed RAM, performances are reasonably
good.
Otherwise you'll experience a continuous I/O buffer
stall condition, this producing a painful performance
degradation.
If you actually have a lot of available RAM, defining
a generous page cache can widely help to speed-up processing:
http://www.sqlite.org/pragma.html#pragma_cache_size
bye Sandro
a.furieri
I've already promised you an answer about
your Spatial View problem: here we are.
PLEASE NOTE: you should *never answer to another
message changing the subject, because such an
action make following the threads impossible;
start a new thread instead.
=================================================
#1) test DB: Berlin.sqlite (created by ogr2ogr)
#2) you already have successfully create a VIEW:
CREATE VIEW osm_line_highway_not_null AS
SELECT OGC_FID AS ROWID, GEOMETRY, osm_id ,highway,
ref,name, tunnel
FROM osm_line
WHERE highway IS NOT NULL;
please note: this is (partially) invalid.
when you create some VIEW it's *always* a good
practice to explicitly assign an alias-name to
each column:
CREATE VIEW osm_line_highway_not_null AS
SELECT OGC_FID AS ROWID, GEOMETRY AS GEOMETRY,
osm_id AS osm_id, highway AS highway,
ref AS ref, name AS name, tunnel AS tunnel
FROM osm_line
WHERE highway IS NOT NULL;
more or less the same, a little bit more pedantic:
but this simple extra-care will save you some big
headaches afterwards (e.g. using the VIEW on QGIS).
#3) registering the VIEW on "views_geometry_columns":
INSERT INTO views_geometry_columns
(view_name, view_geometry, view_rowid, f_table_name,
f_geometry_column)
VALUES ('osm_line_highway_not_null', 'GEOMETRY', 'ROWID',
*** FAILURE *** "SQLite SQL error: foreign key mismatch"
attempting to execute yet another time the same identical
statement, this time using ogr2ogr, the INSERT will be
a successful one.
Really puzzling, isn't ?
POST MORTEM:
=====================
ogr2ogr have created a *broken* "geometry_columns" table:
there isn't any Primary Key defined at all :-(
this obviously happens because ogr2ogr doesn't invokes
(as required) InitSpatialMetadata() in order to correctly
initialize the DB: ogr2ogr directly invokes a generic
"CREATE TABLE ..." statement of its own: but following this
hazardous approach the metadata tables are incorrectly defined.
why we'll get a "foreign key mismatch" ?
really simple to explain: lacking the corresponding Primary
Key, the Foreign Key constraint will inexorably fail on
any case.
why ogr2ogr will succeed instead ?
this too is quite simple to explain: in order to
make Primary/Foreign Key constraints effective, an
appropriate directive is required to be executed:
PRAGMA foreign_keys = 1;
obviously ogr2ogr omits to perform this step, thus
posing the whole DB referential integrity at serious
risk.
conclusion: using the current implementation of
ogr2ogr in order to create any SpatiaLite DB
is a really hazardous option.
I've already prepared several patches for OGR:
and I hope to be able to release all them ASAP.
bye Sandro
Jukka Rahkonen
used to write mails into various OSGeo lists where changing the mail
title starts a new thread. I see that Google groups behave differently
and noticed the mess now when I opened this discussion first time with
a browser.
I had already done some further trials with ogr2ogr. I feel now that a
good result can be achieved by making the initial conversion from for
example MapInfo tab into Spatialite with ogr2ogr and cleaning the
result by making a new conversion with OpenLite from the Spatialite
database generated by ogr2ogr into a new empty Spatialite db. It is a
rather fast operation, with 4.5 million polygons and 5.8 GB file size
it took exactly two hours with a weak laptop PC.
-Jukka-