Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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...")

share|improve this question

3 Answers 3

up vote 7 down vote accepted

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)
share|improve this answer
    
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 2 days ago

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

share|improve this answer
    
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 Jan 28 '12 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. –  Giovanni Manghi Feb 2 '12 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. –  Andre Joost Aug 25 '12 at 5:14

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>
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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