From: | itishree sukla <itishree(dot)sukla(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Issue with POSTGIS |
Date: | 2009-04-28 09:14:30 |
Message-ID: | 1495ee400904280214n1a9c2369t7b96d35ebbda897c@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello ,
My question is related to some functions in PostGIS.
Problem:
We are trying to find x points from a table that are within 20
nautical miles from a given point. Would like to use the ST_Dwithin
function as it would use the GIST index. I could use the
ST_Distance_Spheroid function but that would scan the entire table.
(1) So here's the query I would like to use:
select * from table_A where st_dwithin(shape, GeomFromText('POINT(-90
45)',4269), 20.0/60);
The shape field in table_A is also stored in the coordinate system for
4269. Since a nautical mile translates into 1 arcminute of latitude,
the distance is divided by 60.
(2) I am expecting to get 11 records and confirmed it by using the
st_distance_spheroid function as show below:
select * from table_A where
ST_Distance_Spheroid(shape ,ST_GeomFromText('POINT(-90 45)',4269),
'SPHEROID["GRS 1980",6378137,298.257222101,AUTHORITY["EPSG","7019"]]')
<=1852*20;
(3) One another test I did was to use the following and got 11 records
as expected. Here what I did was to convert to planar coordinates.
Select * from table_A where st_dwithin( transform(shape,
2163),transform(GeomFromText('POINT(-90 45)',4269),2163), 20*1852);
The query shown in (1) gives me 3 records. What am I missing? Please
help.
Thanks & Regards,
Itishree
From | Date | Subject | |
---|---|---|---|
Next Message | Sam Mason | 2009-04-28 09:15:47 | Re: triggers and execute... |
Previous Message | Sam Mason | 2009-04-28 08:42:24 | Re: Query organization question |