[postgis-users] transform geometry and calculate distance query

Chris Hermansen chris.hermansen at timberline.ca
Thu Feb 5 09:00:39 PST 2009

Depending on how you choose to ultimately run this query, if you unwrap
the point by point processing into a loop inside a stored procedure,
then you can set up an auxiliary table that you use to figure out what
UTM zone, and therefore what EPSG, to use for your projection.  This is
better than using plate carrée

mark balman wrote:
> 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.
> Mark
> ------------------------------------------------------------------------
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users


Chris Hermansen         mailto:chris.hermansen at timberline.ca
tel+1.604.714.2878 · fax+1.604.733.0631 · mob+1.778.232.0644
Timberline Natural Resource Group · http://www.timberline.ca
401 · 958 West 8th Avenue  · Vancouver BC · Canada · V5Z 1E5

More information about the postgis-users mailing list