Initial SRS and geometry columms table creation

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

Initial SRS and geometry columms table creation

Ivan Kautter
Listserv subscribers,

I am just starting out with PostgreSQL and PostGIS.  It appears that it is
necessary to create the SRS and geometry_columns tables before importing
ESRI shapefile data.  Is this correct?  One thing that seems odd though is
that in order to create the geometry_columns table one would have to know
what tables are going to be produced by importing GIS data into the database
and the resulting name of the geometry_column.

I am also wondering if it is necessary to create a spatial table first for
each shapefile that one is adding to the database or if one may merely use
the loader to create the feature table then modify it by adding a geometry
column to it.

Lastly, I have a spatial reference system that is not defined in EPSG.  It
is a system for the entire state of California in NAD83 defined thusly:

  # California ALBERS projection information
  PROJECTION
    "proj=aea"
    "y_0=-4000000.0"
    "lon_0=-120.0"
    "lat_0=0.0"
    "lat_1=34.0"
    "lat_2=40.5"
    "units=m"
    "ellps=GRS80"
    "datum=NAD83"

How should I go about specifying the SRID, AUTH_NAME, AUTH_SRID for this
custom SRS?  Is there a standard domain for these fields that people utilize
in PostGIS or are the values in these fields largely arbitrary?

Thanks for your time and attention.  All assistance greatly appreciated.

Ivan Kautter



Reply | Threaded
Open this post in threaded view
|

Re: Initial SRS and geometry columms table creation

Paul Ramsey-2

On Dec 13, 2005, at 8:16 PM, Ivan Kautter wrote:

> Lastly, I have a spatial reference system that is not defined in  
> EPSG.  It is a system for the entire state of California in NAD83  
> defined thusly:
>
> <snip>

> How should I go about specifying the SRID, AUTH_NAME, AUTH_SRID for  
> this custom SRS?

SRID cannot collide with others AUTH_NAME and AUTH_SRID can be  
anything you like.

> Is there a standard domain for these fields that people utilize in  
> PostGIS or are the values in these fields largely arbitrary?

Well, our spatial_ref_sys table can confuse people, because our SRIDs  
match our AUTH_SRIDs, but not that the AUTH_NAME is "EPSG".  So the  
SRID could be anything, but the AUTH_NAME, AUTH_SRID fields would  
match the EPSG definitions for the definitions we ship.  Clear as  
mud?  In theory, you could have multiple authorities, with colliding  
AUTH_SRIDs but distince SRIDs (since SRID is a purely database-
internal number)

> Thanks for your time and attention.  All assistance greatly  
> appreciated.

Enjoy!

P


Reply | Threaded
Open this post in threaded view
|

Re: Initial SRS and geometry columms table creation

Dylan Keon-3
In reply to this post by Ivan Kautter
On 12/13/2005 08:16 PM Ivan Kautter wrote the following:
> Listserv subscribers,
>
> I am just starting out with PostgreSQL and PostGIS.  It appears that it
> is necessary to create the SRS and geometry_columns tables before
> importing ESRI shapefile data.  Is this correct?  One thing that seems
> odd though is that in order to create the geometry_columns table one
> would have to know what tables are going to be produced by importing GIS
> data into the database and the resulting name of the geometry_column.

Yes, that's correct.  From the 1.0.6 README.postgis:

   As postgres run:

     createlang plpgsql yourdatabase
     psql -f lwpostgis.sql -d yourdatabase

This will create the geometry_columns and spatial_ref_sys tables.

> I am also wondering if it is necessary to create a spatial table first
> for each shapefile that one is adding to the database or if one may
> merely use the loader to create the feature table then modify it by
> adding a geometry column to it.

You don't need to create the spatial table first.  The shp2pgsql loader
takes the table name as an argument and will generate SQL statements
that create the table, add the geometry column, and populate the table.

If you plan to insert spatial data manually, first create the table then
add the geometry column in this manner:

   SELECT AddGeometryColumn('wells','the_geom',4326,'POINT',2);

