7

I would like to create a new SpatiaLite table based on a command.

For example, I would like to transform a table to a different coordinate system:

SELECT Transform(Geometry, 3068) FROM points_berlin;

To create a new table I would do this in PostGIS:

CREATE TABLE points_berlin_3068 AS
SELECT Transform(Geometry, 3068) FROM points_berlin;

Although this creates the table and the table has as many rows as the input table it is not a spatial table. How do I get the geometry to show up in the new table?

Improve this question

2 Answers 2

Reset to default
11

SpatiaLite requires you to explicitly register geometry columns in its ecosystem (updating metadata, adding triggers) using the provided management functions; SQLite does not support automatic detection like with PostgreSQLs type-modifier system.

Having created a new table as you specified you should be able to 'spatially enable' it with

SELECT RecoverGeometryColumn('points_berlin_3068', 'geometry', 3068, 'POINT', 'XY');
Improve this answer
3
  • 2
    RecoverGeometryColumn is unchanged but this is a newer document about SpatiaLite functions: gaia-gis.it/gaia-sins/spatialite-sql-5.0.0.html.
    – user30184
    Commented Oct 28, 2020 at 10:46
  • @user30184 Thx.! I use to look up the most commonly compiled version in QGIS.
    – geozelot
    Commented Oct 28, 2020 at 10:49
  • 2
    Interesting workflow, thank you! Worth noting: pay attention to how you name the new geometry colum, as its default name is the command you executed! If you execute the command as I did, you would have to subsequently run SELECT RecoverGeometryColumn('points_berlin_3068', 'Transform(Geometry, 3068)', 3068, 'POINT', 'XY'); since the geometry column is now named Transform(Geometry, 3068). To avoid this you can use an alias in the original select command: CREATE TABLE points_berlin_3068_newest AS SELECT Transform(Geometry, 3068) AS Geometry FROM points_berlin;
    – BritishSteel
    Commented Oct 28, 2020 at 13:53
1

I find thast using RecoverGeometryColumn adds the record to geometry_columnns, and also allows QGIS to read the table. However, ArcGIS cannot read the geometries in the table. Still looking for a way to issue a CREATE TABLE AS statement and have it be a valid table that other GIS products can read.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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