[postgis-users] transform geometry and calculate distance query

[mark balman]

Hi all,

I have a query I need to create that transforms the geometry from lat/long
to plate carree so I can return distances of a point to nearby polygons at a
specified distance (not ideal to use Plate Carree but I cannot find any
other global projection that I can use within the EPSG codes). What I have
so far is as follows:

SELECT ebaname, distance(ST_Transform(ST_geomfromtext('POINT(-77.144
-0.629)',4326),32662), the_geom) as Distance_Metres
FROM ebas
WHERE ST_DWithin(ST_Transform(the_geom, 32662)
,ST_Transform(ST_geomfromtext('POINT(-77.144 -0.629)',4326),32662), 100000)
AND ST_Transform(the_geom, 32662) >
ST_Transform(ST_geomfromtext('POINT(-77.144 -0.629)',4326),32662)
ORDER BY ST_Distance(ST_transform(the_geom,32662),
ST_Transform(ST_geomfromtext('POINT(-77.144 -0.629)',4326),32662))


This seems to work ok, but what I am trying to do is to use an existing
point layer in postgres (same coordinates as specifying the geometry as
geomfromtext) . I cannot seem to figure this out, any help would be
gratefully appreciated.

TIA

Mark
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20090205/b12958e6/attachment.html>