In a previous article we have discussed ( How to create a
SpatiaLite database with QGis 2.8 ) how to import a shapefile
into a SpatiaLite database. The method is the same for other types of spatial
data (kml, gml, …). We will now discuss how to import non-spatialized data
(Excel, txt, csv, …) and especially those containing location attributes (XY,
Lat / Lon, …) while creating their geometries. We will also discuss how to
export SpatiaLite tables as shapefiles.
How to import tables into SpatiaLite
To import a table file into a SpatiaLite
database, follow these steps:
1. Open the Database Manager by clicking Database Manager
under the menu Database s. Expand SpatiaLite
and select the database where you want to import your tabular data into the
tree (here BaseTest )
2. Click the Table menu |Import
a file or layer to open the Import Vector Layer dialog
box .
3. Click on the […] button displayed to the right of the Entry
drop-down list and select the Excel file (here hotels.xls)
to load into the database.
4. Click the Update Options button to load the rest of the dialog
box.
The output table name will be filled with the name of the input file. Note
that all related options regarding spatial data is not editable and is greyish
(as shown in the following screenshot).
This is because SpatiaLite treats the input as a non-spatial array, even
if it has coordinates stored in the table. We will add the spatial component to
the table in a later step.
5. click the OK button to import the file.
6. after a while you will be notified that the import is complete.
To display the new table, you will need to
refresh the tree by selecting BaseTest.sqlite in the tree view
and clicking Refresh. under Database or
press the F5 key on your keyboard. The hotels table should
appear with the table icon next to it.
Note the Google_Longitude and Google_Latitude fields . These
fields contain the hotels coordinates (EPSG 4326). Click on the tab Table
on the information panel to display the table entries. Note that the Preview
tab is not available because the selected table has no geometry.
Be careful about the type of data assigned to your coordinate fields
(here Google_Longitude and Google_Latitude). According to an algorithm that has
always escaped me, switching from Excel cell format to data type to database
has some odd results. You may end up with text-type coordinate fields. In this
case, there will be no errors displayed thereafter, except that your geometry
will be zero and you will have no point in the display. It is therefore at this
stage that we must correct the shot, after it will be too late and it will
start all over again.
If your X and Y fields are in text, open a SQL window in the
database manager (second button of four) then enter one by one the following
SQL queries to create a new numeric field and fill it with the contents of the
text field:
ALTER TABLE your table ADD Xm double precision
UPDATE your_table SET Xm = X
ALTER TABLE your table ADD Ym double precision
UPDATE your_table SET Ym = Y
where X and Y are your text fields.
[/ stextbox]At this point, the import of the table is complete. However, since the
coordinates are present in the table, you can create a point geometry column
and add it to the table. This will transform the table into a layer of
points.
How to add a geometry column 1. With the hotels table selected in the tree view, click Table |Editthe Table to open the TableProperties window .
2. click on the button add a geometric column . In
the new window, set the following options to create the geometry field:
- Name: The name of the field that
will contain the geometry information - Type: the type of geometry, here
POINT - Dimensions: the number of
dimensions (values) for the geometry (2 for XY, 3 for XYZ)
SRID: the coordinate system of the geometry
field
3.
Close the table properties. To view the newly edited table, you will need to
refresh the tree by selecting BaseTest.sqlite in the tree view and
clicking Refresh. under Database or press the F5
key on your keyboard.
The hotels table should now appear with the dot icon next to it.
Now that the hotels table has a geometry field, we must fill it
with the available coordinates for each record. We will do this with an SQL
update request and using the SpatiaLite MakePoint function :
1. Open the SQL window by clicking on the
menu Database -> SQL Window .
2. Enter the following query in the SQL query text box: UPDATE hotels
SET geom = MakePoint (Google_Longitude, Google_Latitude, 4326);
MakePoint is a function of SpatiaLite that creates a new point object.
3. Click the Execute button (F5) to execute the
query. The query will not return any results but will indicate the number of
modified rows.
4. In the SQL window, click the Close button to close the window.
5.
To view the changes made to the hotels table , you will need to refresh
the tree.
6. Under the SpatiaLite section of the information
tab , note that a warning appears indicating that no spatial index has been
defined. To improve the access speed, it is preferable that a spatial index be
created. Click create.
7. To preview the geometry, click the Preview
tab .
How to export tables from SpatiaLite as shapefile
To export a table as a shapefile, proceed as
follows:
1. Open the Database Manager by clicking Database Manager
under Database . Expand SpatiaLite , and then
select the database from which you want to export a table in the tree pane.
2. In the tree view, select the table you want to export.
3.Click on Table |Export to File to open the Export
Vector File dialog box .
4. Click on the […] button to the right of the output
file area and give a name of the output file. Note that you can export
only in shapefile format using this tool.
5. Set the encoding, SRC Source, and target SRC options, or you can leave them
deselected to use the default values. Select Overwrite Existing
if you want to overwrite an existing file.
The following screenshot shows the export to a shapefile of the hotels table.
> According to an algorithm that has always escaped me,
> switching from Excel cell format to data type to database
> has some odd results. You may end up with text-type coordinate
> fields.
Probably because you stored the numeric value using the mainland Europe decimal separator, the comma – separating the fields with a semi-colon or a tab. You should user the British/American separator, the dot.