[postgis-users] RV: Distance Function

[Dan R Greening]

At present, I'm doing something similar to Brigitte, so I have the test SQL
that follows.  When I saw Nicolas' email, I checked what Distance was doing
and it appears to be measuring the "degree distance" of these items.

Can someone help Brigette and myself by concocting a select statement that
computes the Distance correctly instead of the SELECT statement at the end
of this test program?  (After all, what's the point of including PROJ if you
can't do this?)  Alternatively, can someone show an example of how to
project SRID=4326 to a cartesian 3-space with units=meters, so we can
compute this ourselves?

Thanks in advance.

Dan Greening, Ph.D.  CEO BigTribe http://dan.greening.name/contact.htm 


---------------

CREATE TABLE poitest (
  poiid varchar(11) NOT NULL UNIQUE,
  latitude DOUBLE PRECISION NOT NULL,
  longitude DOUBLE PRECISION NOT NULL,
  PRIMARY KEY  (poiid)
);
CREATE INDEX lat_index ON poitest (latitude,longitude);
CREATE INDEX lon_index ON poitest (longitude,latitude);
INSERT INTO poitest (poiid, latitude, longitude) VALUES
('27165',37.766029,-122.430382);
INSERT INTO poitest (poiid, latitude, longitude) VALUES
('11',37.764894,-122.423022);
INSERT INTO poitest (poiid, latitude, longitude) VALUES
('12',37.764829,-122.423882);
INSERT INTO poitest (poiid, latitude, longitude) VALUES
('18',37.762529,-122.396481);
INSERT INTO poitest (poiid, latitude, longitude) VALUES
('19',37.761829,-122.418282);
INSERT INTO poitest (poiid, latitude, longitude) VALUES
('25',37.790508,-122.398332);
INSERT INTO poitest (poiid, latitude, longitude) VALUES
('26',37.788028,-122.395281);

-- 4269 is the standard GCS NAD83 datum (used in Tiger/line)
-- 4326 is the standard GCS WGS84 datum

-- Target datum is NAD83
SELECT
AddGeometryColumn(current_schema()::text,'poitest','geom',4269,'POINT', 2); 

-- Source datum is NAD83
UPDATE poitest set geom = GeometryFromText('POINT('  || longitude || ' ' ||
latitude || ')', 4269);
CREATE INDEX geom_idx ON poitest USING GIST(geom gist_geometry_ops);
VACUUM ANALYZE;

-- Testing using WGS84 (like from a GPS).
select poiid from poitest where geom && GeometryFromText('BOX3D(-122.40798
37.76930,-122.38621 37.78929)',4326);
select p2.poiid,Distance(p1.geom,p2.geom) from poitest p1, poitest p2 where
p1.poiid = '11' ;

-------------

Dan Greening, Ph.D.  CEO BigTribe http://dan.greening.name/contact.htm 
 

> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net 
> [mailto:postgis-users-bounces at postgis.refractions.net] On 
> Behalf Of Nicolas RIBOT
> Sent: Wednesday, August 18, 2004 1:24 AM
> To: PostGIS Users Discussion
> Subject: RE: [postgis-users] RV: Distance Function
> 
> Hello Brigitte,
>  
> I think that the distance function works only on cartesian 
> coordinates.
> For geographic coordinates, the best way is to reproject them 
> in a projected coordinate system and then to measure the distance.
>  
> Nicolas 
> 
> -----Message d'origine-----
> De : luzbrigitte [mailto:luzbrigitte at hotmail.com] Envoyé : 
> mercredi 18 août 2004 01:57 À : postgis-users at postgis.refractions.net
> Objet : [postgis-users] RV: Distance Function
> 
> 
> 
> Hi, Anybody knows how can I measure in Postgis? I tried the 
> distance function but I don´t understand how it function, My 
> data is in geographical coordinates,  for example :
> 
>  
> 
> MY_TABLE
> 
>  
> 
> the_geom                                                date
> hour              |  num_art
> 
> ------------------------------------------------------------|-
> ----------
> -------|-----------------------|---------------
> 
> SRID=-1;POINT(-75.137956 3.566969)     | 17/08/2004 | 04:15:00 p.m.  |
> 1525
> 
> SRID=-1;POINT(-75.128284 3.611129)     | 17/08/2004 | 04:22:00 p.m.  |
> 1525
> 
> SRID=-1;POINT(-75.107022 3.639218)     | 17/08/2004 | 04:22:00 p.m.  |
> 1525
> 
> SRID=-1;POINT(-75.106738 3.640427)     | 17/08/2004 | 04:17:00 p.m.  |
> 1730
> 
>  
> 
> I need to find the distance between last 2 points for each 
> num_art  of the same table.
> 
>  
> 
> Anything idea to respect?
> 
>  
> 
> I have postgres 7.2.2  with postgis 0.7.3
> 
>  
> 
> Thanks a lot for your help.
> 
>  
> 
>  
> 
> Bye,
> 
>  
> 
>  
> 
> Brigitte P.
> 
> Colombia
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>