[postgis-users] How to make some geometry column visible to Qgis

[Sandro Santilli]

On Fri, Oct 19, 2012 at 01:12:54AM -0700, Pieri70 wrote:
> Ciao Sandro
> I must premise that these are my first steps in using postgis, so, be
> patient.
> Then, isn't it possible to query data directly from the main "entities"
> table by creating views based on geometry type?

Yes, it is possible, just not necessarely recommendable.

> Today I was testing this technique without success..
> 
> I have a big table (entities) with all the dxf geometries inside, with
> geometry column "wkb_geometry" and GEOMETRY as data type (srid 3004).
> My syntax is:
> 
> create or replace view public.pointdata as
> select * from entities where layer like 'somepointlayer';

This should work.

> INSERT INTO geometry_columns (f_table_catalog,f_table_schema, f_table_name,
> f_geometry_column, coord_dimension, srid,type)
>   VALUES ('testdb','public', 'pointdata', 'geom', 3, 3004,'POINT');

This shouldn't work, as "geometry_columns" is a view (assuming you correctly
installed PostGIS-2.0). 

> This create a view named pointdata but automatically set f_geometry_column =
> "wkb_geometry" (the name of the source geometry column) and type = GEOMETRY
> as in source table.

That's because you selected *, so you got all the attribute names and
datatypes as found in the original table. Instead you want to cast your
"wkb_geometry" field to a typed geometry:

  SELECT "wkb_geometry::geometry(3004, pointz), ...

--strk;

 http://www.cartodb.com - Map, analyze and build applications with your data

                                       ~~ http://strk.keybit.net