关于postgis视图中geometry项添加

最新推荐文章于 2022-05-04 15:11:45 发布

最新推荐文章于 2022-05-04 15:11:45 发布 · 677 阅读

文章标签：

#SQL #F# #HTML

open webgis 专栏收录该内容

3 篇文章

订阅专栏

本文介绍了在PostGIS中无法直接使用AddGeometryColumn为视图和批量插入操作注册geometry字段的情况，并提供了手动注册的方法。文章详细展示了如何通过SQL语句将视图和衍生表中的geometry字段手动添加到geometry_columns表中。

在视图中，不能使用AddGeometryColumn建立geometry字段，postgis参考中明确指出了：在sql视图和批量插入中这两种情况下，你需要把一个geometry字段注册给一个含geometry字段的表，但是此时不能通过AddGeometryColumn实现( 翻译有点烂)。原文如下：

（

PostGIS 1.5.1 Manual 写道

Two of the cases where you want a geometry column to be registered in the geometry_columns table, but you can't use AddGeometryColumn, is in the case of SQL Views and bulk inserts. For these cases,

），解决办法如下：

PostGIS 1.5.1 Manual 写道

4.3.4. Manually Registering Geometry Columns in geometry_columns

The AddGeometryColumn() approach creates a geometry column and also registers the new column in the geometry_columns table. If your software utilizes geometry_columns, then any geometry columns you need to query by must be registered in this table. Two of the cases where you want a geometry column to be registered in the geometry_columns table, but you can't use AddGeometryColumn, is in the case of SQL Views and bulk inserts. For these cases, you must register the column in the geometry_columns table manually. Below is a simple script to do that.

--Lets say you have a view created like this
CREATE VIEW  public.vwmytablemercator AS
	SELECT gid, ST_Transform(the_geom,3395) As the_geom, f_name
	FROM public.mytable;

--To register this table in AddGeometry columns - do the following
INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
SELECT '', 'public', 'vwmytablemercator', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom)
FROM public.vwmytablemercator LIMIT 1;

--Lets say you created a derivative table by doing a bulk insert
SELECT poi.gid, poi.the_geom, citybounds.city_name
INTO myschema.myspecialpois
FROM poi INNER JOIN citybounds ON ST_Intersects(citybounds.the_geom, poi.the_geom);

--Create index on new table
CREATE INDEX idx_myschema_myspecialpois_geom_gist
  ON myschema.myspecialpois USING gist(the_geom);

--To manually register this new table's geometry column in geometry_columns
-- we do the same thing as with view
INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
SELECT '', 'myschema', 'myspecialpois', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom)
FROM public.myschema.myspecialpois LIMIT 1;

附原文地址:http://www.postgis.org/documentation/manual-1.5/ch04.html#Manual_Register_Spatial_Column