Points, Circles, Indexes, and query-by-Radius?

Bryan,
The default geometric types in PostgreSQL really aren't particularly
well suited for GIS (2-D only, no support for polygons w/ holes, not
particularly indexable, etc) which is why we've started the PostGIS
project. Head to http://postgis.refractions.net for more info, and code
you can download to add better GIS objects (with indexing and conforming
to OpenGIS specs) to PostgreSQL. The package compiles very
straightforwardly under contrib, and is (hopefully) well enough
documented to get a start with.
Paul

Bryan Field-Elliot wrote:

I'm developing a GIS system on PostgreSQL. I need to store a table of
points (latitude and longitude), and do queries such as "return all
points enclosed with the given circle ((X,Y),R)".

Straightforward in PostGIS.

The "point" data type looks convenient, however, it doesn't appear to be
indexable.

Can anyone tell me what the preferred, recommended strategy for this
design pattern is, with respect to PostgreSQL? One which makes good use
of indices, if possible (realizing that I personally don't know a good
way to index for a radius search).

For extra credit, it would be nice if I could store circles instead of
points, and issue queries like the following:

"given a circle ((X,Y),R), return all circles which share some common area"

More tricky. We don't do circles (not an OpenGIS feature type)... if you
vectorized your circles into circular polygons, you would be 90% of the
way there. If you didn't mind querying using a bounding-box instead of a
circle you'd be 100% of the way there.

Opinions would be appreciated -- I looked at the online docs, but could
not find any discussion about optimizing this kind of data schema and
query requirement.

--
__
/
| Paul Ramsey
| Refractions Research
| Email: pramsey@refractions.net
| Phone: (250) 885-0632
\_

Bryan Field-Elliot <bryan_lists@netmeme.org> writes:

I'm developing a GIS system on PostgreSQL. I need to store a table of
points (latitude and longitude), and do queries such as "return all
points enclosed with the given circle ((X,Y),R)".

You might care to look at

http://postgis.refractions.net/postgis-0.5.1.tar.gz

and see if you're not reinventing the wheel.

regards, tom lane

On Thu, 2 Aug 2001, Bryan Field-Elliot wrote:

I'm developing a GIS system on PostgreSQL. I need to store a table of
points (latitude and longitude), and do queries such as "return all
points enclosed with the given circle ((X,Y),R)".

The "point" data type looks convenient, however, it doesn't appear to be
indexable.

Can anyone tell me what the preferred, recommended strategy for this
design pattern is, with respect to PostgreSQL? One which makes good use
of indices, if possible (realizing that I personally don't know a good
way to index for a radius search).

For extra credit, it would be nice if I could store circles instead of
points, and issue queries like the following:

"given a circle ((X,Y),R), return all circles which share some common area"

I haven't yet played with it, but look at PostGIS, an add-on for
PostgreSQL which claims to have better spatial indexing & querying
capabilities for geometric datatypes.

As an aside, can anyone comment on the likelihood of this becoming a part
of mainstream pgsql? Or what is currently happening with pgsql spatial
support, especially regarding fmaps development?

Cheers,

Brent Wood

For extra credit, it would be nice if I could store circles instead of
points, and issue queries like the following:

"given a circle ((X,Y),R), return all circles which share some common area"

I haven't yet played with it, but look at PostGIS, an add-on for
PostgreSQL which claims to have better spatial indexing & querying
capabilities for geometric datatypes.

As an aside, can anyone comment on the likelihood of this becoming a part
of mainstream pgsql? Or what is currently happening with pgsql spatial
support, especially regarding fmaps development?

PostGIS is sitting in my mailbox and I am waiting to hear if it should
be added to contrib. It is 132k.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026