[postgis-users] why specify spheroid?

[Willy-Bas Loos]

It works to get the spheroid from the spatial_ref_sys:

select substring(srtext from position('SPHEROID' in srtext) for
position(',AUTHORITY' in srtext)-position('SPHEROID' in srtext))||']'
from spatial_ref_sys
where srid = 4326
--result: 'SPHEROID["WGS 84",6378137,298.257223563]'

You can even get the srid from one of the geometries yourself, and pass it
to the function.
the sql is a bit awkward, but it's proving the concept that counts:

select distance_spheroid(transform(centroid(l1.the_geom), 4326),
transform(centroid(l2.the_geom), 4326), (substring(sfs.srtext from
position('SPHEROID' in sfs.srtext) for position(',AUTHORITY' in
sfs.srtext)-position('SPHEROID' in sfs.srtext))||']'::text)::spheroid )/1000
as thedistance
from table1 l1
join table1 l2 ON (l1.locid=1 AND l2.locid = 2)
join spatial_ref_sys sfs ON (sfs.srid = srid(l1.the_geom))


But i think there should be a version of distance_sphere that does this for
you.

cheers,

WBL

-- 
"Patriotism is the conviction that your country is superior to all others
because you were born in it." - George Bernard Shaw
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080909/153c4962/attachment.html>