[postgis-users] What coordinate system should I use?

[Pedro Doria Meunier]

Hi Steve,

You should look at the problem programmatically.

My team and I came to ~less the same problem in the early days of my
company (in the FMS area).
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).
I'm talking about using lat/lon geoms and reproject them on-the-fly to
the desired SRID.

You don't have to create another geometry field. You simply have to
*reproject* it to the desired SIRD.

First of all you need to determine the user's specified point SRID. This
can easily be done with the two following helper functions:
(Note: these are in PHP but can easily be adapted to another language)

First break apart the user specified point in its lat/lon components:
(this uses the PostGIS 'astext()' function)

SELECT astext(user_coordinates);

This will return a WKT (Well-Known-Text) string in the following format:
POINT(lon lat)

// extract the geometry from the WKT
$geometry = str_replace("POINT(","", $location);
$geometry = str_replace(")","",$geometry);
$coordinate = explode(" ",$geometry);
$lon=$coordinate[0];
$lat=$coordinate[1];


Determine the UTM zone from long.

// determine UTM zone from LatLON coordinates
// params $long: longitude
// returns (int) utm
function UtmZoneFromLong($lon) {
	$utm = floor(($lon + 180) / 6) + 1;
	return $utm;
}

Now get the SRID

// query the spatial_ref_sys table for the srid of the given utm zone
// params $utm: utm zone string (MUST BE TRAILED WITH 'N' OR 'S'!),
$conn: connection to the db
// returns (int) srid
function SridFromUtmZone($utm, $conn){
$myresult = pg_exec($conn, "SELECT srid FROM spatial_ref_sys WHERE
srtext LIKE 'PROJCS[\"WGS 84 / UTM zone $utm%' LIMIT 1");
$srid=pg_result($myresult, 0, 0);
return $srid;
}

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:

SELECT label, distance(transform(geomfromtext('POINT($lon $lat)',4326),
$srid), transform(p.geometry,$srid)) AS thedistance 
					FROM myPOITable p 
					WHERE expand(transform(p.geometry,$srid),100) &&
transform(geomfromtext('POINT($lon $lat)',4326),$srid) 
					ORDER BY thedistance

As far as the indices go (and searching speed):
First thing to do is to create a GIST index. This can easily be done
with:
CREATE INDEX myPOITable_idx
  ON myPOITable
  USING gist
  (geometry);

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:
(Note: 'spatial_ref_sys' table is your friend... ;-) )

// this example creates an spatial index for zone 56

CREATE INDEX myPOITable_32756_idx
  ON myPOITable
  USING gist
  (transform(geometry, 32756));

Note the transform function...

In case you're wondering how did I come up with the 32756 here's a
simple SQL statement to get it:

SELECT srid FROM spatial_ref_sys WHERE srtext ILIKE '%UTM zone 56S%';


Using the above techniques you'll always allow you to work in the
desired projection and use meters.
Hope this helps,
Pedro Doria Meunier



On Thu, 2008-10-02 at 13:32 +1000, Stephen Baillie wrote:

> I've only just come to PostGIS, although I have a bit of PostgreSQL 
> experience, and I'm rapidly being overwhelmed by the volume of choices 
> available.  So I thought I'd describe my problem in hopes that more 
> experienced folks could make some useful suggestions:
> 
> I've got ~1.2 million entities with location information in lat/long 
> format, and I've been tasked with adding the ability to search based on 
> distance (in m or km) from a user-specified point to the existing search 
> capabilities.  It looks like to do this with PostGIS I need to add 
> another column to my table using |AddGeometryColumn(),| but I'm not sure 
> what SRID I should be using.  My data are roughly bounded by latitude 
> -10 to -50 and longitude 110-160 (Australia), which puts UTM 53J right 
> in the middle, but I'm uncertain as to how much distortion that would 
> suffer at the edges.  Would I be best off converting my lat/long pairs 
> to WGS84, and building some kind of index on that (typically I'd be 
> looking for things in a range around 20km, but could be as much as 
> 400km)?  Or should I stay with lat/long and use spherical distance 
> calculations?  Is there something clever I could do to create 
> distance-based indices, or should I just stick with a bounding box on 
> lat/long?
> 
> Sorry to have so many n00b questions, but any pointers would be appreciated.
> 
> Thanks,
> 
> Steve.
> 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20081002/fc21ab71/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: stock_smiley-3.png
Type: image/png
Size: 876 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20081002/fc21ab71/attachment.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 189 bytes
Desc: This is a digitally signed message part
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20081002/fc21ab71/attachment.pgp>