From xy table to spatial table in spatialite

A short note on how to create a spatial table from a table with latitude and longitude coordinates in your SQLite / Spatialite database. In the example below, the table I am working with is ‘labels’.

First step is to create the Geometry column using AddGeometryColumn. Note that the number 4326 is the EPSG code representing the coordinate reference system WGS84 geodetic. See this post about changing the coordinate system.

Select AddGeometryColumn ('labels', 'Geometry', 4326, 'POINT', 2)

Second step is to populate the newly created Geometry column based on the longitude / latuitude values:

UPDATE labels SET Geometry=MakePoint(longitude, latitude, 4326)

MakePoint() is the Spatial function that builds a point-like Geometry from corresponding coordinates. See the Spatialite reference list or e.g, see this Q&A on stack exchange.

For the opposite, extracting X and Y coordinates from a spatial table, see this post. See also this post about some approaches to get your GPS locations in a spatialite database.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s