[postgis-users] srid and srtext from EPSG database

[Patrick]

Hi all,

Following the thread started on 18 October, I have developed the code needed 
to create WKT strings dynamically from the EPSG database. You can download 
instructions and scripts from [ 
http://prdownloads.sourceforge.net/dspatial/PostgreSQL-CRS.zip?download ]. 
You need to download the PostgreSQL scripts from EPSG as well. The scripts 
create a bundle of tables, functions and a single view which is a full 
replacement for the spatial_ref_sys table shipped with PostGIS. You should 
delete the table before running the last script. If this gives you the 
jitters, then either rename the table (and notice that your apps still work 
just fine), or change the name of the view in the script before you run it.

Opening the view as such is horribly slow, because it is built on the fly 
every time you open it, but more practical commands like SELECT * FROM 
spatial_ref_sys WHERE srid = 4326 are just as fast as you would want them to 
be. And since you have wondered for years now about the local coordinate 
systems for Minnesota, I have also added a function srid_by_placename that 
can do something like:

SELECT coord_ref_sys_code, 
epsg_coordinatereferencesystem_wkt(coord_ref_sys_code, false)
FROM epsg_coordinatereferencesystem
WHERE coord_ref_sys_code IN (SELECT srid_by_placename('%Minnesota%'))
ORDER BY coord_ref_sys_code;

Great place, Minnesota. Which is one example of why using the original EPSG 
database is useful (the function uses the epsg_area table).

There are no further analysis functions (yet), but these could be added if 
the need arises (= users asking for it). I invite everybody to give it a try 
and send me your heartfelt comments, preferably through this list.

So, Paul, in my small world your spatial_ref_sys table has gone and it won't 
come back. If the code proves to be as stable as I think it is, you are most 
welcome to integrate it into PostGIS.

Best regards,
Patrick