[postgis-users] RV: Distance Function
Dan R Greening
greening at bigtribe.com
Wed Aug 18 09:01:29 PDT 2004
Mark,
Sorry I didn't see your response. I can sort-of answer my own question with
this, based on what you said:
select p2.poiid,distance_spheroid(p1.geom,p2.geom,
'SPHEROID["GRS_1980",6378137,298.257222101]') from poitest p1, poitest p2
where p1.poiid = '11' ;
However, now I'm really wanting to know what the point of entering SRID's in
OpenGIS geometries? The Distance function doesn't use them, and you've
recommended a calculation that manually replicates what's already in the
spatial_ref_sys table.
In a related question, if you run that test app below, you'll see an error
in this line:
select poiid from poitest where geom && GeometryFromText('BOX3D(-122.40798
37.76930,-122.38621 37.78929)',4326);
It complains that the && operator is trying to compute overlap from two
different geometries. But shouldn't it just perform a projection to make it
work? Do I need to project this explicitly? And what if I project to some
geometry that requires additional parameters, such as referenced here:
http://xserve.flids.com/pipermail/proj/2004-June/000086.html
As you can see, I'm digging deeper.
But never fear, I can use and very much like what I'm able to use. And I
really appreciate the good work you've all done. Just wondering whether
these inconsistencies are something someone should fix.
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 Dan R Greening
> Sent: Wednesday, August 18, 2004 8:37 AM
> To: 'PostGIS Users Discussion'
> Subject: RE: [postgis-users] RV: Distance Function
>
> 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
> >
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
More information about the postgis-users
mailing list