[postgis-users] field type "public.geometry"

Phil Hurvitz phurvitz at uw.edu
Thu Sep 20 18:51:02 PDT 2012


Thanks Bborie, there is something that we must be doing wrong or 
misunderstanding, or both. I assume we want all geometry columns to be 
associated with the public schema. However, there seems to be a mix.

It seems we have a geometry data type in schema gis and a geometry data 
type in schema public. When search order is changed, PostgreSQL will use 
the data type that it finds first by search order.

twins=# set search_path to public;
SET
twins=# \dT
           List of data types
  Schema |     Name      | Description
--------+---------------+-------------
  public | box2d         |
  public | box3d         |
  public | box3d_extent  |
  public | chip          |
  public | geography     |
  public | geometry      |
  public | geometry_dump |
  public | gidx          |
  public | pgis_abs      |
  public | spheroid      |

To complicate matters, it also seems that the data types in schema gis 
are from `create extension postgis', since they include the raster type 
as well.

twins=# set search_path to gis;
SET
Time: 0.085 ms
twins=# \dT
 
                    List of data types
  Schema |     Name      | 
                                    Description
--------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  gis    | box2d         | postgis type: A box composed of x min, ymin, 
xmax, ymax. Often used to return the 2d enclosing box of a geometry.
  gis    | box2df        |
  gis    | box3d         | postgis type: A box composed of x min, ymin, 
zmin, xmax, ymax, zmax. Often used to return the 3d extent of a geometry 
or collection of geometries.
  gis    | geography     | postgis type: Ellipsoidal spatial data type.
  gis    | geometry      | postgis type: Planar spatial data type.
  gis    | geometry_dump | postgis type: A spatial datatype with two 
fields - geom (holding a geometry object) and path[] (a 1-d array 
holding the position of the geometry within the dumped object.)
  gis    | geoms         |
  gis    | geomval       | postgis raster type: A spatial datatype with 
two fields - geom (holding a geometry object) and val (holding a double 
precision pixel value from a raster band).
  gis    | gidx          |
  gis    | histogram     | postgis raster type: A composite type used as 
record output of the ST_Histogram and ST_ApproxHistogram functions.
  gis    | path_result   |
  gis    | pgis_abs      |
  gis    | quantile      |
  gis    | raster        | postgis raster type: raster spatial data type.
  gis    | reclassarg    | postgis raster type: A composite type used as 
input into the ST_Reclass function defining the behavior of 
reclassification.
  gis    | spheroid      |
  gis    | summarystats  | postgis raster type: A composite type used as 
output of the ST_SummaryStats function.
  gis    | valid_detail  |
  gis    | valuecount    |
  gis    | vertex_result |

So what I think I want to do is install the updated types from the 
postgis extension to the public schema, and then drop those types from 
the gis schema. Looks like a real mess since I can't seem to cast 
geometries in one schema to geometries in another schema.

Thanks for any help anyone can suggest!

-P.

**************************************************************
Philip M. Hurvitz, PhD | Research Assistant Professor | UW-CBE
Urban Form Lab  | 1107 NE 45th Street, Suite 535  | Box 354802
University of Washington, Seattle, Washington  98195-4802, USA
phurvitz at u.washington.edu | http://gis.washington.edu/phurvitz
"What is essential is invisible to the eye." -de Saint-Exupéry
**************************************************************

> Bborie Park bkpark at ucdavis.edu
> Thu Sep 20 15:31:26 PDT 2012
> Previous message: [postgis-users] field type "public.geometry"
> Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
> That would be because the schema "public" isn't in that user's
> "search_path".  The following will show the current session's search_path.
>
> SHOW search_path;
>
> -bborie
>
> On 09/20/2012 02:54 PM, Phil Hurvitz wrote:
>> Hi, one of our users has some tables that show geometry fields as type
>> "public.geometry," e.g.,
>>
>>                   Column                  |       Type       | Modifiers
>> ------------------------------------------+------------------+-----------
>>  ogc_fid                                  | integer          | not null
>>  the_geom                                 | public.geometry  |
>>
>> whereas they used to have more "normal" types, e.g.,
>>
>>
>>                  Column            |          Type           | Modifiers
>> -----------------------------------+-------------------------+-----------
>>  ogc_fid                           | integer                 |
>>  the_geom                          | geometry(Geometry,2926) |
>>
>> I don't know what might have caused this; the data in the field do not
>> seem to have changed, but the field as it is has limited functionality.
>> Can someone suggest a fix? Thanks,
>>
>> -P.
>>
>> **************************************************************
>> Philip M. Hurvitz, PhD | Research Assistant Professor | UW-CBE
>> Urban Form Lab  | 1107 NE 45th Street, Suite 535  | Box 354802
>> University of Washington, Seattle, Washington  98195-4802, USA
>> phurvitz at u.washington.edu | http://gis.washington.edu/phurvitz
>> "What is essential is invisible to the eye." -de Saint-Exupéry
>> **************************************************************
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>
> --
> Bborie Park
> Programmer
> Center for Vectorborne Diseases
> UC Davis
> 530-752-8380
> bkpark at ucdavis.edu





More information about the postgis-users mailing list