<html style="direction: ltr;">
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
<style type="text/css">body p { margin-bottom: 0cm; margin-top: 0pt; } </style>
</head>
<body style="direction: ltr;" bidimailui-charset-is-forced="true"
text="#000000" bgcolor="#FFFFFF">
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. <br>
Then you use the two lon and lat columns to create the actual point
geometries with an SQL update statement. <br>
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<br>
<br>
<tt>SELECT
AddGeometryColumn('data_table','geometry',4326,'POINT',2);</tt><tt><br>
</tt><tt>UPDATE data_table SET geometry=MakePoint(lon, lat, 4326);</tt><br>
<br>
and you're all set.<br>
<br>
<div class="moz-cite-prefix">On 10/28/2015 05:09 PM, Michael.Dodd
wrote:<br>
</div>
<blockquote
cite="mid:52F173986979BD41AE31A411151FFF871719D76A@EX10-MBX-LIVE-B.open.ac.uk"
type="cite">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="Generator" content="Microsoft Word 14 (filtered
medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-fareast-language:EN-US;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri","sans-serif";
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri","sans-serif";
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
<div class="WordSection1">
<p class="MsoNormal">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. <o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">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).
<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">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.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">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.<o:p></o:p></p>
</div>
-- 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.
<br>
<br>
This mail was received via Mail-SeCure System.<br>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
Qgis-user mailing list
<a class="moz-txt-link-abbreviated" href="mailto:Qgis-user@lists.osgeo.org">Qgis-user@lists.osgeo.org</a>
<a class="moz-txt-link-freetext" href="http://lists.osgeo.org/mailman/listinfo/qgis-user">http://lists.osgeo.org/mailman/listinfo/qgis-user</a>
This mail was received via Mail-SeCure System.
</pre>
</blockquote>
<br>
</body>
</html>