Monday, October 20, 2014

Using SpatiaLite GUI to create a point geometry table from an ordinary table

Sometimes I have an ordinary table with numerical latitude and longitude columns and I need to construct a point geometry table from the records in the normal table, e.g. stations_raw. An example of such a table is shown in the screenshot below.

In the SpatiaLite GUI, enter the following SQL command to create a new table named stations:
CREATE TABLE stations AS 
SELECT
PK_UID,
code,
name,
ST_GeomFromText(
'POINT('||lng||' '||lat||')'
4326
)
AS Geometry
FROM stations_raw

Note: 4326 in the example command is just the geographical coordinate system SRID of the data.

Execute the command.


The table stations is created.


The table is still a non-spatial table. In order to change it to a spatial table, the following steps need to be done.

  1. In the SpatiaLite GUI, press mouse right click on the geometry column.


  2. In the pop up menu, choose Recover geometry column,

    The Recover Geometry column dialog box appears.

  3. In the SRID field, type in the data's SRID e.g. 4326 for geographical latitude and longitude data.
  4. In the Dims field, choose the appropriate dimensions of the data, e.g. XY for 2-D.
  5. In the Geometry field, choose the appropriate geometry of the data, e.g. POINT for point data.
  6. Press OK.

    If the parameters are correct, the following message will appear.


    The GUI should show the table as a spatial table (with a globe).


No comments:

Related Posts with Thumbnails