[postgis] The SPATIAL_REF_SYS table

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

[postgis] The SPATIAL_REF_SYS table

Roderick A. Anderson-3
I've got PostGIS installed and stayed up far too late last night
starting to play.  Since I'm a GIS groupie (verses a real user) I'm
doing a bit of fumbling around understanding some of the concepts.

My plan is to have a seamless database of GIS/Mapping data of a fairly
large area.  I currently have about 180 USGS Quads of data for my area
with coordinates in UTM Zone 11N and some UTM Zone 12N (See question 3
below for why I think this is significant).  Coords and elevations are
in meters.  This is in case you haven't had the joy of using these files
while working in a feet and mile country.

To make the data loading easier I'm playing with the DEM data (x, y, z).
I have a perl script that transforms from the USGS DEM format to a
PostgreSQL copy'able format.

So I have three general questions.

1. What is (should be) the difference in the values of the columns SRID
   and AUTH_SRID in the SPATIAL_REF_SYS table?


2. Does my table design make sense?  The idea being I can select a
   random rectangle of elevations or the points for a range of
   elevations.

        gistest=> \d elevation_pnt
              Table "elevation_pnt"
         Attribute |   Type   | Modifier
        -----------+----------+----------
         elevation | bigint   |
         pnt       | geometry |
        Indices: elevation_pnt_elev_idx,
                 elevation_pnt_idx
        Constraints: (srid(pnt) = 26711)
                     (geometrytype(pnt) = 'POINT'::text)

3. Can I have more than one SRID in this table?  If so how do I change
   the constraints to allow this?


TIA,
Rod
--
                      Let Accuracy Triumph Over Victory

                                                       Zetetic Institute
                                                        "David's Sling"
                                                         Marc Stiegler


------------------------ Yahoo! Groups Sponsor ---------------------~-->
Pinpoint the right security solution for your company- Learn how to add 128- bit encryption and to authenticate your web site with VeriSign's FREE guide!
http://us.click.yahoo.com/JNm9_D/33_CAA/yigFAA/PhFolB/TM
---------------------------------------------------------------------~->

To unsubscribe from this group, send an email to:
[hidden email]

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ 



Reply | Threaded
Open this post in threaded view
|

Re: [postgis] The SPATIAL_REF_SYS table

Paul Ramsey-2


"Roderick A. Anderson" wrote:

> 1. What is (should be) the difference in the values of the columns SRID
>    and AUTH_SRID in the SPATIAL_REF_SYS table?

OGC seems to have made a bit of a compromise when they defined this...
because there are organizations out there already, like EPSG, which have
lists of projections defined with set identifiers, they put in this
extra column. So if you were defining an SRID which already had an EPSG
code, the 'SRID' would be the local ID you would use for your database,
and the AUTH_SRID would be the EPSD id number. So for example, you might
have

SRID  | AUTH_NAME  | AUTH_SRID  | SRTEXT
------+------------+------------+-------
   1  |   EPSG     |   1624     | <big SRTEXT string>

The Big List of EPSG SRIDs which Frank helped put together at
http://www.opengis.org/techno/interop/EPSG2WKT.TXT is especially useful
for filling out a SPATIAL_REF_SYS table. Note that the SRID number can
be anything you like: it is local to your computer, unline the EPSG
code, which is defined in the EPSG standard.

> 2. Does my table design make sense?  The idea being I can select a
>    random rectangle of elevations or the points for a range of
>    elevations.
>
>         gistest=> \d elevation_pnt
>               Table "elevation_pnt"
>          Attribute |   Type   | Modifier
>         -----------+----------+----------
>          elevation | bigint   |
>          pnt       | geometry |
>         Indices: elevation_pnt_elev_idx,
>                  elevation_pnt_idx
>         Constraints: (srid(pnt) = 26711)
>                      (geometrytype(pnt) = 'POINT'::text)

Looks fine to me.

> 3. Can I have more than one SRID in this table?  If so how do I change
>    the constraints to allow this?

No, OpenGIS (correctly) expects you to store your features homogeneously
with regard to spatial ref system. I am guessing that you want to store
multiple UTM zone information in the same table? Bear in mind that when
you do that, you are no longer storing things in a 'seamless tile'. It
is like storing all your features in coordinates relative to the USGS
quad they are in: sure, theoretically the data is seamless, but
practically it is very hard to work with data which crosses the sheet
lines. I'd suggest you find a nice planar projection which works for
your entire working area, rather than messing with multiple UTM zones.

