[postgis-users] creating a spatial index for finding objects withing a certaindistance in miles based on lat/lon

Daniel Ceregatti vi at sh.nu
Fri Sep 12 10:13:38 PDT 2003

Paul Ramsey wrote:

> Daniel Ceregatti wrote:
>> This is good news.
>> The post you linked to seems to cover the "conversion from miles to
>> degrees" in the query, but I still need to get my index built using
>> degrees, where all I have are lat/lon. I think I saw something on the
>> list regarding this, so I'll search further.
> Daniel, lat/lon *are* degrees. Degrees are the units of a lat/lon data
> set. You can pretend that lat/lon coordinates are planar for the
> purposes of indexing and bounding box querying, and use the geodetic
> distance function to directly measure distance off of them once you
> have a result set. Thusly: 

I'm not too "geometrically inclined", hence my confusion. So what you're
saying is that my existing index is already appropriate for the query?

Quoting my original post:

SELECT AddGeometryColumn('maps', 'users', 'user_geom', 4269, 'POINT', 2 );
update users set user_geom = GeometryFromText('POINT(' || latitude || '
' || longitude|| ')',4269);
create index user_geom_indx on users using gist (user_geom

Perhaps I should use the undefined SRID of -1 here since the query is
doing all the work?

Sorry for all the newbie questions. A lot of this is really Greek to me. :)

> select * from tbl where geom && <LatLonBoundingBox> and
> distance_spheroid(geom,<LatLonQueryPoint>,<Spheroid>) < <Radius>
> I wonder if we should add in a "distance_sphere" for a less
> computationally intensive conversion from lat/lon to distances than
> the general spheroid case.
> Paul
>> Regarding Oracle, I'm not entirely sure how it does it either. I
>> followed a simple "howto" like set of instructions that created the
>> index based on lat/lon and was subsequently queryable using the sdo_*
>> functions native to oracle, one of which was the sdo_within_distance ()
>> mentioned earlier.
>> Thanks for all your help, Paul and Chris.
>> Daniel Ceregatti

More information about the postgis-users mailing list