This example will create a column (the_geom) for storing 2D point
geometries in the "wells" table, using an SRID of 4326.  A corresponding
entry is added to the geometry_columns table.

--Dylan

Reply | Threaded
Open this post in threaded view
|

Re: Initial SRS and geometry columms table creation

Patrick-2
In reply to this post by Ivan Kautter

"Ivan Kautter" <[hidden email]> wrote in message
news:[hidden email]...

>
> Lastly, I have a spatial reference system that is not defined in EPSG.  It
> is a system for the entire state of California in NAD83 defined thusly:
>
>  # California ALBERS projection information
>  PROJECTION
>    "proj=aea"
>    "y_0=-4000000.0"
>    "lon_0=-120.0"
>    "lat_0=0.0"
>    "lat_1=34.0"
>    "lat_2=40.5"
>    "units=m"
>    "ellps=GRS80"
>    "datum=NAD83"
>

Hi Ivan,

This srs IS defined in EPSG and has coord_ref_sys_code (= srid) 3310. It is
just not in the PostGIS spatial_ref_sys table, probably because the
proj4text is not defined for some reason (Frank?). SRID, AUTH_NAME and
AUTH_SRID should thus be 3310, EPSG, and 3310, respectively. The proj4text
should be (off the top of my head, check on remotesensing.org/geotiff)
+proj=aea +lat_0=0.0 +lat_1=34.0 +lat_2=40.5 +lon_0=-120.0 +x_0=0
+y_0=-4000000.

I have created a little bundle of scripts that implement spatial_ref_sys (an
OGC requirement) as a view from the original EPSG tables. This allows for
much more options in querying and building new CRSs. Not yet complete, but
already quite useful. I found the above info using the query:

select * from spatial_ref_sys where srid in (select
srid_by_placename('United States (USA) - Cal%'));

which gave me 34 CRSs, of which only one matched your parameters.

These scripts can be downloaded from
http://sourceforge.net/project/showfiles.php?group_id=75000&package_id=168409.

Best regards,
Patrick




Reply | Threaded
Open this post in threaded view
|

Re: Initial SRS and geometry columms table creation

Mark Wright
This will get it into your database:

insert into spatial_ref_sys
(srid,auth_name,auth_srid,srtext,proj4text) values
(3310,'EPSG',3310,'PROJCS["Custom",GEOGCS["NAD83",DATUM["North_American_Datum_1983",SPHEROID["GRS
1980",6378137,298.257222101]],PRIMEM["Greenwich",0],UNIT["degree",0.0174532925199433]],PROJECTION["Albers"],PARAMETER["False_Easting",0],PARAMETER["False_Northing",-4000000],PARAMETER["Central_Meridian",-120],PARAMETER["Standard_Parallel_1",34],PARAMETER["Standard_Parallel_2",40.5],UNIT["Meter",1]]','+proj=aea
+lat_1=34.00 +lat_2=40.50 +lat_0=0.00 +lon_0=-120.00 +x_0=0.000
+y_0=-4000000.000 +ellps=GRS80 +units=m +datum=NAD83');

You can get the srtext for this and the nad27 projection from the
projections file at ftp://casil.ucdavis.edu


Mark

On 12/16/05, Patrick <[hidden email]> wrote:

>
> "Ivan Kautter" <[hidden email]> wrote in message
> news:[hidden email]...
> >
> > Lastly, I have a spatial reference system that is not defined in EPSG.  It
> > is a system for the entire state of California in NAD83 defined thusly:
> >
> >  # California ALBERS projection information
> >  PROJECTION
> >    "proj=aea"
> >    "y_0=-4000000.0"
> >    "lon_0=-120.0"
> >    "lat_0=0.0"
> >    "lat_1=34.0"
> >    "lat_2=40.5"
> >    "units=m"
> >    "ellps=GRS80"
> >    "datum=NAD83"


--
Have fun or die trying - but try not to actually die.
http://www.AboveCalifornia.com
Got Mac OS X? Get the AboveCalifornia Sherlock Channel:
sherlock://www.AboveCalifornia.com/sherlock/SherlockChannel.xml?action=add