SQL / SpatiaLite：如何将一个列声明为几何？ [英] SQL/SpatiaLite: how to declare a column as geometry?

问题描述

  CREATE TABLE SomeShapes AS 
 SELECT ash.id，ash.Geometry 
 FROM AllShapes ash 
 WHERE ash.id = 30 
  

$ b $但是，这会返回一个正常表，因此当我尝试将它加载到GIS程序（QGIS）中时，它不会显示几何图形。如何声明geometry列包含几何？

解决方案

您需要创建一个非空间表，然后添加几何列到它。

然后，你可以插入数据到你的表中。

p>

它不能在一步完成（ create table as select ）。从文档：

创建一个Geometry类型的同时对应的表是
创建的是不允许的。您必须先创建表格，然后
再次添加Geometry-column并作为单独的步骤。

<$ （
id INTEGER NOT NULL
PRIMARY KEY AUTOINCREMENT，
name TEXT NOT NULL，
measured_value DOUBLE NOT NULL）; pre> CREATE TABLE test_geom

SELECT AddGeometryColumn（'test_geom'，'Geometry'，4326，'POINT'，'XY'）;


另外，考虑到您可能需要使用空间索引改善性能

  SELECT CreateSpatialIndex（'test_geom'，'Geometry'）; 
  

I am creating a new table through a SQL query from a spatial table:

CREATE TABLE SomeShapes AS
SELECT ash.id, ash.Geometry
FROM AllShapes ash
WHERE ash.id = 30

However, this returns a "normal" table, so when I try to load it in a GIS program (QGIS), it doesn't show the geometry. How do I declare that the geometry column contains, well, geometry?

解决方案

You need to create a "non-spatial" table, and then add the Geometry column to it.

Then, you can insert data into your table.

It can't be done in one single step (create table as select). From the documentation:

Creating a Geometry-type at the same time the corresponding table is created isn't allowed. You always must first create the table, then adding the Geometry-column in a second time and as a separate step.

CREATE TABLE test_geom (
  id INTEGER NOT NULL
    PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  measured_value DOUBLE NOT NULL);

SELECT AddGeometryColumn('test_geom', 'Geometry', 4326, 'POINT', 'XY');

Also, take into account that you may want to use spatial indexes to improve the performance

SELECT CreateSpatialIndex('test_geom', 'Geometry');

这篇关于SQL / SpatiaLite：如何将一个列声明为几何？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！