Re: [Qgis-user] qgis and spatialite

Wed, 28 Oct 2015 14:01:55 -0700

The usual way to make a non-spatial table into a real spatial one, with geometry column and all accompanying metadata is with the function AddGeometryColumn(). It takes 5 parameters: the table name, the name for the geometry column (usually 'geometry'), the EPSG code for the CRS, the type of geometry, and the number of dimensions.
Then you use the two lon and lat columns to create the actual point geometries with an SQL update statement.
So, assuming the data table is imported into Spatialite as "data_tabe", the longitude and latitude are named 'lon' and 'lat', and the numbers are degrees in a WGS84 coordinate system, then you do

SELECT AddGeometryColumn('data_table','geometry',4326,'POINT',2);
UPDATE data_table SET geometry=MakePoint(lon, lat, 4326);

and you're all set.

On 10/28/2015 05:09 PM, Michael.Dodd wrote:

I am trying to use spatialite with qgis but can’t get spatialite to recognise geometry columns even though qgis can display the same data when given a .csv or shapefile. The data consist of a large spreadsheet including lat and long columns, this reads and displays fine in qgis.

 

When I put this into spatialite I can’t get spatialite to recognise that two of the columns are lat and long (I have only just started using spatialite).

 

I can’t convert the full data in qgis into a shapefile and import that into spatialite as spatialite refuses to accept the shapefile saying it contains errors or wrong kind of data.  The actual shapefile seems fine as it opens again in qgis.

 

I can chop the datafile down to just a few columns containing the coordinates and a key, convert that to shapefile and import that into spatialite. I can also import the full spreadsheet into spatialite as a text file but I can’t see a way of joining the two together so that the two separate tables both have the geometry by linking on the key column even though I can see the dialogue that deals with links between tables.

-- The Open University is incorporated by Royal Charter (RC 000391), an exempt charity in England & Wales and a charity registered in Scotland (SC 038302). The Open University is authorised and regulated by the Financial Conduct Authority.

This mail was received via Mail-SeCure System.


_______________________________________________
Qgis-user mailing list
[email protected]
http://lists.osgeo.org/mailman/listinfo/qgis-user
This mail was received via Mail-SeCure System.




_______________________________________________
Qgis-user mailing list
[email protected]
http://lists.osgeo.org/mailman/listinfo/qgis-user

Reply via email to