[postgis-users] Datums and their TOWGS84 transforms (was "Error in SPATIAL_REF_SYS_TABLE")

[Patrick]

"Patrick" <pvanlaake at users.sourceforge.net> wrote in message 
news:dktocr$5au$1 at sea.gmane.org...
> Hi Frank,
>
> "Frank Warmerdam" <warmerdam at pobox.com> wrote in message
> news:931f8ea90511091118y56553892j265d652a2c4b1232 at mail.gmail.com...
>> Note that the automated translation of the EPSG database
>> has several rules for identifying a datum shift (TOWGS84) value
>> to use.
>>  o There must only be one.
>>  o It must be listed going directly from the datum in question
>>    to WGS84.
>>
>> Many datums in the EPSG database have multiple conversions
>> to WGS84 - various approximations appropriate in different local
>> areas.  In these cases I give up and supply none.
>>
> I see at least two solutions to this issue, but both require something way
> past +towgs84.
>
> The first option is to list all possible transformations and let the user
> choose. That means user interaction and this may not be all too practical
> for a tool like PROJ.4.
>
> The second option is to analyze the EPSG area_of_use information that is
> included in several EPSG tables. IIRC all datum transforms have this
> information attached to it. Based on the extent of the data to be
> transformed you can then choose the appropriate set of parameters. For
> PROJ.4 this would require a new file that has all this information in 
> there,
> or a live link to the EPSG database in PostgreSQL. The PostGIS
> spatial_ref_sys is useless here because it uses your (?) logic (sorry 
> about
> that) and thus has no alternatives to offer.
>
> I am working on live interaction with the EPSG database (see my post in 
> this
> list on 11/03)and one of my plans is to provide much greater support for
> datum transforms. If I get that up and running, you'll be the first to 
> know
> (as well as all the other readers of this list). Right now I could still
> accommodate any suggestions (yes indeed, I am still only starting the 
> work),
> so please fire away if you have any particular wishes regarding specific
> types of queries or output formats.
>
> One relatively big hurdle that I am facing is that the EPSG database does
> not link towgs84 parameters directly to a datum. Instead, these parameters
> are tied to a geogcs through a coordinate operation, and the geogcs has a
> datum. This complicates the querying a great deal, especially when trying 
> to
> link a set of parameters to a datum. Any suggestions on this particular
> issue are especially welcome.
>
Ok, that wasn't such a big hurdle after all. The statement is something 
like:

SELECT epsg_datum.datum_code, datum_name, coord_op_name AS operation, 
ellipsoid_name AS ellipsoid, area_name AS area, area_north_bound_lat AS 
north, area_east_bound_lon AS east, area_south_bound_lat AS south, 
area_west_bound_lon AS west, 
epsg_towgs84_wkt(epsg_coordoperation.coord_op_code) AS TOWGS84
FROM epsg_ellipsoid, epsg_area, epsg_coordoperation, epsg_datum
LEFT JOIN epsg_coordinatereferencesystem ON 
(epsg_coordinatereferencesystem.datum_code = epsg_datum.datum_code)
WHERE epsg_ellipsoid.ellipsoid_code = epsg_datum.ellipsoid_code
AND epsg_area.area_code = epsg_coordoperation.area_of_use_code
AND epsg_coordoperation.source_crs_code = 
epsg_coordinatereferencesystem.coord_ref_sys_code
AND epsg_coordoperation.coord_op_type = 'transformation'
AND epsg_coordoperation.target_crs_code = 4326
ORDER BY epsg_datum.datum_code, epsg_coordoperation.coord_op_variant;

Which requires the epsg_towgs84_wkt function. This function, and the above 
statement packaged as a view, are now included in the distribution which can 
be had at [ 
http://sourceforge.net/project/showfiles.php?group_id=75000&package_id=168409&release_id=368202 ] 
.

It lists all the known immediate analytical datum transforms to WGS84 (so 
not polynomial solutions like NADCON) and their geographical area of 
application. Users can then choose their preferred version and applications 
could analyze the north, east, south and west columns for automated 
selection.

Any comments appreciated.

Best regards,
Patrick