[postgis-users] Table w/existing geometry column (without AddGeometryColumn), SRID?

[Stefan Keller]

Kevin: Many thanks for the explanations.

To summarize, I think there are some "best practices" to be changed as
well as some quick fixes and open issues regarding metadata and SRID.
Let's begin with metadata (public.geometry_columns table):

* Quick fix: Document probe_geometry_columns()

* "Best practice" (to be discussed and probably changed): I think that
the usual/"best" way to define a geometry attribute is *not* through
AddGeometryColumn but to create it with all attributes and possibly
(at most) one attribute of type GEOMETRY).

* The current solution managing SRID information on two places is
suboptimal: Why don't we define a view called 'geometry_columns' to be
compliant with OGC and e.g. MapServer? Stored procedures could be
triggeres if needed.

* If this is not possible, best practice should be to issue
'populate_geometry_columns' as a postprocessing step once after the
table has been created.

* Quick fix: Integrate and document populate_geometry_columns().


Regarding the management of SRIDs:

* You wrote: "There aren't any functions in PostGIS that actually use
the values stored in the geometry_columns table.  This includes
coordinate transformation."
=> Does this mean, they use the table/attribute constraint values or
just some mandatory parameters?

* This fact as well as your explanations ("The geometry_columns table
exists to be compliant with the OpenGIS Simple Features Specification
for SQL. This table allows to find tables with geometry columns
quickly so that they don't have to hunt through the system tables.")
could be documented in 'Table geometry_columns()'.

* Decide whether to keep '-1' as default in PostGIS, or to change it
to '0' (or NULL?) as SQL/MM seems to propose.


Finally, when trying to insert geometries I've seen so many situations
where functions throw exceptions because of SRID constraint
violations. The problem could be the WKT parser, we also had problems
using myPoint.setSRID(4326) (from JTS-package
"com.vividsolutions.jts.geom.Point").
=> So, SRID defaults, and SRID (default / value) setting seems to be
still an open issue (in WKT-parser, pgsql2shp, JTS)?

- Stefan


2008/10/24 Kevin Neufeld <kneufeld at refractions.net>:
> Stefan Keller wrote:
>>
>> * Does any postgis-function or spatial index fail when there is no
>> entry in geometry_columns table (except of course where explicit
>> coordinate transformation is needed)?
>>
>
> As far as I'm aware, there aren't any functions in PostGIS that actually use
> the values stores in the geometry_columns table.  This includes coordinate
> transformation.
>
> ie. SELECT ST_Transform('SRID=3005;POINT(940245 567728)', 4326);
> This can be done on the fly ... the point doesn't have to be in a table
> that's registered with geometry_columns.
>
> The geometry_columns table really only exists to be compliant with the
> OpenGIS Simple Features Specification for SQL so that external programs,
> like Mapserver, can use the table instead of hunting through the system
> tables to find tables with geometry columns.
>
>> * The value -1 is used to indicate no specified SRID. Does it make a
>> difference, if the SRID default is '0' instead of '-1' ?
>
> Nope, but you can't change the SRID default in PostGIS.  Right now, it's
> hard coded in many functions (ie. the WKT parser) and external programs (ie.
> pgsql2shp) to use -1.
>
> SELECT GetSRID('POINT(0 0)'::geometry);
>  getsrid
> ---------
>      -1
> (1 row)
>
> However, there's nothing stopping you from setting the SRID = 0 on all your
> geometries.  Interestingly, I believe the SQL/MM standard now uses 0 as the
> default instead of -1.
>
>> * Given all data is stored with SRID set to 0, is it still possible to
>> use all spatial functions (similar to "SELECT ... USING SRID=-1")?
>>
>> -- Stefan
>>
>
> I'm not sure I understand.  As long as the SRIDs are the same for operations
> on pairs of geometries, PostGIS doesn't care what the SRID is (-1, 0, 3005,
> a custom SRID, ...)
>
> Hope this helps,
> Cheers,
> Kevin.
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>