How to set default CRS for a table?

18 views
Skip to first unread message

Chris Smith

unread,
Aug 3, 2022, 10:06:54 PMAug 3
to SpatiaLite Users
Having generally great success loading a spatialite 5 table using geoalchemy2.

One thing that's eluded me thus far is how to set the default coordinate reference system to WGS84, so that one can add the table as a layer to a QGIS project without having QGIS complain about the lack of a CRS for the table.

Thanks,
Chris Smith

a.fu...@lqt.it

unread,
Aug 4, 2022, 2:22:36 AMAug 4
to spatiali...@googlegroups.com
Hi Chris,

you are just required to create your Geometry columns following the
expected method:

1) you must create first your basic Table by declaring all the
xpacted columns but carefully avoiding to declare the
Geometry column itself. someting like:

CREATE TABLE mytable (
id INTEGER PRIMARY KEY,
field_1 TEXT NOT NULL,
field_2 DOUBLE NOT NULL);

2) then you should finally add the Geometry column by calling
the AddGeometryColumn() SQL function.

SELECT AddGeometryColumn('mytable', 'geom', 4326,
'MULTIPOLYGON', 'XY');

this will add to the previous table a further "geom" column
expected to contain Geometries of the 2D MultiPolygon type
using the WGS84 reference system.

Note: AddGeometryColumn() will not simply add the column to
the Table.
It will create a full set of safeguard Triggers forbidding
to insert invalid geometries and will automatically
insert a row into the "metadata table" geometry_columns.

After completing all these steps "mytable" will finally
be promoted to be a genuine Spatial Table, and QGIS will
be able to acces it without any complaint.

3) a very common and useful option is to declare a
Spatial Index supporting the above Geometry column:

SELECT AddSpatialIndex('mytable', 'geom');

this will not simply create the corresponding R*Tree table
but will also automatically create all the Trigerrs required
for correctly synchronizing the main table and its
Spatial Index.

bye Sandro

Chris Smith

unread,
Aug 4, 2022, 6:46:39 AMAug 4
to SpatiaLite Users
Ah, so.

Let me experiment with the SRID argument when I create the table to see if that cures what ails me => https://github.com/geoalchemy/geoalchemy2/blob/5922af10b2af64800d473fdcf8001d49c6c666c4/geoalchemy2/types.py#L75

Thanks!
Reply all
Reply to author
Forward
0 new messages