Paul

------------------------ Yahoo! Groups Sponsor ---------------------~-->
Get your FREE VeriSign guide to security solutions for your web site: encrypting transactions, securing intranets, and more!
http://us.click.yahoo.com/XrFcOC/m5_CAA/yigFAA/PhFolB/TM
---------------------------------------------------------------------~->

To unsubscribe from this group, send an email to:
[hidden email]

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ 



Reply | Threaded
Open this post in threaded view
|

Re: [postgis] The SPATIAL_REF_SYS table

Roderick A. Anderson-3
On Fri, 21 Sep 2001, Paul Ramsey wrote:

> The Big List of EPSG SRIDs which Frank helped put together at
> http://www.opengis.org/techno/interop/EPSG2WKT.TXT is especially useful
> for filling out a SPATIAL_REF_SYS table. Note that the SRID number can
> be anything you like: it is local to your computer, unline the EPSG
> code, which is defined in the EPSG standard.

Grabbed it and loaded the UTM zone 11N tuple.

So using the AUTH_SRID for SRID would be OK on my virgin system.


> No, OpenGIS (correctly) expects you to store your features homogeneously
> with regard to spatial ref system. I am guessing that you want to store
> multiple UTM zone information in the same table? Bear in mind that when
> you do that, you are no longer storing things in a 'seamless tile'. It
> is like storing all your features in coordinates relative to the USGS
> quad they are in: sure, theoretically the data is seamless, but
> practically it is very hard to work with data which crosses the sheet
> lines. I'd suggest you find a nice planar projection which works for
> your entire working area, rather than messing with multiple UTM zones.

I had a feeling this was the way it was designed.  Since I have such
grandious plans I'll look at converting to a more seamless coordinate
system.  I'm going to have to dig out the old surveying and coordinate
geometry text books now.  LAT/LONG?  But I'm thinking - will the
selection functions (BOX3D etal.) work on spherical coordinates?
Probably not!

I'm sure the stuff I'm bringing up has been discussed many times in
other forums so please bear with me as I boot strap myself into the real
world of GIS and mapping.  Also tell me to shutup or pipe-down when it
gets to be too much.


Last thing.  Can anyone point me at readings on planetary level
coordinate systems?  8-|


TIA,
Rod
--
                      Let Accuracy Triumph Over Victory

                                                       Zetetic Institute
                                                        "David's Sling"
                                                         Marc Stiegler


------------------------ Yahoo! Groups Sponsor ---------------------~-->
Pinpoint the right security solution for your company- Learn how to add 128- bit encryption and to authenticate your web site with VeriSign's FREE guide!
http://us.click.yahoo.com/JNm9_D/33_CAA/yigFAA/PhFolB/TM
---------------------------------------------------------------------~->

To unsubscribe from this group, send an email to:
[hidden email]

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ 



Reply | Threaded
Open this post in threaded view
|

RE: [postgis] The SPATIAL_REF_SYS table

Norman Vine
Roderick A. Anderson
>
>Last thing.  Can anyone point me at readings on planetary level
>coordinate systems?

Recent conference on this
http://www.ncgia.ucsb.edu/globalgrids/

Commercial Library
http://www.geodysey.com

OpenSource FlightSimulator with global scenery
http://www.flightgear.org/Docs/Scenery/CoordinateSystem/CoordinateSystem.htm
l
http://www.flightgear.org/Docs/Scenery/SceneryGeneration/SceneryGeneration.h
tml

Celestial Sphere Index // but it is the same problem :-)
http://www.sdss.jhu.edu/htm/index.html

Cheers

Norman Vine


------------------------ Yahoo! Groups Sponsor ---------------------~-->
Pinpoint the right security solution for your company- Learn how to add 128- bit encryption and to authenticate your web site with VeriSign's FREE guide!
http://us.click.yahoo.com/JNm9_D/33_CAA/yigFAA/PhFolB/TM
---------------------------------------------------------------------~->

To unsubscribe from this group, send an email to:
[hidden email]

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/