[postgis-users] transform geometry and calculate distance query

Obe, Regina robe.dnd at cityofboston.gov
Thu Feb 5 06:16:49 PST 2009

 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
FROM ebas
(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,


From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of mark
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),
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.

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>

More information about the postgis-users mailing list