9

I have an sqlite file from my Wigle Android app (wiglewifi.sqlite). It's not a spatial file, just tables with lat/lon and other data.

I can add spatialite layers just fine, but cannot seem to find a way of adding this table in the same way I'd add a delimited text layer - how to go about doing this?

Clarification: It's possible to add as a table (as per @Giovanni-Manghi's answer below), OR it's possible to export the table as a CSV and then add as a delimited text layer, but ideally I'd like a point layer based directly on the SQLite table (in the same way as in ArcGIS I would add a table then right-click and "Display XY data...")

Improve this question
0

4 Answers 4

Reset to default
9

I don't know if this is the answer you want, as it's not a point and click answer, but this is how I would do it probably.

In Spatialite, add a new geometry column. Assuming you want WGS84 (lat/lon)

AddGeometryColumn( yourTableName , geometryColumnName, 4326, 'POINT', 'XY')

Then create the geometry from WKT generated from the X/Y coordinates

update yourTableName set geometryColumnName = GeomFromText('POINT ('+latColumn=' '+lonColumn+')',4326)

Perhaps there is a way of wrapping this into a qgis / spatialite / spatialite-gui function, as I seem to use this fairly frequently, and it's easy to get the quote marks wrong.

EDIT -----

There is actually a more foolproof way I discovered recently - for the second statement use: 

update yourTableName set geometryColumnName = MakePoint(XColumn,YColumn,EPSG projectioncode)
Improve this answer
2
  • InitSpatialMetadata to convert plain sqlite3 database to spatialite one deserves to be mentioned. You either have to do it in transaction or add argument 1 (select InitSpatialMetadata(1)), otherwise it takes very long (half an hour on my computer).
    – Jan Hudec
    Commented Aug 28, 2014 at 15:16
  • 1
    This worked nicely - but for people like me less familiar with Spatialite/SQL it's worth adding that (I found that) the first statement requires 'SELECT' before it. So SELECT AddGeometryColumn(...etc...). Also the table name and geometry column name can have (?should have?) single quote marks around them - so yourtablename would be 'yourtablename' and geometryColumnName would be 'geometryColumnName'.
    – Rostranimin
    Commented Sep 18, 2017 at 17:33
5

Since QGIS can read OGR layers, try using OGR's Virtual Format: http://gdal.org/ogr/drv_vrt.html .

Create a text file with the following content and open it as vector layer in QGIS (Layer -> Add Vector Layer). Just replace the table name as well as the latitude and longitude column names according to your database table. It's recommended to store the file with the suffix .vrt

<OGRVRTDataSource>
    <OGRVRTLayer name="table_name">
        <SrcDataSource>/path/to/your/sqlite.db</SrcDataSource> 
        <SrcLayer>table_name</SrcLayer> 
        <GeometryType>wkbPoint</GeometryType>
            <LayerSRS>WGS84</LayerSRS>
        <GeometryField encoding="PointFromColumns" x="lng" y="lat"/> 
    </OGRVRTLayer>
</OGRVRTDataSource>
Improve this answer
1
  • You might also want to add the attribute relativeToVRT="1" to the SrcDataSource tag e.g. when the sqlite file is located right next to the .vrt/xml file so that a declaration of a relative path might be more convenient.
    – klaus
    Commented Jul 17, 2021 at 23:56
1

Use the standard "add vector..." dialog or drag and drop the .db file in the QGIS canvas.

Improve this answer
3
  • That works to add the table as a non-spatial layer only ... how do I plot the points as I would for a delimited text layer, identifying x/y (lon/lat) columns?
    – Simbamangu
    Commented Jan 28, 2012 at 20:06
  • 1
    I don't know if there as way to turn any x/y columns in a whatsoever table into a new layer of points, I don't think so (it would be a nice new feature). Meanwhile just save your Sqlite table into csv and import it again, With the "xytools" plugin you can also import xls and ods tables.
    – gioman
    Commented Feb 2, 2012 at 11:36
  • I agree that it would be a nice idea for a plugin. There are many people that just have a sqlite table and want to use it as-is.
    – AndreJ
    Commented Aug 25, 2012 at 5:14
0

Artisan way: a)@Giovanni-Manghi's way, plus b) Import the XYlayer again to your SQlite, then c) Erase previous table in your SQlite

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.