Sorry, it seemed that you were asking about the indexing problem, which the 
manual describes how to solve (and it got into the manual because it is such a 
frequently asked question). See my follow up e-mail regarding the spheroid 
problem. For the magic formula, look in the archives:

http://postgis.refractions.net/pipermail/postgis-users/2002-August/001424.html

HTH.
Chris

Quoting Daniel Ceregatti <vi@sh.nu>:

> The problem for me here has not been constructing the query to obtain
> the correct data in the speediest fashion, but in creating the proper
> index based on a "spheroid" set of distances. After some testing, it
> seems clear that what I've created using the statements in my original
> email is a 2D flat plain geometry: the distance between point (1 1) and
> point (2 2) is the same as the distance between point (89 89) and point
> (90 90).
> 
> I'm no map maker, and I'm not familiar with all the terminology postgis
> uses, but I have successfully implemented this in oracle. I guess the
> best way to describe "this" is: "A table with people and their
> geographic location on the Earth in latitude/longitude that is
> searchable by other users, enabling them to find people within a certain
> distance from them on the Earth, anywhere on the Earth. A spatial index
> enables these queries to work very quickly on a table with as many as
> 300,000 rows. By very quickly I mean, less than one second on a P4
> 2.4Ghz with 1G of ram and IDE disks.".
> 
> So far, everything I've read about postgis suggests this functionality
> is not possible.
> 
> Please pardon my ignorance if I am incorrect (which I hope I am)
> 
> Daniel Ceregatti
> 
> chodgson@refractions.net wrote:
> 
> >http://postgis.refractions.net/docs/x526.html#AEN530
> >
> >RTFM :)
> >
> >Chris
> >
> >Quoting Daniel Ceregatti <vi@sh.nu>:
> >
> >
> >
> >>Hi,
> >>
> >>I've been searching this list for a document that covers the creation of
> >>a spatial index that would allow me to query on distance in miles based
> >>on lat/lon. So far I've come up empty. I want functionality that would
> >>allow a person, via a web interface to a database, who may be located
> >>anywhere in the world, to look for people in the database that are
> >>within a certain distance in miles from the person searching. Each
> >>person in the database has a lat/lon associated with their profile. I've
> >>tried to read the documentation to attempt to extrapolate this
> >>information but some things are still rather vague to me. Perhaps I'm
> >>simply not searching for the correct terminology. Anyhow, I've attempted
> >>the following using a postgis enabled postgres server:
> >>
> >>CREATE TABLE users (
> >>  username varchar (50),
> >>  latitude numeric,
> >>  longitude numeric,
> >>  zip numeric
> >>);
> >>COPY users (username, latitude, longitude, zip) FROM stdin;
> >>babe5000        41.2642 -74.3694        10990
> >>usuck_247       32.8068 -117.1685       92111
> >>NIKMM   33.8563 -116.5712       92262
> >>-- snip...
> >>\.
> >>
> >>This imported appoximately 220,000 rows. I used data from an existing
> >>oracle system. This system uses oracle spatial. My goal is to replace
> >>oracle with postgres, as a proof-of-concept. I continued:
> >>
> >>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
> >>GIST_GEOMETRY_OPS);
> >>
> >>VACUUM ANALYZE users;
> >>
> >>So far so good. This seems to have created what I need, as far as I can
> >>tell. Thing is, I then query this new column using the examples provided:
> >>
> >>select count (1) from users where distance (user_geom, GeometryFromText
> >>('POINT (34.0998  -118.4128)', 4269)) < 100;
> >>
> >>(That point in the GeometryFromText argument happens to be lat/lon for
> >>Beverly Hills, CA...you know, 90210)
> >>
> >>The problem here is that "100" seems to mean "100 units". I have yet to
> >>determine exactly what this unit is.
> >>
> >>I obtained the SRID for the queries above from this post:
> >>
> >>http://postgis.refractions.net/pipermail/postgis-users/2003-July/002791.html
> >>
> >>This post also had some useful info, as it referenced the oracle
> >>equivalent "SRID" of 8307, which I used to create my oracle based
> >>spatial index. Oracle spatial has a function called sdo_within_distance
> >>that accepts two geometries and a unit identifier, as in 'MILES', and
> >>returns true or false if the two are within the distance. Example
> >>
> >>"select sdo_within_distance (select user_geom from users where username
> >>= 'searchee', select user_geom from users where username = 'searcher',
> >>'distance=100 unit=MILE') from dual"
> >>
> >>My questions are: Is there any way to have a query match rows based on a
> >>defined distance criterion, such as miles, using spatial with postgis?
> >>If so, what am I doing wrong? How do I specify the distance unit?
> >>
> >>Thanks,
> >>
> >>Daniel Ceregatti
> >>
> >>
> >>
> >
> >
> >
> >
> >
> >_______________________________________________
> >postgis-users mailing list
> >postgis-users@postgis.refractions.net
> >http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> >
> 





