[postgis-users] transform geometry and calculate distance query

[Obe, Regina]

Mark,
 
 Lets say your table is called sometable and is in 4326 projection, then
it would look something like
 
SELECT ebas.ebaname, distance(ebas.the_geom, pt.the_geom) as
Distance_Metres
FROM ebas
 INNER JOIN 
(SELECT  gid, ST_Transform(the_geom, 32662) As the_geom 
FROM sometable WHERE gid = 1) As pt
ON  ST_DWithin(ST_Transform(ebas.the_geom, 32662),pt.the_geom, 100000)
ORDER BY ST_Distance(ST_transform(ebas.the_geom,32662), pt.the_geom)
 
The gid = 1 is if you just want to pull one record -- you can change the
where to limit to subset of point table.
 
If you are going to do this often, you should really put a functional
index on your table with this transform.
 
something like
CREATE INDEX idx_the_geom_32662_ebas  ON ebas  USING gist
  (st_transform(the_geom, 32662))
  WHERE the_geom IS NOT NULL;

and same on your point table.
 
Also be forewarned.  I've never worked with Plate Carree, but as a
general rule of thumb, the larger the area a projection supports
the more inaccurate the measurement.
 
 
Hope that helps,
Regina

________________________________

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of mark
balman
Sent: Thursday, February 05, 2009 5:59 AM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] transform geometry and calculate distance query


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


-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20090205/023e019c/attachment.html>