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
phurv...@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
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
