<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
<META NAME="GENERATOR" CONTENT="GtkHTML/3.16.3">
</HEAD>
<BODY>
Hi Steve,<BR>
<BR>
You should look at the problem programmatically.<BR>
<BR>
My team and I came to ~less the same problem in the early days of my company (in the FMS area).<BR>
Our goal was to determine the street name within 20 meters of the vehicle's location as well as getting the nearest POI (also giving its distance in meters).<BR>
I'm talking about using lat/lon geoms and reproject them on-the-fly to the desired SRID.<BR>
<BR>
You don't have to create another geometry field. You simply have to *reproject* it to the desired SIRD.<BR>
<BR>
First of all you need to determine the user's specified point SRID. This can easily be done with the two following helper functions:<BR>
(Note: these are in PHP but can easily be adapted to another language)<BR>
<BR>
First break apart the user specified point in its lat/lon components:<BR>
(this uses the PostGIS 'astext()' function)<BR>
<BR>
SELECT astext(user_coordinates);<BR>
<BR>
This will return a WKT (Well-Known-Text) string in the following format:<BR>
POINT(lon lat)<BR>
<BR>
// extract the geometry from the WKT<BR>
$geometry = str_replace("POINT(","", $location);<BR>
$geometry = str_replace(")","",$geometry);<BR>
$coordinate = explode(" ",$geometry);<BR>
$lon=$coordinate[0];<BR>
$lat=$coordinate[1];<BR>
<BR>
<BR>
Determine the UTM zone from long.<BR>
<BR>
// determine UTM zone from LatLON coordinates<BR>
// params $long: longitude<BR>
// returns (int) utm<BR>
function UtmZoneFromLong($lon) {<BR>
$utm = floor(($lon + 180) / 6) + 1;<BR>
return $utm;<BR>
}<BR>
<BR>
Now get the SRID<BR>
<BR>
// query the spatial_ref_sys table for the srid of the given utm zone<BR>
// params $utm: utm zone string (MUST BE TRAILED WITH 'N' OR 'S'!), $conn: connection to the db<BR>
// returns (int) srid<BR>
function SridFromUtmZone($utm, $conn){<BR>
$myresult = pg_exec($conn, "SELECT srid FROM spatial_ref_sys WHERE srtext LIKE 'PROJCS[\"WGS 84 / UTM zone $utm%' LIMIT 1");<BR>
$srid=pg_result($myresult, 0, 0);<BR>
return $srid;<BR>
}<BR>
<BR>
Now assuming that you have a POI table named 'myPOITable' with lat/lon geoms (WGS84 (4326)) and a field name 'label' and want to get the labels of the nearest POIs in a 100 meter radius:<BR>
<BR>
SELECT label, distance(transform(geomfromtext('POINT($lon $lat)',4326),$srid), transform(p.geometry,$srid)) AS thedistance <BR>
FROM myPOITable p <BR>
WHERE expand(transform(p.geometry,$srid),100) && transform(geomfromtext('POINT($lon $lat)',4326),$srid) <BR>
ORDER BY thedistance<BR>
<BR>
As far as the indices go (and searching speed):<BR>
First thing to do is to create a GIST index. This can easily be done with:<BR>
CREATE INDEX myPOITable_idx<BR>
ON myPOITable<BR>
USING gist<BR>
(geometry);<BR>
<BR>
Since your geoms span across several UTM zones you should determine all the zones that you need and then create an index for each of them:<BR>
(Note: 'spatial_ref_sys' table is your friend... <IMG SRC="cid:1222935259.12763.46.camel@localhost.localdomain" ALIGN="middle" ALT=";-)" BORDER="0"> )<BR>
<BR>
// this example creates an spatial index for zone 56<BR>
<BR>
CREATE INDEX myPOITable_32756_idx<BR>
ON myPOITable<BR>
USING gist<BR>
(transform(geometry, 32756));<BR>
<BR>
Note the transform function...<BR>
<BR>
In case you're wondering how did I come up with the 32756 here's a simple SQL statement to get it:<BR>
<BR>
SELECT srid FROM spatial_ref_sys WHERE srtext ILIKE '%UTM zone 56S%';<BR>
<BR>
<BR>
Using the above techniques you'll always allow you to work in the desired projection and use meters.<BR>
Hope this helps,<BR>
Pedro Doria Meunier<BR>
<BR>
<TABLE CELLSPACING="0" CELLPADDING="0" WIDTH="100%">
<TR>
<TD>
<BR>
</TD>
</TR>
</TABLE>
<BR>
On Thu, 2008-10-02 at 13:32 +1000, Stephen Baillie wrote:
<BLOCKQUOTE TYPE=CITE>
<PRE>
<FONT COLOR="#000000">I've only just come to PostGIS, although I have a bit of PostgreSQL </FONT>
<FONT COLOR="#000000">experience, and I'm rapidly being overwhelmed by the volume of choices </FONT>
<FONT COLOR="#000000">available. So I thought I'd describe my problem in hopes that more </FONT>
<FONT COLOR="#000000">experienced folks could make some useful suggestions:</FONT>
<FONT COLOR="#000000">I've got ~1.2 million entities with location information in lat/long </FONT>
<FONT COLOR="#000000">format, and I've been tasked with adding the ability to search based on </FONT>
<FONT COLOR="#000000">distance (in m or km) from a user-specified point to the existing search </FONT>
<FONT COLOR="#000000">capabilities. It looks like to do this with PostGIS I need to add </FONT>
<FONT COLOR="#000000">another column to my table using |AddGeometryColumn(),| but I'm not sure </FONT>
<FONT COLOR="#000000">what SRID I should be using. My data are roughly bounded by latitude </FONT>
<FONT COLOR="#000000">-10 to -50 and longitude 110-160 (Australia), which puts UTM 53J right </FONT>
<FONT COLOR="#000000">in the middle, but I'm uncertain as to how much distortion that would </FONT>
<FONT COLOR="#000000">suffer at the edges. Would I be best off converting my lat/long pairs </FONT>
<FONT COLOR="#000000">to WGS84, and building some kind of index on that (typically I'd be </FONT>
<FONT COLOR="#000000">looking for things in a range around 20km, but could be as much as </FONT>
<FONT COLOR="#000000">400km)? Or should I stay with lat/long and use spherical distance </FONT>
<FONT COLOR="#000000">calculations? Is there something clever I could do to create </FONT>
<FONT COLOR="#000000">distance-based indices, or should I just stick with a bounding box on </FONT>
<FONT COLOR="#000000">lat/long?</FONT>
<FONT COLOR="#000000">Sorry to have so many n00b questions, but any pointers would be appreciated.</FONT>
<FONT COLOR="#000000">Thanks,</FONT>
<FONT COLOR="#000000">Steve.</FONT>
</PRE>
</BLOCKQUOTE>
</BODY>
</HTML>